Oracle 定义变量避坑：PL/SQL、绑定变量与替换变量机制辨析

读过太多写在纸上、跑起来就翻车的 PL/SQL 代码，我发现一个很有意思的规律：问题很少出在复杂逻辑上，反倒集中在最不起眼的那一行——Oracle 中定义变量。有人把DECLARE段里的变量、SQL*Plus 里的&替换变量、以及 SQL 语句里的:b1绑定变量混为一谈，写出来的脚本在开发库跑得好好的，一上生产就抛 ORA-01008；有人在循环里用字面量拼 SQL，共享池被撑成碎片，硬解析把 CPU 吃满；还有人写了v_name VARCHAR2(10)然后往里塞 12 个汉字，报 ORA-06502 却查半天查不出原因。这些坑的根子都在同一处：没搞清 Oracle 里"变量"这个词到底指几种完全不同的机制，它们各自的生命周期、作用域、类型规则和性能代价都不一样。

这篇内容不打算写成语法手册。我按照实际排查问题的顺序，把 PL/SQL 标量变量、锚定类型、复合类型、包变量、绑定变量、替换变量这几套机制拆开讲，重点放在为什么这么设计、参数和类型该怎么选、哪些写法看着没问题其实很危险。适合已经能写简单存储过程、但总在小地方踩坑的同学，也适合需要给别人做代码评审、想快速判断一段 PL/SQL 是否有隐患的从业者。全文的示例都基于模拟环境和虚构表结构，可以放心对照自己的库改写。

1. 先把"变量"这个词在 Oracle 里拆开看

1.1 四套机制，四种生命周期

同一个词在 Oracle 的技术语境里至少对应四种东西，它们的差别大到不该放在一起讨论：

看表格可能还没感觉，说个具体场景。你写了个脚本，开头DEFINE dept = 10，后面写WHERE deptno = &dept。这里的&dept在 SQL 发给数据库之前就已经被字符串替换成10了，数据库端收到的是WHERE deptno = 10，一个纯字面量。这跟WHERE deptno = :b1完全是两回事——后者数据库收到的是带占位符的语句，执行时再绑值，可以复用执行计划。同样是"传个值进去"，一个影响共享池，一个不影响，差别就在这里。

而包变量又是另一套逻辑。它在会话层面持久存在，会话不断开就一直活着。连接池普及之后，这个特性从优点变成了隐患：你以为的新会话，其实是复用的旧会话，上一轮留下的包变量值还在。我见过一个统计程序因为包变量没重置，第二次调用时数字翻倍，查了两天才定位到。

注意：判断一段代码里的"变量"属于哪一类，只看写法不看上下文是不够的。带冒号的是绑定变量，带 & 且出现在 SQL*Plus 脚本里的是替换变量，出现在DECLARE/IS ... BEGIN之间的是 PL/SQL 变量，声明在包规范里的是包变量。四种混用是常见事故源。

1.2 为什么先把分类搞清楚比背语法重要

语法本身没什么难度，变量名 类型 [:= 初值];一行就完事。真正花时间的是判断"我这个场景该用哪一种"。举个很实际的例子：你需要在一个批处理脚本里执行同一条 UPDATE 一百次，只是每次的条件值不同。用替换变量写，就是一百条文本不同的 SQL，一百次硬解析；用绑定变量写，一条 SQL 编译一次、执行一百次。数据量小时看不出差别，上了千万级、并发几十个会话的时候，这个选择直接决定系统是稳还是崩。

再比如，你在存储过程里需要跨多次调用记住某个配置值。用包变量可以做到，但它跟着会话走，不定时清理就会串数据；改成每次查询配置表，性能又下来了；折中方案是用包变量加一个"已加载"标志位，或者干脆接受那点查询开销。这种取舍没有标准答案，但前提是你得清楚每种机制的边界在哪。

2. PL/SQL 声明段的写法与被忽略的规则

2.1 基本声明语法与初始化语义

