MySQL 存储过程｜游标｜触发器完整笔记，面试题+代码案例全覆盖-平芜编程栈

本篇为MySQL高级SQL编程完整学习笔记，主要讲解存储过程、存储函数、游标、条件处理程序以及触发器。从基础概念、语法示例到踩坑点，结合可执行SQL案例，配套16道高频面试题，方便复习巩固，可直接用于期末备考与面试复习。

1 学习目标

1. 了解存储过程的特点和优缺点

2. 掌握创建、调用、查看和删除存储过程的语法

3. 掌握MySQL中的变量类型

4. 掌握系统变量、用户自定义变量和局部变量的使用方法

5. 掌握SQL编程中涉及到的语法规则，包括：条件判断、参数分类、选择分支、循环

6. 掌握游标的定义与使用

7. 掌握条件处理程序的定义与使用

8. 掌握存储函数的定义与使用

9. 可以根据实际业务编写存储过程

10. 掌握触发器的使用场景与分类

11. 掌握触发器的创建、查看和删除方法

12. 可以根据实际业务定义触发器

2 面试问题

1. 存储过程的作用是什么？

2. 如何创建一个存储过程？

3. MySQL中的变量都有哪几种？

4. 如何定义一个变量？

5. MySQL中使用变量是否需要提前声明？

6. MySQL中的参数分为哪几种？

7. 用过游标吗？游标的作用是什么？

8. 了解条件处理程序吗？介绍一下如何使用

9. 存储函数与存储过程的区别是什么？

10. 如何查看数据库中创建的存储过程？

11. 什么是触发器？

12. MySQL中触发器分为几种类型？

13. 行级触发器与语句级触发器的区别是什么？

14. 说一下了解的触发器使用场景都有哪些？

15. 如果对一表中的数据进行更新，要在日志表中记录该条记录更新前与更新后的值，如何实现？

16. 如何查看数据库中创建的触发器？

3 存储过程

3.1 存储过程是什么

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字和参数来执行，并获取相应的结果。

3.2 特点

1. 封装性：将业务逻辑封装在数据库内部，减少应用程序的复杂性。

2. 可维护性：集中管理数据库操作，便于维护和更新。

3. 可重用性：可以被多次调用，提高代码的重用性。

3.3 优缺点

优点

1. 性能优化：存储过程在创建时编译并存储在数据库中，执行速度比单个SQL语句快。

2. 代码重用：存储过程可以重复调用，减少重复代码，提高代码的可维护性。

3. 安全性：可以限制用户直接访问数据库，通过存储过程间接访问，从而保证系统安全性。

4. 事务管理：可以在存储过程中实现复杂的事务逻辑。

5. 降低耦合：当表结构发生变化时，只需要修改相应的存储过程，应用程序的改动较小。

缺点

1. 可移植性差：存储过程不能跨数据库移植，更换数据库时需要重新编写。

2. 调试困难：只有少数数据库管理系统支持存储过程的调试，开发和维护困难。

3. 不适合高并发场景：在高并发场景下，存储过程可能会增加数据库的压力，难以维护。

3.4 语法

3.4.1 创建

创建存储过程前要修改SQL语句结束标识符DELIMITER //，因为存储过程内部SQL用;，会提前终止创建语句。

-- 修改SQL语句结束标识符为 // DELIMITER // -- 创建存储过程 CREATE PROCEDURE 存储过程名 (参数列表) BEGIN -- SQL语句 END // -- 修改SQL语句结束标识符为 ; DELIMITER ;

3.4.2 调用

CALL 存储过程名 (参数列表);

3.4.3 查看

-- 查看指定数据库中创建的存储过程 SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名'; -- 查看存储过程的定义 SHOW CREATE PROCEDURE 存储过程名;

3.4.4 删除

DROP PROCEDURE [IF EXISTS] 存储过程名;

3.5 示例

-- 创建存储过程 CREATE PROCEDURE p_calAvg() BEGIN select name, chinese + math + english as total from exam; END; -- 调用存储过程 CALL p_calAvg();

4 变量

MySQL变量分为三类：系统变量、用户自定义变量、局部变量。

4.1 系统变量

