★ FEATURED ARTICLE Oracle 存储过程开发说来说去其实就是在做两件事把一大段复杂的业务逻辑拆成可复用的代码块以及用最稳妥的方式处理数据。不少刚接触的开发者喜欢把存储过程当成把SQL装进过程里真到手上线才发现游标怎么写、动态SQL怎么拼、异常怎么抛每一样都藏着坑。这篇文章把我这些年写Oracle存储过程时最常用到、也最该用对的语句段整理出来覆盖过程骨架、游标处理、动态SQL、批量操作、异常链路和排查手段适合正在用PL/SQL写业务逻辑的开发同学也适合想系统梳理一遍常用语句的老手随手翻翻。1. 过程的三段式骨架声明区、执行区与异常区的规范写法任何一个存储过程无论业务多复杂骨架都逃不出三个区域。很多人写了几百行过程回头一看连自己都理不清参数和局部变量往往就是骨架没搭稳。1.1 参数模式的区分IN、OUT、IN OUT到底怎么选存储过程的参数声明是整个过程的入口定义也是最容易写含糊的地方。先看一个标准的带参数过程CREATE OR REPLACE PROCEDURE prc_handle_order( p_order_id IN NUMBER, p_status OUT VARCHAR2, p_note IN OUT VARCHAR2 ) AS v_discount NUMBER(8,2) : 0; BEGIN SELECT status, remark INTO p_status, p_note FROM order_table WHERE order_id p_order_id; v_discount : calc_discount(p_order_id); p_note : p_note || 优惠金额 || v_discount; EXCEPTION WHEN NO_DATA_FOUND THEN p_status : NOT_FOUND; END prc_handle_order;参数模式的选择逻辑其实很明确IN只读入参过程内部不允许修改。这是绝大多数参数的正确归属。OUT只负责把结果带出去调用前不关心它的初始值。对应上面例子里的p_status。IN OUT既要读入又要把处理后的值带出对应p_note。很多人混淆 OUT 和 IN OUT 的用法常见的问题是在 OUT 参数上做逻辑判断这是不安全的——OUT 参数的初始值在 Oracle 里是未定义的拿它做判断等于在赌运气。1.2 局部变量声明与赋值语句的细节过程体内用:做赋值用SELECT ... INTO ...把查询结果怼进变量。这里有两个必须养成的习惯第一SELECT INTO 必须有且仅有一行返回。别指望它像游标那样容忍空结果查不到数据直接抛NO_DATA_FOUND返回多行抛TOO_MANY_ROWS。所以只要查询条件是动态的或者过滤字段不满足唯一性就一定要用游标或者MAX/聚合兜底。第二变量命名要有前缀规范。我的习惯是入参用p_开头局部变量用v_开头常量用c_开头。这不是形式主义——PL/SQL 里变量名和表/字段名冲突时一旦WHERE condition v_status写成了WHERE condition status系统会傻傻分不清结果就是运行时行为诡异。前缀能帮你从根源上规避这类同名遮蔽问题。1.3 过程体内语句块的组织习惯执行区不是把语句从上到下罗列完就收工。我一般会按取数 → 校验 → 计算 → 回写四步走用注释把段落切开。万一某段逻辑需要临时关闭直接注释掉整个段落而不是逐行删这样排错时能快速定位是哪个环节出的问题。这里补充一个很实用的语句DBMS_OUTPUT.PUT_LINE。它只负责输出调试信息很多人把它当成日志用但要注意它默认不输出到客户端工具里要开启 serveroutput 才能看到。真要留痕还是得写日志表后面第6部分会说具体语句。2. 游标的三类写法与适用边界优先用FOR循环的理由游标是存储过程里最绕不开的语句但大多数场景根本不需要你手动 OPEN、FETCH、CLOSE。把三类写法吃透你才知道什么场景用什么最省事。2.1 隐式游标的便捷与限制最常见的隐式游标就是SELECT INTO和FOR ... IN (SELECT ...)。它的优点是写起来短缺点是逻辑没法一步步控制。比如你要在循环里做复杂的过滤或者需要边遍历边修改同一张表隐式游标就力不从心了。2.2 显式游标的完整语句链显式游标要求四句经典CURSOR cur IS SELECT ...声明、OPEN cur打开、FETCH cur INTO ...取数、CLOSE cur关闭。写起来长但它提供最多的控制力。比如跳行、按批次提交、逐行判断后决定是否继续。CURSOR cur_emp IS SELECT emp_id, salary FROM emp_table WHERE dept_id p_dept; v_emp_id emp_table.emp_id%TYPE; v_salary emp_table.salary%TYPE; BEGIN OPEN cur_emp; LOOP FETCH cur_emp INTO v_emp_id, v_salary; EXIT WHEN cur_emp%NOTFOUND; IF v_salary 5000 THEN UPDATE emp_table SET salary v_salary * 1.2 WHERE emp_id v_emp_id; END IF; END LOOP; CLOSE cur_emp; END;过程里反复出现%TYPE和%ROWTYPE这两个属性是 Oracle 给开发者省事的杀器——你在变量声明里写成emp_table.salary%TYPE以后表结构改了字段类型存储过程不用跟着改否则你会在上线时因为类型不匹配被一堆ORA-06502追着跑。2.3 为什么大多数业务场景直接用FOR循环FOR cur_rec IN cur_emp LOOP ... END LOOP;是显式游标的简化版。它替你把 OPEN、FETCH、CLOSE 全部包了循环结束后游标自动关闭。不需要手动写EXIT WHEN甚至忘了 CLOSE 导致游标泄漏的问题也天然规避了。我把自己的选型逻辑说直白一点如果只是遍历查询结果做处理无脑用 FOR 循环游标代码最短最安全。如果需要同时打开多个游标并且游标之间存在关联用显式游标但务必在EXCEPTION块里补CLOSE防止异常后游标不释放。如果游标要作为参数传给另一个过程才需要用到SYS_REFCURSOR游标变量。游标变量涉及OPEN cur_var FOR SELECT ...这种写法它最大的价值是动态性——返回结果集给 Java、Python 等外部程序时基本靠它。这部分和第3部分的动态SQL是联动的。3. 动态SQL的正确姿势EXECUTE IMMEDIATE背后的绑定变量逻辑存储过程免不了要处理表名、字段名、过滤条件在运行时才确定的场景。这时候写死在代码里的 SQL 就没法用了必须上动态SQL。3.1 拼串与绑定变量的分水岭动态SQL最朴素的做法是字符串拼接比如v_sql : SELECT name FROM || v_table || WHERE id || v_id; EXECUTE IMMEDIATE v_sql;这条语句能跑但埋了雷。v_id是数值那还好万一是字符串你就需要在外面包一堆单引号写起来奇丑无比。更要命的是每次拼接出来的 SQL 文本不同Oracle 每次都要做一次硬解析性能肉眼可见地掉。正确的姿势是绑定变量v_sql : SELECT name FROM || v_table || WHERE id :id; EXECUTE IMMEDIATE v_sql INTO v_name USING v_id;用USING把变量传进去SQL 文本保持不变可以走共享池的软解析同时从根上杜绝了 SQL 注入。带不带 INTO 的规则是查询返回单行就得补INTO不返回结果的 DML/DDL直接EXECUTE IMMEDIATE v_sql。3.2 动态SQL里的表名处理边界绑定变量不是万能的表名和字段名没法绑定。也就是说SELECT * FROM :table是跑不通的。表名只能拼进去且必须自己严格控制白名单——我在项目里规定所有动态表名只能从一个配置表里取配合DBMS_ASSERT做校验不能直接拿外部入参拼。v_sql : SELECT count(*) FROM || v_table || WHERE status :status; EXECUTE IMMEDIATE v_sql INTO v_cnt USING ACTIVE;3.3 动态游标与批量取数的配合语句如果你动态SQL返回的是多行结果集EXECUTE IMMEDIATE就不好使了要换成OPEN ... FORv_sql : SELECT emp_id, emp_name FROM employee WHERE dept_id :dept; OPEN cur_ref FOR v_sql USING p_dept_id;这个写法我强烈建议每个做报表类存储过程的人都记下来。它比EXECUTE IMMEDIATE更灵活配合%ROWTYPE或FETCH BULK COLLECT INTO可以直接把动态结果集灌进集合变量后面批量处理就有抓手了。4. 批量数据操作的三板斧BULK COLLECT、LIMIT与FORALL写存储过程的人迟早会遇到一个坎循环里一条条 UPDATE更新两万行数据跑了十分钟。问题不出在SQL本身而出在每次循环都在反复做上下文切换。Oracle 解决这个问题就三板斧BULK COLLECT批量取数、LIMIT限制每次取多少、FORALL批量执行DML。4.1 BULK COLLECT一次把数据装进集合普通游标循环是一条条 FETCHBULK COLLECT是一次性把结果集装进集合变量。写法变化如下SELECT emp_id, salary BULK COLLECT INTO v_emp_ids, v_salaries FROM emp_table WHERE dept_id p_dept;注意BULK COLLECT可以接LIMIT子句控制分批大小。一次性取百万行会导致 PGA 暴涨分批次取才是稳妥做法FETCH cur_emp BULK COLLECT INTO v_emp_ids, v_salaries LIMIT 1000;这段语句写在循环里配合EXIT WHEN cur_emp%NOTFOUND就能实现每1000行一批的处理节奏。4.2 FORALL集合驱动的批量DML集合变量准备好之后不要用 FOR 循环逐个执行 UPDATE要交给FORALLFORALL i IN 1 .. v_emp_ids.COUNT UPDATE emp_table SET salary v_salaries(i) * 1.1 WHERE emp_id v_emp_ids(i);FORALL会把整批 UPDATE 一次性发送给 SQL 引擎性能提升极其明显。它的限制是内部不能做复杂判断如果需要过滤请在BULK COLLECT的阶段就用 WHERE 把数据筛好。4.3 批量操作失败的兜底语句FORALL有个特性一批里其中一行失败默认整个语句回滚。如果你希望跳过失败行继续处理后续行需要加SAVE EXCEPTIONSBEGIN FORALL i IN 1 .. v_emp_ids.COUNT SAVE EXCEPTIONS UPDATE emp_table SET salary v_salaries(i) * 1.1 WHERE emp_id v_emp_ids(i); EXCEPTION WHEN OTHERS THEN FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP dbms_output.put_line(行号 || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || 错误码 || SQL%BULK_EXCEPTIONS(j).ERROR_CODE); END LOOP; END;SQL%BULK_EXCEPTIONS这个集合记录了出错的行索引与错误码是在FORALL失败后精确定位脏数据的重要手段。没有它你只能盲猜是哪一行出的错。5. 异常处理语句的编排把错误信息准确传回给调用方存储过程里的异常处理写不好就两种结果要么异常被吞掉、调用方一脸懵要么异常抛得太粗、不知道具体哪里挂了。异常语句虽然就几个关键字但编排结构是有讲究的。5.1 异常类型与捕获顺序Oracle 的异常分三类预定义异常、非预定义异常、自定义异常。预定义异常常见的有NO_DATA_FOUND、TOO_MANY_ROWS、DUP_VAL_ON_INDEX。捕获顺序上具体异常放前面WHEN OTHERS放最后否则具体的永远捕获不到。EXCEPTION WHEN NO_DATA_FOUND THEN p_err_msg : 查无记录; WHEN DUP_VAL_ON_INDEX THEN p_err_msg : 唯一键冲突; WHEN OTHERS THEN p_err_msg : SQLERRM; END;5.2 RAISE_APPLICATION_ERROR与异常信息回传给外部应用返回业务错误用RAISE_APPLICATION_ERROR是最规范的手段。它允许你自定义错误号和错误信息IF v_stock p_quantity THEN RAISE_APPLICATION_ERROR(-20001, 库存不足当前可用 || v_stock); END IF;错误号范围限制在-20000到-20999之间。它抛出的异常会被 Java/C# 等客户端捕获到错误信息直接展示给用户。这里要提醒一句错误信息要写成业务能看懂的文案别把ORA-01403这种内部崩溃原样抛出去。5.3 日志与异常的统一出口异常处理后不能只dbms_output我在真实项目中都会写一个日志过程在异常块里调用PROCEDURE prc_log_error( p_proc_name VARCHAR2, p_step VARCHAR2, p_err_code NUMBER, p_err_msg VARCHAR2 );然后在每个过程的WHEN OTHERS里统一写WHEN OTHERS THEN prc_log_error(prc_handle_order, v_current_step, SQLCODE, SQLERRM); RAISE;RAISE和RAISE_APPLICATION_ERROR的唯一区别是前者保留原始错误后者抛自定义信息。需要保留原始错误现场就RAISE需要拦截并改写错误信息就RAISE_APPLICATION_ERROR。6. 存储过程开发中容易反复踩的坑与排查语句最后一节说点实际的。经验都是在坑里攒出来的这一节我把高频踩坑场景和对应的排查语句列一下方便你复制即用。6.1 过程编译报错查USER_ERRORS精准定位编译报错时客户端只会显示大致行号具体错误得查数据字典。最常用的排查语句是SELECT line, position, text FROM user_errors WHERE name PRC_HANDLE_ORDER ORDER BY line;注意name要大写。如果过程依赖了别的对象并且对方变了过程会变成INVALID这时需要重新编译ALTER PROCEDURE prc_handle_order COMPILE;6.2 过程运行慢先查执行计划再改代码存储过程慢撇开实在复杂的业务逻辑多半是循环内逐条SQL。一个很有用的排查方式是对过程里最重的查询语句跑EXPLAIN PLAN或者直接用现成的执行计划输出语句EXPLAIN PLAN FOR SELECT ... FROM ... WHERE ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);看到FULL TABLE SCAN大表时先别急着加索引看看是不是动态SQL拼接导致绑定变量失效了。我在实践中发现很多存储过程突然变慢的案例就是开发时把:1换成实际值拼进SQL导致执行计划无法复用。6.3 数据量一大就报undo或临时表空间不足批量更新大量数据时如果一条 UPDATE 影响了太多行undo 空间未必撑得住。常见的做法是分段提交这时就能用上前面游标循环 每批 COMMIT 的写法。比如按主键范围循环处理每个范围结束后提交一次。6.4 过程依赖的源语句查看USER_SOURCE接手别人的过程想快速知道它里面用了什么表、什么语句直接查SELECT line, text FROM user_source WHERE name PRC_HANDLE_ORDER AND type PROCEDURE ORDER BY line;这条语句在重构老系统时简直是救命稻草比在一个几千行的过程文件里翻找快得多。6.5 临时调试语句组我调试存储过程时有固定的三件套-- 开启输出 SET SERVEROUTPUT ON; -- 查看过程中的变量借助临时调试段 -- 在过程中临时插入 -- DBMS_OUTPUT.PUT_LINE(当前v_salary || v_salary); -- 查看某过程是否有效 SELECT status FROM user_objects WHERE object_name PRC_HANDLE_ORDER;调试完成后插入的PUT_LINE要记得删掉否则上线后每一次调用都会产生额外输出开销。写存储过程这件事语句本身都好抄难的是理解每条语句背后的边界条件。你把这些常用语句段串起来先搞清楚每个段落在什么场景下该出现再动手编码开发效率和稳定性都会比从网上抄一段改一改高出一大截。
阅读完成 · 觉得有帮助？
