存储过程

导学

存储过程（Stored Procedure）是将一组 SQL 语句封装在数据库服务器端执行的程序单元。它可以接受参数、定义变量、使用流程控制，适合封装复杂的业务逻辑，减少网络往返。

定义

存储过程：预编译并存储在数据库中的一组 SQL 语句，可通过名称调用执行。存储过程在服务器端运行，减少了客户端与服务器之间的网络通信。

核心语法

DELIMITER //

CREATE PROCEDURE 过程名([参数列表])
[特性声明]
BEGIN
    -- SQL 语句和流程控制
END //

DELIMITER ;

参数模式

模式含义用途
IN传入参数（默认）向过程传递值
OUT传出参数从过程返回值
INOUT传入并传出传入值，过程修改后返回

完整示例一：无参数的存储过程

当前数据状态

首先建立示例表并插入数据：

CREATE DATABASE IF NOT EXISTS school;
USE school;

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前 employees 表数据：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

操作语句

创建一个无参数存储过程，用于统计员工总数：

DELIMITER //

CREATE PROCEDURE GetEmployeeCount()
BEGIN
    SELECT COUNT(*) AS total FROM employees;
END //

DELIMITER ;

-- 调用存储过程
CALL GetEmployeeCount();

操作后的结果

调用 CALL GetEmployeeCount() 后返回：

total
2

结果解读

GetEmployeeCount() 无参数过程直接查询 employees 表的总行数。无参数过程适合封装固定的查询逻辑，客户端无需传递任何值即可获取结果。

完整示例二：带 IN 参数的存储过程

当前数据状态

基于上面已建立的 employees 表，当前数据如下：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

操作语句

创建带 IN 参数的存储过程，按部门查询员工：

DELIMITER //

CREATE PROCEDURE GetEmployeesByDept(IN p_dept VARCHAR(20))
BEGIN
    SELECT emp_name, dept, score
    FROM employees
    WHERE dept = p_dept;
END //

DELIMITER ;

-- 调用：查询技术部的员工
CALL GetEmployeesByDept('技术部');

操作后的结果

调用 CALL GetEmployeesByDept('技术部') 后返回：

emp_namedeptscore
大翔技术部100
白歌技术部NULL

结果解读

IN 参数 p_dept 将值 '技术部' 传入过程内部，过程使用该值作为 WHERE 条件过滤数据。IN 是默认参数模式，可以省略不写。

再调用一次查询其他部门：

CALL GetEmployeesByDept('市场部');

返回：

emp_namedeptscore
(空)(空)(空)

完整示例三：带 OUT 参数的存储过程

当前数据状态

基于 employees 表：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

操作语句

创建带 OUT 参数的存储过程，返回统计值：

DELIMITER //

CREATE PROCEDURE GetDeptStats(
    IN p_dept VARCHAR(20),
    OUT p_count INT,
    OUT p_avg_score DECIMAL(5,2)
)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM employees WHERE dept = p_dept;

    SELECT AVG(score) INTO p_avg_score
    FROM employees WHERE dept = p_dept;
END //

DELIMITER ;

-- 调用前先定义用户变量接收 OUT 值
SET @cnt = 0, @avg = 0;
CALL GetDeptStats('技术部', @cnt, @avg);
SELECT @cnt AS 人数, @avg AS 平均分;

操作后的结果

执行后返回：

人数平均分
2100.0000

结果解读

  • p_dept 是 IN 参数，传入 '技术部' 作为查询条件
  • p_count 和 p_avg_score 是 OUT 参数，过程内部通过 SELECT ... INTO 将结果赋值给它们
  • 调用时必须传入用户变量 @cnt 和 @avg 来接收返回值
  • 过程执行完毕后，通过 SELECT @cnt, @avg 查看返回的结果

再测试其他部门：

SET @cnt2 = 0, @avg2 = 0;
CALL GetDeptStats('市场部', @cnt2, @avg2);
SELECT @cnt2 AS 人数, @avg2 AS 平均分;

返回：

人数平均分
0NULL

完整示例四：带 INOUT 参数的存储过程

当前数据状态

基于 employees 表：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

操作语句

创建带 INOUT 参数的存储过程，实现累加器功能：

