头歌数据库课程实验（存储过程、函数与触发器）

zzj_262610

1854人浏览 · 2025-05-18 21:28:45

zzj_262610 · 2025-05-18 21:28:45 发布

第1关：创建存储过程

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

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

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

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

存储过程的创建和查询
创建存储过程：create procedure 存储过程名(参数)

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

CREATE PROCEDURE proc1()
BEGIN
SELECT * FROM user;
END;
执行存储过程：call 存储过程名

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

IN：输入参数，也是默认模式，表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回；
OUT：输出参数，该值可在存储过程内部被改变，并可返回；
INOUT：输入输出参数，调用时指定，并且可被改变和返回。
存储过程的查询和删除
我们如何在数据库中查询我们已经创建过的存储过程呢：

SHOW PROCEDURE STATUS WHERE db='数据库名';
查看存储过程的详细定义信息：

SHOW CREATE PROCEDURE 数据库.存储过程名;
当我们不再需要某个存储过程时，我们可以使用：

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

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

开始你的任务吧，祝你成功！

 use teachingdb;
DELIMITER //
CREATE PROCEDURE pro_findname(IN keyword VARCHAR(20))
BEGIN
    SELECT *
    FROM student
    WHERE sname LIKE CONCAT('%', keyword, '%');
END //
DELIMITER ;

第2关：创建函数-count_credit

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

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

mysql 自定义函数的定义；
自定义函数如何创建。
自定义函数的定义
自定义函数是一种过程式数据库对象，与存储过程十分相似的。是由 SQL 语句和过程式语句组成的代码片段。
自定义函数的创建
语法如下：

CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … ) RETURNS <类型> <函数主体>
编程要求
根据提示，在右侧编辑器补充代码，设计函数 count_credit，根据学号计算该学生的总学分，只有当成绩大于等于60分时才能获得该门课程的学分。

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

开始你的任务吧，祝你成功！

 use teachingdb;

DELIMITER //

CREATE FUNCTION count_credit(sno_input CHAR(5))
    RETURNS INT
    DETERMINISTIC
BEGIN
    DECLARE total_credit INT DEFAULT 0;
    DECLARE cno VARCHAR(10);
    DECLARE credit INT;
    DECLARE done INT DEFAULT FALSE;

    -- 声明游标
    DECLARE cur CURSOR FOR
        SELECT c.credit
        FROM score s
                 JOIN course c ON s.cno = c.cno
        WHERE s.sno = sno_input AND s.grade >= 60;

    -- 定义游标操作完成时的处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;

    read_loop: LOOP
        -- 取出数据
        FETCH cur INTO credit;
        -- 如果游标操作完成，退出循环
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 累加学分
        SET total_credit = total_credit + credit;
    END LOOP;

    -- 关闭游标
    CLOSE cur;

    -- 返回总学分
    RETURN total_credit;
END //

DELIMITER ;

第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 flag  int  default 1;
  declare psno char(5);
  declare stucur cursor for select sno from student;
  declare continue handler for not found set flag=0;
  open stucur;
  while flag=1 do 
  fetch stucur into psno;
  update student set totalcredit=count_credit(psno) where sno=psno;
  end while;
  close stucur;
  end $
  delimiter ;
 /**********End**********/

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

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

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

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

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

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

触发器的创建
下面我们来创建一个触发器
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name FOR EACH ROW
trigger_body
trigger_time：触发器触发时机，有before和after
trigger_event：触发器触发事件，有insert,update,delete三种
trigger_body：触发器主体语句
after触发器
delete触发器
建一个触发器 t_d_s，当删除表 student 中某个学生的信息时，同时将 grade 表中与该学生有关的数据全部删除。

CREATE TRIGGER trigger_t1
AFTER DELETE ON student
FOR EACH ROW
BEGIN
DELETE FROM grade WHERE studentid = old.studentid;
END
UPDATE 创建一触发器 t_u_s，实现在更新学生表的学号时，同时更新 grade 表中的相关记录的 student 的 id 值。
CREATE TRIGGER t_u_s
AFTER UPDATE ON student
for EACH ROW
BEGIN
UPDATE grade SET studentid = new.studentid WHERE studentid = old.studentid;
END
INSERT 创建一个存储过程，根据 student 表中数据，一次性更新 class 表中每个班的人数
CREATE PROCEDURE p_tao()
BEGIN
DECLARE num int;
DECLARE cid VARCHAR(20);
DECLARE done boolean DEFAULT true;
DECLARE cur CURSOR FOR
SELECT classid,COUNT(*)
FROM student
GROUP BY classid;
DECLARE CONTINUE HANDLER FOR NOT found SET done = false;

UPDATE class set studentnum = 0;
OPEN cur;
FETCH cur INTO cid,num;
WHILE done DO
UPDATE class SET StudentNum = num WHERE classid = cid;
FETCH cur INTO cid,num;
END WHILE;
CLOSE cur;
END
CALL p_tao();
编程要求
根据提示，在右侧编辑器补充代码，创建触发器 sum_credit，实现对 student 表总学分的计算，当 score 中添加记录时，student 表总学分的值做相应改变。当课程成绩大于等于60分时，将该课程的学分加到该学生的总学分中。

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

开始你的任务吧，祝你成功！

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  delimiter $
  create trigger sum_credit before insert on score for each row
  Begin
  update student set totalcredit= totalcredit+
  (select credit from course where cno=new.cno)
  where student.sno=new.sno and new.grade>=60;

  End
  $
  delimiter ;

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