从0到1搞懂 SQL 存储过程：原理、实战与避坑指南

原创于 2025-09-08 09:00:27 发布 · 848 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

SQL 专栏收录该内容

10 篇文章

订阅专栏

从0到1搞懂 SQL 存储过程：原理、实战与避坑指南

这是一篇为小白打造的由浅入深教程：读完你将知道什么是存储过程、为什么/何时使用、如何快速上手（MySQL 与 PostgreSQL 示例），以及常见坑与工程化最佳实践。

一、什么是存储过程（Stored Procedure）？

存储过程是预先保存在数据库中的一段 SQL 语句集合，可以带参数，支持变量、流程控制、异常处理。
本质：把“多条 SQL + 逻辑”封装成一个可复用的数据库端函数/过程，通过一次调用完成复杂操作。
价值：减少网络往返、封装业务数据逻辑、提升批量处理性能、统一安全与审计。

对比：

视图：封装查询，不含复杂流程控制；存储过程：可包含增删改查、循环、条件与事务控制。
函数（不同引擎差异）：多用于返回值计算；过程更偏向执行动作（PG11+ 也有真正的 PROCEDURE）。

二、何时该用存储过程？

需要在数据库内进行批处理、报表汇总、数据清洗。
高频、稳定的复合操作（例如下单写多表、结算日批量跑账）。
对外隐藏表结构与复杂逻辑，只暴露调用接口。
对延迟敏感，减少多次往返请求。

不建议过度使用的场景：

快速变化的业务规则，大量逻辑更适合应用层管理与版本控制。
强依赖多数据源/外部服务的流程，维护成本高。

三、快速上手：MySQL 实战

下面以 MySQL 为例，展示常用语法与模式。

1）创建最简存储过程

注意 MySQL 中需要修改分隔符避免与语句内的分号冲突。

DELIMITER //
CREATE PROCEDURE sp_get_user_by_id(IN p_user_id INT)
BEGIN
  SELECT id, name, email
  FROM users
  WHERE id = p_user_id;
END //
DELIMITER ;

-- 调用
CALL sp_get_user_by_id(1001);

2）IN / OUT / INOUT 参数

DELIMITER //
CREATE PROCEDURE sp_count_orders_by_user(
  IN p_user_id INT,
  OUT p_order_count INT
)
BEGIN
  SELECT COUNT(*) INTO p_order_count
  FROM orders
  WHERE user_id = p_user_id;
END //
DELIMITER ;

-- 调用并取出 OUT 参数
SET @cnt = 0;
CALL sp_count_orders_by_user(1001, @cnt);
SELECT @cnt AS order_count;

3）变量与流程控制（IF、CASE、WHILE）

DELIMITER //
CREATE PROCEDURE sp_apply_discount(IN p_user_id INT, IN p_rate DECIMAL(5,2))
BEGIN
  DECLARE v_level VARCHAR(10);
  DECLARE v_factor DECIMAL(5,2) DEFAULT 1.00;

  SELECT level INTO v_level FROM members WHERE user_id = p_user_id;

  IF v_level = 'gold' THEN
    SET v_factor = 0.80; -- 8 折
  ELSEIF v_level = 'silver' THEN
    SET v_factor = 0.90; -- 9 折
  ELSE
    SET v_factor = 1.00;
  END IF;

  -- 将折扣应用到该用户未支付订单
  UPDATE orders
  SET amount = amount * v_factor * (1 - p_rate)
  WHERE user_id = p_user_id AND status = 'pending';
END //
DELIMITER ;

4）事务与异常处理（HANDLER）

DELIMITER //
CREATE PROCEDURE sp_transfer(
  IN p_from INT,
  IN p_to   INT,
  IN p_amount DECIMAL(10,2)
)
BEGIN
  DECLARE exit HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
  END;

  START TRANSACTION;

  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;

  COMMIT;
END //
DELIMITER ;

要点：

DECLARE ... HANDLER 捕获异常，出错即回滚。
注意 DDL 可能隐式提交；在过程内尽量避免混用 DDL 与 DML 事务。

5）返回结果集 vs 返回 OUT 参数

直接 SELECT ...：客户端收到结果集（更适合查询类过程）。
OUT 参数：适合返回单值或少量聚合指标。

四、PostgreSQL 实战（PL/pgSQL）

PostgreSQL 11 之前常用 FUNCTION 承担“过程”角色；PG11+ 引入 PROCEDURE + CALL，且 PROCEDURE 能进行顶级事务控制。

1）等价函数（返回结果集）