PL/SQL 块的结构是DECLARE（可选）→BEGIN→EXCEPTION（可选）→END;。变量只能声明在DECLARE和BEGIN之间，或者存储过程/函数的IS/AS与BEGIN之间。声明必须放在所有可执行语句之前，不能写到一半再回头声明一个新变量，这是新手最常撞的编译错误 ORA-06550。

DECLARE v_count NUMBER := 0; v_name VARCHAR2(50) := 'unknown'; v_hiredate DATE DEFAULT SYSDATE; v_rate CONSTANT NUMBER := 0.13; v_flag BOOLEAN NOT NULL := FALSE; BEGIN v_count := v_count + 1; DBMS_OUTPUT.PUT_LINE(v_name || ' / ' || v_count); END; /

几条容易被忽略的规则，逐条说清楚：

未显式初始化的变量一律是 NULL，不是零，也不是空字符串。这一条听起来理所当然，但后果非常具体：v_count := v_count + 1在v_count为 NULL 时结果还是 NULL，不报错，也不加一。数字类的累加器不初始化，最后算出来的总额就是 NULL，前端展示成空白，排查时还以为数据丢了。所以我的习惯是数字变量一律给:= 0，字符串给:= NULL或者明确的空串语义，日期能不给初值就不给——给个SYSDATE更容易掩盖"忘了赋值"的问题。

:=和DEFAULT完全等价，代码库里两种写法都有，团队内最好统一，评审时少一道判断。

声明处不能加约束。你写v_name VARCHAR2(50) CHECK (...)会直接编译失败。声明段唯一允许的约束是NOT NULL，而且NOT NULL变量必须在声明时同时初始化，否则编译期就报错。这个设计其实挺好理解：约束检查要写逻辑，声明段只做类型和初值，逻辑一律放到BEGIN里。

标识符长度在旧版本里限制为 30 字节。12.2 之后放宽到 128 字节，但如果你的库还得兼容更早的版本，或者有跨版本迁移的需求，命名还是老实控制在 30 字节内。中文字符按字节算，一个汉字通常占 3 字节，起个长中文变量名很容易超。

2.2 %TYPE 与 %ROWTYPE：让变量跟着表结构走

硬编码类型是 PL/SQL 里最隐蔽的技术债之一。表里的employee_name从VARCHAR2(50)扩到VARCHAR2(200)，所有手工写死VARCHAR2(50)的变量全都会在赋值时报 ORA-06502，而且是运行时才报，编译期一点提示都没有。%TYPE就是解决这个问题的：

DECLARE v_name employees.employee_name%TYPE; v_sal employees.salary%TYPE; v_row employees%ROWTYPE; BEGIN SELECT employee_name, salary INTO v_name, v_sal FROM employees WHERE employee_id = 100; SELECT * INTO v_row FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE(v_row.employee_name); END; /

%TYPE的价值在于编译期绑定：类型定义跟着源对象走，源对象改了，引用它的 PL/SQL 单元下次编译时自动拿到新类型，不用手工同步。%ROWTYPE更进一步，把整行做成了一个记录类型，字段名和表列一一对应，写v_row.salary就等同于employees.salary。

但这两个东西有代价，得知道：第一，它们依赖于被引用的对象，如果表在编译时不存在或者当前用户没权限，编译直接失败，而不是运行时才失败；第二，如果引用的对象被删了重建，相关的 PL/SQL 会变成INVALID状态，得重新编译。线上做表结构变更的时候，这个连锁反应要提前评估。

还有一点很实用：%TYPE也可以引用另一个变量或者游标字段。写v_total v_sum.salary%TYPE是合法的，前提是v_sum在同一个声明段里先声明。这种用法在临时计算场景里能省掉不少类型推导。

2.3 CONSTANT 与 NOT NULL 的组合坑

CONSTANT的语义很简单：声明时给初值，之后不能再赋值，赋值会编译报错。它的实际价值不在技术层面，而在可读性和可维护性——把0.13这样的魔法数字提成c_tax_rate CONSTANT NUMBER := 0.13，半年后回来看代码的人（很可能是你自己）能立刻知道这是什么。

