MySQL存储过程与触发器：参数传递、级联删除与数据校验实战

存储过程的定义与参数传递

存储过程是预编译的 SQL 集合，通过参数传递实现灵活的数据处理。根据业务需求，可选择不带参数、仅输入参数或同时包含输入输出的模式。

第1步：创建并调用无参存储过程

目的是查询所有课程的平均成绩，无需外部变量介入。

1. 定义过程：使用 DELIMITER // 修改结束符，避免与代码内部的分号冲突。

delimiter//
CREATE PROCEDURE sp_avggrade()
COMMENT '查询每门课程学生的平均成绩的功能'
BEGIN
SELECT cno as 课程号, avg(grade) as 平均成绩
FROM sc
GROUP BY cno;
end//
delimiter;

2. 调用执行：直接使用 CALL 语句触发。

call sp_avggrade();

第2步：创建带输入参数 (IN) 的存储过程

需要针对特定课程查询平均分时，通过 IN 关键字声明输入参数，将外部值传入过程内部。

1. 定义过程：参数 c_no 被定义为 CHAR(2) 类型。

delimiter//
CREATE PROCEDURE sp_course_avggrade(IN c_no CHAR(2))
COMMENT '通过输入课程编号参数查询指定课程编号的课程平均成绩的功能'
BEGIN
SELECT cno, AVG(grade)
FROM sc
WHERE cno = c_no;
END//
delimiter;

调用时传入具体的课程编号即可获取结果，例如：CALL sp_course_avggrade('1');

第3步：创建带输入输出参数 (IN/OUT) 的存储过程

当需要将计算结果返回给外部变量供后续逻辑使用时，需使用 OUT 参数。此例用于统计某院系的学生人数。

1. 定义过程： _sdept 为输入院系名称，num 为输出计数结果。

delimiter//
CREATE PROCEDURE sp_sdept_student2(IN _sdept CHAR(10), OUT num int)
BEGIN
SELECT COUNT(sno) INTO num
FROM student
WHERE _sdept = sdept;
END//
delimiter;

2. 调用并获取返回值：首先必须初始化用户变量，然后调用过程，最后从变量中读取结果。

SET @num = 10; -- 初始化变量
CALL sp_sdept_student2('计科', @num);
SELECT @num AS '人数';

触发器的创建与数据校验

触发器是在特定事件（如删除、插入）发生时自动执行的存储程序，常用于维护表间一致性或实施复杂的数据完整性约束。

第4步：BEFORE DELETE 触发器实现级联清理

目的：当学生表 student 中的数据被删除时，自动清除选课表 sc 中该学生相关的历史记录，防止产生孤儿数据。

1. 创建触发器：在 DELETE 操作前执行，通过 OLD.sno 引用即将被删除的记录主键。

delimiter//
CREATE TRIGGER delete_stu
BEFORE DELETE
ON student
FOR EACH ROW
BEGIN
DELETE FROM sc
WHERE sc.sno = old.sno;
END//
delimiter;

验证方法：执行学生删除语句后，检查 sc 表中对应学号的记录是否已消失。

DELETE FROM student WHERE sno = '201513';

第5步：BEFORE INSERT 触发器实施数值校验

目的：防止录入不合法的成绩数据（如大于100或小于0）。相比外键约束，这种方法能提供更友好的错误提示。

1. 创建触发器：利用 SIGNAL SQLSTATE 语法抛出异常，中断当前插入操作。

delimiter//
CREATE TRIGGER scgrade
BEFORE INSERT ON sc
FOR EACH ROW
BEGIN
IF new.grade > 100 OR new.grade < 0 THEN
SIGNAL SQLSTATE '45000'
SET message_text = '录入成绩不符合规定，拒绝插入';
END IF;
END//
delimiter;

验证方法：尝试插入一个无效成绩，数据库应返回预设的错误信息而非静默失败或插入错误数据。

INSERT INTO sc(sno, cno, grade) VALUES ('2002720', '1', 200);

以上就是 MySQL 数据库实验中触发器和存储过程的详细内容，更多关于 MySQL 存储过程触发器应用的文章请关注本站其它相关文章！