头歌实训之存储过程、函数与触发器

🌟 各位看官好，我是maomi_9526！

🌍 种一棵树最好是十年前，其次是现在！

🚀 今天来学习C语言的相关知识。

👍 如果觉得这篇文章有帮助，欢迎您一键三连，分享给更多人哦

目录

第1关：创建存储过程

第2关：创建函数-count_credit

第3关：存储过程中使用游标

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

第5关：创建触发器-级联删除

总结：头歌第二关及第三关

问题分析：

---

第1关：创建存储过程

任务描述
本关任务：创建存储过程,实现对学生姓名进行模糊查询。

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

存储过程的定义
 存储过程(Stored Procedure)是数据库中一组预编译的SQL语句集合，存储在数据库中，可以被应用程序调用。存储过程可以包含复杂的逻辑、变量、条件判断和循环等，能够实现一些复杂的业务逻辑，提高代码的复用性和执行效率。

存储过程的创建
创建存储过程格式：

DELIMITER //
CREATE PROCEDURE 过程名([参数列表])
BEGIN
    -- SQL语句
END //
DELIMITER ;

• DELIMITER：MySQL 默认的语句分隔符是分号（;），但在存储过程中，分号可能会被用作语句的分隔符，因此需要使用 DELIMITER 关键字来改变分隔符，通常将其改为 // 或其他符号，以免与存储过程内部的分号冲突。
• 存储过程名称：存储过程的名称，用于后续调用存储过程时标识该存储过程。
• 参数列表：存储过程的参数，可以有输入参数（IN）、输出参数（OUT）和输入输出参数（INOUT）。参数的格式为 参数名 参数类型，例如 IN id INT 表示一个输入参数 id，类型为整数。
• BEGIN...END：存储过程的主体部分，包含需要执行的 SQL 语句。

执行存储过程

• 调用存储过程的语法如下：

CALL 存储过程名称(参数值);

• 存储过程名称：要调用的存储过程的名称。
• 参数值：调用存储过程时传递的参数值（实参），参数值的顺序和类型应与存储过程定义时的参数列表一致。

编程要求

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

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

---

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

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

第2关：创建函数-count_credit

任务描述
本关任务：创建和使用存储函数。

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

• 创建存储函数；
• 调用存储函数。

创建存储函数

• 存储函数（Stored Function）是一种在数据库中存储的可调用的函数，类似于存储过程，但存储函数的主要目的是返回一个值，而存储过程主要用于执行一系列操作。存储函数可以被 SQL 语句直接调用，也可以在其他存储过程或存储函数中调用。
• 创建存储函数格式

DELIMITER //
CREATE FUNCTION 函数名称 (参数列表)
RETURNS 返回值类型  READS SQL DATA
BEGIN
    -- 函数的 SQL 语句
    RETURN 返回值;
END//
DELIMITER ;

调用函数

• SELECT 函数名称(参数值);
• 在表达式中使用函数名称(参数值)。

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

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

---

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

 use teachingdb;/****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/delimiter ##create Function count_credit(v_sno char(6))returns int reads sql dataBegindeclare sums int ;select sum(credit) into sums from course natural join score where grade >=60 and v_sno=sno;return sums;end ##delimiter ;/**********End**********/

第3关：存储过程中使用游标

任务描述
本关任务：创建存储过程 ，在存储过程中定义游标计算总学分。

相关知识
为了完成本关任务，你需要掌握：
1.游标的定义；
2.游标的使用。

游标的定义
游标（Cursor）主要用于存储过程和函数中，用于逐行处理查询结果集。

• 在存储过程或函数中，使用 DECLARE CURSOR 语句来创建游标。

DECLARE cursor_name CURSOR FOR select_statement;

游标的使用

• 打开游标

OPEN cursor_name;

• 获取数据

FETCH cursor_name INTO var_name1, var_name2, ...;

• 关闭游标

CLOSE cursor_name;

举例：通过游标获取users表中的id,name，并输出结果。

DELIMITER //
CREATE PROCEDURE process_users()
BEGIN
   -- 声明变量
   DECLARE finished INTEGER DEFAULT 0;
   DECLARE user_id INT DEFAULT 0;
   DECLARE user_name VARCHAR(50) DEFAULT '';
   -- 声明游标
   DECLARE my_cursor CURSOR FOR SELECT id, name FROM users;
   -- 声明结束处理
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
   -- 打开游标
   OPEN my_cursor;
   -- 获取数据
   fetch_loop: LOOP
       FETCH my_cursor INTO user_id, user_name;
       IF finished = 1 THEN
           LEAVE fetch_loop;
       END IF;
       -- 处理每一行数据
       SELECT user_id, user_name;
   END LOOP;
   -- 关闭游标
   CLOSE my_cursor;
END //
DELIMITER ;

编程要求
根据提示，在右侧编辑器补充代码，创建存储过程 p_count_credit，在存储过程中创建游标stucur保存所有学生的学号，使用loop循环从游标中提取每个学生的学号，根据当前学号调用count_credit 函数计算学生的总学分，并更新学生表中的总学分值。

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

---

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

 use teachingdb;/****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/drop procedure if exists p_count_credit;delimiter ##create procedure  p_count_credit()begindeclare v_sno varchar(20);declare v_credit int default 0;declare stucur cursor for select sno from student;declare exit handler for not found close stucur ;open stucur;while true dofetch stucur into v_sno;update student set totalcredit =count_credit(v_sno) where sno=v_sno;end while;close stucur;select *from student;end ##delimiter ;/**********End**********/

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

任务描述
本关任务：创建触发器 sum_credit，实现对 student 表总学分的计算。

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

