MYSQL实训七——存储过程、函数与触发器

Posted on by hackdl

第1关：创建存储过程

任务描述

本关任务：创建存储过程 pro_findname 对学生姓名进行模糊查找,输入任一字输出姓名中含有该字的全部学生。

相关知识

为了完成本关任务，你需要掌握： 1.存储过程的定义； 2.存储过程的创建和查询； 3.存储过程的查询和删除。

存储过程的定义

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

简单的说存储过程就是具有名字的一段代码，用来完成一个特定的功能。

存储过程的创建和查询

创建存储过程：create procedure 存储过程名(参数)

  • 下面我们来创建第一个存储过程

每个存储的程序都包含一个由 SQL 语句组成的主体。此语句可能是由以分号（;）字符分隔的多个语句组成的复合语句。例如：



    

  1. CREATE PROCEDURE proc1()
    2. 

  2. BEGIN
    3. 

  3. SELECT * FROM user;
    4. 

  4. END;
    5. 



    

  • 

    执行存储过程：call 存储过程名
    

    • 

  • 

    创建带有参数的存储过程 存储过程的参数有三种：
    

      

    • 
IN：输入参数，也是默认模式，表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回；
      • 

    • 
OUT：输出参数，该值可在存储过程内部被改变，并可返回；
      • 

    • 
INOUT：输入输出参数，调用时指定，并且可被改变和返回。
      • 

    

    • 



存储过程的查询和删除


我们如何在数据库中查询我们已经创建过的存储过程呢：




    

  1. SHOW PROCEDURE STATUS WHERE db='数据库名';
    2. 



查看存储过程的详细定义信息：




    

  1. SHOW CREATE PROCEDURE 数据库.存储过程名;
    2. 



当我们不再需要某个存储过程时，我们可以使用：




    

  1. DROP PROCEDURE [IF EXISTS] 数据库名.存储过程名;
    2. 



编程要求


根据提示，在右侧编辑器补充代码，创建存储过程pro_findname 对学生姓名进行模糊查找,输入任一字输出姓名中含有该字的全部学生。


测试说明


平台会对你编写的代码进行测试，将调用你编写的存储过程:call pro_findname(‘明’)，具体输出请参考右侧测试集。


 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
 delimiter $$
    create procedure pro_findname(in name char(3))
  Begin
  select * from student where sname like concat('%',name,'%');

  End $$
  delimiter ;
 /**********End**********/





第2关：创建函数-count_credit


任务描述


本关任务：设计函数 count_credit，根据学号计算该学生的总学分，只有当成绩大于等于60分时才能获得该门课程的学分。


相关知识


为了完成本关任务，你需要掌握：


    

  1. mysql 自定义函数的定义；
    2. 

  2. 自定义函数如何创建。
    3. 



自定义函数的定义


    

  • 自定义函数是一种过程式数据库对象，与存储过程十分相似的。是由 SQL 语句和过程式语句组成的代码片段。
    • 



自定义函数的创建


语法如下：






CREATE FUNCTION  ( [   [ ,  ] ] … ) RETURNS  






编程要求


根据提示，在右侧编辑器补充代码，设计函数 count_credit，根据学号计算该学生的总学分，只有当成绩大于等于60分时才能获得该门课程的学分。


测试说明


平台会对你编写的代码进行测试：本题中该学生选“马蓉”，学号为“97001”)


 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
 delimiter $$

  CREATE FUNCTION count_credit(stuno char(6)) returns int

  reads sql data

  Begin

  declare stucno char(3) ;

  declare cred int default 0;

  declare t_cred int default 0;

  declare done int default false;

  declare stucur cursor for select cno from score where sno=stuno and grade>=60;

  declare continue handler for not found set done=true;

  open stucur;

loop_cursor:loop

fetch stucur into stucno;

if done then leave loop_cursor; End if;

select credit into cred from course where cno=stucno;

set t_cred=t_cred+cred;

end loop;

return t_cred;

end$$

delimiter ;

 /**********End**********/





第3关：存储过程-调用函数count_credit


任务描述


本关任务：创建存储过程 p_count_credit，调用 count_credit 函数更新学生表的总学分值。


相关知识


为了完成本关任务，你需要掌握： 1.存储过程的定义； 2.存储过程的创建和查询； 3.存储过程的查询和删除。


存储过程的定义


存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。


存储过程是为了完成特定功能的 SQL 语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行。


存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。


简单的说存储过程就是具有名字的一段代码，用来完成一个特定的功能。


编程要求


根据提示，在右侧编辑器补充代码，创建存储过程 p_count_credit，调用count_credit 函数更新学生表的总学分值。


测试说明


平台会对你编写的代码进行测试，将调用你编写的存储过程p_count_credit，函数的count_credit的功能和第2关中的相同，具体输出请参考右侧测试集。


 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  DELIMITER $$

  CREATE PROCEDURE p_count_credit()

  BEGIN

  DECLARE stuno CHAR(6);

  DECLARE done int default false;

  DECLARE stucur CURSOR FOR SELECT sno FROM student;

  DECLARE CONTINUE HANDLER FOR not found set done=true;

  open stucur;

  loop_cursor:loop

  fetch stucur into stuno;

  if done then leave loop_cursor; end if;

  update student set totalcredit=count_credit(stuno) where sno=stuno;

  end loop;

  end$$

  delimiter ;
  
 /**********End**********/