坑在于组合使用。v_flag BOOLEAN NOT NULL := FALSE是合法的，但如果你写成v_flag BOOLEAN NOT NULL，编译就不通过，因为没有初值。而v_x NUMBER NOT NULL := NULL这种写法，虽然语法上过得去，语义上等于自相矛盾，运行起来该变量一旦参与赋值就会报错。我在代码评审时会把这类写法当作逻辑混乱的信号，要求作者重新想清楚：这个变量到底是"必须有值"还是"可能没有值"？

另一个实用细节：CONSTANT变量不能出现在SELECT INTO的接收列表里，也不能作为OUT参数的实际参数。这不算坑，但很多人在写工具函数时第一次遇到会愣一下，以为是权限或者版本问题。

3. 数据类型怎么挑：从 NUMBER 到 BOOLEAN

3.1 数值类型：NUMBER、PLS_INTEGER、SIMPLE_INTEGER 的真实差别

三种数值类型都能存整数，但底层实现和性能差得挺远：

NUMBER是数据库原生类型，最大精度 38 位，能存小数，代价是所有运算都走数据库的数值运算库。好处是它和表列类型一致，SELECT ... INTO出来不用转换，跨版本行为稳定。

PLS_INTEGER和BINARY_INTEGER是 PL/SQL 引擎的原生整数类型，用机器字长存储，运算走硬件指令或者接近硬件的路径，速度明显快于NUMBER。范围取决于平台，32 位机器上大约是 ±2^31，64 位机器上大约是 ±2^63。溢出会抛 ORA-01426。

SIMPLE_INTEGER是 11g 引入的，本质上是PLS_INTEGER加了NOT NULL约束，并且不做溢出检查。这意味着它更快，但溢出时不会报错，而是静默回绕——一个很大的正数加一下变成负数，不抛异常。我个人只在循环计数器这种范围完全可控、确定不会溢出的场景用它，涉及业务数据的计算一律不用。

选型的判断标准其实很朴素：

有个细节值得单独说：NUMBER不指定精度和标度时，行为等同于NUMBER(*,*)，是个浮点语义的东西，不是定点。做金额累加如果全程用不带精度的NUMBER，某些极端情况下会出现末位偏差。做财务计算老老实实写NUMBER(18,2)，哪怕变量只是临时中转——这个习惯能省掉大量对账麻烦。

3.2 字符类型：VARCHAR2 的长度语义最容易被误解

VARCHAR2(10)里的 10 到底是字节还是字符，取决于两件事：定义时的长度语义设置，以及显式写没写CHAR/BYTE关键字。默认值由初始化参数NLS_LENGTH_SEMANTICS决定，很多库配的是BYTE。

区别非常实际：在AL32UTF8字符集下，一个汉字通常占 3 个字节，VARCHAR2(10)按字节算只能塞下 3 个汉字。你以为能存 10 个，实际塞第 4 个就报 ORA-12899 value too large。而VARCHAR2(10 CHAR)就是实打实 10 个字符。涉及中文姓名、地址、商品名的字段，我一律显式写CHAR语义，宁可稍微多占点空间。

PL/SQL 变量和表列还有一处不对称：PL/SQL 里VARCHAR2最大可以到 32767 字节，而表列在标准配置下是 4000 字节（开启扩展数据类型后可以到 32767）。这意味着中间变量能装下的数据，往表里一插就可能超长。这个差异在处理长文本拼接、日志写入时特别容易撞上。

CHAR类型要特别小心，它是定长的，不足部分用空格补齐。CHAR(10)存'abc'实际是'abc' + 7 个空格。比较的时候 Oracle 用空格填充语义，'abc' = 'abc '可能返回真，但用LENGTH拿到的是 10。做校验、拼接口报文时，CHAR的空格会带入下游，我基本不在新代码里用它。

3.3 BOOLEAN 的边界与替代方案

BOOLEAN只能存在于 PL/SQL 里，不能作为表列类型，也不能直接写进 SQL 语句的WHERE条件。这个限制源于 SQL 引擎的三值逻辑（真、假、NULL）和 PL/SQL 的布尔类型不是同一套实现。较新的版本对"在 SQL 里调用返回 BOOLEAN 的 PL/SQL 函数"放宽了限制，但表列层面的限制在很长时间里都存在。