CREATE OR REPLACE FUNCTION fn_get_user_by_id(p_user_id INT)
RETURNS TABLE(id INT, name TEXT, email TEXT)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
  SELECT u.id, u.name, u.email
  FROM users u
  WHERE u.id = p_user_id;
END;
$$;

-- 调用
SELECT * FROM fn_get_user_by_id(1001);

2）PROCEDURE 与事务、异常

CREATE OR REPLACE PROCEDURE sp_transfer(p_from INT, p_to INT, p_amount NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
  BEGIN
    PERFORM 1; -- 占位
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
  EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'transfer failed: %', SQLERRM;
    ROLLBACK; -- 仅在 CALL 的事务块层面有效
    RETURN;
  END;
END;
$$;

-- 调用
CALL sp_transfer(1, 2, 100.00);

提示：在 PG 中，FUNCTION 内不能 COMMIT/ROLLBACK；PROCEDURE 通过 CALL 可进行更灵活的事务控制（需在合适的事务上下文中）。

五、典型应用场景示例

1）日终汇总报表

输入：日期区间
过程：
- 批量扫描订单表，聚合出 GMV、客单价、各品类销量
- 将结果写入 daily_metrics 表（可幂等：先删后插或 UPSERT）

-- MySQL 示例（幂等写入）
DELIMITER //
CREATE PROCEDURE sp_build_daily_metrics(IN p_date DATE)
BEGIN
  REPLACE INTO daily_metrics(stat_date, gmv, orders, avg_order)
  SELECT p_date,
         SUM(amount) AS gmv,
         COUNT(*)    AS orders,
         AVG(amount) AS avg_order
  FROM orders
  WHERE DATE(paid_at) = p_date AND status = 'paid';
END //
DELIMITER ;

2）审计日志写入（统一入口）

DELIMITER //
CREATE PROCEDURE sp_write_audit(
  IN p_actor INT,
  IN p_action VARCHAR(64),
  IN p_target VARCHAR(64),
  IN p_payload JSON
)
BEGIN
  INSERT INTO audit_logs(actor_id, action, target, payload, created_at)
  VALUES(p_actor, p_action, p_target, p_payload, NOW());
END //
DELIMITER ;

六、优缺点速览

优点：

降低网络往返、批量处理性能好
封装复杂逻辑、隐藏表结构细节
权限控制更集中（只授予 EXECUTE）

缺点：

版本化与测试相对应用层更困难
跨数据库差异大，迁移成本高
过度依赖会造成“数据库里堆业务”，降低可维护性

七、工程化与最佳实践

版本管理：
- 使用迁移工具（如 Flyway、Liquibase、dbmate）管理 CREATE/ALTER/DROP PROCEDURE，确保可重复部署。
- 设计幂等：CREATE OR REPLACE（PG）、DROP ... IF EXISTS + CREATE（MySQL 8+ 部分对象支持）。
命名与分层：
- 前缀化按域名空间：如 sp_order_*、sp_user_*。
- 一个过程只做一件事，输入输出清晰。
性能：
- 优先保证 SQL 可用索引；避免非 SARGable 条件（函数包裹列、隐式类型转换）。
- 批处理分批提交，控制事务粒度，避免超长事务。
安全：
- 仅授予 EXECUTE 权限；敏感表不直接对应用账号开放。
- 动态 SQL 需参数化（PG 用 format() + quote_ident/quote_literal，MySQL 用预编译）。
测试：
- 准备固定测试数据集，写回归用例；比对结果集与副作用表。
- 为关键过程建立慢日志监控与执行计划审查。

八、常见坑与避坑技巧

MySQL 分隔符 DELIMITER 忘改，导致创建失败。
在过程内混入 DDL 触发隐式提交，打断事务一致性。
未捕获异常导致半更新；应设置 HANDLER（MySQL）或 EXCEPTION（PG）。
大事务长时间持锁，引发阻塞或死锁；应细分批次与合理索引。
过度返回大结果集，丧失“减少往返”的价值；返回必要字段。
跨引擎移植：语法、数据类型、异常与事务行为差异大。

九、小抄（Cheat Sheet）

MySQL 创建：DELIMITER // ... CREATE PROCEDURE ... END // DELIMITER ;
PG 函数：CREATE OR REPLACE FUNCTION ... LANGUAGE plpgsql，返回 TABLE 可直接 SELECT * FROM fn(...)
PG 过程：CREATE OR REPLACE PROCEDURE ...; CALL sp(...);
参数类型：IN / OUT / INOUT
异常：MySQL DECLARE ... HANDLER；PG EXCEPTION WHEN ... THEN ...
事务：在过程内谨慎控制，避免 DDL；PG 需 PROCEDURE 配合 CALL 进行顶级事务控制。