存储过程

存储过程（Stored Procedure）是预编译并存储在数据库中的一组 SQL 语句，像一个"数据库端的函数"，可以被反复调用。它能封装复杂业务逻辑、提升性能（预编译）、降低网络开销。本文以 MySQL 语法为主，兼顾通用标准。

为什么需要存储过程

优势	说明
减少网络往返	多条 SQL 打包成一次调用
预编译优化	首次编译后复用执行计划
封装复用	复杂逻辑集中管理
权限控制	只暴露存储过程，不开放表级权限
业务一致性	事务在存储过程内保证

劣势：与代码逻辑耦合、跨库迁移成本高、调试困难。所以互联网应用常把复杂逻辑放应用层，存储过程多用于数据完整性约束、批量任务、报表等场景。

创建存储过程

基本语法

sql

CREATE PROCEDURE 过程名 (参数列表)
BEGIN
    过程体（SQL 语句 + 流程控制）
END

DELIMITER 说明

MySQL 默认用 ; 作为语句结束符，但过程体内部也有 ;，所以创建前临时把结束符改成 //：

sql

DELIMITER //

CREATE PROCEDURE sp_hello()
BEGIN
    SELECT 'Hello, World';
END //

DELIMITER ;

完整示例

sql

-- 查询所有英雄
DELIMITER //
CREATE PROCEDURE sp_get_all_heros()
BEGIN
    SELECT id, name, hp_max FROM heros;
END //
DELIMITER ;

调用与删除存储过程

sql

-- 调用
CALL sp_get_all_heros();

-- 查看已定义的过程
SHOW PROCEDURE STATUS WHERE Db = '数据库名';

-- 查看过程定义
SHOW CREATE PROCEDURE sp_get_all_heros;

-- 删除
DROP PROCEDURE IF EXISTS sp_get_all_heros;

参数：IN / OUT / INOUT

参数类型	方向	说明
`IN`（默认）	传入	调用时传入，过程内不能改回
`OUT`	传出	过程把结果赋给它，调用方接收
`INOUT`	双向	既传值又取值

sql

-- 根据定位统计英雄数量，通过 OUT 返回
DELIMITER //
CREATE PROCEDURE sp_count_by_role(
    IN p_role VARCHAR(20),
    OUT p_count INT
)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM heros WHERE role_main = p_role;
END //
DELIMITER ;

-- 调用
CALL sp_count_by_role('战士', @cnt);
SELECT @cnt;   -- 取出 OUT 参数值

INOUT 示例：

sql

DELIMITER //
CREATE PROCEDURE sp_add(INOUT p_num INT)
BEGIN
    SET p_num = p_num + 10;
END //
DELIMITER ;

SET @n = 5;
CALL sp_add(@n);
SELECT @n;   -- 15

变量

局部变量（DECLARE）

在过程内用 DECLARE 声明，SET/SELECT ... INTO 赋值：

sql

DELIMITER //
CREATE PROCEDURE sp_calc()
BEGIN
    DECLARE v_total INT DEFAULT 0;     -- 声明局部变量，默认 0
    DECLARE v_avg DECIMAL(10,2);

    SELECT SUM(hp_max), AVG(hp_max)
      INTO v_total, v_avg               -- INTO 赋值
    FROM heros;

    SELECT v_total AS total_hp, v_avg AS avg_hp;
END //
DELIMITER ;

用户变量（@）

以 @ 开头，作用于整个会话，不依赖存储过程：

sql

SET @x = 10;
SELECT @x;

流程控制

IF ... ELSE

sql

DELIMITER //
CREATE PROCEDURE sp_check(IN p_hp INT)
BEGIN
    IF p_hp > 7000 THEN
        SELECT '高血量';
    ELSEIF p_hp > 5000 THEN
        SELECT '中血量';
    ELSE
        SELECT '低血量';
    END IF;
END //
DELIMITER ;

CASE

sql