实际写法上，BOOLEAN用在过程内部的标志位没问题，一旦要出这个边界就得转换。常见的几种方案：

• 表列用CHAR(1)存'Y'/'N'，PL/SQL 内部转成BOOLEAN，转换点集中在出入口。
• 表列用NUMBER(1)存 0/1，数据库层面可以加CHECK约束。
• 新版数据库提供了 SQL 层的布尔类型，如果库版本允许，可以用，但要考虑迁移成本和工具链兼容性。

转换的时候别忘了 NULL。BOOLEAN有真、假、NULL 三态，从CHAR(1)转过来时，如果原值是 NULL，转成BOOLEAN也是 NULL，IF v_flag THEN在 NULL 时走的是ELSE分支，这个行为经常和"未设置"的业务语义对不上。我在转换函数里会显式处理：IF p_val = 'Y' THEN RETURN TRUE; ELSE RETURN FALSE; END IF;，把 NULL 收拢成FALSE，除非业务上确实需要区分三态。

4. 作用域、命名与遮蔽：一个变量到底被谁看见

4.1 块嵌套与标签限定

PL/SQL 支持块嵌套，内层块能看见外层块声明的变量，反之不行。这个规则本身简单，问题出在同名遮蔽上：

DECLARE v_x NUMBER := 1; BEGIN DECLARE v_x NUMBER := 2; -- 遮蔽了外层 BEGIN DBMS_OUTPUT.PUT_LINE('inner: ' || v_x); -- 2 END; DBMS_OUTPUT.PUT_LINE('outer: ' || v_x); -- 1 END; /

内层块结束后，内层变量销毁，外层变量恢复可见。想要在内层访问外层的同名变量，得用标签：

<<outer_block>> DECLARE v_x NUMBER := 1; BEGIN DECLARE v_x NUMBER := 2; BEGIN DBMS_OUTPUT.PUT_LINE('inner: ' || v_x); -- 2 DBMS_OUTPUT.PUT_LINE('outer: ' || outer_block.v_x); -- 1 END; END; /

标签还有第二个用途：EXIT outer_loop WHEN ...直接从嵌套循环里跳出来，比设标志位干净得多。

我个人的规矩是：除了循环里的临时下标，不允许任何两个变量重名。遮蔽能省掉几个字符，代价是排查问题时多看十几分钟。代码评审看到内层重复声明外层已有变量，我会直接要求改名。

4.2 变量名与列名同名的经典陷阱

这个坑杀伤力极大，而且特别隐蔽。看这段：

DECLARE deptno NUMBER := 20; v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM employees WHERE deptno = deptno; END; /

这里的WHERE deptno = deptno不会拿变量去比列。PL/SQL 的解析规则是：在 SQL 语句内部，同名的标识符优先解析为列名。所以这行等价于WHERE deptno = deptno，恒真（忽略 NULL），返回全表行数。如果你的业务预期是"部门 20 的人数"，结果会大得离谱，而且完全不报错。

解法有两个，都推荐用上：

• 变量加前缀，比如v_deptno、p_deptno，从命名上就和列名区分开。这是最省事的做法，几乎所有团队都这么干。
• 如果确实要同名，用块标签限定：WHERE deptno = block_label.deptno。

顺带说个同源问题：游标或者存储过程的参数如果和列名同名，WHERE col = param同样会把param解析成列名。存储过程参数建议统一加p_前缀，游标参数加c_或者直接用p_，把命名规范当纪律执行，比事后查 bug 划算得多。

4.3 包变量：会话级状态的双刃剑

包规范里声明的变量，生命周期是整个会话。它可以做几件 PL/SQL 局部变量做不到的事：

跨调用保持状态。比如一个查询配置的函数，第一次调用读表，把结果存进包变量，后面直接返回。这种"懒加载"模式下，配置读取从每秒几千次降到一次。

在会话内共享。多个过程需要读同一份上下文时，包变量是天然的传递通道，比层层传参干净。

只初始化一次。包变量的初始化在会话内第一次访问包时执行，后续不再执行。可以用这个特性做一次性的注册动作。

