1. 存储过程
概念: 存储过程是一组预编译的 SQL 语句集合,存储在数据库中,可通过名称调用。支持条件判断、循环等逻辑控制,适合封装复杂业务逻辑。
详细讲解了 MySQL 中的存储过程、变量、SQL 编程控制流(IF/CASE/WHILE/REPEAT/LOOP)、游标及条件处理程序,以及触发器的概念、语法与应用示例。内容涵盖存储过程的创建、调用、优缺点分析,系统变量与用户变量的区别,各类循环结构的使用,游标的声明与遍历,以及 INSERT/UPDATE/DELETE 触发器的实现逻辑。

概念: 存储过程是一组预编译的 SQL 语句集合,存储在数据库中,可通过名称调用。支持条件判断、循环等逻辑控制,适合封装复杂业务逻辑。

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML转Markdown 互为补充。 在线工具,Markdown转HTML在线工具,online
优势:
缺点:
语法:
-- 修改 SQL 语句结束符为 DELIMITER
DELIMITER //
-- 创建存储过程
CREATE PROCEDURE IF NOT EXISTS 存储过程名 (参数列表)
BEGIN
-- SQL 语句
END//
-- 还原 SQL 语句结束符为 ;
DELIMITER ;
-- 调用存储过程
CALL 存储过程名 (参数列表);
-- 删除存储过程
DROP PROCEDURE IF EXISTS 存储过程名;
-- 查看当前数据库已创建的存储过程
SELECT * FROM information_schema.routines WHERE routine_schema = '数据库名';
在 MySQL 中,存储过程、函数等数据库对象的信息可以通过
information_schema中的routines系统视图查询。这个视图包含所有数据库中的存储过程和函数的元数据信息。
- routine_schema:所属的数据库名
- routine_name:存储过程/函数名称
- routine_type:对象类型 (procedure 或 function)
- data_type:函数返回值类型,存储过程没有返回值
- routine_definition:存储过程/函数的定义语句
系统变量分为全局变量和会话变量。
- 会话变量:仅影响当前会话连接,每个客户端连接可以有自己的会话变量设置(默认从全局变量继承其初始值)。
- 全局变量:影响整个服务器运行环境,MySQL 服务器启动时从 my.ini 文件读取初始值。
-- 查询全局/会话变量
SHOW [GLOBAL|SESSION] VARIABLES [LIKE ...];
SELECT @@[GLOBAL|SESSION].系统变量名;
-- 修改全局/会话变量
SET [GLOBAL|SESSION] 系统变量名 = 值;
SET @@[GLOBAL|SESSION].系统变量名 = 值;
未指定
[GLOBAL|SESSION]时默认查询/修改SESSION变量。上述修改操作仅为内存级修改,MySQL 服务器启动时从 my.ini 文件读取初始值。
用户变量以
@符号开头,作用域为当前会话连接,不需要提前声明。常在存储过程中作为参数传递,用于存储查询中间结果。
-- 设置用户变量
SET @user_demo = 值;
-- 查询用户变量
SELECT @user_demo;
-- 设置并查询用户变量
SELECT @user_demo := 值;
局部变量仅存在于存储过程、函数、触发器中,使用
DECLARE声明。
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS query()
BEGIN
DECLARE num INT DEFAULT 0;
SET num := 100;
SELECT num;
END//
DELIMITER ;
CALL query();
| 类型 | 描述 |
|---|---|
| IN | 输入型参数,调用存储过程时传入,默认参数类型 |
| OUT | 输出型参数,可以作为存储过程的返回值 |
| INOUT | 输入输出型参数 |
IF 语法:
IF 条件 1 THEN
-- sql 语句 1
ELSEIF 条件 2 THEN
-- sql 语句 2
ELSE
-- sql 语句 3
ENDIF;
示例:
-- 示例 1
DELIMITER //
CREATE PROCEDURE demo_if(IN score INT, OUT result VARCHAR(20))
BEGIN
IF score >= 90 THEN
SET result := '优秀';
ELSEIF score >= 80 AND score < 90 THEN
SET result = '良好';
ELSEIF score >= 70 AND score < 80 THEN
SET result = '及格';
ELSE
SET result = '不及格';
ENDIF;
END//
DELIMITER ;
CALL demo_if(60, @result);
SELECT @result;
-- 示例 2
SET @score := 70;
DELIMITER //
CREATE PROCEDURE demo2_if(INOUT score INT)
BEGIN
SET score := score + 10;
END//
DELIMITER ;
CALL demo2_if(@score);
SELECT @score;
语法:
-- 简单 CASE 表达式:将一个表达式与一组简单值进行比较
CASE case_value WHEN when_value1 THEN result1 WHEN when_value2 THEN result2 ... ELSE else_result END;
-- 搜索 CASE 表达式:将一个表达式与一组独立的布尔表达式进行比较
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE else_result END;
示例:
-- 示例 1
DELIMITER //
CREATE PROCEDURE demo_case(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 demo_case(10002, @result);
SELECT @result;
-- 示例 2
DELIMITER //
CREATE PROCEDURE demo2_case(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 demo2_case(7, @result);
SELECT @result;
语法:
WHILE search_condition DO
statement_list
END WHILE;
示例:
DELIMITER //
CREATE PROCEDURE demo_while(IN n INT)
BEGIN
DECLARE sum INT DEFAULT 0;
WHILE n > 0 DO
SET sum := sum + n;
SET n := n - 1;
END WHILE;
SELECT sum;
END//
DELIMITER ;
CALL demo_while(100);
先执行一次
statement_list,再判断search_condition是否成立。
语法:
REPEAT
statement_list
UNTIL search_condition
END REPEAT;
示例:
DELIMITER //
CREATE PROCEDURE demo_repeat(IN n INT)
BEGIN
DECLARE sum INT DEFAULT 0;
REPEAT
SET sum := sum + n;
SET n := n - 1;
UNTIL n <= 0
END REPEAT;
SELECT sum;
END//
DELIMITER ;
CALL demo_repeat(100);
语法:
loop_label: LOOP
statement_list
END LOOP loop_label;
控制 LOOP 循环的关键字:
- LEAVE:用于退出循环,相当于其他语言中的 BREAK。
- ITERATE:用于跳过当前迭代,继续下一次循环,相当于其他语言中的 CONTINUE。
示例:
-- 求 1-100 所有偶数的和
DELIMITER //
CREATE PROCEDURE demo_loop(IN n INT)
BEGIN
DECLARE total INT DEFAULT 0;
sum_loop: LOOP
IF n <= 0 THEN LEAVE sum_loop; END IF;
IF n % 2 = 1 THEN SET n := n - 1; ITERATE sum_loop; END IF;
SET total := total + n;
SET n := n - 1;
END LOOP sum_loop;
SELECT total;
END//
DELIMITER ;
CALL demo_loop(100);
游标 (cursor): 是一种数据库对象,用于在存储过程或函数中逐行处理查询结果集。当游标遍历到结果集末尾时,会触发 NOT FOUND 状态,需要结合条件处理程序结束循环,避免无限执行。
语法:
-- 声明游标
DECLARE cursor_name CURSOR FOR 查询语句;
-- 打开游标
OPEN cursor_name;
-- 获取游标记录
FETCH cursor_name INTO 变量列表;
条件处理程序: 用于捕获和处理 SQL 执行过程中的异常或特定状态,需与游标配合使用。
语法:
DECLARE handler_type HANDLER FOR condition statement;
- handler_type:条件处理程序类型
- CONTINUE:继续执行后续语句
- EXIT:终止当前 BEGIN...END 块
- condition:指定了处理程序会响应的条件类型
- mysql_error_code:MySQL 错误码
- SQLSTATE sqlstate_value:表示特定的 SQL 状态码
- NOT FOUND:捕获所有以'02'开头的 sqlstate 代码
- SQLWARNING:捕获所有以'01'开头的 sqlstate 代码的警告
- SQLEXCEPTION:捕获所有不以'00'(成功)、'01'(警告)、'02'(未找到) 开头的 sqlstate 代码的错误
- statement:SQL 语句
初始化数据:
-- 创建班级表
CREATE TABLE class (id INT PRIMARY KEY);
-- 创建学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
sno VARCHAR(10),
age INT,
gender TINYINT DEFAULT 1,
enroll_date DATETIME DEFAULT NOW(),
class_id INT,
FOREIGN KEY(class_id) REFERENCES class(id)
);
-- 向班级表插入数据
INSERT INTO class VALUES(1),(2);
-- 向学生表插入数据
INSERT INTO student (name,sno,age,class_id) VALUES('刘备','100001',18,1),('关羽','100002',18,1),('张飞','100003',18,2),('赵云','100004',18,2);
示例:
DELIMITER //
CREATE PROCEDURE demo_cursor(IN input_class_id INT)
BEGIN
-- 声明变量用于接收每一列的结果
DECLARE student_name VARCHAR(20);
DECLARE class_id INT;
DECLARE flg BOOL DEFAULT TRUE;
-- 声明游标用于接收查询结果
DECLARE demo_cursor CURSOR FOR
SELECT s.name AS student_name, c.id AS id
FROM student AS s, class AS c
WHERE s.class_id = c.id AND s.class_id = input_class_id;
-- 声明处理程序
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flg := FALSE;
-- 创建新表
CREATE TABLE class_new (
id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(20),
class_id INT,
FOREIGN KEY(class_id) REFERENCES class(id)
);
-- 开启游标
OPEN demo_cursor;
read_loop: LOOP
FETCH demo_cursor INTO student_name, class_id;
IF NOT flg THEN LEAVE read_loop; END IF;
INSERT INTO class_new VALUES(NULL, student_name, class_id);
END LOOP read_loop;
-- 关闭游标
CLOSE demo_cursor;
END//
DELIMITER ;
CALL demo_cursor(1);
运行结果:
| id | student_name | class_id |
|---|---|---|
| 1 | 刘备 | 1 |
| 2 | 关羽 | 1 |
存储函数: 是一种存储在数据库中的可重用代码块,接收参数并返回一个值。与存储过程不同,存储函数必须包含返回值,且可以使用 SELECT 直接调用。
语法:
CREATE FUNCTION 存储函数名 (参数列表) RETURNS 返回值类型 [characteristic]
characteristic: 可选特性
- DETERMINISTIC:表明函数对于相同的输入参数总是返回相同的结果
- NOT DETERMINISTIC:默认选项,表示函数可能对相同的输入返回不同的结果
- NO SQL:表示函数不包含任何 SQL 语句
- READS SQL DATA:表示函数包含读取数据的 SQL 语句 (SELECT)
- MODIFIES SQL DATA:表示函数包含修改数据的 SQL 语句 (INSERT/UPDATE/DELETE)
- CONTAINS SQL:表示函数仅包含读取数据的 SQL 语句,不修改数据
示例:
CREATE FUNCTION func1(n INT) RETURNS INT DETERMINISTIC
BEGIN
DECLARE time INT DEFAULT 0;
WHILE n > 0 DO
SET time := time + n;
SET n := n - 1;
END WHILE;
RETURN time;
END;
SELECT func1(100);
触发器: 一种与表相关的数据库对象,在指定事件 (如 INSERT、UPDATE、DELETE) 发生时自动执行预定义的 SQL 语句。
触发器类型:
- 从触发时机区分
- BEFORE 触发器:在触发事件执行前激活
- AFTER 触发器:在触发事件完成后再执行
- 从触发事件区分
- INSERT 触发器:响应数据插入操作
- UPDATE 触发器:响应数据更新操作
- DELETE 触发器:响应数据删除操作
- 从作用粒度区分
- 行级触发器:针对受影响的每一行数据都会触发一次
- 语句级触发器:整个 SQL 语句执行完毕后仅触发一次 (MySQL 暂不支持)
触发器中的 NEW 和 OLD
- NEW:表示触发事件中的新数据
- OLD:表示触发事件前的旧数据
语法:
CREATE TRIGGER 触发器名 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW
BEGIN
-- 触发器逻辑
END;
初始化数据:
-- 创建日志表
CREATE TABLE log (
id INT PRIMARY KEY AUTO_INCREMENT,
operation_type VARCHAR(20) NOT NULL COMMENT '操作类型',
operation_time DATETIME NOT NULL COMMENT '操作时间',
operation_id INT NOT NULL COMMENT '操作的记录的 id',
operation_date VARCHAR(500) COMMENT '操作数据'
);
-- insert 触发器
CREATE TRIGGER trg_insert AFTER INSERT ON student FOR EACH ROW
BEGIN
INSERT INTO log VALUES(NULL, 'insert', NOW(), NEW.id, CONCAT(NEW.id,',',NEW.name,',',NEW.sno,',',NEW.age,',',NEW.gender,',',NEW.enroll_date,',',NEW.class_id));
END;
-- 向学生表插入数据
INSERT INTO student VALUES(NULL, '马超', 200001, 18, 1, NOW(), 1);
执行结果:
| id | operation_type | operation_time | operation_id | operation_date |
|---|---|---|---|---|
| 1 | insert | 2025-10-31 17:25:17 | 9 | 9,马超,200001,18,1,2025-10-31 17:25:17,1 |
-- update 触发器
CREATE TRIGGER trg_update AFTER UPDATE ON student FOR EACH ROW
BEGIN
INSERT INTO log VALUES(NULL, '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;
-- 更新学生表的数据
UPDATE student SET age = 20, enroll_date = NOW(), class_id = 2 WHERE name = '马超';
执行结果:
| id | operation_type | operation_time | operation_id | operation_date |
|---|---|---|---|---|
| 3 | insert | 2025-10-31 17:40:14 | 10 | 10,马超,200001,18,1,2025-10-31 17:40:14,1 |
| 4 | update | 2025-10-31 17:41:11 | 10 | 更新前数据:10,马超,200001,18,1,2025-10-31 17:40:14,1,更新后数据:10,马超,200001,20,1,2025-10-31 17:41:11,2 |
-- delete 触发器
CREATE TRIGGER trg_delete AFTER DELETE ON student FOR EACH ROW
BEGIN
INSERT INTO log VALUES(NULL, 'delete', NOW(), OLD.id, CONCAT('删除的数据: ',OLD.id,',',OLD.name,',',OLD.sno,',',OLD.age,',',OLD.gender,',',OLD.enroll_date,',',OLD.class_id));
END;
DELETE FROM student WHERE name = '马超';
执行结果:
| id | name | sno | age | gender | enroll_date | class_id |
|---|---|---|---|---|---|---|
| 1 | 刘备 | 100001 | 18 | 1 | 2025-10-26 22:11:23 | 1 |
| 2 | 关羽 | 100002 | 18 | 1 | 2025-10-26 22:11:23 | 1 |
| 3 | 张飞 | 100003 | 18 | 1 | 2025-10-26 22:11:23 | 2 |
| 4 | 赵云 | 100004 | 18 | 1 | 2025-10-26 22:11:23 | 2 |
| id | operation_type | operation_time | operation_id | operation_date |
|---|---|---|---|---|
| 3 | insert | 2025-10-31 17:40:14 | 10 | 10,马超,200001,18,1,2025-10-31 17:40:14,1 |
| 4 | update | 2025-10-31 17:41:11 | 10 | 更新前数据:10,马超,200001,18,1,2025-10-31 17:40:14,1,更新后数据:10,马超,200001,20,1,2025-10-31 17:41:11,2 |
| 5 | delete | 2025-10-31 17:47:09 | 10 | 删除的数据:10,马超,200001,20,1,2025-10-31 17:41:11,2 |