系统变量是MySQL服务器的配置变量，控制服务器行为和性能。分为全局变量GLOBAL、会话变量SESSION。

4.1.1 查看系统变量

-- 查看所有系统变量 SHOW [GLOBAL|SESSION] VARIABLES; -- 查看指定的系统变量，模糊查询 SHOW [GLOBAL|SESSION] VARIABLES LIKE 'xxx'; -- 使用SELECT查看指定系统变量 SELECT @@[GLOBAL|SESSION].系统变量名; --示例：查看以auto开头的全局系统变量 SHOW GLOBAL VARIABLES LIKE 'auto%'; --示例：查看以char开头的会话系统变量 SHOW SESSION VARIABLES LIKE 'char%'; --示例：查看事务自动提交全局系统变量 SELECT @@GLOBAL.autocommit;

4.1.2 设置系统变量

SET [GLOBAL|SESSION] 系统变量名 = 值; SET @@[SESSION].系统变量名 = 值; --示例：设置事务自动提交会话变量为关闭/开启 SET @@SESSION.autocommit = 0; SET autocommit = 1;

注意：

1. 不写GLOBAL/SESSION，默认设置会话SESSION变量

2. 会话关闭，SESSION变量失效；MySQL重启，GLOBAL变量失效，永久生效需要改配置文件。

4.2 用户自定义变量

用户自定义变量在SQL会话中定义，不需要提前声明，作用域：当前会话。

4.2.1 赋值

--方式1 SET SET @var_name = expr [, @var_name] ...; --方式2 推荐，区分赋值和等于比较 SET @var_name := expr [, @var_name] ...; --方式3 select SELECT @var_name := expr [, @var_name] ...; --方式4 查询结果赋值 SELECT 列名 INTO @var_name FROM 表名 WHERE ...;

示例

-- 方式1：给用户变量@age赋值为18 SET @age := 18; -- 查看变量的值，输出：18 SELECT @age; -- 查询student表id=1的sno字段，把查询出来的结果存入变量@sno SELECT sno INTO @sno FROM student WHERE id = 1; -- 打印@sno的值，就是id=1学生的sno SELECT @sno; -- 统计student表总记录行数，存入变量@count SELECT count(*) INTO @count FROM student; -- 输出学生总条数 SELECT @count; -- count(*)一定有返回结果（表空返回0），所以@count一定会被赋值。 --未赋值变量返回NULL SELECT @var;

注意：SQL中等号=既是比较运算符，又是赋值，自定义变量赋值推荐使用 :=

4.3 局部变量

局部变量只在存储过程/函数/触发器内有效，必须用DECLARE声明，作用域：BEGIN ... END块内部。

4.3.1 声明

DECLARE 变量名 变量类型 [DEFAULT 默认值] ...;

4.3.2 赋值

SET var_name = 值; SET var_name := 值; SELECT 列名 INTO var_name FROM 表名 WHERE ...;

示例

CREATE PROCEDURE p1() BEGIN --定义局部变量，指定默认值 DECLARE stu_count INT DEFAULT 0; --把查询结果赋值给局部变量 select count(*) into stu_count from student; --使用局部变量 select stu_count; END; call p1();

4.4 注意事项

1. 变量名不区分大小写。

2. 存储过程/函数中，局部变量必须先声明后使用。

3. 用户自定义变量会话结束失效；局部变量存储过程执行结束失效。

4. 不要使用MySQL保留字作为变量名。

5 SQL编程（存储过程内部逻辑）

5.1 IF条件判断

5.1.1 语法

IF 条件1 THEN ... [ELSEIF 条件2 THEN ...] [ELSE ...] END IF;

练习示例

DELIMITER // CREATE PROCEDURE p2() BEGIN --定义初始分数变量 DECLARE score INT DEFAULT 86; --定义等级结果变量 DECLARE result VARCHAR(10); IF score >= 90 THEN SET result := '优秀'; ELSEIF score >= 80 AND score < 90 THEN SET result := '良好'; ELSEIF score >= 60 AND score < 80 THEN SET result := '及格'; ELSE SET result := '不及格'; END IF; SELECT result; END // DELIMITER ; CALL p2();