DELIMITER //

CREATE PROCEDURE AddScoreToTotal(INOUT p_total DECIMAL(10,2), IN p_addend DECIMAL(5,2))
BEGIN
    SET p_total = p_total + p_addend;
END //

DELIMITER ;

-- 初始累加值为 0
SET @total_score = 0;

-- 加上员工的成绩
CALL AddScoreToTotal(@total_score, 100);

SELECT @total_score AS 总分;

操作后的结果

执行后返回：

总分
100

结果解读

  • INOUT 参数 p_total 既传入初始值，又接收修改后的值
  • 第一次调用时 @total_score = 0，加上 100 后变为 100
  • INOUT 参数 p_total 传入初始值，过程修改后返回新值
  • INOUT 适合需要传入值并在过程内修改后返回的场景

完整示例五：删除存储过程

操作语句

-- 安全删除：如果存在则删除
DROP PROCEDURE IF EXISTS GetEmployeeCount;

-- 删除后再次调用会报错
-- CALL GetEmployeeCount();  -- ERROR 1305 (42000): PROCEDURE school.GetEmployeeCount does not exist

结果解读

DROP PROCEDURE IF EXISTS 是安全的删除语法。如果存储过程不存在不会报错；如果存在则直接删除。删除后该过程名不能再被调用。

存储过程的特性声明

特性声明用于指定存储过程的执行特性和权限模型。

当前数据状态

基于 employees 表：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

操作语句

创建带特性声明的存储过程：

DELIMITER //

CREATE PROCEDURE SafeReadEmployees()
SQL SECURITY INVOKER
READS SQL DATA
BEGIN
    SELECT emp_id, emp_name, dept FROM employees;
END //

DELIMITER ;

-- 调用
CALL SafeReadEmployees();

操作后的结果

返回：

emp_idemp_namedept
1大翔技术部
2白歌技术部

结果解读

特性说明
SQL SECURITY INVOKER以调用者权限执行（安全，推荐）
SQL SECURITY DEFINER以创建者权限执行（需谨慎）
READS SQL DATA过程只读取数据
MODIFIES SQL DATA过程修改数据
NO SQL过程不含 SQL

SQL SECURITY INVOKER 表示过程执行时检查调用者的权限，而不是创建者的权限。这可以防止低权限用户通过调用高权限用户创建的过程来越权操作。

存储过程的优缺点

优点缺点
减少网络往返调试困难，错误信息不友好
封装复杂逻辑可移植性差，不同数据库语法差异大
预编译，执行快版本控制困难，逻辑分散在 DB 中
增强安全性（权限控制）难以单元测试

常见误区

误区正解
存储过程一定比程序层快减少网络开销是真的，但复杂逻辑在程序层更易维护。现代微服务架构中存储过程使用较少。
DEFINER 权限更安全相反，INVOKER 更安全，DEFINER 可能让低权限用户执行高权限操作。
存储过程可以返回结果集可以，但 OUT 参数更适合返回值。返回多结果集需要客户端特殊处理。

面试考点

Q：存储过程和函数的区别？

存储过程通过 CALL 调用，可以有 IN/OUT/INOUT 参数，可以返回多个结果集，不能用在 SQL 表达式中；函数通过名称调用，必须有返回值，只能有 IN 参数，可以嵌套在 SQL 中使用。

Q：存储过程的优缺点？

优点：减少网络开销、封装业务逻辑、预编译执行快。缺点：调试困难、可移植性差、版本控制不便、不适合复杂业务逻辑。

Q：SQL SECURITY DEFINER 和 INVOKER 的区别？

DEFINER 以创建者的权限执行；INVOKER 以调用者的权限执行。DEFINER 可能带来权限提升风险，生产环境推荐 INVOKER。

小结

  • 存储过程封装多条 SQL 和逻辑控制，在服务器端执行
  • 参数分为 IN、OUT、INOUT 三种模式
  • 减少了网络往返，但调试和维护成本较高
  • 推荐 SQL SECURITY INVOKER 以确保安全

下一章引子：存储过程可以执行但不返回值，而存储函数则像 SQL 中的自定义函数——必须有返回值，可以嵌套在查询中。