代价也很明确：

连接池下状态会残留。应用服务器用连接池，你的"新会话"往往是复用的旧会话，包变量里还是上一轮的值。必须显式在入口重置，或者在包规范里加一个reset过程，在获取连接后调用。我见过统计翻倍、权限串号的线上故障，根因都是这个。

它不参与事务回滚。包变量赋值不走事务日志，ROLLBACK之后变量还保持修改后的值。如果业务逻辑依赖"回滚到干净状态"，得自己写补偿。

并发会话之间互相隔离，但同一会话内的递归调用共享。这一点在写递归函数时要小心，同一份状态被递归层级反复修改，很容易算错。

我的实践是：包变量只用于可重建的缓存，也就是丢了也能重新算出来的数据；一旦涉及业务状态，宁可用临时表或者显式传参。缓存的失效策略还要写清楚，什么时候重置、谁负责重置，写在包的头部注释里。

5. 绑定变量与替换变量：SQL 层那套完全不同的玩法

5.1 绑定变量的声明与执行

绑定变量是 SQL 层面的占位符，写法是一个冒号加名字。在命令行工具里，得先声明再赋值：

VARIABLE b_deptno NUMBER VARIABLE b_name VARCHAR2(50) EXEC :b_deptno := 20 EXEC :b_name := 'RESEARCH' SELECT employee_id, employee_name FROM employees WHERE deptno = :b_deptno AND employee_name LIKE :b_name || '%'; PRINT b_deptno

也能在 PL/SQL 块里直接给绑定变量赋值：

BEGIN :b_deptno := 30; END; /

绑定变量的关键特性：SQL 文本不变，值在执行时传入。数据库对这条 SQL 只做一次解析（软解析或硬解析取决于共享池里有没有），后续执行直接复用游标和执行计划。这在批量操作和 OLTP 高并发场景下是决定性的。

绑定变量有明确的使用边界，踩过才知道：

• 不能用在 DDL 或者对象名的位置。DROP TABLE :tab不合法，表名、列名、ORDER BY的排序方向都没法绑定，只能用动态 SQL 拼接，而拼接就必须自己做白名单校验。
• 不能直接绑定NULL字面量，得先赋一个 NULL 值进去。
• IN列表不能整体绑定。WHERE id IN (:list)只当单个值处理，想要动态长度列表，要么用OR展开，要么用集合类型配合TABLE()函数。

5.2 替换变量与它的隐藏风险

替换变量是 SQL*Plus 层面的文本替换，发生在语句发给数据库之前。三种常见写法：

-- 方式一：DEFINE 预定义 DEFINE v_dept = 20 SELECT * FROM employees WHERE deptno = &v_dept; -- 方式二：&& 只提示一次，之后复用 SELECT * FROM employees WHERE deptno = &&dept_input; -- 方式三：ACCEPT 交互式接收 ACCEPT v_date PROMPT '请输入日期(YYYY-MM-DD): ' SELECT * FROM orders WHERE order_date >= TO_DATE('&v_date','YYYY-MM-DD');

好处是写脚本传参方便，一条 SQL 模板能配不同参数跑。坏处：

每次值不同，SQL 文本就不同，等于每条都是新语句。循环里用替换变量跑一千次，就是一千次硬解析，共享池里躺着一千份几乎一样的执行计划。数据量小没感觉，量大直接拖垮性能。

存在注入风险。替换是纯文本替换，值里带引号、带OR 1=1会改变语句结构。脚本是内部用的还好，一旦被包装成对外接口，这就是漏洞。

依赖 NLS 设置。用&直接塞日期字符串进去，能不能解析成功取决于会话的日期格式参数，换台机器结果就变。必须显式TO_DATE(..., 'YYYY-MM-DD')。

还有个容易忽略的点：&在字符串字面量里也会触发替换。如果你的字符串里含&，比如邮箱拼接或者 URL 参数，脚本会卡在等待输入的状态。这时候需要用SET DEFINE OFF关闭替换，或者把&换成CHR(38)。