5.2 参数

5.2.1 参数分类

类型	说明
IN	输入类型，调用存储过程传入值，默认参数类型
OUT	输出类型，可以作为存储过程返回值
INOUT	输入输出类型，既做输入，也可以输出

5.2.2 语法

DELIMITER // CREATE PROCEDURE 存储过程名 ([IN/OUT/INOUT 参数名 参数类型], ...) BEGIN --SQL语句 END // DELIMITER ;

示例：分数等级，传入分数输出等级

DELIMITER // CREATE PROCEDURE p3(IN score INT, OUT result VARCHAR(10)) BEGIN IF score >= 90 THEN SET result := '优秀'; ELSEIF score >= 80 AND score < 90 THEN SET result := '良好'; ELSEIF score >= 60 AND score < 80 THEN SET result := '及格'; ELSE SET result := '不及格'; END IF; END // DELIMITER ; --调用 CALL p3(98,@res); SELECT @res;

INOUT示例：分数加10分

DELIMITER // CREATE PROCEDURE p4(INOUT score INT) BEGIN SET score := score + 10; END // DELIMITER ; SET @score :=98; CALL p4(@score); SELECT @score;

5.3 CASE分支

语法一：等值匹配

CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE

case_value 和when_value做等于比较，匹配到就执行THEN，都不匹配执行ELSE。没有ELSE会报错。

语法二：条件表达式匹配

CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE

每个WHEN写布尔条件，哪个条件为真执行对应THEN。

示例1：状态码含义

DELIMITER // CREATE PROCEDURE p5(IN code INT, OUT result varchar(50)) BEGIN CASE code WHEN 0 THEN SET result := '成功'; WHEN 10001 THEN SET result := '用户名或密码错误'; WHEN 10002 THEN SET result := '您没有对应的权限，请联系管理员'; WHEN 20001 THEN SET result := '你传入的参数有误'; WHEN 20002 THEN SET result := '没有找到相应的结果'; ELSE SET result := '服务器错误，请联系管理员'; END CASE; END // DELIMITER ; CALL p5(10001,@result); SELECT @result;

示例2：月份判断季度

DELIMITER // CREATE PROCEDURE p6(IN month INT, OUT result varchar(50)) BEGIN CASE WHEN month >=1 AND month <=3 THEN SET result := '第一季度'; WHEN month >=4 AND month <=6 THEN SET result := '第二季度'; WHEN month >=7 AND month <=9 THEN SET result := '第三季度'; WHEN month >=10 AND month <=12 THEN SET result := '第四季度'; ELSE SET result := '非法参数'; END CASE; END // DELIMITER ; CALL p6(6,@result); SELECT @result;

注意：statement_list可以多条SQL，不能空，可以用BEGIN...END块；没有ELSE匹配不到会报错。

5.4 循环

5.4.1 WHILE循环

先判断条件，条件成立执行循环体。

WHILE search_condition DO statement_list END WHILE;

示例：1累加到n

DELIMITER // CREATE PROCEDURE p7(IN n INT) BEGIN DECLARE total INT DEFAULT 0; WHILE n > 0 DO SET total := total + n; SET n := n - 1; END WHILE; SELECT total; END // DELIMITER ; CALL p7(100);

5.4.2 REPEAT循环

先执行一次循环体，再判断条件，条件成立UNTIL退出循环，类似do‑while。

REPEAT statement_list UNTIL search_condition END REPEAT;

示例：累加1~n

DELIMITER // CREATE PROCEDURE p8(IN n INT) BEGIN DECLARE total int DEFAULT 0; REPEAT SET total := total + n; SET n := n - 1; UNTIL n <= 0 END REPEAT; SELECT total; END // DELIMITER ; CALL p8(100);

5.4.3 LOOP循环

无限循环，依靠LEAVE退出（break）；ITERATE继续下一轮（continue），需要标签label。

• LEAVE label：退出整个循环

• ITERATE label：结束本次循环，直接下一轮

语法：

[begin_label:] LOOP statement_list END LOOP [end_label]

示例1：1累加到n