DELIMITER //
CREATE PROCEDURE sp_role_type(IN p_role VARCHAR(20))
BEGIN
    CASE p_role
        WHEN '法师' THEN SELECT '远程/法术';
        WHEN '射手' THEN SELECT '远程/物理';
        WHEN '坦克' THEN SELECT '前排/肉盾';
        ELSE SELECT '其他';
    END CASE;
END //
DELIMITER ;

WHILE 循环

sql

-- 循环插入数字
DELIMITER //
CREATE PROCEDURE sp_fill(IN p_n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= p_n DO
        INSERT INTO numbers (val) VALUES (i);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

还有 REPEAT ... UNTIL 和 LOOP 循环，可根据条件选择。

游标（CURSOR）

游标用于逐行遍历查询结果集。存储过程里处理多行数据时使用：

sql

DELIMITER //
CREATE PROCEDURE sp_iterate()
BEGIN
    DECLARE v_name VARCHAR(50);
    DECLARE done INT DEFAULT 0;
    -- 声明游标：绑定查询
    DECLARE cur CURSOR FOR SELECT name FROM heros WHERE hp_max > 6000;
    -- 声明 NOT FOUND 处理器：遍历完置 done=1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    loop_label: LOOP
        FETCH cur INTO v_name;          -- 取一行
        IF done THEN
            LEAVE loop_label;           -- 遍历完退出
        END IF;
        SELECT v_name;                   -- 处理该行
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

游标步骤：DECLARE（声明）→ OPEN（打开）→ FETCH（取行）→ CLOSE（关闭）。必须声明 NOT FOUND 处理器，否则遍历到最后会报错。

错误处理（条件处理器）

用 DECLARE ... HANDLER 捕获异常，避免事务中断后无人回滚：

sql

DELIMITER //
CREATE PROCEDURE sp_transfer(
    IN p_from INT, IN p_to INT, IN p_amt DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;                        -- 出错回滚
        SELECT '转账失败';
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - p_amt WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amt WHERE id = p_to;
    COMMIT;
    SELECT '转账成功';
END //
DELIMITER ;

处理器	触发时机
`CONTINUE HANDLER`	出错后继续执行
`EXIT HANDLER`	出错后终止过程

存储过程 vs 函数 vs 触发器

特性	存储过程	函数	触发器
返回值	可无（OUT 参数）	必须有返回	无
调用方式	`CALL`	表达式/`SELECT` 中	事件自动触发
能否被 SELECT 用	不能	能	不能
事务	支持	有限	支持
用途	复杂业务/批量	计算返回单值	数据变更后自动处理

存储过程最佳实践

命名规范：sp_ 前缀 + 动词（sp_get_xxx）；
参数校验：入口校验输入合法性，避免非法数据进入；
结合事务：多步写操作包 START TRANSACTION，配错误处理器回滚；
控制复杂度：过长的存储过程难以维护，拆分成多个；
注意迁移成本：存储过程依赖特定数据库方言，跨库（MySQL→PG/Oracle）需重写；
应用层优先：互联网场景优先把业务逻辑放应用层，存储过程用于强约束与批处理。

总结

语法速查

操作	语法
创建	`CREATE PROCEDURE 名(参数) ... BEGIN ... END`
调用	`CALL 名(参数)`
删除	`DROP PROCEDURE 名`
局部变量	`DECLARE v INT DEFAULT 0`
赋值	`SET v = ...` / `SELECT ... INTO v`
条件	`IF / ELSEIF / ELSE`、`CASE`
循环	`WHILE / REPEAT / LOOP`
游标	`DECLARE/OPEN/FETCH/CLOSE`
异常	`DECLARE EXIT HANDLER FOR SQLEXCEPTION`

关键点

存储过程 = 预编译的 SQL 集合，封装复杂逻辑、减少网络往返；
参数 IN（入）/OUT（出）/INOUT（双向）控制数据方向；
流程控制（IF/CASE/循环）+ 变量 + 游标 + 错误处理器构成过程语言能力；
MySQL 需用 DELIMITER 修改结束符以创建多语句过程；
互联网场景权衡：复杂逻辑优先应用层，存储过程用于事务强约束、批处理、报表。