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 ... FOR：
v_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：一次把数据装进集合
普通游标循环是一条条 FETCH，BULK 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，要交给FORALL：
FORALL 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 EXCEPTIONS：
BEGIN 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要记得删掉，否则上线后每一次调用都会产生额外输出开销。
写存储过程这件事，语句本身都好抄，难的是理解每条语句背后的边界条件。你把这些常用语句段串起来，先搞清楚每个段落在什么场景下该出现，再动手编码，开发效率和稳定性都会比"从网上抄一段改一改"高出一大截。
