前几天有位做数据迁移的朋友丢过来一段脚本，说在客户端里跑得好好的，搬到调度平台上就报 PLS-00103，提示某个位置遇到了不该出现的符号。我打开一看，问题朴素得让人想笑：变量的声明被塞进了 BEGIN 之后。这类事故在 Oracle 环境里特别常见，原因在于“定义变量”这件事在这个数据库里根本不止一种写法——PL/SQL 块里的 DECLARE、客户端工具的替换变量、会话级的绑定变量、存储过程的形参、包体里的全局变量、动态 SQL 里的 USING 占位符，每一种的生命周期、可见范围、绑定时机都不一样，混着用就必然出事。更麻烦的是，很多错误不报异常，只是结果悄悄不对，等发现的时候数据已经跑偏了。

这篇内容把 Oracle 中定义变量这件事从头到尾捋一遍：什么场景该用哪种定义方式、为什么这么选、写的时候有哪些必须交代的细节，以及我这些年真正被坑过的几个地方。变量名和列名撞车、空串等于 NULL、包变量跨会话保持状态这几个，吃过亏的人都懂。适合刚接触 PL/SQL 的朋友对照着抄，也适合写了几年但一直没系统梳理过的同行查漏补缺。

1. 先把“变量”这个词拆开：Oracle里至少六种东西都被叫作变量

1.1 三种最容易混淆的变量形态

新手最容易犯的错，是把客户端工具的替换变量当成 PL/SQL 变量来用。这两者的差别大到什么程度？替换变量是在文本层面工作——客户端在发送 SQL 到数据库之前，把&name这样的占位符替换成实际字符串，数据库服务器压根不知道有过“变量”这回事。而 PL/SQL 变量是编译期就确定的实体，有类型、有作用域、有内存地址，编译器要检查类型是否匹配。

绑定变量又是另一回事。它以:name的形式出现在 SQL 文本里，服务器收到的是一个带占位符的语句加上一组绑定值，游标可以被共享复用。它既不是文本替换，也不是 PL/SQL 的局部变量，而是 SQL 引擎层面的输入参数。

我在排查性能问题时见过太多次这种场景：开发同学在客户端里用&id拼了一段 SQL，手工跑很快，上线后换成程序拼字符串，执行计划一路走偏。根因就是把三种变量形态混为一谈。

1.2 六种定义位置速查

把常见的定义位置列成一张表，遇到问题先定位自己处在哪一格，能省下大量试错时间。

这张表里最需要留意的是包规范变量——它的生命周期是整个数据库会话，不是一次调用。这意味着同一个连接上第二次调用过程时，读到的可能是上一次残留的值。后面我会单独讲这个坑。

2. PL/SQL块的声明段：写法、初始化与三个绕不开的细节

2.1 声明段的骨架和初始化顺序

一个完整的匿名块结构是这样的：

