存储过程

封装 SQL 逻辑 / 参数传递 / 变量 / 游标 / 触发器简介

什么是存储过程

到现在为止，我们写的每条 SQL 都是手敲一遍就跑。但实际开发中，有些操作会反复执行——比如"给入职满一年的员工涨薪 5%"，每次都重写一遍 UPDATE 很麻烦。

存储过程就是把一段 SQL 逻辑存到数据库里，起个名字，以后直接调用。

手敲 SQL（每次都要写）                  存储过程（写一次反复用）

每次：                                  一次定义：
写 UPDATE → 改日期 → 检查条件            CREATE PROCEDURE annual_raise()
跑 SQL                                    BEGIN
                                         UPDATE users SET salary = salary * 1.05
每次：                                      WHERE DATEDIFF(CURDATE(), hire_date) > 365;
写 ... → 改 ... → 检查 ...              END;
跑 SQL
                                        每次只需：
                                         CALL annual_raise();

为什么用存储过程

不用	用了
每次手写 SQL，可能漏条件	逻辑封装好，调用不会出错
改逻辑要到处找代码	只改存储过程一处
SQL 在应用里写死，调优要改代码	SQL 在数据库里，DBA 单独调
网络来回传多条 SQL	只传一个 CALL 指令

当然也有代价：存储过程的语法写起来比普通 SQL 啰嗦，调试也不如应用层方便。适合逻辑简单、调用频繁、对数据库操作集中的场景。

第一个存储过程

最简单的版本——无参数，就是封装一段查询：

-- 修改分隔符（因为存储过程内部会有分号）
DELIMITER //

CREATE PROCEDURE get_beijing_users()
BEGIN
    SELECT name, age, salary
    FROM users
    WHERE city = '北京';
END//

-- 恢复分隔符
DELIMITER ;

📝 DELIMITER // 是临时把语句结束符从 ; 改成 //，这样存储过程内部的 ; 不会被误认为是整个 CREATE PROCEDURE 的结束。写完再改回来。

调用：

CALL get_beijing_users();

+------+------+----------+
| name | age  | salary   |
+------+------+----------+
| 张三 |   25 | 10000.00 |
| 孙七 |   22 |  7500.00 |
| 郑十 |   27 |  7800.00 |
+------+------+----------+

和跑一条 SELECT 结果一样，但可以反复调用。

带参数的存储过程

存储过程真正有用的地方是传参数。

IN 参数：传入值

DELIMITER //

CREATE PROCEDURE get_users_by_city(IN city_name VARCHAR(50))
BEGIN
    SELECT name, age, salary
    FROM users
    WHERE city = city_name;
END//

DELIMITER ;

调用时传参：

CALL get_users_by_city('上海');
CALL get_users_by_city('杭州');

换城市只需改参数，不用改存储过程。

多参数

DELIMITER //

CREATE PROCEDURE get_users_by_salary(
    IN min_salary DECIMAL(8,2),
    IN max_salary DECIMAL(8,2)
)
BEGIN
    SELECT name, city, salary
    FROM users
    WHERE salary BETWEEN min_salary AND max_salary
    ORDER BY salary DESC;
END//

DELIMITER ;

-- 调用
CALL get_users_by_salary(8000, 12000);

OUT 参数：返回值

OUT 参数用来往外传值：

DELIMITER //

CREATE PROCEDURE count_by_city(
    IN city_name VARCHAR(50),
    OUT total INT
)
BEGIN
    SELECT COUNT(*) INTO total
    FROM users
    WHERE city = city_name;
END//

DELIMITER ;

-- 调用（声明一个变量来接结果）
CALL count_by_city('北京', @count);
SELECT @count;   -- 3

INTO total 把查询结果塞进 OUT 参数，调用时用 @变量名 来接。

INOUT 参数：又进又出

DELIMITER //

CREATE PROCEDURE double_value(INOUT val INT)
BEGIN
    SET val = val * 2;
END//

DELIMITER ;

SET @num = 10;
CALL double_value(@num);
SELECT @num;   -- 20

变量和流程控制

存储过程内部可以声明变量、写判断、写循环——像个微型程序。

变量

DELIMITER //

CREATE PROCEDURE salary_stats()
BEGIN
    DECLARE avg_sal DECIMAL(8,2);
    DECLARE max_sal DECIMAL(8,2);
    DECLARE min_sal DECIMAL(8,2);

    SELECT AVG(salary), MAX(salary), MIN(salary)
    INTO avg_sal, max_sal, min_sal
    FROM users;

    SELECT avg_sal AS 平均值, max_sal AS 最高, min_sal AS 最低;
