存储过程和函数是 MySQL 中实现“编写可复用逻辑”的主要方式。它们允许你将多条 SQL 语句封装成一个可调用的单元，保存在服务器端，减少网络交互，集中管理业务规则。尽管现代开发中倾向于将业务逻辑放在应用层，但在某些场景下（如批量数据处理、定时任务、对数据一致性要求极高的复杂操作），存储过程和函数依然是非常实用的工具。
10.2.1 存储过程 vs 函数：区别与选择
首先需要明确两者的核心区别，否则容易混用导致错误。
| 对比项 | 存储过程 (PROCEDURE) | 函数 (FUNCTION) |
|--------|---------------------|------------------|
| 返回值 | 可以有多个输出参数，但本身不强制返回值 | 必须有且只能有一个返回值 |
| 调用方式 | 用 CALL 语句单独调用 | 可在 SQL 语句中像内置函数一样调用，如 SELECT my_func(col) |
| 事务控制 | 可以在过程内使用 COMMIT / ROLLBACK | 不能直接包含事务控制语句 |
| 使用场景 | 执行一系列操作，可能包含增删改、流程控制 | 计算、转换、格式化，必须无副作用（通常不能修改数据） |
| DDL/DML 限制 | 可包含任何 SQL 语句 | 通常不允许修改数据的语句（除非声明为 NOT DETERMINISTIC 等） |
简单来说：如果目的是执行一批操作，用存储过程；如果目的是通过计算得到一个值并在 SQL 中引用，用函数。
10.2.2 创建与调用存储过程
创建语法：
DELIMITER $$
CREATE PROCEDURE procedure_name( [IN|OUT|INOUT] param_name datatype [, ...] )
BEGIN
-- 过程体，可以包含多条语句
END$$
DELIMITER ;其中：
IN参数：调用者传入值，过程内部只读（默认）。OUT参数：过程内部赋值，调用者接收返回值。INOUT参数：既读又写，调用者传入值后可被过程修改并返回。
由于过程体内部通常有多条语句，需要用分号分隔，这与客户端的默认分隔符冲突。因此一般先用 DELIMITER 临时更改分隔符，写完后恢复。
调用方式：
CALL procedure_name(param1, @out_var);
SELECT @out_var; -- 获取 OUT 参数的值实战示例： 编写一个存储过程，根据用户 ID 更新最后登录时间，并返回用户名。
DELIMITER //
CREATE PROCEDURE update_login_time(
IN p_user_id INT,
OUT p_user_name VARCHAR(50)
)
BEGIN
UPDATE users SET last_login = NOW() WHERE id = p_user_id;
SELECT username INTO p_user_name FROM users WHERE id = p_user_id;
END//
DELIMITER ;
-- 调用
CALL update_login_time(101, @name);
SELECT @name;示例中使用了 SELECT ... INTO 将查询结果赋值给 OUT 参数。这是存储过程中非常常见的赋值方式。
查看与删除：
SHOW CREATE PROCEDURE procedure_name; -- 查看定义
DROP PROCEDURE IF EXISTS procedure_name;MySQL 系统表 mysql.proc（或使用 information_schema.ROUTINES）也存储了所有过程和函数的元数据。
10.2.3 创建与调用函数
创建语法：
CREATE FUNCTION function_name( param_name datatype [, ...] )
RETURNS return_datatype
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
-- 函数体，必须包含 RETURN 语句
END函数的参数只有 IN 模式，不能指定 OUT/INOUT。函数体内部必须有 RETURN 语句返回值。
由于函数可能用于 SQL 语句中，MySQL 要求声明函数的特性：
DETERMINISTIC：确定性函数，给定相同的输入一定返回相同的结果。NOT DETERMINISTIC：表示结果可能依赖于当前时间、随机数等，默认为此项。READS SQL DATA、MODIFIES SQL DATA等，用于标明函数是否会读写数据，不过这些声明主要是信息性的，实际执行由代码决定。
重要限制： 在严格模式下，函数通常不允许修改数据（INSERT/UPDATE/DELETE），否则在 SELECT 语句中调用时会报错。如果你的函数确实需要修改数据，应该使用存储过程。
调用方式： 直接在 SQL 语句中使用。
实战示例： 创建一个函数，计算订单总金额（根据订单 ID 汇总订单明细）。
DELIMITER //
CREATE FUNCTION calc_order_total(p_order_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE total DECIMAL(10,2);
SELECT SUM(price * quantity) INTO total
FROM order_items
WHERE order_id = p_order_id;
RETURN IFNULL(total, 0);
END//
DELIMITER ;
-- 调用
SELECT id, calc_order_total(id) AS total FROM orders;查看与删除：
SHOW CREATE FUNCTION function_name;
DROP FUNCTION IF EXISTS function_name;10.2.4 流程控制语句
存储过程和函数的强大之处在于支持丰富的流程控制结构，可以写出类似编程语言的逻辑。
1. 条件分支 IF / CASE
-- IF 结构
IF score >= 90 THEN
SET grade = 'A';
ELSEIF score >= 80 THEN
SET grade = 'B';
ELSE
SET grade = 'C';
END IF;
-- CASE 结构（两种模式）
-- 模式一：简单 CASE（类似 switch）
CASE user_role
WHEN 'admin' THEN SET perm = 'ALL';
WHEN 'user' THEN SET perm = 'LIMITED';
ELSE SET perm = 'GUEST';
END CASE;
-- 模式二：搜索 CASE（类似 if-else）
CASE
WHEN age < 18 THEN SET group = 'teen';
WHEN age BETWEEN 18 AND 65 THEN SET group = 'adult';
ELSE SET group = 'senior';
END CASE;2. 循环 LOOP / WHILE / REPEAT
MySQL 提供了三种循环结构，均需要用 LEAVE（类似 break）或 ITERATE（类似 continue）来控制。
LOOP：简单循环，需要显式使用LEAVE跳出。WHILE：入口条件判断，满足条件时执行。REPEAT：出口条件判断，至少执行一次。
-- LOOP 示例
DECLARE i INT DEFAULT 1;
my_loop: LOOP
SET i = i + 1;
IF i >= 10 THEN
LEAVE my_loop;
END IF;
END LOOP my_loop;
-- WHILE 示例
WHILE i < 10 DO
SET i = i + 1;
END WHILE;
-- REPEAT 示例
REPEAT
SET i = i + 1;
UNTIL i >= 10 END REPEAT;循环中可以配合 CONTINUE HANDLER 等条件来处理异常，但通常不建议在存储过程中编写过于复杂的循环逻辑，因为数据库是用于集合操作的，逐行处理往往性能很差。如果真的需要，尽量用批量 SQL 或游标替代。
10.2.5 游标（Cursor）
游标用于遍历 SELECT 语句返回的结果集，逐行处理。因为 SQL 是面向集合的，当确实需要逐行操作时（例如为每行调用外部系统或生成特定格式的日志），游标是唯一的选择。但务必注意：游标效率较低，谨慎用在大量数据上。
使用步骤：
- 声明游标：绑定到一个
SELECT语句。 - 打开游标：执行查询，准备读取。
- 循环获取下一行数据到本地变量。
- 关闭游标。
示例： 创建一个存储过程，遍历用户表，将积分大于 1000 的用户的等级更新为“VIP”。
DELIMITER $$
CREATE PROCEDURE update_user_level()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE uid INT;
DECLARE points INT;
-- 声明游标
DECLARE cur CURSOR FOR
SELECT id, total_points FROM users WHERE total_points > 1000;
-- 声明异常处理器：游标数据遍历完毕时设置 done = TRUE
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO uid, points;
-- 如果没有更多行，退出循环
IF done THEN
LEAVE read_loop;
END IF;
-- 逐行处理
UPDATE users SET user_level = 'VIP' WHERE id = uid;
END LOOP;
CLOSE cur;
END$$
DELIMITER ;关键点：
- 必须先声明变量，再声明游标，最后声明异常处理器，顺序错误会导致编译失败。
NOT FOUND是预定义的错误条件，当游标没有更多行时会触发，我们用它来安全退出循环。- 注意事务隔离：如果循环中有大量修改，考虑拆分为小批量提交，或者使用一个
UPDATE ... WHERE语句一步完成（应尽量避免用游标做这种简单更新）。
游标的性能瓶颈主要在于逐行操作引发的上下文切换和锁竞争。对于数据量较大的业务，优先考虑用关联更新或临时表等集合方式解决。
10.2.6 实际使用建议与避坑指南
- 不要过度依赖存储过程
存储过程将业务逻辑搬进数据库，会造成“胖数据库”问题：版本管理困难、难以测试、调试不便，而且会将计算压力集中在数据库服务器上。在现代系统架构中，除非有明确的理由（如强一致性要求、复杂数据清洗），否则业务逻辑放在应用层更易于维护。
- 注意权限与安全性
创建存储过程和函数需要 CREATE ROUTINE 权限，调用时还需要 EXECUTE 权限。如果使用了 DEFINER 特权定义的函数，可能被用于 SQL 注入攻击，因此要严格限制函数的用法，避免在带有不安全输入的 SQL 中拼接执行。
- 变量声明与赋值
DECLARE 只能在 BEGIN ... END 块的最开始，所有声明必须在任何语句之前。
赋值可以用 SET @var = value（用户变量，作用域为连接）或 DECLARE var ... DEFAULT value ... SET var = value（本地变量，作用域为过程块）。
- 事务与自动提交
存储过程中可以显式使用 START TRANSACTION / COMMIT / ROLLBACK，但前提是调用时的自动提交模式已关闭（或者在过程中主动控制）。如果存储过程中途发生异常，需要配合 DECLARE EXIT HANDLER 回滚事务并记录日志。
- 性能与维护
存储过程第一次被调用时会进行编译，生成中间代码缓存。之后调用省去了解析时间，但整体执行效率的提升往往没有你想象的那么大。在 8.0 中，强制使用严格模式可能使某些依赖宽松行为的旧脚本失效。因此在迁移升级时务必测试。
- 调试手段有限
MySQL 没有内置的逐步调试器，只能通过 SELECT 打印中间变量或写入日志表。所以在设计复杂过程时，尽量保持逻辑简单，拆分为多个小过程，便于排查问题。
总的来说，存储过程和函数是 MySQL 提供的强有力工具，但用得好是利器，用不好可能成为性能杀手和运维噩梦。通常推荐的策略是：能用 SQL 一句搞定的绝不循环，能用应用层代码搞定的不放进数据库，但在那些真正需要原子性、减少网络开销的批量任务中，大胆且谨慎地使用它们。