DECLARE v_count NUMBER := 0; v_name VARCHAR2(50) := 'unknown'; c_tax_rate CONSTANT NUMBER(5,4) DEFAULT 0.0600; v_flag BOOLEAN NOT NULL := FALSE; BEGIN v_count := v_count + 1; DBMS_OUTPUT.PUT_LINE('count=' || v_count || ', name=' || v_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; /

几个必须交代的点。第一，:=和DEFAULT完全等价，选哪个纯看团队规范，但同一个项目里最好统一，混着写会让代码 review 变得很累。第二，声明了NOT NULL的变量必须在声明处初始化，否则编译期直接报 PLS-00218，这个限制很多人第一次遇到会懵。第三，常量必须初始化，这个不用多说。

初始化顺序上有一个容易忽略的规则：同一个声明段里，后面的变量可以引用前面已经声明的变量，反过来不行。所以下面这段代码会编译失败：

DECLARE v_total NUMBER := v_price * 2; -- 编译报错，v_price 还没声明 v_price NUMBER := 100; BEGIN NULL; END; /

这不是什么高级知识，但确实有人习惯按字母顺序排列变量，结果就踩上了。我的做法是：把有依赖关系的变量按依赖顺序摆，没有依赖的再按业务含义分组，反正也没人规定必须字母序。

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

写死类型是最省事也最容易出事的做法。表字段从VARCHAR2(20)扩到VARCHAR2(200)，代码里所有写死 20 的地方都得改一遍，漏一个就是一个ORA-06502: PL/SQL: 数字或值错误。

%TYPE直接引用列的类型：

DECLARE v_emp_name emp.emp_name%TYPE; v_sal emp.sal%TYPE; BEGIN SELECT emp_name, sal INTO v_emp_name, v_sal FROM emp WHERE emp_id = 100; END; /

这样表结构一改，变量类型自动跟着变，不用动代码。%ROWTYPE更进一步，整个游标行一把抓：

DECLARE v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE emp_id = 100; DBMS_OUTPUT.PUT_LINE(v_emp.emp_name || ' / ' || v_emp.sal); END; /

%ROWTYPE的代价是SELECT *——如果表有一百个字段而你只用两个，内存和网络传输都在浪费。我一般在大表上避免用%ROWTYPE，只在字段少、结构稳定的配置表上用它换省事。另外游标的%ROWTYPE更好用一些，CURSOR c IS SELECT emp_id, emp_name FROM emp;配合c%ROWTYPE，只用到的列，类型还能跟着游标定义走。

有个细节值得说：%TYPE引用的是声明时刻的类型，如果表结构在编译之后又改了，包或过程会变成INVALID状态，下次调用时自动重编译。这个自动重编译在很多场景下是好事情，但如果你依赖的是固定长度的缓冲区逻辑，重编译之后行为可能会变。

2.3 嵌套块的作用域与同名遮蔽

PL/SQL 的块可以嵌套，内层块能访问外层块声明的变量，反过来不行。真正麻烦的是同名遮蔽：

DECLARE v_num NUMBER := 1; BEGIN DECLARE v_num NUMBER := 2; BEGIN DBMS_OUTPUT.PUT_LINE('inner=' || v_num); -- 输出 2 END; DBMS_OUTPUT.PUT_LINE('outer=' || v_num); -- 输出 1 END; /

内层的v_num把外层的遮蔽掉了，外层想要访问被遮蔽的变量，只能靠块标签：

<<outer_block>> DECLARE v_num NUMBER := 1; BEGIN DECLARE v_num NUMBER := 2; BEGIN DBMS_OUTPUT.PUT_LINE(outer_block.v_num); -- 输出 1 END; END outer_block; /

我个人的态度很明确：靠块标签解决遮蔽问题，说明命名已经出了状况。真正该做的是给不同层级的变量加前缀区分，比如外层用v_、内层用lv_、参数用p_、全局用g_。这种匈牙利命名法被不少人嫌弃过时，但在 PL/SQL 这种作用域规则比较宽松的语言里，它省下来的排查时间远大于那点阅读负担。

3. 客户端工具里的DEFINE、ACCEPT与VARIABLE：脚本参数化的正确姿势

3.1 替换变量的两种写法与那个害人的 &

DEFINE定义的是替换变量，本质是文本宏。&name每次遇到都会提示输入，&&name只在第一次提示，后续复用同一份输入：

DEFINE v_dept = 10 SELECT * FROM emp WHERE deptno = &v_dept; SELECT * FROM emp WHERE deptno = &&v_dept;

两者差别看起来小，实际影响不小。用&写一个要在脚本里出现十次的变量，等于让执行的人敲十次回车；而且十次里只要有一次手抖敲错，前后逻辑就自相矛盾。我写脚本时基本只用&&，一次性定义清楚，后面全部引用。

真正让人抓狂的是字符串里出现的&。比如一条插入语句里带了个'AT&T'，客户端会把它当成替换变量提示输入。这时候需要SET DEFINE OFF关掉替换功能，或者改用别的触发字符（SET DEFINE ^）。这类问题在包含 URL、邮件地址、公司名称的脚本里特别常见。

还有&1到&9这组位置参数，配合命令行传参用得很多：

sqlplus user/pass@service @script.sql 20240101

-- script.sql SELECT * FROM orders WHERE order_date >= TO_DATE('&1', 'YYYYMMDD');

这种写法在批处理里很实用，但要记住它在客户端完成替换，SQL 文本每次都是新的，硬解析跑不掉。数据量不大、执行频率低的任务无所谓，高频调用的核心脚本就得考虑别的方案。

3.2 VARIABLE声明绑定变量的完整链路

VARIABLE声明的是绑定变量，它的生命周期是整个会话，可以在 SQL 和 PL/SQL 之间来回传递：

VARIABLE v_count NUMBER VARIABLE v_name VARCHAR2(50) EXEC :v_count := 0 BEGIN SELECT COUNT(*) INTO :v_count FROM emp WHERE deptno = 10; SELECT MAX(emp_name) INTO :v_name FROM emp WHERE deptno = 10; END; / PRINT v_count PRINT v_name SELECT * FROM emp WHERE deptno = :v_count;

这条链路做调试的时候非常好用——你可以把过程内部算出来的中间值输出到绑定变量，再拿去做后续查询验证。比满屏DBMS_OUTPUT.PUT_LINE清爽得多，尤其是输出内容很长、客户端缓冲区又不够的时候。

一个容易忽略的限制：VARIABLE不能声明BOOLEAN、ROWTYPE、%TYPE这类 PL/SQL 专有类型，只能是 SQL 能识别的类型。因为它的可见范围是 SQL 引擎那一侧，PL/SQL 的私货它认不了。

3.3 ACCEPT 让脚本可交互，也悄悄破坏了自动化

ACCEPT适合做交互式脚本：

ACCEPT v_dept PROMPT '请输入部门编号: ' NUMBER ACCEPT v_start PROMPT '请输入起始日期(YYYYMMDD): ' DEFAULT '20240101' SELECT * FROM emp WHERE deptno = &v_dept;

ACCEPT的好处是能直接指定类型（NUMBER/CHAR/DATE）和默认值，输入校验比裸的&强一点。坏处也很明显：一旦脚本被挂到调度器上无人值守，PROMPT就会卡在那里等输入，任务假死。

我的处理习惯是给这类脚本加一个开关参数，交互模式下用ACCEPT，批处理模式下从位置参数取：

DEFINE v_mode = 'BATCH' COLUMN dummy NEW_VALUE v_dept NOPRINT SELECT CASE WHEN '&v_mode' = 'BATCH' THEN '&1' ELSE '10' END AS dummy FROM dual;

这写法谈不上优雅，但能保证同一份脚本在两种场景下都能跑，省去了维护两个版本的麻烦。

4. 绑定变量为什么值得较真：从硬解析到执行计划稳定性

4.1 一条字面量SQL在共享池里经历了什么

服务器收到一条 SQL，先拿它的文本做哈希，去共享池的库缓存里找有没有一模一样的游标。找到叫软解析，找不到就要走优化器生成执行计划，这叫硬解析。硬解析的开销不只是 CPU——还要申请 latch、占用共享池内存、可能触发 LRU 淘汰其他游标。

问题在于文本必须完全一致才算命中。WHERE id = 1和WHERE id = 2在库缓存眼里是两条毫不相干的 SQL。所以用拼接字面量的方式跑循环，等于每轮都在做一次硬解析。

我在一套测试环境上做过对比：循环执行一万次查询，一次用字面量拼接，一次用绑定变量，其余条件完全一致。字面量那组的硬解析次数接近一万，CPU 时间肉眼可见地高出一截，共享池里堆了大量只执行过一次的游标。绑定变量那组只硬解析一次，后面全是软解析甚至直接复用。

这张表不是要你无脑全上绑定变量，后面会讲它的反作用。

4.2 改写前后的真实差异

拼接字面量的写法一般长这样：

FOR r IN (SELECT id FROM orders WHERE status = 'NEW') LOOP EXECUTE IMMEDIATE 'UPDATE orders SET flag = 1 WHERE id = ' || r.id; END LOOP;

改写成绑定变量只需要动一处：

FOR r IN (SELECT id FROM orders WHERE status = 'NEW') LOOP EXECUTE IMMEDIATE 'UPDATE orders SET flag = 1 WHERE id = :1' USING r.id; END LOOP;

USING把值走绑定通道送进去，SQL 文本固定不变。改动量极小，收益在数据量大时非常明显。我见过一个批量更新任务，改完之后整体耗时下降了一半多，原因就是硬解析的 CPU 开销和 latch 争用被消掉了。

4.3 绑定变量窥探与自适应游标：不绑定不对，绑了也不一定对

绑定变量不是银弹。优化器在第一次执行时要看到具体值才能估算选择率，这个动作叫绑定变量窥探。问题是它窥探到的第一个值可能没有代表性——某个字段绝大多数值只对应几十行，偶尔有一两个值对应上百万行。如果第一次执行恰好用了那个上百万行的值，生成的执行计划（比如全表扫描）会被后续所有小结果集的查询复用，性能直接崩掉。

后续版本引入了自适应游标共享，允许同一个 SQL 根据不同的绑定值生成多个子游标，缓解了这个问题但没根治。实际工作中我的判断标准是这样：

• 字段分布均匀、执行频率高 → 绑定变量，收益稳定。
• 字段严重倾斜、高低峰差异大 → 慎重，必要时对极端值单独走字面量或加 hint。
• 一次性跑批、语句不重复 → 绑定与否差别不大，重点看代码可读性。

提示：判断字段是否倾斜，可以先跑一次SELECT col, COUNT(*) FROM t GROUP BY col ORDER BY 2 DESC看看头部值的占比。头部占比超过两成、尾部大量只有个位数，基本就属于倾斜严重，绑定变量要谨慎对待。

5. 动态SQL里的变量传递：EXECUTE IMMEDIATE的USING与RETURNING

5.1 按位置传参与按名称传参

USING支持两种传参方式。按位置是最常见的：

EXECUTE IMMEDIATE 'UPDATE emp SET sal = sal * :1 WHERE deptno = :2' USING 1.1, 20;

按名称写起来啰嗦一点，但在参数多、顺序容易搞混的场景下更安全：

EXECUTE IMMEDIATE 'UPDATE emp SET sal = sal * :1 WHERE deptno = :2' USING IN ratio => 1.1, IN dept_id => 20;

我给的建议很简单：参数超过三个就用按名称。曾经有个批量作业里参数顺序写反了，两个都是 NUMBER 类型，编译期毫无提示，跑出来的结果是薪资乘了部门号，日志里还一切正常，等到业务方反馈数据异常已经过了两天。

USING还可以用IN、OUT、IN OUT标注方向，配合输出变量：

DECLARE v_new_sal NUMBER; BEGIN EXECUTE IMMEDIATE 'UPDATE emp SET sal = sal * :1 WHERE emp_id = :2 RETURNING sal INTO :3' USING 1.1, 100, OUT v_new_sal; DBMS_OUTPUT.PUT_LINE('调整后薪资: ' || v_new_sal); END; /

注意这里的INTO :3是 SQL 层面的返回占位符，跟 PL/SQL 的SELECT INTO不是一回事，别混。

5.2 输出参数与查询结果集

动态执行查询时，单行用INTO：

EXECUTE IMMEDIATE 'SELECT emp_name FROM emp WHERE emp_id = :1' INTO v_name USING 100;

多行结果集就得用OPEN FOR打开游标再循环取：

OPEN c_ref FOR 'SELECT emp_id, emp_name FROM emp WHERE deptno = :1' USING v_dept; LOOP FETCH c_ref INTO v_id, v_name; EXIT WHEN c_ref%NOTFOUND; -- 处理 END LOOP; CLOSE c_ref;

这套组合在报表类动态查询里用得非常多，因为查询条件是按调用方传入的字段组合动态拼出来的。条件拼字符串、值走USING，这个分界线要划清楚。

5.3 拼接还是绑定：安全和性能的双重账

动态 SQL 里最容易犯的错是把用户输入直接拼进语句。除了众所周知的注入风险，还有一个现实问题：拼进去的值会改变 SQL 文本，导致无法共享游标。有些输入里带单引号，拼接直接语法报错；换成绑定变量，这些问题一次性全消失。

-- 不要这样写 sqltext := 'SELECT * FROM emp WHERE emp_name = ''' || p_name || ''''; -- 应该这样 sqltext := 'SELECT * FROM emp WHERE emp_name = :1'; EXECUTE IMMEDIATE sqltext INTO v_emp USING p_name;

表名、列名这类标识符没法绑定，只能拼，那就必须做白名单校验。我会维护一张允许的表名列表，输入不在列表里直接抛异常，宁可报错也不放行。

6. 那些年踩过的变量坑：从报错码到静默的错误结果

6.1 变量名与列名撞车：不报错才是最可怕的

这个坑值得排在第一位。当 PL/SQL 变量名和表列名完全相同时，SQL 语句里的标识符解析优先取列名：

DECLARE emp_id NUMBER := 100; v_name VARCHAR2(50); BEGIN SELECT emp_name INTO v_name FROM emp WHERE emp_id = emp_id; -- 恒真条件 END; /

WHERE emp_id = emp_id两个都是列名，条件永远成立，结果就是全表扫描后随便取一行。如果表里只有一行还好，多行直接抛ORA-01422: 实际返回的行数超出请求的行数；如果恰好只有一行，你会发现结果莫名其妙是别人。

这类问题最难查的地方在于没有编译错误，没有明显异常。我的规避办法只有一条：变量一律带前缀。PL/SQL 代码里所有自定义标识符必须带v_、p_、g_、c_中之一，表列名不加前缀，从命名上就不可能撞上。团队里推这套规范的时候有人嫌麻烦，直到出过一次线上数据错误，全票通过。

6.2 空字符串、NULL 与 NOT NULL 的连锁反应

Oracle 里空字符串''等价于 NULL。这个设计跟大多数数据库都不一样，导致从其他数据库转过来的人频繁翻车：

DECLARE v_name VARCHAR2(20) := ''; BEGIN IF v_name IS NULL THEN DBMS_OUTPUT.PUT_LINE('确实是 NULL'); -- 会走这里 END IF; IF v_name = '' THEN DBMS_OUTPUT.PUT_LINE('永远不会到这里'); -- NULL 参与比较结果是 UNKNOWN END IF; END; /

判断空值只能用IS NULL，用= ''或者!= ''都是错的方向。更隐蔽的是拼接：'a' || NULL结果是'a'，看起来像正常拼接，实际吞掉了一个字段；而NULL + 1结果是 NULL，如果这个值再参与后续累加，整条计算链全是 NULL，最后插入表里就是空。

变量声明为NOT NULL时必须在声明处初始化，这个前面提过。我想补一句的是，业务上语义为“必须有值”的变量，加上NOT NULL约束其实是好事，编译器帮你守着，赋值阶段一旦为 NULL 立刻抛异常，比拖到落库阶段再报错要早得多。前提是你的业务逻辑确实不会有 NULL，比如计数类、标志位类的变量，加上去没坏处。

6.3 VARCHAR2(10) 到底能放几个汉字

VARCHAR2(10)里的 10 是字符还是字节，取决于NLS_LENGTH_SEMANTICS参数。默认是 BYTE 语义，在常见的多字节字符集下，一个汉字通常占 3 个字节，10 个字节实际只能放下 3 个汉字再加 1 个字节。存 4 个汉字妥妥地抛ORA-12899: value too large for column。

我现在的习惯是，凡是存中文的字段一律显式写CHAR语义，不依赖环境参数。这条规则推到建表和变量定义两边，能避免大量“测试环境能存、生产环境报错”的诡异问题——两个环境的初始化参数未必一致。

PL/SQL 里VARCHAR2的上限比 SQL 里高：SQL 里一般是 4000 字节，PL/SQL 里可以到 32767 字节。所以同样的VARCHAR2(5000)在 PL/SQL 块里合法，建表就报错。这个差异在写临时变量时要注意，别以为 PL/SQL 能定义表就能存。

6.4 包变量的会话状态：最难复现的那类问题

包规范里声明的变量，生命周期是整个会话：

CREATE OR REPLACE PACKAGE pkg_counter AS g_total NUMBER := 0; PROCEDURE add_one; END pkg_counter; / CREATE OR REPLACE PACKAGE BODY pkg_counter AS PROCEDURE add_one IS BEGIN g_total := g_total + 1; END; END pkg_counter; /

同一个连接上调用三次，g_total就是 3。换一个连接调用，又从 0 开始。这个特性被用来做会话级缓存很合适，用来做全局计数就是灾难——连接池里十个连接，计数器就是十份互不相干的数。

我遇到过一个真实场景：某个过程用包变量缓存配置表内容，第一次调用时加载，后续直接读内存。单测环境每次新建连接，一切正常；生产环境走连接池，配置改了之后部分请求读到的还是旧缓存，部分是新值，排查了整整两天才发现问题出在这个包变量上。

解法也简单，需要跨会话一致的数据就不要放包变量，或者提供一个显式的刷新过程，在配置变更后主动调用。会话级缓存本身没错，错的是把它当成了数据库级缓存。

7. 我自己的命名和组织习惯

写到这里，把几条一直在用、也确实帮我躲过坑的习惯列出来。变量声明集中放在块顶部，不要散落在 BEGIN 之间；所有自定义标识符强制加前缀，从命名上杜绝与列名撞车；存中文的字段一律标CHAR语义；动态 SQL 里只拼标识符、绝不拼值；参数超过三个的USING一律按名称传。

还有一条：写完一段带变量的逻辑，顺手在脑子里过一遍“这段如果第二次执行、换一个会话执行、变量为 NULL 时执行，分别会怎样”。三个问题问下来，八成以上的隐患能在提交前发现。