DELIMITER // CREATE PROCEDURE p9(IN n INT) BEGIN DECLARE total int DEFAULT 0; sum_label: LOOP IF n <= 0 THEN LEAVE sum_label; END IF; SET total := total + n; SET n := n - 1; END LOOP sum_label; SELECT total; END // DELIMITER ; CALL p9(100);

示例2：累加1‑n之间偶数

DELIMITER // CREATE PROCEDURE p10(IN n INT) BEGIN DECLARE total int DEFAULT 0; sum_label: LOOP IF n <= 0 THEN LEAVE sum_label; END IF; --奇数直接跳过本次循环 IF n % 2 = 1 THEN SET n := n -1; ITERATE sum_label; END IF; SET total := total + n; SET n := n - 1; END LOOP sum_label; SELECT total; END // DELIMITER ; CALL p10(100);

5.5 游标cursor

MySQL游标：存储过程/函数中，对select查询结果集逐行读取；只读，不能更新数据。
声明顺序：变量 → 游标 → 条件处理程序handler
完整流程：DECLARE声明游标 → OPEN打开游标 → FETCH INTO读取一行 → CLOSE关闭游标

语法：

--声明游标 DECLARE 游标名 CURSOR FOR 查询语句; --打开游标 OPEN 游标名; --读取一行数据到变量 FETCH 游标名 INTO 变量1[,变量2...]; --关闭游标 CLOSE 游标名;

坑：游标读完所有行，继续FETCH会抛出 NOT FOUND 1329错误，需要搭配条件处理程序handler捕获。

错误版本（死循环报错）

DELIMITER // CREATE PROCEDURE p11(IN class_id INT) BEGIN DECLARE student_name VARCHAR(20); DECLARE class_name VARCHAR(20); DECLARE s_cursor CURSOR FOR select s.`name` student_name, c.`name` class_name from student s, class c where s.class_id = c.id and s.class_id = class_id; DROP TABLE IF EXISTS t_student_class; CREATE TABLE IF NOT EXISTS t_student_class ( id BIGINT PRIMARY KEY AUTO_INCREMENT, student_name VARCHAR(20), class_name VARCHAR(20) ); OPEN s_cursor; WHILE TRUE DO FETCH s_cursor INTO student_name, class_name; INSERT INTO t_student_class VALUES (NULL, student_name, class_name); END WHILE; CLOSE s_cursor; END // DELIMITER ; --调用报错 ERROR 1329 No data - zero rows fetched

5.6 条件处理程序handler

遇到警告/错误的时候定义处理逻辑，避免存储过程异常崩溃。

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement;

• handler_action：

CONTINUE：继续往下执行

EXIT：终止当前BEGIN‑END块

• condition_value：

mysql_error_code：错误码

SQLSTATE 状态码

SQLWARNING：所有01开头警告

NOT FOUND：所有02开头（游标读取完毕就是NOT FOUND）

SQLEXCEPTION：没有被上面捕获的错误

游标修复完整版（搭配handler）