第4关：创建触发器-计算总学分


任务描述


本关任务：创建触发器 sum_credit，实现对 student 表总学分的计算，当 score 中添加记录时，student 表总学分的值做相应改变。当课程成绩大于等于60分时，将该课程的学分加到该学生的总学分中。


相关知识


为了完成本关任务，你需要掌握： 1.触发器的定义； 2.触发器的创建； 3.after 触发器。


触发器的定义


触发器（Trigger）是存储在系统内部的一段程序代码。


触发器和存储过程类似，可以把它看作是一个特殊的存储过程。两者的区别是，触发器无需人工调用，当程序满足定义条件时就会被 MySQL 自动调用。这些条件可以称为触发事件，包括 INSERT、UPDATE 和 DELETE 操作。


核心就是数据库 SQL 语言层面的代码封装与重用。


触发器的创建


    

  • 下面我们来创建一个触发器
    • 





    

  1. CREATE TRIGGER trigger_name trigger_time trigger_event
    2. 

  2. 

    3. 

  3. ON table_name FOR EACH ROW 
    4. 

  4. 

    5. 

  5. trigger_body
    6. 

  6. 

    7. 

  7. trigger_time：触发器触发时机，有before和after
    8. 

  8. 

    9. 

  9. trigger_event：触发器触发事件，有insert,update,delete三种
    10. 

  10. 

    11. 

  11. trigger_body：触发器主体语句
    12. 



after触发器


    

  • delete触发器
    • 



建一个触发器 t_d_s，当删除表 student 中某个学生的信息时，同时将 grade 表中与该学生有关的数据全部删除。




    

  1. CREATE TRIGGER trigger_t1
    2. 

  2. AFTER DELETE ON student
    3. 

  3. FOR EACH ROW
    4. 

  4. BEGIN 
    5. 

  5. DELETE FROM grade WHERE studentid = old.studentid;
    6. 

  6. END
    7. 



    

  • UPDATE 创建一触发器 t_u_s，实现在更新学生表的学号时，同时更新 grade 表中的相关记录的 student 的 id 值。
    • 





    

  1. CREATE TRIGGER t_u_s
    2. 

  2. AFTER UPDATE ON student
    3. 

  3. for EACH ROW
    4. 

  4. BEGIN
    5. 

  5. UPDATE grade SET studentid = new.studentid WHERE studentid = old.studentid;
    6. 

  6. END
    7. 



    

  • INSERT 创建一个存储过程，根据 student 表中数据，一次性更新 class 表中每个班的人数
    • 





    

  1. 

    2. 

  2. CREATE PROCEDURE p_tao()
    3. 

  3. BEGIN
    4. 

  4. DECLARE num int;
    5. 

  5. DECLARE cid VARCHAR(20);
    6. 

  6. DECLARE done boolean DEFAULT true;
    7. 

  7. DECLARE cur CURSOR FOR
    8. 

  8. SELECT classid,COUNT(*)
    9. 

  9. FROM student
    10. 

  10. GROUP BY classid; 
    11. 

  11. DECLARE CONTINUE HANDLER FOR NOT found SET done = false;
    12. 

  12. 

    13. 

  13. UPDATE class set studentnum = 0;
    14. 

  14. OPEN cur;
    15. 

  15. FETCH cur INTO cid,num;
    16. 

  16. WHILE done DO 
    17. 

  17. UPDATE class SET StudentNum = num WHERE classid = cid; 
    18. 

  18. FETCH cur INTO cid,num; 
    19. 

  19. END WHILE;
    20. 

  20. CLOSE cur;
    21. 

  21. END
    22. 

  22. 

    23. 

  23. CALL p_tao();
    24. 

  24. 

    25. 



编程要求


根据提示，在右侧编辑器补充代码，创建触发器 sum_credit，实现对 student 表总学分的计算，当 score 中添加记录时，student 表总学分的值做相应改变。当课程成绩大于等于60分时，将该课程的学分加到该学生的总学分中。


测试说明


平台会对你编写的代码进行测试：平台会用“马小燕”为测试用例进行测试。


 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  delimiter $$
create trigger sum_credit after insert on score

  for each row

  Begin

  update student set totalcredit=totalcredit+

  (select credit from course where cno=new.cno)

  where sno=new.sno and new.grade>=60;

  end $$
  delimiter ;

  
 /**********End**********/







第5关：创建触发器-练习级联删除操作


任务描述


本关任务：创建级联删除触发器 del_student_score，当删除 student 表中的学生时，也删除 score 表中的对应学号的学生成绩记录。


相关知识


见第4关。


编程要求


根据提示，在右侧编辑器补充代码，创建级联删除触发器 del_student_score，当删除 student 表中的学生时，也删除 score 表中的对应学号的学生成绩记录。


 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
 delimiter $$

  drop trigger if exists del_student_score$$

  create trigger del_student_score before delete

  on student

  for each row

  Begin

  delete from score where sno=old.sno;

  end$$

  delimiter ;

  
 /**********End**********/




			



		


		

			
服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

			
服务器托管

			
咨询：董先生

			
电话13051898268 QQ/微信93663045！

			

				上一篇: 

				下一篇: