完整教程：MySQL 存储过程完整实战手册---一篇吃透 Stored Procedure

目录

1. 存储过程是什么（作用总览）
2. 典型使用场景与真实案例
3. 使用前必须知道的 7 大注意事项
4. 语法骨架与变量详解
5. 参数模式：IN / OUT / INOUT
6. 流程控制：IF / CASE / 循环
7. 游标（Cursor）：行级遍历
8. 条件处理程序（Handler）：捕获异常
9. 存储函数（Function）与过程的区别
10. 性能 & 运维 FAQ
11. 一句话总结

---

1. 存储过程是什么（作用总览）

• 预编译的数据库代码块，存储在服务器端
• 可接受参数、返回结果、封装复杂业务
• 一次创建，多次调用 → 减少网络往返、提高性能
• 支持事务、异常处理、游标，实现“数据库内脚本”

一句话：把多条 SQL + 控制逻辑打包成“数据库里的方法”。

---

2. 典型使用场景与真实案例

场景	说明	示例片段
① 批量数据清洗	ETL nightly job	遍历临时表，异常行写入错误日志
② 订单下单闭环	扣库存 → 写订单 → 记日志	三表操作包事务
③ 对账/结转	月末快照	游标逐行累计金额
④ 分页返回 + 总计	Web 报表一次返回“列表+总数”	OUT 参数带回总数
⑤ 安全隔离	只开放 CALL 权限，不暴露表	隐藏底层字段

---

3. 使用前必须知道的 7 大注意事项

1. 移植性差：MySQL / SQL Server / Oracle 语法差异大
2. 调试困难：8.0 前无断点，靠 SELECT debug_info;
3. 版本管理：过程代码存库，要入 Git + 迁移脚本
4. 过度复杂会使业务逻辑碎片化 → 轻量封装即可
5. 默认权限 DEFINER 需回收 SUPER，防止 SQL 注入提权
6. 大循环注意 max_sp_recursion_depth & innodb_lock_wait_timeout
7. 线上大表用游标时，务必加 WHERE 范围 + 主键索引，防止全表锁

---

4. 语法骨架与变量详解

DELIMITER $$
CREATE PROCEDURE sp_demo(IN p_age INT,
OUT p_cnt INT)
BEGIN
/* 1. 局部变量 */
DECLARE v_total INT DEFAULT 0;
DECLARE v_msg VARCHAR(100);
/* 2. 业务逻辑 */
SELECT COUNT(*) INTO v_total
FROM user
WHERE age > p_age;
SET p_cnt = v_total;
-- 返回给调用者
END$$
DELIMITER ;

变量类型对比

类型	作用域	赋值方式	初始值
局部变量 DECLARE	当前 BEGIN…END	SET / SELECT ... INTO	DEFAULT 或 NULL
用户变量 @var	当前会话	SET @var=1;	无，需手动初始化
参数 IN/OUT/INOUT	过程内部	调用者传入	调用者给定

---

5. 参数模式：IN / OUT / INOUT

CREATE PROCEDURE sp_param_demo(
IN p_id INT, -- 只读
OUT p_name VARCHAR(50), -- 返回
INOUT p_sal DECIMAL(10,2) -- 双向
)
BEGIN
SELECT name, salary INTO p_name, p_sal
FROM employee
WHERE emp_id = p_id;
SET p_sal = p_sal * 1.1;
-- 加薪 10%
END;
-- 调用
SET @sal = 0;
CALL sp_param_demo(100, @name, @sal);
SELECT @name, @sal;
-- 拿到结果

---

6. 流程控制：IF / CASE / 循环

① IF

IF v_total >
100 THEN
SET v_msg = 'many';
ELSEIF v_total >
10 THEN
SET v_msg = 'some';
ELSE
SET v_msg = 'few';
END IF;

② CASE

CASE v_level
WHEN 1 THEN SET v_desc = 'A';
WHEN 2 THEN SET v_desc = 'B';
ELSE SET v_desc = 'C';
END CASE;

③ 循环：LOOP / WHILE / REPEAT

-- WHILE 示例：批量插入 100 条
CREATE PROCEDURE sp_batch_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100 DO
INSERT INTO log(msg) VALUES (CONCAT('msg-',i));
SET i = i + 1;
END WHILE;
END;

---

7. 游标（Cursor）：行级遍历

场景：对结果集逐行处理。

CREATE PROCEDURE sp_cursor_sum(OUT o_total DECIMAL(10,2))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_sal DECIMAL(10,2);
DECLARE cur CURSOR FOR SELECT salary FROM employee;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- ④ 异常处理
SET o_total = 0;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_sal;
IF done THEN
LEAVE read_loop;
END IF;
SET o_total = o_total + v_sal;
END LOOP;
CLOSE cur;
END;

---

8. 条件处理程序（Handler）：捕获异常

语法

DECLARE <handler_type>
  HANDLER FOR <condition_value>
    <statement>
      ;

• handler_type: CONTINUE / EXIT / UNDO(仅 DB2)
• condition_value: SQLSTATE '02000' / NOT FOUND / SQLEXCEPTION

示例：重复键不中断，继续跑

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
BEGIN
INSERT INTO err_log(info) VALUES ('duplicate key');
END;

---

9. 存储函数（Function）与过程的区别

对比项	存储过程	存储函数
返回值	0~n 个 OUT/INOUT 参数	必须返回一个标量
调用方式	CALL proc()	SELECT func() 可嵌在 SQL 内部
事务/游标	✅ 支持	✅ 支持（8.0+）
副作用	可更新表	若声明 DETERMINISTIC 仍可更新表

函数示例：计算个人所得税

CREATE FUNCTION f_tax(p_sal DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE v_tax DECIMAL(10,2);
IF p_sal <= 5000 THEN
SET v_tax = 0;
ELSE
SET v_tax = (p_sal - 5000) * 0.2;
END IF;
RETURN v_tax;
END;
-- 使用
SELECT name, salary, f_tax(salary) AS tax FROM employee;

---

10. 性能 & 运维 FAQ

Q1 存储过程真的比应用程序快？

• 减少网络往返，批量逻辑快 2~3 倍；但 CPU 密集运算不如应用层。
  Q2 如何调试？
• MySQL 8.0 可用 SET @debug = 1; 内部 SELECT debug_info;
• 或拆成临时表，每一步 INSERT INTO tmp_log VALUES (step, value);
  Q3 线上热更新？
• CREATE OR REPLACE 会加元数据锁，大并发时先建新版本，再 RENAME 切换。

---

一句话总结

存储过程 = “数据库内的脚本方法”，擅长封装、批量、事务、行级遍历；
牢记“复杂逻辑适度下沉、版本+权限+索引三件套”，就能在性能与安全之间取得最佳平衡。