触发器的定义
触发器（Trigger）是一种特殊的存储过程，它在表上的数据发生变化（如INSERT、UPDATE 和 DELETE操作）时自动执行。触发器的主要作用是维护数据的完整性和一致性，或者在数据变化时执行一些自动化的操作。

• 触发时机：触发器可以设置在数据操作之前（BEFORE）或之后（AFTER）执行。
• 触发事件：可以是INSERT、UPDATE或DELETE操作。

创建触发器的语法：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- 触发器要执行的SQL语句
END;

• BEFORE 或 AFTER 指定触发器应在事件之前还是之后触发。
• INSERT, UPDATE, 或 DELETE 指定触发器应该响应的事件类型。

触发器表

• 在 MySQL 触发器中，NEW 和 OLD 是两个特殊的虚拟表（实际上它们是行数据的引用），用于访问触发事件中涉及的数据。这两个虚拟表的具体含义和用途取决于触发器所关联的操作类型（INSERT、UPDATE 或 DELETE）。
• NEW 表
  • NEW 表表示即将插入到表中的新行数据，或者在更新操作中表示更新后的新值。
  • 可用场景： 在 BEFORE INSERT 和 AFTER INSERT 中，NEW 表示将要插入的新行。 在 BEFORE UPDATE 和 AFTER UPDATE 中，NEW 表示更新后的字段值。
  • 用法：可以通过 NEW.column_name 来访问或修改某一列的值。

示例
（1）创建一触发器 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

• OLD 表
• OLD 表表示被删除或更新的旧行数据。
• 可用场景： 在 BEFORE DELETE 和 AFTER DELETE 中，OLD 表示将要被删除的行。 在 BEFORE UPDATE 和 AFTER UPDATE 中，OLD 表示更新前的字段值。
• 用法：可以通过 OLD.column_name 来访问某一列的值，但不能对其进行修改（因为它是只读的）。

示例
（2）建一个触发器 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

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

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

---

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

 use teachingdb;/****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/delimiter ##create trigger sum_creditafter insert on score for each rowBeginif(new.grade>=60) thenupdate student set totalcredit =totalcredit+(select credit from course where cno=new.cno)where student.sno=new.sno;end if;end ##delimiter ;/**********End**********/

第5关：创建触发器-级联删除

任务描述
本关任务：创建级联删除触发器 。

相关知识
为了完成本关任务，你需要掌握：
1.级联删除的定义；
2.级联删除的工作原理。

级联删除的定义

• 级联删除（Cascade Delete）是数据库管理系统中的一种功能，用于定义外键约束时指定的删除规则。当一个表中的记录被删除时，所有关联的记录也会根据级联删除规则自动删除。这确保了数据的一致性，避免出现孤立记录（即那些指向已删除记录的外键值）。

级联删除的工作原理

• 级联删除的工作原理
  • 假设你有两个表：Parent 和 Child。Child 表通过外键关联到 Parent 表。当你在 Parent 表上设置级联删除时，如果从 Parent 表中删除一条记录，那么所有在 Child 表中与这条记录相关联的记录也将被自动删除。

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

测试说明
平台会对你编写的代码进行测试。

---

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

 use teachingdb;/****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/delimiter ##create trigger del_studnet_scorebefore delete on student for each rowbegindelete from score where sno=old.sno;end ##delimiter ;/**********End**********/

总结：头歌第二关及第三关

常见答题系统工作原理： 

问题分析：

在头歌平台的测试过程中，由于他们只进行公开测试用例的验证，部分潜在的逻辑错误可能没有被及时发现。这种情况尤其会影响到 MySQL 相关的习题，因为这些习题通常会涉及到数据库操作的细节，如果没有合适的隐藏测试用例，某些问题可能不会暴露。

例如实验五第二关：

自己代码：

官方代码：

use teachingdb;  /****请在此编写代码，操作完毕之后点击评测******/    /**********Begin**********/   delimiter ##   create Function count_credit(v_sno char(6))   returns float reads sql data   Begin   declare sums float;   select sum(credit) into sums from course natural join score where grade >=60 and v_sno=sno;     return sums;   end ##   delimiter ;     /**********End**********/

CREATE DEFINER=`root`@`localhost` FUNCTION `count_credit`(stuno CHAR(6))  RETURNS int(11)     READS SQL DATA BEGIN     DECLARE stucno CHAR(3);       DECLARE cred INT DEFAULT 0;     DECLARE t_cred INT DEFAULT 0;     DECLARE done INT DEFAULT FALSE;          -- 定义游标，从score表中获取学生所选课程编号     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

 勘误：因为官方只进行公开实例进行测试，这里都可以进行通过

 

Mysql习题一般都是继续使用官方的代码进行测试，如果你在第三关依旧写出float的代码 */

 use teachingdb;/****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/
select sum(credit) from score natural join course  where sno='96002';drop procedure if exists p_count_credit;delimiter ##create procedure  p_count_credit()begindeclare v_sno varchar(20);declare v_credit float default 0;declare stucur cursor for select sno from student;declare exit handler for not found close stucur ;open stucur;while true dofetch stucur into v_sno;select sum(credit) into v_credit from score natural join course where grade>=60 and sno=v_sno;update student set totalcredit =ifnull(v_credit,0) where sno=v_sno;end while;close stucur;select *from student;end ##delimiter ;/**********End**********/

由于头歌平台的测试机制仅依赖于公开的测试用例，这导致了部分潜在的逻辑问题没有被及时发现。尽管我们无法修改平台的测试方式或其隐藏的测试用例，但我们可以选择按照平台提供的标准函数进行测试，以确保代码在实际评测时能够正确运行。

本次纠错并没有涉及复杂的新知识点，而是希望同学们能够理解，为什么在这种情况下，某些潜在的错误未被立即发现，当大家出现这种情况也可以通过下面这种方法来通过测试：