存储过程与函数

约 1073 字大约 4 分钟

布欧-Lewyon

2026-05-15

存储过程（Stored Procedure）和存储函数（Stored Function）是一组预编译的 SQL 语句，存储在数据库服务端。调用时只需传入参数，无需逐条发送 SQL。

存储过程 vs 存储函数

对比维度	存储过程（PROCEDURE）	存储函数（FUNCTION）
返回值	可有可无（OUT 参数可返回多个值）	必须返回一个值
调用方式	`CALL proc()`	`SELECT func()`
事务控制	允许 `COMMIT` / `ROLLBACK`	不允许
SQL 语句限制	几乎任意 SQL	不允许 SELECT ... INTO OUTFILE 等

创建存储过程

DELIMITER $$   -- 将分隔符改为 $$，因为过程体内包含 ;

CREATE PROCEDURE get_employee_by_dept(IN dept_name VARCHAR(50))
BEGIN
    SELECT e.name, e.salary
    FROM employee e
    JOIN department d ON e.dept_id = d.id
    WHERE d.name = dept_name;
END$$

DELIMITER ;   -- 改回默认分隔符

-- 调用
CALL get_employee_by_dept('技术部');

参数模式

模式	说明
`IN`（默认）	传入参数，过程内不能修改传入的值
`OUT`	传出参数，将过程中的值赋给调用方变量
`INOUT`	既传入又传出

DELIMITER $$

CREATE PROCEDURE get_dept_salary_stats(
    IN dept_id INT,
    OUT avg_sal DECIMAL(10,2),
    OUT max_sal DECIMAL(10,2)
)
BEGIN
    SELECT AVG(salary), MAX(salary)
    INTO avg_sal, max_sal
    FROM employee
    WHERE dept_id = dept_id;
END$$

DELIMITER ;

-- 调用（用 @ 定义用户变量接收 OUT 值）
CALL get_dept_salary_stats(1, @avg, @max);
SELECT @avg, @max;

流程控制

条件语句

DELIMITER $$

CREATE PROCEDURE check_salary(
    IN emp_id INT,
    OUT result VARCHAR(50)
)
BEGIN
    DECLARE sal DECIMAL(10,2);

    SELECT salary INTO sal FROM employee WHERE id = emp_id;

    IF sal IS NULL THEN
        SET result = '员工不存在';
    ELSEIF sal > 20000 THEN
        SET result = '高薪';
    ELSEIF sal > 10000 THEN
        SET result = '中薪';
    ELSE
        SET result = '低薪';
    END IF;
END$$

DELIMITER ;

循环

DELIMITER $$

CREATE PROCEDURE generate_numbers(IN max_val INT)
BEGIN
    DECLARE i INT DEFAULT 1;

    -- 创建临时表存放结果
    CREATE TEMPORARY TABLE IF NOT EXISTS numbers (n INT);

    WHILE i <= max_val DO
        INSERT INTO numbers VALUES (i);
        SET i = i + 1;
    END WHILE;

    SELECT * FROM numbers;
    DROP TEMPORARY TABLE numbers;
END$$

DELIMITER ;

游标

在存储过程中逐行处理结果集：

DELIMITER $$

CREATE PROCEDURE process_high_salary()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_name VARCHAR(50);
    DECLARE emp_salary DECIMAL(10,2);

    -- 定义游标
    DECLARE cur CURSOR FOR
        SELECT name, salary FROM employee WHERE salary > 15000;

    -- 定义 NOT FOUND 处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO emp_name, emp_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 对每行做处理
        INSERT INTO high_salary_log VALUES (emp_name, emp_salary);
    END LOOP;

    CLOSE cur;
END$$

DELIMITER ;

存储函数

DELIMITER $$

CREATE FUNCTION get_annual_salary(emp_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC   -- 相同输入总是相同输出
READS SQL DATA  -- 函数只读数据
BEGIN
    DECLARE monthly DECIMAL(10,2);
    SELECT salary INTO monthly FROM employee WHERE id = emp_id;
    RETURN monthly * 13;   -- 假设 13 薪
END$$

DELIMITER ;

-- 使用
SELECT name, get_annual_salary(id) AS annual FROM employee;

函数特性声明：

声明	说明
`DETERMINISTIC`	相同输入返回相同结果
`NO SQL`	不包含 SQL 语句
`READS SQL DATA`	读数据但不写
`MODIFIES SQL DATA`	写数据

适用场景与注意事项

不推荐使用存储过程的场景：

原因	说明
调试困难	没有 IDE 级别的断点调试（MySQL 8.0 的 DEBUG 功能有限）
版本管理	存储过程无法像代码一样做 Git 版本控制
数据库迁移	从 MySQL 换到 PostgreSQL 需要重写存储过程
性能与扩展	逻辑分散在数据库层，难以水平扩展

推荐使用存储过程的场景：

需要严格的事务封装（如资金扣减 + 日志记录）
对延迟极敏感的操作（减少应用与数据库之间的往返次数）
数据库管理员（DBA）执行的运维任务

管理存储过程

-- 查看存储过程状态
SHOW PROCEDURE STATUS WHERE Db = 'demo';

-- 查看定义
SHOW CREATE PROCEDURE get_employee_by_dept;

-- 删除
DROP PROCEDURE IF EXISTS get_employee_by_dept;

小结

存储过程用 CALL 调用，可带 IN/OUT/INOUT 参数；存储函数用 SELECT 调用，必须返回一个值。
支持 IF/CASE/LOOP/WHILE 流程控制和游标逐行处理。
存储过程的优点是减少网络往返、封装业务逻辑；缺点是调试难、版本管理难、迁移成本高。
目前多数业务选择将逻辑放在应用层实现，存储过程仅用于对一致性要求极高的场景。