-- 修改语句结束符为 //，避免存储过程内部 ; 提前结束整个存储过程定义 DELIMITER // -- 创建存储过程 p12，传入输入参数 class_id 整型，代表班级编号 CREATE PROCEDURE p12(IN class_id INT) BEGIN -- 定义局部变量，保存游标读取出来的学生姓名 DECLARE student_name VARCHAR(20); -- 定义局部变量，保存游标读取出来的班级名称 DECLARE class_name VARCHAR(20); --定义结束标记变量，布尔类型，默认false，false代表游标还有数据，true代表游标数据读完 DECLARE is_done bool DEFAULT FALSE; --1.声明游标：游标相当于结果集指针 --查询指定班级下学生姓名、班级名称，把查询结果交给游标s_cursor管理 DECLARE s_cursor CURSOR FOR select s.`name` student_name, c.`name` class_name from student s, class c where s.class_id = c.id and s.class_id = class_id; --2.条件处理程序：游标FETCH取不到数据(遍历完毕触发NOT FOUND) --设置is_done = true，并且CONTINUE表示不报错，继续往下执行代码 DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done := TRUE; -- 如果表t_student_class存在就删除 DROP TABLE IF EXISTS t_student_class; -- 创建存放结果的表，id自增主键，存学生名、班级名 CREATE TABLE IF NOT EXISTS t_student_class ( id BIGINT PRIMARY KEY AUTO_INCREMENT, student_name VARCHAR(20), class_name VARCHAR(20) ); OPEN s_cursor; -- 打开游标，执行游标绑定的select查询，拿到结果集 read_loop: LOOP -- 开启循环，循环标签 read_loop，方便跳出循环 -- 从游标当前行取出数据，赋值给局部变量 student_name, class_name FETCH s_cursor INTO student_name, class_name; -- 判断标记，如果is_done为true，说明游标全部读取完毕，跳出循环 IF is_done THEN LEAVE read_loop; END IF; -- 把拿到的学生、班级名字插入结果表，id传NULL使用自增 INSERT INTO t_student_class VALUES (NULL, student_name,class_name); END LOOP read_loop; -- 循环结束，回到LOOP开头继续下一轮 CLOSE s_cursor; --关闭游标，释放游标资源 END // DELIMITER ; -- 把结束符改回分号; --调用存储过程，传入班级id=1，执行全部逻辑 CALL p12(1); SELECT * FROM t_student_class;

5.7 存储函数 function

存储函数：必须有返回值，参数只能IN，类似内置函数；用SELECT 函数()调用。
binlog开启的时候，必须指定characteristic属性。

语法：

CREATE FUNCTION 函数名称 ([参数列表]) RETURNS type [characteristic ...] BEGIN --SQL语句 RETURN ...; END;

characteristic属性：

1. DETERMINISTIC：相同输入总是返回相同结果

2. NO SQL：不包含SQL

3. READS SQL DATA：包含查询select

4. MODIFIES SQL DATA：包含写操作update delete

示例：1累加到n

DELIMITER // CREATE FUNCTION fun1(n INT) RETURNS INT DETERMINISTIC BEGIN DECLARE total int DEFAULT 0; WHILE n > 0 DO SET total := total + n; SET n := n - 1; END WHILE; RETURN total; END // DELIMITER ; SELECT fun1(100);

存储过程 vs 存储函数区别

1. 存储过程：用CALL调用，参数支持IN/OUT/INOUT，可以没有返回值；

2. 存储函数：SELECT调用，只能IN参数，必须RETURN返回一个值。

6 触发器 trigger

触发器和表绑定，当表发生INSERT / UPDATE / DELETE，自动执行预设SQL。
触发时间：BEFORE操作之前 / AFTER操作之后
MySQL只支持行级触发器 FOR EACH ROW，每一行变化触发一次；没有语句级触发器。

关键字：

• NEW：新增/修改后的新数据（INSERT、UPDATE可以读取NEW）

• OLD：修改前、被删除的旧数据（UPDATE、DELETE可以读取OLD）

触发器类型	OLD	NEW
INSERT	无	NEW代表新增行
UPDATE	OLD修改前数据	NEW修改之后数据
DELETE	OLD被删除的数据	无

6.1 语法

--创建 CREATE TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_stmt; END; --查看 SHOW TRIGGERS; --删除 DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

trigger_time：BEFORE | AFTER
trigger_event：INSERT | UPDATE | DELETE

6.2 练习：记录学生表操作日志

--日志表 create table student_log ( id bigint primary key auto_increment, operation_type varchar(10) not null comment '操作类型:insert/update/delete', operation_time datetime not null comment '操作时间', operation_id bigint not null comment '操作的记录ID', operation_data varchar(500) comment '操作数据' );

6.3 INSERT触发器