5.3 绑定变量窥探与游标共享的真实影响

绑定变量让 SQL 文本唯一，但执行计划的选择依赖具体值。同一个WHERE status = :b1，传'ACTIVE'可能只返回几十行，适合走索引；传'CLOSED'可能返回九成数据，走全表扫描更快。数据库第一次执行时"偷看"了绑定值，缓存下的计划可能对后续的取值极不友好——这就是绑定变量窥探。

较新的版本用自适应游标共享来缓解：同一条 SQL 在不同绑定值下允许存在多份执行计划，由系统判断什么时候该生成新计划。但这不是万能药，它有自己的启发式规则和资源开销。

实操上怎么处理这类问题：

• 数据分布严重倾斜的列，考虑用字面量或者/*+ CURSOR_SHARING_EXACT */之类的提示，让不同取值走不同计划。
• 定期查共享池里的高负载 SQL，看是否存在"同一条 SQL 多份计划、且执行效率差异巨大"的情况。
• 统计信息必须及时更新，计划选择的准确性高度依赖统计信息的质量。

5.4 一张对照表把四种"变量"彻底分清

这张表建议贴在工位上。遇到"值传不进去"或者"性能不对"的问题，先对着表确认自己用的是哪一类，能省掉一大半排查时间。

6. 复合类型与批量取数：让变量装得下一组值

6.1 RECORD 与 %ROWTYPE 的取舍

RECORD是自定义的结构体，字段类型自由组合；%ROWTYPE是表或游标的整行镜像。写数据处理逻辑时，我优先用%ROWTYPE，因为列增删时不用改代码；只有在需要"临时拼一个不属于任何表的结构"时才自定义RECORD。

DECLARE TYPE t_summary IS RECORD ( dept_name VARCHAR2(60), emp_count PLS_INTEGER, total_sal NUMBER(18,2) ); r_sum t_summary; BEGIN SELECT d.dept_name, COUNT(e.employee_id), NVL(SUM(e.salary), 0) INTO r_sum.dept_name, r_sum.emp_count, r_sum.total_sal FROM departments d LEFT JOIN employees e ON e.deptno = d.deptno WHERE d.deptno = 20 GROUP BY d.dept_name; END; /

SELECT INTO有个硬性要求：必须且只能返回一行。返回零行抛 ORA-01403，返回多行抛 ORA-01422。用聚合函数加GROUP BY的查询，如果分组结果为空，一行都不返回，同样抛 ORA-01403。处理方法有两种：要么加NVL配合始终返回一行的写法（比如把聚合放到子查询里再外层包一层），要么用BEGIN ... EXCEPTION WHEN NO_DATA_FOUND THEN ...显式捕获。我倾向于后者，意图更明确。

6.2 集合三兄弟：关联数组、嵌套表、变长数组

需要装"一组值"时，三种集合类型各有适用面：

关联数组最灵活，下标可以是负数、可以不连续，甚至可以用字符串当键，做内存字典特别顺手。嵌套表适合需要在 SQL 里用TABLE()展开的场景。变长数组适合长度确定的小集合，比如固定几个状态码。

有个高频操作要记住：集合用之前必须初始化。嵌套表和变长数组写t := t_type();才能用，否则报 ORA-06531。关联数组不需要初始化，直接赋值就行。这个不对称经常让人栽跟头。

遍历集合时，如果下标可能不连续，别用FOR i IN 1..t.COUNT，会撞上不存在的下标。正确做法是t.FIRST和t.NEXT(i)：

DECLARE TYPE t_map IS TABLE OF NUMBER INDEX BY PLS_INTEGER; m t_map; i PLS_INTEGER; BEGIN m(10) := 1; m(30) := 2; -- 下标不连续 i := m.FIRST; WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE(i || ' => ' || m(i)); i := m.NEXT(i); END LOOP; END; /

用1..COUNT遍历上面这个集合，第二次循环就会因为下标 11 不存在而报错。这个 bug 在关联数组场景里出现频率极高。

6.3 BULK COLLECT 与 LIMIT：批量取数的正确姿势

