Mysql之存储过程（下）
🏝️专栏：Mysql_猫咪-9527的博客-ZEEKLOG博客
🌅主页：猫咪-9527-ZEEKLOG博客
“欲穷千里目，更上一层楼。会当凌绝顶，一览众山小。”
目录
4. 存储过程
存储过程是由一组 SQL 语句构成的数据库对象，存储过程可以被存储在数据库中并在需要时执行。它支持控制流结构（如 IF 语句、CASE 语句、循环等），能够封装和重用数据库操作，提升代码的模块化和可维护性。
4.1 IF 判断
IF 语句是 MySQL 存储过程中的一种常见控制流结构，用于根据特定条件执行不同的 SQL 语句。它可以单独使用，也可以与 ELSE 和 ELSEIF 配合使用，从而形成多分支的条件判断结构。
语法：
IF condition THEN -- 执行的 SQL 语句 ELSEIF condition THEN -- 执行的 SQL 语句 ELSE -- 执行的 SQL 语句 END IF; 示例：
DELIMITER $$ CREATE PROCEDURE CheckScore() BEGIN DECLARE score INT DEFAULT 58; DECLARE result VARCHAR(10); IF score >= 80 THEN SET result := '优秀'; ELSEIF score >= 60 THEN SET result := '及格'; ELSE SET result := '不及格'; END IF; SELECT result; END$$ DELIMITER ; 
4.2 存储过程参数
存储过程可以接受三种类型的参数：IN、OUT 和 INOUT。每种类型的参数有不同的作用，分别用于数据的输入、输出或同时输入输出。
语法：
CREATE PROCEDURE 存储过程名称 (IN/OUT/INOUT 参数名 参数类型) BEGIN -- SQL 语句 END; 4.2.1 IN 参数使用
IN 参数用于向存储过程传递输入值，存储过程内部可以使用这些值进行计算或查询操作。
示例：
DELIMITER $$ CREATE PROCEDURE CheckScoreIN(IN score INT) BEGIN DECLARE result VARCHAR(10); IF score >= 80 THEN SET result := '优秀'; ELSEIF score >= 60 THEN SET result := '及格'; ELSE SET result := '不及格'; END IF; SELECT result; END$$ DELIMITER ; 
4.2.2 OUT 参数使用
OUT 参数用于存储过程返回值。执行存储过程时，返回结果将通过 OUT 参数传递给调用者。
示例：
DELIMITER $$ CREATE PROCEDURE CheckScoreOUT(IN score INT, OUT result VARCHAR(20)) BEGIN IF score >= 80 THEN SET result := '优秀'; ELSEIF score >= 60 THEN SET result := '及格'; ELSE SET result := '不及格'; END IF; END$$ DELIMITER ; 
4.2.3 INOUT 参数使用
INOUT 参数既可以作为输入值，也可以用作存储过程返回的输出值。
示例：
DELIMITER $$ CREATE PROCEDURE CheckScoreINOUT(INOUT score DOUBLE) BEGIN SET score := score * 0.5; END$$ DELIMITER ; 
4.3 CASE 语句
CASE 语句是一种条件表达式，允许根据不同的条件选择不同的结果。它通常用于 SELECT 查询、UPDATE 语句或存储过程中的条件判断。
语法：
CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 WHEN value3 THEN result3 ELSE default_result END 示例：
DELIMITER $$ CREATE PROCEDURE CheckGrade(IN score INT, OUT grade VARCHAR(3)) BEGIN CASE WHEN score BETWEEN 90 AND 100 THEN SET grade := 'A'; WHEN score BETWEEN 80 AND 89 THEN SET grade := 'B'; WHEN score BETWEEN 60 AND 79 THEN SET grade := 'C'; WHEN score BETWEEN 0 AND 59 THEN SET grade := 'D'; ELSE SET grade := 'NO'; END CASE; END$$ DELIMITER ; 4.4 WHILE 循环
WHILE 循环是 MySQL 中的一种常见控制结构，用于在指定条件为 TRUE 时重复执行一组 SQL 语句，直到条件不再满足为止。
语法：
WHILE condition DO -- 执行的 SQL 语句 END WHILE; 示例：
DELIMITER $$ CREATE PROCEDURE SumNumbers(IN n INT) BEGIN DECLARE a INT DEFAULT 0; WHILE n >= 0 DO SET a := a + n; SET n := n - 1; END WHILE; SELECT a; END$$ DELIMITER ; 4.5 REPEAT 循环
REPEAT 循环与 WHILE 循环类似，不同之处在于它在每次执行代码块后才检查条件，直到满足退出条件为止。
语法：
REPEAT -- 执行的 SQL 语句 UNTIL condition END REPEAT; 示例：
DELIMITER $$ CREATE PROCEDURE SumNumbersUsingRepeat(IN n INT) BEGIN DECLARE a INT DEFAULT 0; REPEAT SET a := a + n; SET n := n - 1; UNTIL n <= 0 END REPEAT; SELECT a; END$$ DELIMITER ; 4.6 LOOP 循环
LOOP 循环是另一种控制结构，适用于需要反复执行某些操作，直到满足特定退出条件时停止。与 REPEAT 和 WHILE 循环不同，LOOP 必须显式使用 LEAVE 语句来退出循环，否则它会无限执行。
语法：
loop_label : LOOP -- 执行的 SQL 语句 IF condition THEN LEAVE loop_label; -- 退出循环 END IF; END LOOP; 示例：
DELIMITER $$ CREATE PROCEDURE SumOddNumbers(IN n INT) BEGIN DECLARE a INT DEFAULT 0; sum_loop: LOOP IF n % 2 = 0 THEN SET n := n - 1; ITERATE sum_loop; END IF; SET a := a + n; SET n := n - 1; IF n = 0 THEN LEAVE sum_loop; END IF; END LOOP; SELECT a; END$$ DELIMITER ; 5.游标
在 MySQL 中，游标（Cursor）是一种用于遍历查询结果集的机制。游标允许你逐行处理查询结果，对于一些复杂的操作，比如逐行处理数据、进行复杂的计算等，游标非常有用。通常，游标用于存储过程中，在需要逐行处理结果集时使用。
5.1 声明游标
使用 DECLARE 语句声明游标。在声明游标时，需要指定游标基于的查询。
语法：
DECLARE 游标名 CURSOR FOR 查询语句; 5.2 打开游标
通过 OPEN 语句打开游标并执行查询。
语法：
OPEN 游标名; 5.3 取出数据
使用 FETCH 语句从游标中获取一行数据。
语法：
FETCH NEXT FROM 游标名 INTO 变量列表; FETCH 会将游标当前指向的行的数据加载到指定的变量中。
5.4Handler (异常处理器)
在 MySQL 中，Handler（异常处理器） 是一种用于处理存储过程中的异常和错误的机制。Handler 可以帮助在遇到特定条件（如查询结果为空、错误发生等）时，自动执行指定的操作，从而提高程序的健壮性和灵活性。
MySQL 提供了多种 Handler 类型，主要通过 DECLARE 语句声明。在存储过程中，Handler 用于捕获特定条件（如查询没有返回结果、触发错误等），并执行相应的操作。
Handler 机制的目的是：当遇到异常或特定条件时，不需要让存储过程中断，而是可以继续执行后续的逻辑。
Handler 的基本语法:
DECLARE handler_type HANDLER FOR condition_value action; handler_type：指定处理器的类型，常见的类型有：CONTINUE：遇到异常或条件时继续执行后续语句。EXIT：遇到异常或条件时退出存储过程或循环。
condition_value：指定条件，可以是：NOT FOUND：表示没有找到数据（通常与SELECT查询结果为空时结合使用）02。SQLWARNING：表示 SQL 警告。01SQLEXCEPTION：表示 SQL 异常（如查询错误、插入失败等），除01和02
action：在满足指定条件时要执行的操作，例如设置变量、打印错误信息、退出等。
declare exit handler for sqlstate '02000';------退出码退出 declare exit handler for not found ;------------未找到退出01 declare exit handler for sqlwarning;------------sql警告退出025.4 关闭游标
当数据处理完毕后，使用 CLOSE 语句关闭游标。
语法：
CLOSE 游标名; 5.5 游标的完整示例
完整的示例：
delimiter ## create procedure s10(in p_sdept varchar(10)) begin declare c_snme varchar(20); declare c_sno varchar(5); declare c_birthday date; declare c_name cursor for select sno,snme,birthday from student where p_sdept=sdept; declare exit handler for not found close c_name; create table if not exists r1 ( id varchar(5), name varchar(20), birthday date ); open c_name; while true do fetch c_name into c_sno,c_snme,c_birthday; insert into r1 values( c_sno,c_snme,c_birthday); end while; close c_name; end ## delimiter ; 
6. 触发器
- 定义：预先绑定在表上的一段 SQL 代码，当满足指定事件（
INSERT/UPDATE/DELETE）并到达指定时机（BEFORE/AFTER）时，由 MySQL 自动执行。 - 作用：数据校验、自动补充派生字段、写审计日志、同步冗余表等。
- 执行粒度：MySQL 只支持 行级触发器（
FOR EACH ROW），没有语句级触发器。对表里每一行受影响的记录都会执行一次触发器体。
6.1 创建触发器
创建触发器时，首先使用 DELIMITER 临时改变语句分隔符，以便在触发器体内使用分号。然后，通过 CREATE TRIGGER 语句创建触发器。
DELIMITER $$ -- 临时换分隔符，避免碰到触发器体内的分号 CREATE TRIGGER 触发器名 {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名 FOR EACH ROW BEGIN -- 触发器体：可写多条语句 -- 可用 NEW.列名 访问“新值” -- 可用 OLD.列名 访问“旧值”（对于 INSERT 没旧值，DELETE 没新值） END$$ DELIMITER ; -- 恢复默认分隔符 命名约定：推荐表名_事件_时机_trigger，如student_ai_trigger表示 After Insert 的触发器。
6.2. 查看、删除触发器
-- 查看当前库所有触发器 SHOW TRIGGERS\G -- 查看某触发器的创建语句 SHOW CREATE TRIGGER student_ai_log\G -- 删除触发器 DROP TRIGGER IF EXISTS student_ai_log; 6.3. 注意事项与坑
| 主题 | 说明 |
|---|---|
| 权限 | 需要 TRIGGER 权限（或 SUPER）才能创建/删除。 |
| 单表同事件多触发器 | MySQL 8.0 允许同一 “事件+时机” 创建多个触发器，但 5.7 及更早版本只允许一个。 |
| 递归触发 | 触发器里对同一张表再 INSERT/UPDATE/DELETE 会再次触发，谨防无限递归。 |
| 事务 | 触发器在当前事务中执行，若触发器报错，整个外层语句会回滚。 |
| NEW/OLD 只读限制 | BEFORE UPDATE 中可修改 NEW.xxx 来影响即将写入的值；其他场景 NEW/OLD 均只读。 |
| 不支持 COMMIT/ROLLBACK | 触发器体内禁止显式提交或回滚。 |
DELIMITER ## CREATE TRIGGER tb_user_insert_trigger AFTER INSERT ON student FOR EACH ROW BEGIN INSERT INTO user_logs (operation, operate_time, operate_id, operate_params) VALUES ( 'insert', NOW(), NEW.sno, CONCAT( '插入的数据内容为：sno=', NEW.sno, ', name=', NEW.snme ) ); END## DELIMITER ;