-- 修改语句结束符为//，防止触发器内部分号;提前结束定义 DELIMITER // -- 创建触发器 trg_student_insert -- AFTER INSERT：在student表**插入数据完成之后**触发 -- FOR EACH ROW：行级触发器，每插入1行，触发器就执行1次（MySQL只有行级触发器） create trigger trg_student_insert after insert on student for each row begin -- 向日志表student_log写入一条操作记录 insert into student_log (operation_type,operation_time,operation_id,operation_data) values ( 'insert', -- 操作类型：新增 now(), -- 操作发生时间，当前系统时间 new.id, -- 新插入学生记录的id（NEW代表刚插入的那一行数据） -- concat拼接新行全部字段，把多个字段拼成一个字符串存入日志 concat(new.id,',',new.name,',',new.sno,',',new.age,',',new.gender,',',new.enroll_date,',',new.class_id) ); end // DELIMITER ; -- 恢复结束符为分号; --测试：向student插入一条学生数据 insert into student values (null , '曹操', '300001', 28, 1, '2024‑09‑01',3); -- 查询日志表，查看触发器自动生成的日志记录 select * from student_log;

6.4 UPDATE触发器

DELIMITER // create trigger trg_student_update after update on student for each row begin insert into student_log (operation_type,operation_time,operation_id,operation_data) values ( 'update', now(), new.id, concat(old.id,',',old.name,',',old.sno,',',old.age,',',old.gender,',',old.enroll_date,',',old.class_id, '||', new.id,',',new.name,',',new.sno,',',new.age,',',new.gender,',',new.enroll_date,',',new.class_id) ); end // DELIMITER ; --测试 update student set age =20, class_id=2 where name='曹操'; select * from student_log;

6.5 DELETE触发器

DELIMITER // create trigger trg_student_delete after delete on student for each row begin insert into student_log (operation_type,operation_time,operation_id,operation_data) values ( 'delete', now(), old.id, concat(old.id,',',old.name,',',old.sno,',',old.age,',',old.gender,',',old.enroll_date,',',old.class_id) ); end // DELIMITER ; --测试 delete from student where name='曹操'; select * from student_log;

7 面试问题解答

1. 存储过程的作用是什么？
把一组业务SQL封装，编译存储在数据库；可以重复调用，减少重复代码，封装业务逻辑，提升复用性。

2. 如何创建存储过程？
修改结束符DELIMITER，CREATE PROCEDURE 名(参数) BEGIN ... END，改回分号结束符；CALL调用。

3. MySQL中的变量都有哪几种？
系统变量(GLOBAL全局、SESSION会话)、用户自定义变量@var、局部变量DECLARE（存储过程内部）。

4. 如何定义一个变量？
系统变量：SET @@xxx；
用户变量：SET @a := 10；
局部变量：DECLARE a INT DEFAULT 0（只能在BEGIN‑END内部）。

5. MySQL中使用变量是否需要提前声明？
局部变量DECLARE必须声明；用户自定义变量@不需要声明，直接赋值使用。

6. MySQL中的参数分为哪几种？
IN输入，OUT输出，INOUT输入输出。

7. 用过游标吗？游标的作用是什么？
游标用来逐行读取select查询的结果集；注意读完会NOT FOUND报错，要搭配DECLARE HANDLER捕获异常。

8. 了解条件处理程序吗？介绍一下如何使用
DECLARE ... HANDLER FOR NOT FOUND，捕获存储过程内部异常，选择CONTINUE继续/EXIT退出块，用来处理游标读完、SQL报错。

9. 存储函数与存储过程的区别是什么？
存储过程CALL调用，支持IN/OUT/INOUT；存储函数SELECT调用，只能IN，必须RETURN返回值。

10. 如何查看数据库中创建的存储过程？
SHOW CREATE PROCEDURE xxx；查询information_schema.ROUTINES系统表。

11. 什么是触发器？
绑定表，当发生insert/update/delete自动执行SQL，用于日志记录、数据校验。

12. MySQL中触发器分为几种类型？
INSERT触发器、UPDATE触发器、DELETE触发器，分为BEFORE / AFTER触发时机。

13. 行级触发器与语句级触发器的区别是什么？
行级FOR EACH ROW，每受影响一行触发一次；语句级整条SQL只触发一次。MySQL只支持行级触发器。

14. 触发器使用场景？
数据变更日志记录、数据校验、数据同步、备份变更数据。

15. 更新一条记录，日志表记录更新前后的值怎么实现？
AFTER UPDATE触发器，OLD拿修改前，NEW拿修改后，把concat拼接新旧字段插入日志表。

16. 如何查看触发器？
SHOW TRIGGERS;