逐行FETCH INTO是 PL/SQL 性能的经典杀手。每次 fetch 都是一次上下文切换，一万行就是一万次切换，耗时主要花在切换上而不是数据上。BULK COLLECT一次取一批，切换次数降几个数量级。

DECLARE TYPE t_emp_tab IS TABLE OF employees%ROWTYPE; l_rows t_emp_tab; CURSOR c_emp IS SELECT * FROM employees WHERE deptno = 20; BEGIN OPEN c_emp; LOOP FETCH c_emp BULK COLLECT INTO l_rows LIMIT 500; EXIT WHEN l_rows.COUNT = 0; FORALL i IN 1 .. l_rows.COUNT INSERT INTO emp_archive VALUES l_rows(i); END LOOP; CLOSE c_emp; END; /

LIMIT的取值是门经验活。不设LIMIT是最常见的错误，一次性把几百万行拉进 PGA，轻则会话变慢，重则 ORA-04030 内存不足。设太小，循环次数多，收益打折。我一般在 500 到 5000 之间试，先用 1000 跑一遍看内存和耗时，再往上调。表很宽（列多、有大字段）时往下调，表窄可以往上调。

FORALL和普通FOR循环的区别要搞清楚：FORALL是把整个集合一次性交给 SQL 引擎，减少交互次数；它不是循环语句，里面只能有一条 DML。想在里面写IF判断是不行的，得先把数据过滤好再交给FORALL。

BULK COLLECT还可以配合RETURNING INTO用在 DML 之后，把受影响的行抓回来。批量更新后需要知道具体改了哪些行时很有用：

DECLARE TYPE t_id_tab IS TABLE OF employees.employee_id%TYPE; l_ids t_id_tab; BEGIN UPDATE employees SET salary = salary * 1.05 WHERE deptno = 20 RETURNING employee_id BULK COLLECT INTO l_ids; DBMS_OUTPUT.PUT_LINE('updated: ' || l_ids.COUNT); END; /

7. 参数、动态 SQL 与变量的传递通道

7.1 参数模式与默认值

存储过程的参数本质上是"从外部传入的变量"，三种模式的行为差异必须清楚：

IN参数在过程内部是只读的，改它编译不过。OUT参数进过程时是 NULL，不管你传什么进去，这是很多人以为"能传个初始值进去"结果拿到 NULL 的原因。IN OUT才会保留传入值。

默认值的写法要注意一个陷阱：默认值可以是表达式，但不能引用同一参数列表里后面才声明的参数。写p_b VARCHAR2 DEFAULT p_a当p_a在后面声明时会报错。另外，如果中间某个参数用了默认值，调用时想跳过它，得用命名参数写法：proc(p_a => 1, p_c => 3)，位置参数没法跳过。

NOCOPY提示值得单独提一句。默认情况下OUT和IN OUT参数在进出过程时都会做一次值拷贝，集合类型大起来这个拷贝开销很可观。加NOCOPY可以让它走引用传递：

PROCEDURE load_data(p_rows IN OUT NOCOPY t_big_tab) IS ...

代价是异常安全性下降：正常返回才保证值写回，如果过程中途抛异常，实参的状态是不确定的。用在批量导入这类"要么全成要么重来"的场景没问题，用在需要精确回滚的逻辑里就危险。

7.2 EXECUTE IMMEDIATE 里的 USING 与 INTO

动态 SQL 用变量做参数，语法上有个固定套路：

DECLARE l_sql VARCHAR2(500); l_name VARCHAR2(50); l_cnt PLS_INTEGER; BEGIN l_sql := 'SELECT employee_name FROM employees WHERE employee_id = :1'; EXECUTE IMMEDIATE l_sql INTO l_name USING 100; l_sql := 'UPDATE employees SET salary = salary * :1 WHERE deptno = :2'; EXECUTE IMMEDIATE l_sql USING 1.05, 20; COMMIT; END; /

规则：INTO接查询结果的接收变量，USING接传入值，默认都是IN模式。要让动态 SQL 往外写值，得写成USING OUT：

EXECUTE IMMEDIATE 'BEGIN calc_total(:1, :2); END;' USING IN 20, OUT l_total;

