前几天有位做数据迁移的朋友丢过来一段脚本说在客户端里跑得好好的搬到调度平台上就报 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 六种定义位置速查把常见的定义位置列成一张表遇到问题先定位自己处在哪一格能省下大量试错时间。定义位置典型写法生效范围谁解析PL/SQL 声明段v_id NUMBER : 1;当前块及其嵌套块PL/SQL 编译器存储过程形参p_id IN NUMBER过程体内PL/SQL 编译器包规范变量g_cnt NUMBER;整个会话PL/SQL 运行时客户端替换变量DEFINE v 1/v当前脚本客户端工具绑定变量VARIABLE v NUMBER/:v当前会话SQL 引擎动态 SQL 占位符USING v1, v2单条动态语句SQL 引擎这张表里最需要留意的是包规范变量——它的生命周期是整个数据库会话不是一次调用。这意味着同一个连接上第二次调用过程时读到的可能是上一次残留的值。后面我会单独讲这个坑。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;两者差别看起来小实际影响不小。用写一个要在脚本里出现十次的变量等于让执行的人敲十次回车而且十次里只要有一次手抖敲错前后逻辑就自相矛盾。我写脚本时基本只用一次性定义清楚后面全部引用。真正让人抓狂的是字符串里出现的。比如一条插入语句里带了个ATT客户端会把它当成替换变量提示输入。这时候需要SET DEFINE OFF关掉替换功能或者改用别的触发字符SET DEFINE ^。这类问题在包含 URL、邮件地址、公司名称的脚本里特别常见。还有1到9这组位置参数配合命令行传参用得很多sqlplus user/passservice 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 时间肉眼可见地高出一截共享池里堆了大量只执行过一次的游标。绑定变量那组只硬解析一次后面全是软解析甚至直接复用。对比项字面量拼接绑定变量硬解析次数接近语句条数通常只有 1 次共享池占用随语句数量线性增长基本恒定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与RETURNING5.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 输出参数与查询结果集动态执行查询时单行用INTOEXECUTE 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。定义方式语义多字节字符集下的实际容量VARCHAR2(10)字节默认约 3 个汉字VARCHAR2(10 CHAR)字符10 个汉字NVARCHAR2(10)字符国家字符集10 个汉字我现在的习惯是凡是存中文的字段一律显式写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 时执行分别会怎样”。三个问题问下来八成以上的隐患能在提交前发现。 
长河君 · 2026/11/3 21:03:54 · 阅读 1.2k Oracle变量定义全解析：PL/SQL、绑定变量与动态SQL避坑
前几天有位做数据迁移的朋友丢过来一段脚本说在客户端里跑得好好的搬到调度平台上就报 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 六种定义位置速查把常见的定义位置列成一张表遇到问题先定位自己处在哪一格能省下大量试错时间。定义位置典型写法生效范围谁解析PL/SQL 声明段v_id NUMBER : 1;当前块及其嵌套块PL/SQL 编译器存储过程形参p_id IN NUMBER过程体内PL/SQL 编译器包规范变量g_cnt NUMBER;整个会话PL/SQL 运行时客户端替换变量DEFINE v 1/v当前脚本客户端工具绑定变量VARIABLE v NUMBER/:v当前会话SQL 引擎动态 SQL 占位符USING v1, v2单条动态语句SQL 引擎这张表里最需要留意的是包规范变量——它的生命周期是整个数据库会话不是一次调用。这意味着同一个连接上第二次调用过程时读到的可能是上一次残留的值。后面我会单独讲这个坑。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;两者差别看起来小实际影响不小。用写一个要在脚本里出现十次的变量等于让执行的人敲十次回车而且十次里只要有一次手抖敲错前后逻辑就自相矛盾。我写脚本时基本只用一次性定义清楚后面全部引用。真正让人抓狂的是字符串里出现的。比如一条插入语句里带了个ATT客户端会把它当成替换变量提示输入。这时候需要SET DEFINE OFF关掉替换功能或者改用别的触发字符SET DEFINE ^。这类问题在包含 URL、邮件地址、公司名称的脚本里特别常见。还有1到9这组位置参数配合命令行传参用得很多sqlplus user/passservice 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 时间肉眼可见地高出一截共享池里堆了大量只执行过一次的游标。绑定变量那组只硬解析一次后面全是软解析甚至直接复用。对比项字面量拼接绑定变量硬解析次数接近语句条数通常只有 1 次共享池占用随语句数量线性增长基本恒定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与RETURNING5.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 输出参数与查询结果集动态执行查询时单行用INTOEXECUTE 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。定义方式语义多字节字符集下的实际容量VARCHAR2(10)字节默认约 3 个汉字VARCHAR2(10 CHAR)字符10 个汉字NVARCHAR2(10)字符国家字符集10 个汉字我现在的习惯是凡是存中文的字段一律显式写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 时执行分别会怎样”。三个问题问下来八成以上的隐患能在提交前发现。