整理到第21篇终于轮到存储过程了。在SQL这块存储过程是个有点“争议”的话题有人喜欢把所有业务逻辑都塞进数据库有人一听存储过程就皱眉觉得它调试难、维护难、还绑死数据库。我在项目里两种极端都见过也踩过不少坑。这篇就按我自己的实践经验把存储过程从“是什么”到“怎么用”再到“哪些坑最容易踩”完整梳理一遍新手能当入门教程看老手也可以对照检查一下自己的用法。1. 存储过程到底是什么先理清这三个基本问题1.1 存储过程与普通SQL的本质区别存储过程简单说就是把一组SQL语句、流程控制逻辑、参数处理、错误处理、事务管理打包在一起以命名对象的形式存放在数据库服务端之后通过CALL或者EXEC调用一次就能执行整段逻辑。普通SQL是“即用即走”的客户端把语句发给数据库数据库解析、优化、生成执行计划执行完了就完事。存储过程则是“先建好反复用”的一次创建多次调用数据库还能把执行计划缓存下来下次直接复用。我经常打一个比方普通SQL相当于你每次吃饭都去厨房现场配菜、洗菜、切菜、炒菜而存储过程等于你先写好一份固定菜谱厨师照着菜谱一键出菜。出菜快、步骤稳定但是如果菜谱本身写得烂那翻车也是一翻一整桌。这里面有一个很重要的性能逻辑。一个业务操作如果涉及十几条SQL应用层一条一条发过去每次都有网络往返开销。数据量小的时候感觉不明显并发一上来这部分的延迟和数据库连接占用就非常可观。而把这些SQL放进一个存储过程里应用层只发一次调用请求数据库端按既定顺序执行网络开销瞬间降下来。这也是存储过程在传统单体架构时代那么流行的根本原因之一。1.2 存储过程、函数、触发器千万别记混很多初学者会把存储过程、函数、触发器混在一起面试被问到也容易答乱。我刚开始接触时也犯过这个错后来自己做了一个对照表才彻底分清楚。对比项存储过程函数触发器核心用途执行业务逻辑、批量操作、多步事务返回一个值或一张表供查询计算使用表发生INSERT/UPDATE/DELETE时自动执行返回值一般没有靠OUT参数或结果集返回必须有返回值标量或表没有返回值调用方式CALL / EXEC 显式调用直接在SELECT等语句中调用由数据库自动触发不能显式调用事务控制可以灵活控制事务提交、回滚一般不包含事务操作对事务控制有严格限制典型场景订单归档、报表汇总、批量同步金额计算、格式转换、名称拼接审计日志、数据校验、冗余字段同步三个概念的边界在不同数据库里有细微差别比如SQL Server的函数也能做一些流程控制Oracle里的函数还能用于SQL语句中。但你在设计阶段只要抓住上面这个判断框架基本不会选错。1.3 谁最需要弄懂存储过程后端开发者是最直接的受众因为你在写业务接口时总会有意无意碰到数据量大、事务复杂、多表联动的场景这块儿到底是应用层写循环还是数据库层写过程必须有一个清晰的判断依据。DBA就更不用说了日常巡检、性能优化、慢查询治理存储过程都是绕不开的目标。还有做数据清洗、ETL、数据迁移的工程师我见过太多拿着脚本一行行执行到一半失败然后从头再来的情况其实把这些活儿整理成存储过程恢复和重跑都会省心很多。2. 为什么要用存储过程价值与代价都得摆上桌2.1 真正有用之处预编译、网络开销、权限控制、事务封装先聊性能。数据库对存储过程的执行计划是有缓存机制的。以MySQL 8.0为例存储过程在首次执行后相关SQL的执行计划会被缓存后续调用直接复用SQL Server的计划缓存更是把参数化查询的复用做得非常成熟。也就是说一个被频繁调用的存储过程省去了解析和生成执行计划的成本。注意这不是说存储过程就一定比普通SQL快如果过程内部写得稀烂再快的执行计划也顶不住全表扫描加逐行游标。再聊网络开销。一次存储过程的调用客户端和数据库之间只发生一次“请求-响应”交互。相比之下同样的逻辑写十几条SQL逐条发送每一条都是一个Round Trip。在高延迟的网络环境下这个差异会被放大得很明显。我曾经在一个跨机房调用的业务里做过对比把六个查询步骤合并进一个存储过程后接口总耗时下降了将近一半。权限控制是很多人忽略的价值点。应用层连接数据库的账号完全可以只授予某个存储过程的EXECUTE权限而不给它直接查表的权限。这样表结构不会暴露给应用层敏感字段的直查也被挡住了属于一种很实用的安全边界。事务控制就更直接了多条SQL要么全部成功要么全部回滚这个原子性放在存储过程里比放在应用层里一层层try-catch要靠谱得多。2.2 代价同样现实调试、版本管理、迁移、耦合存储过程最大的痛点就是调试体验差。后端代码可以在IDE里打断点、看变量、看调用栈存储过程基本靠造数、跑日志、查临时表来定位问题。MySQL的存储过程连个像样的调试器都没有Oracle的PL/SQL Developer倒是能做一点但和现代IDE的调试体验还是没法比。版本管理也一样头疼。团队用Git管理代码但存储过程和数据库对象很难像Java、Go代码那样清晰走Code Review和分支合并。我在一个项目里见过最典型的乱象脚本目录里十几个同类文件分不清哪个是当前生产版本某个过程到底是哪个版本在跑全靠猜。数据库锁定问题更直接——存储过程用的是数据库私有语法。MySQL的DELIMITER、SQL Server的TOP和OUTPUT参数、Oracle的PL/SQL块换一个数据库就基本等于重写一遍。至于耦合存储过程一旦承担了核心业务逻辑数据库就变成了业务的一部分后续想换库、想扩展都会被它死死拽住。2.3 我的选型判断适合与不适合的场景这些年下来我有一套比较个人的判断标准未必适合所有团队但可以给你参考。适合用存储过程的场景批量数据处理比如订单归档、日志清理、历史数据统计多步骤强事务操作比如先扣库存再生成订单明细中间任何一步失败都要全部回滚定时执行的数据库端任务比如每天凌晨跑一次汇总报表还有多个应用共享同一套复杂查询逻辑时过程能保证口径统一。不太建议用存储过程的场景简单CRUD这类操作用ORM一行代码就搞定没必要绕一大圈业务逻辑快速迭代的系统比如营销活动规则一天三变每次改动都要走数据库发布流程效率太低还有需要对接消息队列、第三方API、复杂分布式事务的业务这类场景应用层才是正确位置。3. 四种主流数据库的存储过程语法差异对照着写就行3.1 MySQL/MariaDB先把DELIMITER搞明白MySQL存储过程最劝退新手的点就是DELIMITER。因为MySQL默认用分号作为语句结束符而存储过程内部又全都是分号结尾的SQL如果不把结束符临时换掉客户端看到第一个分号就以为整个语句已经结束了后续内容全变成一堆零散报错。正确写法是把结束符临时换成//或者$$。DELIMITER // CREATE PROCEDURE sp_get_user(IN uid INT, OUT uname VARCHAR(50)) BEGIN SELECT name INTO uname FROM users WHERE id uid; END // DELIMITER ;整个过程就三步先DELIMITER //改掉客户端的分句规则然后写存储过程写完之后DELIMITER ;改回来。要注意DELIMITER是客户端工具的命令不是SQL语法所以在Navicat、命令行、JDBC里使用时的体验会略有差异。调用用CALL sp_get_user(1, name);然后通过SELECT name;查看返回参数。3.2 SQL ServerT-SQL默认全是输入参数SQL Server的存储过程叫Procedure用T-SQL写。它的一个特点是参数默认都是输入方向的输出参数必须显式标记OUTPUT。比如上面那个例子在SQL Server里是这样CREATE PROCEDURE dbo.usp_GetUser UserId INT, UserName NVARCHAR(50) OUTPUT AS BEGIN SELECT UserName name FROM dbo.Users WHERE id UserId; END调用方式也完全不同DECLARE name NVARCHAR(50); EXEC dbo.usp_GetUser 1, name OUTPUT; SELECT name;SQL Server里没有DELIMITER的概念因为它的批处理分隔符GO是给客户端工具用的和存储过程内部定义无关。命名建议用usp_前缀我一个人维护的库里都坚持这个前缀时间长了检索起来非常方便。3.3 Oracle/openGaussPL/SQL全靠斜杠收尾Oracle的存储过程用PL/SQL写语法上和MySQL、SQL Server都差得比较大。它的特点是声明区放在IS/AS后面赋值用SELECT INTO整段程序结束时必须有个独立一行的斜杠/来告诉客户端提交这个块。CREATE OR REPLACE PROCEDURE sp_get_user( p_uid IN NUMBER, p_uname OUT VARCHAR2 ) IS BEGIN SELECT name INTO p_uname FROM users WHERE id p_uid; END sp_get_user; /调用时在SQL*Plus或PL/SQL Developer里可以这样VARIABLE v_name VARCHAR2(50); EXEC sp_get_user(1, :v_name); PRINT v_name;openGauss作为国产数据库里和Oracle兼容性做得比较好的一个存储过程语法大体沿用PL/SQL这套但在部分内置函数、游标属性、异常类型上还是有差异。我的建议是从Oracle迁到openGauss时不要指望完全无缝涉及存储过程的部分一定要重新过一遍语法细节。3.4 语法差异速查表对比项MySQL/MariaDBSQL ServerOracle / openGauss创建关键字CREATE PROCEDURECREATE PROCEDURECREATE OR REPLACE PROCEDURE语句块BEGIN...ENDBEGIN...END声明区IS/AS BEGIN...END参数输出OUTOUTPUTOUTOracle参数双向INOUT不支持同款用OUTPUT绕IN OUT结束标记DELIMITER //无独立一行 /调用方式CALL proc(...)EXEC proc ...EXEC/BEGIN proc(); END;报错处理DECLARE ... HANDLERTRY...CATCHEXCEPTION WHEN OTHERS这张表不需要背写之前查一眼就行。但建议你把每种数据库的差异点单独存一个文档真遇到跨库迁移时这份笔记能省你至少两天的时间。4. 堆料拆解参数、变量、流程控制、游标与错误处理4.1 参数模式IN、OUT、INOUT到底怎么选存储过程的参数方向直接决定了这个过程的接口设计。IN参数是传入值属于只读性质过程内部可以改它的临时副本但不影响调用者OUT参数是把值从过程内部传出去调用方只有等过程跑完才能拿到值INOUT则是既要接收传入值最后又要把处理结果返回。我的建议是接口设计时尽量不用INOUT因为它的语义最容易让人困惑。能拆成IN加OUT或者干脆用结果集返回的就不要做成一个双向参数。在实际项目里返回单值用OUT参数还行但要返回多行多列就别折腾参数了直接SELECT结果集出来让调用方按表结构去接收。4.2 变量与流程控制条件判断和循环的写法变量声明在不同数据库里不太一样。MySQL要在BEGIN块开头用DECLARE声明SQL Server直接在AS块里声明Oracle则在IS/AS后面声明。循环写法差别也不小下面给一个MySQL的完整示例DELIMITER // CREATE PROCEDURE p_fill(IN max_num INT) BEGIN DECLARE i INT DEFAULT 1; DROP TEMPORARY TABLE IF EXISTS tmp_nums; CREATE TEMPORARY TABLE tmp_nums (n INT); WHILE i max_num DO INSERT INTO tmp_nums VALUES (i); SET i i 1; END WHILE; SELECT * FROM tmp_nums; END // DELIMITER ;这里有个我反复踩坑后的习惯存储过程里能不用循环就不要用循环。绝大多数逐行处理的业务逻辑都可以改写成INSERT...SELECT、UPDATE...JOIN、或者窗口函数的集合操作。循环是最后手段不是默认手段。上面这个例子纯粹是演示语法实际填充序号表我会直接用递归CTE或者数字表三五行就搞定了。4.3 游标能不用就别用要用就一次讲明白游标是存储过程里最容易被滥用也最容易导致性能雪崩的东西。它的本质是一条一条取数据然后在过程内部逐行处理。这跟数据库底层的集合操作思维完全是反的。我见过一个报表存储过程处理5万行数据用了游标跑了40多分钟后来改写成两条集合SQL不到10秒出结果差距就这么夸张。但有些场景确实需要游标比如逐行调用外部接口、逐行根据临时计算结果再触发另一个过程。真要用把标准模板记牢。MySQL里游标的标准用法是这样DECLARE done INT DEFAULT 0; DECLARE v_id BIGINT; DECLARE cur CURSOR FOR SELECT id FROM tmp_tbl; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; OPEN cur; read_loop: LOOP FETCH cur INTO v_id; IF done 1 THEN LEAVE read_loop; END IF; -- 逐行处理逻辑比如UPDATE某张表 END LOOP; CLOSE cur;这里有一个新手极容易踩的坑MySQL里DECLARE的声明顺序是固定的必须先把变量都声明完再声明游标最后声明HANDLER顺序不对直接报1064语法错误。另外HANDLER里不小心漏了SET done 1游标就会一直循环到天荒地老。4.4 事务与错误处理批量操作不背锅的前提存储过程里做多步写操作必须把事务和异常处理安排好。MySQL里常见的做法是声明EXIT HANDLER遇到SQL异常就回滚DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; -- 业务SQL COMMIT;SQL Server对应的是TRY...CATCHOracle/openGauss对应的是EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;。每次写存储过程前先问自己一句这个过程如果中间失败数据是处于一致状态还是半截状态回答不清楚就别急着写业务逻辑先把事务边界画清楚。还有一点很重要操作大批量数据时不要把所有数据都塞进一个大事务里。几百万行数据在一个事务里UPDATE锁的持有时间会让你等到怀疑人生。必要时循环分批提交比如每处理1000行提交一次。这个思路跟猫头鹰抓老鼠是一样的目标大就分块咬一口吞得下才怪。5. 完整案例批量归档订单数据的存储过程5.1 需求与设计思路为什么要放数据库里跑我拿一个真实常见的需求来讲订单表里堆积了大量历史订单应用查询变慢需要把90天前已经完结的订单归档到历史表原表只保留近期热数据。这个需求如果放在应用层做得先查询出符合条件的订单再一条条插进历史表再删除原记录整个过程要写不少代码还要处理中途异常。如果做成存储过程按归档条件一次性INSERT加DELETE配合定时任务运维成本极低。用存储过程做这个事还有一个理由归档逻辑涉及数据一致性。插入历史表和删除原表之间不能插入别的操作如果只复制不删除原表越来越大只删除不复制历史数据直接丢。用事务把两步包起来要么都成功要么都失败这才是归档该有的样子。5.2 MySQL完整实现从建表到调用先准备两张表。原表orders和历史表orders_history结构保持一致历史表额外加一个archived_at记录归档时间。CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL, created_at DATETIME NOT NULL, KEY idx_status_created (status, created_at) ); CREATE TABLE orders_history LIKE orders; ALTER TABLE orders_history ADD COLUMN archived_at DATETIME NULL;归档存储过程完整代码如下DELIMITER // CREATE PROCEDURE sp_archive_orders(IN p_days INT) BEGIN DECLARE v_cutoff DATETIME; DECLARE v_insert_count INT DEFAULT 0; DECLARE v_delete_count INT DEFAULT 0; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; INSERT INTO archive_log(log_type, message) VALUES (ERROR, orders archive failed); COMMIT; END; SET v_cutoff DATE_SUB(NOW(), INTERVAL p_days DAY); START TRANSACTION; INSERT INTO orders_history (id, order_no, user_id, amount, status, created_at, archived_at) SELECT id, order_no, user_id, amount, status, created_at, NOW() FROM orders WHERE status 2 AND created_at v_cutoff; SET v_insert_count ROW_COUNT(); DELETE FROM orders WHERE status 2 AND created_at v_cutoff; SET v_delete_count ROW_COUNT(); INSERT INTO archive_log(log_type, message, affected_rows) VALUES (INFO, archive ok, v_insert_count v_delete_count); COMMIT; END // DELIMITER ;这里说几个关键细节。ROW_COUNT()只能取最近一条语句影响的行数所以INSERT之后立刻取一次DELETE之后再取一次千万别在中间插其他SQL语句。日志表archive_log先建好字段至少包括id、log_type、message、affected_rows、created_at。调用直接CALL sp_archive_orders(90);。如果数据量特别大建议把DELETE改成按主键范围循环删除或者参考pt-archiver那个思路每次只处理一小批然后循环避免单条大事务把表锁死。这种过程配合MySQL的Event Scheduler可以把它设成每天凌晨自动执行相当于一个轻量版的数据库定时任务。5.3 换到SQL Server和Oracle时改哪些如果这套逻辑要换成SQL Server语法上的改动不算大不需要DELIMITERINSERT SELECT和DELETE照写影响行数用ROWCOUNT取事务用BEGIN TRANSACTION、COMMIT、ROLLBACK异常处理换成BEGIN TRY...BEGIN CATCH。Oracle的差异就明显一些没有自增主键插入历史表时ID需要考虑用序列生成时间函数用SYSDATE行数用SQL%ROWCOUNT异常块放在EXCEPTION里处理。迁移的时候我一般不会边改边试而是先把语法差异列出来把每个差异点对应到原逻辑中的具体位置再动手。这样能减少一半以上的返工。6. 实际踩坑记录常见问题与排查技巧6.1 创建阶段常见报错与解法存储过程创建期的大多数报错都能通过看错误信息定位但有几个错误信息特别容易误导人。第一个是MySQL的“You have an error in your SQL syntax”多半是DELIMITER没改或者BEGIN...END不配对。第二个是“PROCEDURE already exists”MySQL不支持CREATE OR REPLACE PROCEDURE必须DROP PROCEDURE IF EXISTS之后再重建。第三个是注释符踩坑MySQL里--注释要求后面必须跟一个空格或者控制字符写成--注释这种会被当成语句的一部分直接报语法错。还有一个老手也会犯的错过程名用了保留字或函数名。比如把过程起名叫sp_update没问题但直接叫update就会触发保留字冲突。我的习惯是所有存储过程一律加sp_前缀既能避开保留字又方便检索。6.2 运行阶段锁、权限、长事务存储过程跑得很慢最常见的三个原因表缺索引、过程内部大量游标循环、长时间持锁导致阻塞叠加。排查思路是先在测试库用真实数据量跑一次开启慢查询日志或者看执行计划重点检查过程中每条SQL的执行计划是否走了预期索引。权限问题也很隐蔽。应用连接账号如果只有表查询权限没有存储过程的EXECUTE权限调用时会报权限不足。正确做法是只授权EXECUTE不要顺手把整个库的DDL权限都授出去。这是很多安全评审里会查的点。长事务这块再强调一句事务里包含大范围UPDATE/DELETE时行锁会一直持有到事务结束。这个过程里其他会话的同表操作全部被堵住。解决方向要么缩小事务粒度要么分批提交要么把操作放在业务低峰期执行。6.3 业务代码调用MyBatis、EF、Prisma遇到的那些坎后端框架调用存储过程最常见的坑是参数映射和返回值接收。MyBatis里要用statementTypeCALLABLE输出参数在Mapper的Java方法上用OutParam注解或者XML里的modeOUT声明接收时还要手动从CallableStatement里取。Entity Framework用FromSqlRaw或ExecuteSqlRaw调数据库优先模式下问题不大但一旦涉及输出参数代码会明显变啰嗦。Node.js里用Prisma的话可以用prisma.$queryRaw执行CALL语句但OUT参数支持得很别扭我通常会改成让存储过程直接返回结果集再用queryRaw去接收。sqlsugar是.net生态里一个很流行的ORM它对存储过程的调用封装得比较友好通过db.Ado.UseStoredProcedure()可以指定执行存储过程参数也用匿名对象传。但注意用ORM调存储过程时应用层的参数化机制不会处理数据库内部的动态SQL所以存储过程内部的拼接逻辑才更要小心。6.4 迁移和版本控制别让存储过程成为黑匣子存储过程最怕变成没人看得懂的“黑匣子”。我的习惯是每个过程文件独立存放在项目里的数据库脚本目录下文件名带版本号或日期过程头部写清楚用途、参数说明、修改人、修改日期。DDL变更用Flyway或Liquibase管理至少能做到环境间可重现。跨数据库迁移时工具只能搬结构和基础数据存储过程里的私有语法必须逐个手工核对。比如最近在做的国产数据库迁移Oracle的过程迁移到openGauss单是异常类型和游标属性的差异就排查了快一周。迁移后还要做一次全量对比测试重点验证边界参数。6.5 安全底线动态SQL的参数化我见过不少存储过程里用动态SQL拼接字符串然后直接执行。这种写法一旦拼进用户可控的输入就等于给注入攻击开了大门。如果实在要动态拼SQL必须用绑定变量传参。MySQL用PREPARE/EXECUTE/USING方式Oracle用EXECUTE IMMEDIATE ... USINGSQL Server用sp_executesql加参数列表。另外给应用授权时也要遵循最小权限只给能完成业务的最小权限范围。7. 最后一点实在话存储过程不是万能药我在实际项目里见过两拨人一拨啥都想往数据库里塞结果数据库变成巨型业务怪物改一个字段要前后端加DBA一起开会另一拨彻底不用存储过程结果发现复杂批处理和跨服务共享逻辑在应用层写起来又长又难维护。我的做法是给存储过程划定使用范围复杂批处理、强事务、多应用共享的统计口径优先考虑存储过程快速迭代的前台业务、简单读写、涉及外部系统调用的逻辑放应用层。另外每次在过程里加动态SQL或者循环逻辑前先问自己一句“这玩意儿真有必要吗”很多时候答案是“没有”。存储过程是个很好用的工具但它跟任何工具一样用得顺手的前提是知道什么时候该用它什么时候不该用。