几个必须记住的限制：

• USING只能传值，不能传对象名、列名。所有标识符位置都得靠字符串拼接，而拼接就必须做校验。
• 占位符在动态 SQL 里用:1、:2这种位置写法更稳，用命名写法在某些版本上会有兼容问题。
• 动态 SQL 里的绑定变量不会让语句进入普通游标缓存，重复执行同样文本仍然可能反复解析。DBMS_SQL或者OPEN FOR配合游标变量在极端场景下更可控。

拼接标识符时的校验，我通常用一个白名单函数处理，只允许字母、数字、下划线，长度限制在合理范围内，并且和元数据表比对确认对象真实存在。字符串拼接进入 SQL 的地方，一律过这道卡，没有例外。

8. 常见问题与排查速查

8.1 报错速查表

8.2 三个真实踩坑场景的排查路径

场景一：累加结果是 NULL。一个统计存储过程，跑完总额是空的，明细表里明明有数据。排查顺序：先看累加变量有没有初始化，v_total NUMBER;这样声明就是 NULL，v_total := v_total + x永远是 NULL；再看SUM的结果是不是 NULL，聚合没有匹配行时SUM返回 NULL 不是 0，得用NVL(SUM(x), 0)；最后看SELECT INTO有没有覆盖掉已经算好的值。这三个原因占了绝大多数。

场景二：明明有数据却查不到。一段WHERE deptno = deptno的代码，业务说"按部门过滤没生效"。八成就是变量名和列名撞了，被解析成了恒真条件。另一种可能是隐式类型转换：变量是NUMBER而列是VARCHAR2，或者反过来，索引失效加上比较语义变化，结果就飘了。第三种是字符集或者尾部空格问题，CHAR列和VARCHAR2变量比较时补空格导致不等。排查时用DBMS_OUTPUT把实际传入的值打出来，别猜。

场景三：批量任务越跑越慢。第一轮快，后面越来越卡。查三处：共享池里是不是堆了成百上千条结构相同、字面量不同的 SQL（替换变量或者拼接导致）；BULK COLLECT是不是没设LIMIT导致 PGA 持续增长；包变量里缓存的集合是不是只增不减。第三点在长事务里特别致命，一个关联数组当缓存用，从不清理，跑一晚上能吃掉几个 G 的内存。

8.3 一份可以直接抄的变量声明规范

我们内部执行的一套规矩，据反馈确实减少了不少事故，直接列出来：

1. 变量统一加前缀：局部变量v_，参数p_，常量c_，全局g_。前缀不是形式主义，是防止列名遮蔽的最低成本手段。
2. 数字变量必须显式初始化，除非它在每个分支里都会被赋值。
3. 优先用%TYPE和%ROWTYPE，别手工抄类型。
4. 字符变量显式写CHAR长度语义，涉及中文的一律这么写。
5. 声明段不做任何业务计算，初值只给常量或者SYSDATE这类无副作用的表达式。
6. 包变量必须在包规范头部注释里写清生命周期和重置时机，没写清楚的不允许进主干。
7. 脚本里的替换变量只用于人类交互输入，自动化的批量操作一律用绑定变量。
8. 任何进入字符串拼接的标识符必须过白名单校验函数。
9. BULK COLLECT一律带LIMIT，没有例外。

第 9 条是我加的硬性规定，起因是一次夜间批处理把 PGA 撑爆，会话被系统干掉，第二天要重跑六小时的任务。加了个LIMIT 1000之后，同样的任务内存占用稳定在几十兆，总耗时反而降了一点点——因为避免了频繁的内存扩张和换页。

最后分享一个我自己常用的小技巧：调试期间在DECLARE段末尾加一个PROCEDURE p(...)打印辅助过程，参数用VARCHAR2接收，调用时直接p('v_total', v_total)。比到处写DBMS_OUTPUT.PUT_LINE干净，也不会因为类型不同反复改格式串。上线前把调用处注释掉就行，声明留着不占运行开销。这个习惯帮我省过好几次因为变量没赋值或者被意外覆盖导致的长时间排查。