尧图网页设计 
简介一份精选自实际工作场景的Oracle存储过程案例资料包包含118个完整案例面向数据库开发工程师、运维人员及存储过程初学者解决从理论语法到真实业务落地之间的连接问题。压缩包共120个文件以118个带完整注释的SQL案例为主体另含1份PDF开发指南和1个说明文档整体仅649KB下载与查阅都很方便。案例按用途分为两类61个真实业务案例覆盖客户日周月统计、绩效考核、周期报表、抽检数据处理、消息推送、订单处理等贴近生产的场景57个通用案例则聚焦序列、表及列操作、主键唯一索引约束、事务管理、权限控制、导出文件、视图、迭代、备份、参数校验等可复用模块能大幅降低重复开发成本。目前已有1689人学习下载配合PDF从入门到进阶的讲解以及每个案例的注释说明既适合自学提高也能作为团队内部的代码参考手册。 做了这么多年Oracle开发和性能调优我一直觉得存储过程这个技术点最缺的不是语法书而是场景感。语法书会把 CREATE OR REPLACE PROCEDURE 的格式写给你看但真到了业务里面对一张要修的数据表、一个要接的接口、一个慢到要报警的定时任务很多人还是会卡住。这套118个真实应用场景的Oracle存储过程案例及开发指南就是站在“从入门到熟练使用”这条主线上把语法落回到真实业务里的问题集适合刚接触PL/SQL的新手也适合被各种诡异报错折磨过一段时间的运维和开发。我整理这118个案例的时候并不是按语法点硬编的而是先把项目里实际写过的存储过程全部翻出来按业务场景筛了一遍再去补充日常需求里高频出现的技术点。这篇博文就围绕这套案例库的设计思路、拆解方法、典型实操和排障经验展开希望能帮你建立起自己的场景库而不是背语法。1. 场景库的整体设计思路为什么是118个案例1.1 用业务场景反推技术点而不是用语法堆教程语法手册和场景案例的区别有点像字典和菜谱字典告诉你每个字怎么写、什么意思但不会告诉你番茄炒蛋该先放油还是先放盐。项目里遇到的真实需求千奇百怪可底层技术点其实就那么二三十个。118个真实场景的价值就是把这些技术点放到不同业务条件下重新组合让你在面对新需求的时候有模板可依。比如同样是 UPDATE 语句放进存储过程里就有好几种玩法按主键更新单条按条件批量更新遍历游标逐行更新用 BULK COLLECT 分批更新甚至动态拼接表名再更新。这些玩法并不是为了炫技而是对应不同数据量和不同业务约束。案例里会明确告诉你什么场景用哪种写法为什么选这种写法换了写法又会踩什么坑。1.2 118个案例的四阶段划分我在设计这套案例时把它分成了四个阶段目的是降低学习曲线也方便你按需查阅阶段案例数量覆盖范围代表场景入门15个基础语法、单表增删改查、序列、判断循环新增员工、修改部门、查询单条记录常用40个分页查询、批量更新、业务编号生成、报表统计模糊搜索分页、批量状态更新、日报统计进阶40个动态SQL、游标嵌套、批量收集、异常重试大数据量修复、异构表数据迁移、接口数据落库熟练23个自治事务、调度任务、并行处理、性能调优定时归档、日志记录、任务监控这四部分的划分依据是日常Oracle开发里技术点出现的频率。入门案例解决“能不能写出来”的问题常用案例解决“写得对不对”的问题进阶案例解决“跑得快不快”的问题熟练案例解决“系统稳不稳”的问题。你不需要按顺序全部刷完遇到具体问题直接跳到对应阶段去查就行。2. 拆解案例的正确姿势核心语法与编码规范2.1 每个案例都先拆成四个要素很多初学者拿到一个存储过程案例上来就盯着代码一行行看这是效率最低的方式。我一般建议先拆成四个要素业务背景、输入输出、处理流程、性能约束。拿“批量更新客户等级”举例。业务背景是营销部门要根据近30天消费金额重新给客户打标签输入是金额阈值和生效日期输出是更新行数性能约束是客户表可能有几百万行不能一把梭全部UPDATE。拆完这些再去看代码你才能明白为什么要用游标、为什么要分批提交、为什么需要记录日志。如果只是背代码换个表、换个字段你就不会了。所以在118个案例里每个案例我都先写业务背景再贴代码最后补优化思路。你照着这个思路去拆自己项目里的存储过程也会清晰很多。2.2 参数模式与变量命名细节决定成败Oracle存储过程的参数有三种模式IN、OUT、IN OUT。IN是传入参数过程内部不能改它的值OUT是传出参数只能赋值给调用者IN OUT是双向既能传入又能传出修改。我习惯用一个例子帮人理解IN像把材料递给厨师厨师只能按材料做菜不能退回去换OUT像传菜出来的窗口IN OUT则像顾客点单时提出少盐厨师根据要求调整后再端出来。变量声明里最值得养成习惯的是使用 %TYPE 和 %ROWTYPE。比如v_salary emp.salary%TYPE; v_emp emp%ROWTYPE;这样写的好处是如果表结构里 salary 字段从 NUMBER(10,2) 改成 NUMBER(12,2)存储过程不需要跟着改重新编译就能适配。如果写死 VARCHAR2(20)字段一扩容就容易出现 ORA-06502 数值溢出错误。命名规范方面我自己的习惯是存储过程用 PROC_业务_动作参数用 P_ 前缀变量用 V_ 前缀游标用 C_ 前缀异常用 E_ 前缀。这套规范在团队协作里尤其有用别人看你的代码三秒钟就知道哪个是参数、哪个是局部变量排查问题能省一半时间。2.3 异常与事务存储过程里最容易翻车的地方异常处理有个很大的误区WHERE OTHERS 后面什么都不写直接把错误吞掉。这样做的后果是过程“成功”执行了但数据可能只处理了一半而且你不知道哪里出了问题。更合理的模板是这样BEGIN -- 业务逻辑 COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(没有找到对应数据); ROLLBACK; WHEN OTHERS THEN ROLLBACK; RAISE; END;事务控制的分寸也要拿捏。我见过很多批量导入过程每循环一行就 COMMIT 一次结果跑到第3万行报错前面2万多次提交全部生效想回滚都回滚不了。正确做法是批处理场景按批次提交比如每500行或每1000行提交一次单个业务动作的场景让调用方统一控制事务存储过程内部不要随便 COMMIT。这个原则在118个案例里反复出现因为它直接决定了数据安全。3. 从入门到熟练几个有代表性的实操案例串讲3.1 入门单表增删改查与自增序号的获取每个新手写的第一个存储过程几乎都是单表INSERT。这里最容易忽略的是主键怎么生成。Oracle不像MySQL有自增字段一般靠序列。一个最基础的员工新增过程可以这样写CREATE OR REPLACE PROCEDURE PROC_ADD_EMPLOYEE( p_name IN VARCHAR2, p_dept IN VARCHAR2, p_salary IN NUMBER ) AS v_empno NUMBER; BEGIN SELECT seq_empno.NEXTVAL INTO v_empno FROM dual; INSERT INTO emp(empno, name, dept, salary, hire_date) VALUES (v_empno, p_name, p_dept, p_salary, SYSDATE); DBMS_OUTPUT.PUT_LINE(新增员工编号: || v_empno); COMMIT; END;插入之后推荐用 SQL%ROWCOUNT 做一个影响行数判断防止INSERT语句写错却没插进去IF SQL%ROWCOUNT 0 THEN RAISE_APPLICATION_ERROR(-20001, 没有插入任何记录); END IF;这个小技巧非常实用。真实项目里存储过程经常被别的程序调用如果插入失败外层毫无感知后面整个流程都会跑偏。3.2 常用阶段分页查询与模糊搜索的存储过程封装分页查询在报表系统里几乎天天用。Oracle 12c以上可以写 OFFSET FETCH但11g还大量存在所以兼容写法用的是 ROWNUM 嵌套。一个返回游标结果集的分页过程可以这样写CREATE OR REPLACE PROCEDURE PROC_PAGE_QUERY( p_kw IN VARCHAR2, p_page IN NUMBER, p_size IN NUMBER, p_total OUT NUMBER, p_cursor OUT SYS_REFCURSOR ) AS v_start NUMBER : (p_page - 1) * p_size; v_end NUMBER : p_page * p_size; BEGIN -- 返回总记录数 SELECT COUNT(*) INTO p_total FROM emp WHERE name LIKE % || p_kw || %; -- 返回当前页数据 OPEN p_cursor FOR SELECT * FROM ( SELECT t.*, ROWNUM rn FROM (SELECT * FROM emp WHERE name LIKE % || p_kw || % ORDER BY empno) t WHERE ROWNUM v_end ) WHERE rn v_start; END;返回 SYS_REFCURSOR 的好处是前端可以像处理普通数据集一样读取不需要为了把结果装进临时表费劲。这里有个原则分页封装适合查询条件相对固定的模块如果查询条件能无限动态组合那不如直接在应用层拼SQL存储过程硬封装反而会变成一个巨大的动态SQL泥潭。3.3 进阶BULK COLLECT与FORALL的批量性能优化批量数据修复是存储过程最能体现价值的场景。我之前处理过一张400万行的日志表要把状态为“P”的记录改成“D”。最开始的版本是游标逐行UPDATE跑了47分钟业务方等得直拍桌子。后来改成 BULK COLLECT FORALL同样的数据量只用了1分20秒左右。为什么差距这么大因为PL/SQL引擎每执行一次SQL语句都要和SQL引擎做一次上下文切换。逐行修改等于每一行都要来回切一次切换次数和数据量成正比批量收集则是一次性把500条数据取到内存再一次FORALL推给SQL引擎切换次数直接除以500。打个比方一个碗一个碗地端到对面厨房和把所有碗装进箱子里一车拉过去工作量差很多。核心代码大致是这样DECLARE CURSOR cur_data IS SELECT id FROM t_log WHERE status P; TYPE t_id_list IS TABLE OF t_log.id%TYPE; v_ids t_id_list; BEGIN OPEN cur_data; LOOP FETCH cur_data BULK COLLECT INTO v_ids LIMIT 500; EXIT WHEN v_ids.COUNT 0; FORALL i IN 1 .. v_ids.COUNT UPDATE t_log SET status D WHERE id v_ids(i); COMMIT; END LOOP; CLOSE cur_data; END;这里有两个细节LIMIT 500 是我比较习惯的批大小太小切换次数还是多太大PL/SQL内存占用会明显上升每一批处理完就 COMMIT是为了避免单个大事务把回滚段撑爆也能防止处理到一半失败导致全量回滚。3.4 熟练自治事务与DBMS_SCHEDULER把过程自动化自治事务这个概念很多用Oracle两三年的人也没用过。它的作用简单说就是在主事务里开一个独立小事务这个小事务的提交或回滚不影响主事务。最典型的场景是写日志表——主流程出错要回滚但你希望错误日志能留下来。写法就是在过程声明部分加一行CREATE OR REPLACE PROCEDURE PROC_LOG_WRITE( p_msg VARCHAR2 ) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO t_process_log(log_time, msg) VALUES (SYSDATE, p_msg); COMMIT; END;主存储过程里调用它即使主事务最后 ROLLBACK日志记录也依然存在。这个技巧在做数据修复、接口对账的时候特别有用不然你根本不知道过程跑到哪一步倒下的。日常运维里还要把存储过程调度起来Oracle自带的 DBMS_SCHEDULER 比操作系统定时任务更可控也方便看运行日志。比如每天凌晨2点执行归档过程BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name JOB_PROC_DAILY_ARCHIVE, job_type PLSQL_BLOCK, job_action BEGIN PROC_DAILY_ARCHIVE; END;, start_date SYSTIMESTAMP, repeat_interval FREQDAILY; BYHOUR2; BYMINUTE0; BYSECOND0, enabled TRUE, comments 每日凌晨2点执行归档过程 ); END;调度类任务最容易被忽略的是执行时长监控。如果一个定时任务正常情况下10分钟跑完某天突然跑了1小时还没结束多半是锁等待或数据量异常。所以我在自己的项目里所有调度任务都会包一个外层日志过程每次执行完记录开始时间、结束时间和处理行数超过阈值直接告警。4. 高频报错与调试技巧实录4.1 高频报错速查表存储过程写多了你会发现大部分报错都是同一批问题。我把这些年碰到的高频错误整理成了一个速查表按这个方向排查基本十拿九稳报错信息常见原因排查方向ORA-01403: no data foundSELECT INTO 没有查到数据确认数据是否存在或改用游标、聚合函数ORA-01422: exact fetch returns moreSELECT INTO 返回了多行检查数据重复必要时加 ROWNUM 或改游标ORA-06502: numeric or value error数值溢出或类型转换失败检查变量类型、长度、字符串拼接长度ORA-06550: PLS-00103PL/SQL编译错误看行号和符号多半是拼写或分号问题ORA-01008: not all variables bound绑定变量未全部赋值检查动态SQL中的占位符和USING子句ORA-00001: unique constraint violated唯一约束或主键冲突查主键、唯一索引检查数据是否重复ORA-1555: snapshot too old快照过旧UNDO空间不足缩短大事务提交间隔增大UNDO表空间ORA-1555这个错最容易出现在长事务里。以前我处理一张千万级流水表时遇到过原因是单条UPDATE没分批提交导致查询一致性快照被覆盖。后来改成 LIMIT 500 分批提交问题直接消失。4.2 调试三板斧DBMS_OUTPUT、日志表、断点调试存储过程没有玄学我常用的方式就三种。第一是 DBMS_OUTPUT.PUT_LINE最快速的打点输出。在PL/SQL Developer里运行前先执行 SET SERVEROUTPUT ON或者直接点“输出”标签页就能看到打印结果。适合确认变量值、SQL%ROWCOUNT、异常位置。第二是日志表配自治事务适合后台跑批任务。前面提到的 PROC_LOG_WRITE 就是干这个用的每一阶段写一行日志跑挂了看日志就能定位到具体步骤。第三是PL/SQL Developer自带的调试器适合处理复杂分支。左侧右键存储过程选择Test填写入参后进入调试模式在关键行按 F9 设置断点鼠标悬停在变量上就能看到实时值。这个方法在你搞不清楚循环到底走了多少遍的时候比一遍遍加DBMS_OUTPUT高效得多。4.3 从118个案例提炼出的五条开发规范案例看多了以后最后拼的其实是规范。我把自己在项目里强制执行的五条规范分享出来每一条都是用线上故障换来的第一命名必须语义化存储过程用 PROC_业务_动作参数用P_变量用V_游标用C_。第二变量声明尽量用 %TYPE 和 %ROWTYPE不要写死长度第三动态SQL必须用绑定变量不要直接拼接字符串否则既慢又有SQL注入风险第四批处理必须分批提交绝对禁止循环内COMMIT第五WHEN OTHERS 里必须记录日志或重新抛出不允许空手吞异常。这五条规范不复杂但能把存储过程从“能跑就行”提升到“能放心运维”的水平。118个案例里凡是涉及生产环境的场景几乎都能看到这些规范的影子。我自己的体会是存储过程这东西写一百遍语法不如在一个真实场景里调试一遍。把这118个案例当成一个场景库遇到新需求先去库里找相似模板再按业务调整比每次都从零开始写要靠谱得多。最后再分享一个小技巧遇到一个跑得慢的存储过程先别急着改代码把涉及的SQL单独拿出来看执行计划往往问题根本不在过程逻辑而在索引和表设计上。本文还有配套的精品资源点击获取 Oracle存储过程118个真实场景案例：从入门到性能调优实战指南
简介一份精选自实际工作场景的Oracle存储过程案例资料包包含118个完整案例面向数据库开发工程师、运维人员及存储过程初学者解决从理论语法到真实业务落地之间的连接问题。压缩包共120个文件以118个带完整注释的SQL案例为主体另含1份PDF开发指南和1个说明文档整体仅649KB下载与查阅都很方便。案例按用途分为两类61个真实业务案例覆盖客户日周月统计、绩效考核、周期报表、抽检数据处理、消息推送、订单处理等贴近生产的场景57个通用案例则聚焦序列、表及列操作、主键唯一索引约束、事务管理、权限控制、导出文件、视图、迭代、备份、参数校验等可复用模块能大幅降低重复开发成本。目前已有1689人学习下载配合PDF从入门到进阶的讲解以及每个案例的注释说明既适合自学提高也能作为团队内部的代码参考手册。 做了这么多年Oracle开发和性能调优我一直觉得存储过程这个技术点最缺的不是语法书而是场景感。语法书会把 CREATE OR REPLACE PROCEDURE 的格式写给你看但真到了业务里面对一张要修的数据表、一个要接的接口、一个慢到要报警的定时任务很多人还是会卡住。这套118个真实应用场景的Oracle存储过程案例及开发指南就是站在“从入门到熟练使用”这条主线上把语法落回到真实业务里的问题集适合刚接触PL/SQL的新手也适合被各种诡异报错折磨过一段时间的运维和开发。我整理这118个案例的时候并不是按语法点硬编的而是先把项目里实际写过的存储过程全部翻出来按业务场景筛了一遍再去补充日常需求里高频出现的技术点。这篇博文就围绕这套案例库的设计思路、拆解方法、典型实操和排障经验展开希望能帮你建立起自己的场景库而不是背语法。1. 场景库的整体设计思路为什么是118个案例1.1 用业务场景反推技术点而不是用语法堆教程语法手册和场景案例的区别有点像字典和菜谱字典告诉你每个字怎么写、什么意思但不会告诉你番茄炒蛋该先放油还是先放盐。项目里遇到的真实需求千奇百怪可底层技术点其实就那么二三十个。118个真实场景的价值就是把这些技术点放到不同业务条件下重新组合让你在面对新需求的时候有模板可依。比如同样是 UPDATE 语句放进存储过程里就有好几种玩法按主键更新单条按条件批量更新遍历游标逐行更新用 BULK COLLECT 分批更新甚至动态拼接表名再更新。这些玩法并不是为了炫技而是对应不同数据量和不同业务约束。案例里会明确告诉你什么场景用哪种写法为什么选这种写法换了写法又会踩什么坑。1.2 118个案例的四阶段划分我在设计这套案例时把它分成了四个阶段目的是降低学习曲线也方便你按需查阅阶段案例数量覆盖范围代表场景入门15个基础语法、单表增删改查、序列、判断循环新增员工、修改部门、查询单条记录常用40个分页查询、批量更新、业务编号生成、报表统计模糊搜索分页、批量状态更新、日报统计进阶40个动态SQL、游标嵌套、批量收集、异常重试大数据量修复、异构表数据迁移、接口数据落库熟练23个自治事务、调度任务、并行处理、性能调优定时归档、日志记录、任务监控这四部分的划分依据是日常Oracle开发里技术点出现的频率。入门案例解决“能不能写出来”的问题常用案例解决“写得对不对”的问题进阶案例解决“跑得快不快”的问题熟练案例解决“系统稳不稳”的问题。你不需要按顺序全部刷完遇到具体问题直接跳到对应阶段去查就行。2. 拆解案例的正确姿势核心语法与编码规范2.1 每个案例都先拆成四个要素很多初学者拿到一个存储过程案例上来就盯着代码一行行看这是效率最低的方式。我一般建议先拆成四个要素业务背景、输入输出、处理流程、性能约束。拿“批量更新客户等级”举例。业务背景是营销部门要根据近30天消费金额重新给客户打标签输入是金额阈值和生效日期输出是更新行数性能约束是客户表可能有几百万行不能一把梭全部UPDATE。拆完这些再去看代码你才能明白为什么要用游标、为什么要分批提交、为什么需要记录日志。如果只是背代码换个表、换个字段你就不会了。所以在118个案例里每个案例我都先写业务背景再贴代码最后补优化思路。你照着这个思路去拆自己项目里的存储过程也会清晰很多。2.2 参数模式与变量命名细节决定成败Oracle存储过程的参数有三种模式IN、OUT、IN OUT。IN是传入参数过程内部不能改它的值OUT是传出参数只能赋值给调用者IN OUT是双向既能传入又能传出修改。我习惯用一个例子帮人理解IN像把材料递给厨师厨师只能按材料做菜不能退回去换OUT像传菜出来的窗口IN OUT则像顾客点单时提出少盐厨师根据要求调整后再端出来。变量声明里最值得养成习惯的是使用 %TYPE 和 %ROWTYPE。比如v_salary emp.salary%TYPE; v_emp emp%ROWTYPE;这样写的好处是如果表结构里 salary 字段从 NUMBER(10,2) 改成 NUMBER(12,2)存储过程不需要跟着改重新编译就能适配。如果写死 VARCHAR2(20)字段一扩容就容易出现 ORA-06502 数值溢出错误。命名规范方面我自己的习惯是存储过程用 PROC_业务_动作参数用 P_ 前缀变量用 V_ 前缀游标用 C_ 前缀异常用 E_ 前缀。这套规范在团队协作里尤其有用别人看你的代码三秒钟就知道哪个是参数、哪个是局部变量排查问题能省一半时间。2.3 异常与事务存储过程里最容易翻车的地方异常处理有个很大的误区WHERE OTHERS 后面什么都不写直接把错误吞掉。这样做的后果是过程“成功”执行了但数据可能只处理了一半而且你不知道哪里出了问题。更合理的模板是这样BEGIN -- 业务逻辑 COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(没有找到对应数据); ROLLBACK; WHEN OTHERS THEN ROLLBACK; RAISE; END;事务控制的分寸也要拿捏。我见过很多批量导入过程每循环一行就 COMMIT 一次结果跑到第3万行报错前面2万多次提交全部生效想回滚都回滚不了。正确做法是批处理场景按批次提交比如每500行或每1000行提交一次单个业务动作的场景让调用方统一控制事务存储过程内部不要随便 COMMIT。这个原则在118个案例里反复出现因为它直接决定了数据安全。3. 从入门到熟练几个有代表性的实操案例串讲3.1 入门单表增删改查与自增序号的获取每个新手写的第一个存储过程几乎都是单表INSERT。这里最容易忽略的是主键怎么生成。Oracle不像MySQL有自增字段一般靠序列。一个最基础的员工新增过程可以这样写CREATE OR REPLACE PROCEDURE PROC_ADD_EMPLOYEE( p_name IN VARCHAR2, p_dept IN VARCHAR2, p_salary IN NUMBER ) AS v_empno NUMBER; BEGIN SELECT seq_empno.NEXTVAL INTO v_empno FROM dual; INSERT INTO emp(empno, name, dept, salary, hire_date) VALUES (v_empno, p_name, p_dept, p_salary, SYSDATE); DBMS_OUTPUT.PUT_LINE(新增员工编号: || v_empno); COMMIT; END;插入之后推荐用 SQL%ROWCOUNT 做一个影响行数判断防止INSERT语句写错却没插进去IF SQL%ROWCOUNT 0 THEN RAISE_APPLICATION_ERROR(-20001, 没有插入任何记录); END IF;这个小技巧非常实用。真实项目里存储过程经常被别的程序调用如果插入失败外层毫无感知后面整个流程都会跑偏。3.2 常用阶段分页查询与模糊搜索的存储过程封装分页查询在报表系统里几乎天天用。Oracle 12c以上可以写 OFFSET FETCH但11g还大量存在所以兼容写法用的是 ROWNUM 嵌套。一个返回游标结果集的分页过程可以这样写CREATE OR REPLACE PROCEDURE PROC_PAGE_QUERY( p_kw IN VARCHAR2, p_page IN NUMBER, p_size IN NUMBER, p_total OUT NUMBER, p_cursor OUT SYS_REFCURSOR ) AS v_start NUMBER : (p_page - 1) * p_size; v_end NUMBER : p_page * p_size; BEGIN -- 返回总记录数 SELECT COUNT(*) INTO p_total FROM emp WHERE name LIKE % || p_kw || %; -- 返回当前页数据 OPEN p_cursor FOR SELECT * FROM ( SELECT t.*, ROWNUM rn FROM (SELECT * FROM emp WHERE name LIKE % || p_kw || % ORDER BY empno) t WHERE ROWNUM v_end ) WHERE rn v_start; END;返回 SYS_REFCURSOR 的好处是前端可以像处理普通数据集一样读取不需要为了把结果装进临时表费劲。这里有个原则分页封装适合查询条件相对固定的模块如果查询条件能无限动态组合那不如直接在应用层拼SQL存储过程硬封装反而会变成一个巨大的动态SQL泥潭。3.3 进阶BULK COLLECT与FORALL的批量性能优化批量数据修复是存储过程最能体现价值的场景。我之前处理过一张400万行的日志表要把状态为“P”的记录改成“D”。最开始的版本是游标逐行UPDATE跑了47分钟业务方等得直拍桌子。后来改成 BULK COLLECT FORALL同样的数据量只用了1分20秒左右。为什么差距这么大因为PL/SQL引擎每执行一次SQL语句都要和SQL引擎做一次上下文切换。逐行修改等于每一行都要来回切一次切换次数和数据量成正比批量收集则是一次性把500条数据取到内存再一次FORALL推给SQL引擎切换次数直接除以500。打个比方一个碗一个碗地端到对面厨房和把所有碗装进箱子里一车拉过去工作量差很多。核心代码大致是这样DECLARE CURSOR cur_data IS SELECT id FROM t_log WHERE status P; TYPE t_id_list IS TABLE OF t_log.id%TYPE; v_ids t_id_list; BEGIN OPEN cur_data; LOOP FETCH cur_data BULK COLLECT INTO v_ids LIMIT 500; EXIT WHEN v_ids.COUNT 0; FORALL i IN 1 .. v_ids.COUNT UPDATE t_log SET status D WHERE id v_ids(i); COMMIT; END LOOP; CLOSE cur_data; END;这里有两个细节LIMIT 500 是我比较习惯的批大小太小切换次数还是多太大PL/SQL内存占用会明显上升每一批处理完就 COMMIT是为了避免单个大事务把回滚段撑爆也能防止处理到一半失败导致全量回滚。3.4 熟练自治事务与DBMS_SCHEDULER把过程自动化自治事务这个概念很多用Oracle两三年的人也没用过。它的作用简单说就是在主事务里开一个独立小事务这个小事务的提交或回滚不影响主事务。最典型的场景是写日志表——主流程出错要回滚但你希望错误日志能留下来。写法就是在过程声明部分加一行CREATE OR REPLACE PROCEDURE PROC_LOG_WRITE( p_msg VARCHAR2 ) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO t_process_log(log_time, msg) VALUES (SYSDATE, p_msg); COMMIT; END;主存储过程里调用它即使主事务最后 ROLLBACK日志记录也依然存在。这个技巧在做数据修复、接口对账的时候特别有用不然你根本不知道过程跑到哪一步倒下的。日常运维里还要把存储过程调度起来Oracle自带的 DBMS_SCHEDULER 比操作系统定时任务更可控也方便看运行日志。比如每天凌晨2点执行归档过程BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name JOB_PROC_DAILY_ARCHIVE, job_type PLSQL_BLOCK, job_action BEGIN PROC_DAILY_ARCHIVE; END;, start_date SYSTIMESTAMP, repeat_interval FREQDAILY; BYHOUR2; BYMINUTE0; BYSECOND0, enabled TRUE, comments 每日凌晨2点执行归档过程 ); END;调度类任务最容易被忽略的是执行时长监控。如果一个定时任务正常情况下10分钟跑完某天突然跑了1小时还没结束多半是锁等待或数据量异常。所以我在自己的项目里所有调度任务都会包一个外层日志过程每次执行完记录开始时间、结束时间和处理行数超过阈值直接告警。4. 高频报错与调试技巧实录4.1 高频报错速查表存储过程写多了你会发现大部分报错都是同一批问题。我把这些年碰到的高频错误整理成了一个速查表按这个方向排查基本十拿九稳报错信息常见原因排查方向ORA-01403: no data foundSELECT INTO 没有查到数据确认数据是否存在或改用游标、聚合函数ORA-01422: exact fetch returns moreSELECT INTO 返回了多行检查数据重复必要时加 ROWNUM 或改游标ORA-06502: numeric or value error数值溢出或类型转换失败检查变量类型、长度、字符串拼接长度ORA-06550: PLS-00103PL/SQL编译错误看行号和符号多半是拼写或分号问题ORA-01008: not all variables bound绑定变量未全部赋值检查动态SQL中的占位符和USING子句ORA-00001: unique constraint violated唯一约束或主键冲突查主键、唯一索引检查数据是否重复ORA-1555: snapshot too old快照过旧UNDO空间不足缩短大事务提交间隔增大UNDO表空间ORA-1555这个错最容易出现在长事务里。以前我处理一张千万级流水表时遇到过原因是单条UPDATE没分批提交导致查询一致性快照被覆盖。后来改成 LIMIT 500 分批提交问题直接消失。4.2 调试三板斧DBMS_OUTPUT、日志表、断点调试存储过程没有玄学我常用的方式就三种。第一是 DBMS_OUTPUT.PUT_LINE最快速的打点输出。在PL/SQL Developer里运行前先执行 SET SERVEROUTPUT ON或者直接点“输出”标签页就能看到打印结果。适合确认变量值、SQL%ROWCOUNT、异常位置。第二是日志表配自治事务适合后台跑批任务。前面提到的 PROC_LOG_WRITE 就是干这个用的每一阶段写一行日志跑挂了看日志就能定位到具体步骤。第三是PL/SQL Developer自带的调试器适合处理复杂分支。左侧右键存储过程选择Test填写入参后进入调试模式在关键行按 F9 设置断点鼠标悬停在变量上就能看到实时值。这个方法在你搞不清楚循环到底走了多少遍的时候比一遍遍加DBMS_OUTPUT高效得多。4.3 从118个案例提炼出的五条开发规范案例看多了以后最后拼的其实是规范。我把自己在项目里强制执行的五条规范分享出来每一条都是用线上故障换来的第一命名必须语义化存储过程用 PROC_业务_动作参数用P_变量用V_游标用C_。第二变量声明尽量用 %TYPE 和 %ROWTYPE不要写死长度第三动态SQL必须用绑定变量不要直接拼接字符串否则既慢又有SQL注入风险第四批处理必须分批提交绝对禁止循环内COMMIT第五WHEN OTHERS 里必须记录日志或重新抛出不允许空手吞异常。这五条规范不复杂但能把存储过程从“能跑就行”提升到“能放心运维”的水平。118个案例里凡是涉及生产环境的场景几乎都能看到这些规范的影子。我自己的体会是存储过程这东西写一百遍语法不如在一个真实场景里调试一遍。把这118个案例当成一个场景库遇到新需求先去库里找相似模板再按业务调整比每次都从零开始写要靠谱得多。最后再分享一个小技巧遇到一个跑得慢的存储过程先别急着改代码把涉及的SQL单独拿出来看执行计划往往问题根本不在过程逻辑而在索引和表设计上。本文还有配套的精品资源点击获取