END//

DELIMITER ;

DECLARE 声明局部变量，SELECT INTO 赋值。

IF 判断

DELIMITER //

CREATE PROCEDURE salary_grade(IN user_id INT)
BEGIN
    DECLARE user_salary DECIMAL(8,2);

    SELECT salary INTO user_salary
    FROM users WHERE id = user_id;

    IF user_salary > 12000 THEN
        SELECT '高薪' AS 等级;
    ELSEIF user_salary > 8000 THEN
        SELECT '中等' AS 等级;
    ELSE
        SELECT '偏低' AS 等级;
    END IF;
END//

DELIMITER ;

循环

DELIMITER //

CREATE PROCEDURE print_numbers(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= n DO
        SELECT i;
        SET i = i + 1;
    END WHILE;
END//

DELIMITER ;

💡 存储过程的循环性能远不如在应用层循环，尽量避免在存储过程里写大量循环。

管理存储过程

查看

SHOW PROCEDURE STATUS WHERE Db = 'tutorial';   -- 列出所有存储过程
SHOW CREATE PROCEDURE get_beijing_users;       -- 看完整定义

删除

DROP PROCEDURE IF EXISTS get_beijing_users;

修改

MySQL 不支持 ALTER PROCEDURE 改逻辑，只能删了重建。可以用 DROP + CREATE 或直接 CREATE OR REPLACE（MySQL 8.0+ 不完全支持 OR REPLACE 在 PROCEDURE 上，建议显式 DROP 再 CREATE）。

游标（知道就行）

游标可以逐行处理结果集，但性能和复杂度都不理想：

DELIMITER //

CREATE PROCEDURE demo_cursor()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE user_name VARCHAR(20);
    DECLARE cur CURSOR FOR SELECT name FROM users WHERE city = '北京';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO user_name;
        IF done THEN LEAVE read_loop; END IF;
        SELECT user_name;   -- 逐行输出
    END LOOP;
    CLOSE cur;
END//

DELIMITER ;

⚠️ 游标的使用场景极其有限——绝大多数"逐行处理"的需求用 JOIN + 聚合就能完成，没必要上循环。知道有这个工具，但不建议主动用它。

触发器（顺带一提）

触发器是自动执行的存储过程——当某张表发生 INSERT / UPDATE / DELETE 时，自动触发一段逻辑：

-- 每次往 orders 插入数据时，自动记录一条日志
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
INSERT INTO order_logs (order_id, created_at) VALUES (NEW.id, NOW());

触发器的坑很多（调试难、排查难、死锁风险），实际项目中对它都很克制。知道概念即可。

什么时候用存储过程？

适合	不适合
复杂报表，多条 SQL 组合计算	简单的单条增删改查
批量数据处理（定时任务）	业务逻辑频繁变化
对性能要求高，减少网络往返	跨表业务逻辑（放应用层更好维护）

💡 实用建议：存储过程不是必需品，但学会它能让你在面对"定时涨薪"、"月底生成报表"这类需求时多一个选择。简单的用着舒服，复杂的还是交给应用层。

小结

存储过程把 SQL 逻辑存到数据库里，调用时一句 CALL 搞定：

1. 封装重复逻辑 — 经常执行的 SQL 组合，写成存储过程一劳永逸
2. 参数让过程变灵活 — IN 传值进去，OUT 传值出来，INOUT 又进又出
3. 内部能写判断和循环 — DECLARE 变量、IF/ELSEIF 判断、WHILE 循环
4. 游标知道有就行 — 逐行处理的能力，但绝大多数场景 JOIN + 聚合更高效
5. 触发器自动执行 — INSERT/UPDATE/DELETE 时自动触发，但排查困难，生产环境慎用

💡 存储过程适合逻辑简单、调用频繁、对数据库操作集中的场景。简单的用它很舒服，复杂的还是放应用层更好维护。

自主练习

1. 写一个不带参数的存储过程，列出所有工资 > 10000 的人
2. 写一个带 IN 参数的存储过程，根据城市查人，并按工资排序
3. 写一个带 OUT 参数的存储过程，返回指定城市的平均工资
4. 写一个有 IF 判断的存储过程，传入名字，工资 > 10000 显示"高薪"，否则显示"继续努力"