第17周 存储过程、自定义函数与触发器

第17周 · 存储过程、自定义函数与触发器

这周进入"数据库编程"：把重复的业务逻辑封装在数据库里。三类对象——存储过程（一段可调用程序）、自定义函数（算个值返回）、触发器（某操作发生时自动执行）。综合度最高，但用完整案例带练就不难。

---

一、知识点：为什么需要数据库编程

同样一段查询（如"按系查学生"）在很多地方都要用，每次重写麻烦。把它们封装成存储过程/函数，调用一行就行；触发器则让"插入成绩时自动记日志"这种事无需人工干预。

---

二、操作教程：存储过程 CREATE PROCEDURE

⚠️ 过程体里有分号，需先用 DELIMITER 临时把结束符改成 //，结束再改回 ;。

-- 改结束符，避免过程体内分号提前结束语句
DELIMITER //

CREATE PROCEDURE p_by_dept(IN d VARCHAR(30))
BEGIN
  SELECT * FROM student WHERE sdept = d;
END //

DELIMITER ;

-- 调用：查计算机系
CALL p_by_dept('计算机系');

要点： - IN d VARCHAR(30)：输入参数（调用时传入）。 - BEGIN ... END：过程体。 - CALL 名(参数)：调用存储过程。

参数模式： | 模式 | 含义 | | --- | --- | | IN | 调用时传入（默认） | | OUT | 过程返回给调用者 | | INOUT | 既可传入又可返回 |

---

三、操作教程：自定义函数 CREATE FUNCTION

函数必须返回一个值，可在 SQL 里像 UPPER() 那样直接使用。

DELIMITER //

CREATE FUNCTION f_level(g INT) RETURNS CHAR(1)
BEGIN
  RETURN CASE
           WHEN g >= 90 THEN 'A'
           WHEN g >= 80 THEN 'B'
           WHEN g >= 60 THEN 'C'
           ELSE 'D'
         END;
END //

DELIMITER ;

-- 调用（当函数用）
SELECT f_level(85);     -- 返回 'B'

RETURNS 声明返回类型，RETURN 返回值。函数体一般不能有 SELECT 出结果集（与存储过程区别之一）。

---

四、知识点：触发器 CREATE TRIGGER

触发器是绑定在表上、由 INSERT/UPDATE/DELETE 自动触发的一段程序，不需要手动调用。

-- 插入 sc 之前校验成绩是否合法（0~100）
DELIMITER //

CREATE TRIGGER trg_check_grade
BEFORE INSERT ON sc
FOR EACH ROW
BEGIN
  IF NEW.grade < 0 OR NEW.grade > 100 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '成绩必须在0~100之间';
  END IF;
END //

DELIMITER ;

触发器要点： - BEFORE / AFTER：操作之前还是之后触发。 - INSERT / UPDATE / DELETE：触发事件。 - NEW：新行的值（INSERT/UPDATE 用）；OLD：旧行的值（UPDATE/DELETE 用）。 - 场景：① 审计日志（插入成绩时自动写一条记录到 log 表）；② 数据校验（如上面成绩范围检查）。

---

五、知识点：三者区别与适用场景

对象	调用方式	返回值	典型用途
存储过程	CALL 主动调用	可通过 OUT 返回多个	封装业务步骤、批量处理
函数	嵌在 SQL 里	返回单个值	计算（如成绩等级）
触发器	自动触发	无	审计、校验、同步

触发器慎用：它"看不见地"自动执行，滥用会让数据变更难以追踪。明确需要时再用。

---

六、综合实操：作业三题

-- 题1：按系查学生的存储过程
DELIMITER //
CREATE PROCEDURE p_by_dept(IN d VARCHAR(30))
BEGIN
  SELECT * FROM student WHERE sdept = d;
END //
DELIMITER ;
CALL p_by_dept('计算机系');

-- 题2：成绩等级函数
DELIMITER //
CREATE FUNCTION f_level(g INT) RETURNS CHAR(1)
BEGIN
  RETURN CASE WHEN g>=90 THEN 'A' WHEN g>=80 THEN 'B' WHEN g>=60 THEN 'C' ELSE 'D' END;
END //
DELIMITER ;
SELECT f_level(85);

-- 题3：触发器概念（见上文；AFTER INSERT 适合审计日志，如插入成绩自动写 log）

---

七、易混点排查表

现象	解决方法
建存储过程报错"不明结束"	漏 DELIMITER；过程体含分号必须用 // 包裹
存储过程和函数分不清	过程用 CALL、可无返回值；函数嵌 SQL、必须返回值
触发器里 NEW/OLD 用错	INSERT 用 NEW，DELETE 用 OLD，UPDATE 两者都有
触发器莫名改了数据	触发器自动执行；排查时看表上绑了哪些触发器
函数里想 SELECT 出结果	函数一般不能返回结果集；要返回集用存储过程
参数模式混淆	IN 传入、OUT 传出、INOUT 双向

---

八、小测验（自测）

1. 创建存储过程为什么要用 DELIMITER 改结束符？调用存储过程用什么命令？
2. 存储过程和自定义函数的主要区别（调用方式、返回值）？
3. 触发器 BEFORE INSERT 和 AFTER INSERT 分别适合什么场景？NEW 指什么？
4. 写一个返回成绩等级（A/B/C/D）的函数 f_level(g INT) 的框架（CASE 结构）。

---

【声明】本文由 AI 辅助生成，仅供 MySQL 数据库教学参考，如有疑误以教材为准。