干了十几年Oracle开发存储过程写过几千个踩过的坑也不少。这篇文章就把我日常开发中反复用到的存储过程语句、写法和背后的一些门道一次性做个梳理。不管你是刚接触PL/SQL的新手还是写了不少存储过程但想查漏补缺的老手这篇都值得你花点时间看一遍。1. 存储过程的基本骨架创建与修改1.1 最基础的CREATE OR REPLACE PROCEDURECREATE OR REPLACE PROCEDURE proc_name ( p_param1 IN NUMBER, p_param2 OUT VARCHAR2, p_param3 IN OUT DATE ) IS -- 声明区 v_local_var NUMBER : 0; BEGIN -- 执行区 NULL; EXCEPTION -- 异常处理区 WHEN OTHERS THEN NULL; END proc_name; /这是存储过程的完整骨架我从入行第一天就用这个模板一直用到现在。说几个新手容易忽略的点CREATE OR REPLACE这个语法的好处是存储过程可以原地覆盖不需要先DROP再CREATE。但我见过不少同事在测试环境直接DROP万一正式库里没权限脚本就咔一下断在那了。所以能用OR REPLACE就别用DROP。IS和AS在存储过程中通用写哪个都行纯粹是个人习惯。不过如果是在包PACKAGE里定义规范上更常见的是用AS。结尾那个斜杠/是SQL*Plus、PL/SQL Developer里的执行分隔符在别的工具里可能要用不同的方式执行。这个斜杠不是存储过程的一部分而是客户端工具识别这一段SQL结束的标志好多新人忘了写结果怎么也编译不过。参数这块我多说一句IN参数不能赋值OUT和IN OUT参数必须在过程中赋值。开发规范里我一般建议能IN就IN能少用OUT就少用OUT因为存储过程的OUT参数在Java、Python这类外部调用时既不好调试也不好做类型映射能用返回值解决的尽量用返回值。1.2 参数类型选择IN、OUT、IN OUT参数类型的选择是存储过程设计里最容易被低估的环节。-- IN参数只读传值进过程 CREATE OR REPLACE PROCEDURE query_emp_info ( p_emp_id IN NUMBER ) IS v_emp_name VARCHAR2(50); BEGIN SELECT emp_name INTO v_emp_name FROM emp WHERE emp_id p_emp_id; DBMS_OUTPUT.PUT_LINE(员工姓名 || v_emp_name); END; /IN参数是最常用的调用方传入一个值过程内部只能读取不能修改它的值。OUT参数用于把过程内部计算结果返回给调用方。一个常见错误是只给OUT参数赋值而忘了在调用方预留变量接收。在存储过程内部OUT参数一开始是NULL值很多新人以为调用方传了什么值进来直接拿OUT参数参与计算结果算出NULL查半天查不出来。这是我在带新人时反复强调的一个点。IN OUT参数兼具两者特性既能读又能写但用的场景其实不多。比如数据交换、值调换这种需求才需要用IN OUT。我自己写代码是能避免就避免因为IN OUT参数在程序调用时容易产生赋值顺序上的困惑代码可读性也会下降。参数还有一个小技巧是定义默认值CREATE OR REPLACE PROCEDURE proc_with_default ( p_param1 IN NUMBER DEFAULT 100, p_param2 IN VARCHAR2 DEFAULT TEST ) IS ...加了DEFAULT之后调用时可以只传p_param1p_param2用默认值。这个在用存储过程做报表参数、或迁移脚本需要灵活控制开关的场合非常好用。2. 变量与赋值的常见写法2.1 变量声明标量、%TYPE、%ROWTYPE存储过程内部变量这块我见过很多团队写得比较随意但良好的声明习惯能省下大把排错时间。-- 标量声明 v_count NUMBER(10); v_description VARCHAR2(200); v_create_date DATE; v_rate NUMBER(8, 4); -- %TYPE声明自动继承列的类型 v_emp_name emp.emp_name%TYPE; -- %ROWTYPE声明整行记录 v_emp_record emp%ROWTYPE;%TYPE是我强烈建议多用的。理由很简单如果EMP表的EMP_NAME列从VARCHAR2(50)改成了VARCHAR2(100)你所有声明为emp.emp_name%TYPE的变量都不需要改编译自动适应。而如果你写死VARCHAR2(50)那改表结构后你所有的存储过程都得排查一遍有没有超长风险累死个人。%ROWTYPE就更方便了直接声明一个和表结构一致的记录变量。搭配游标或者SELECT INTO整行时特别好使CREATE OR REPLACE PROCEDURE print_emp_info (p_emp_id IN NUMBER) IS v_emp emp%ROWTYPE; BEGIN SELECT * INTO v_emp FROM emp WHERE emp_id p_emp_id; DBMS_OUTPUT.PUT_LINE(姓名 || v_emp.emp_name || 薪资 || v_emp.salary); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(员工不存在); END; /这里有个多年经验总结——SELECT * INTO v_emp查询结果多行时会触发TOO_MANY_ROWS一行都没有会触发NO_DATA_FOUND。所以凡是SELECT INTO都应该配套异常处理这个习惯必须养成。2.2 赋值语句: 与 SELECT INTOPL/SQL的赋值运算符是冒号等号:不是等号。等号在IF判断、WHERE条件里才是等号。这个区分不搞清楚编译报错事小逻辑错乱事大。v_total : v_price * v_quantity; v_emp_name : 张三; v_flag : TRUE; -- 从表中取单个值赋值 SELECT COUNT(*) INTO v_count FROM order_items WHERE order_id p_order_id; -- 从表中取整行赋值 SELECT * INTO v_order_rec FROM orders WHERE order_id p_order_id;赋值这块我有一个实践经验不要在一个表达式里做太多的隐式类型转换。比如把VARCHAR2的123赋给NUMBER变量Oracle会自动转换但一旦源数据里混进了12a这类脏值运行时直接报ORA-06502。我现在处理外部传入的字符串参数都会显式加一个验证或者用TO_NUMBER包一层并捕获异常宁可多写两行也不要让错误在底层爆发。另外一个值得提醒的点变量初始化。Oracle的变量声明时可以同时赋初值v_counter NUMBER : 0; v_flag BOOLEAN : FALSE;别偷懒等后面赋值尤其是循环计数器、累加器这类变量声明时初始化能避免很多NULL参与的诡异bug。NULL参与任何算术运算结果都是NULL这个规则坑过无数人。3. 流程控制语句让业务逻辑活起来3.1 条件分支IF和CASE存储过程里最常用的条件分支就是IF和CASE。-- IF/ELSIF/ELSE结构 IF v_count 100 THEN v_level : HIGH; ELSIF v_count 10 THEN v_level : MEDIUM; ELSE v_level : LOW; END IF; -- 注意是ELSIF不是ELSEIF也不是ELSE IF写IF判断时有个多年养成的习惯先判断空的、异常的情况再判断正常情况——也就是早退逻辑。比如IF p_param IS NULL THEN RETURN; END IF;把参数校验放在最前面后面的代码逻辑会干净很多。如果你在一大堆业务代码后面写IF p_param IS NULL那这代码写的水平就不太行。CASE语句适合做多值匹配。有两种写法等值匹配和条件匹配-- 等值匹配 CASE v_status WHEN PENDING THEN v_action : 等待审核; WHEN APPROVED THEN v_action : 已批准; WHEN REJECTED THEN v_action : 已拒绝; ELSE v_action : 未知状态; END CASE; -- 条件匹配 CASE WHEN v_amount 10000 THEN v_commission : v_amount * 0.1; WHEN v_amount 5000 THEN v_commission : v_amount * 0.08; ELSE v_commission : v_amount * 0.05; END CASE;这里还有个CASE表达式和CASE语句的区别。CASE表达式可以直接用在SELECT语句里返回一个值不需要END CASE只需要ENDSELECT order_id, CASE WHEN total_amount 10000 THEN 大单 ELSE 普通单 END AS order_level FROM orders;CASE表达式和CASE语句搞混是新手常见的编译错误来源。记住语句用END CASE表达式只用END。3.2 循环体LOOP、WHILE、FOR循环是存储过程里处理批量数据的核心工具。三种循环各有各的适用场景。基本的LOOP循环LOOP v_counter : v_counter 1; EXIT WHEN v_counter 10; -- 必须要有退出条件 END LOOP;WHILE循环WHILE v_counter 10 LOOP v_counter : v_counter 1; END LOOP;FOR循环是最好用的因为它的上限不用自己维护计数器也不容易死循环FOR i IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP; -- 倒序 FOR i IN REVERSE 1..10 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP;我自己在实际开发中用得最多的是FOR循环配游标也就是游标FOR循环这个后面专门讲。这里先提一个循环中常见的错误——在循环内部修改循环变量FOR i IN 1..10 LOOP i : i 1; -- 编译会报错循环变量不能赋值 END LOOP;Oracle的FOR循环变量是隐式声明的你没法改它也不应该改它。如果你需要跳跃或者动态调整步长老老实实用WHILE。还有一个非常实用的控制语句CONTINUE。它用来跳过本次循环剩余部分直接进入下一次循环。Oracle 11g之后才有这个功能FOR i IN 1..10 LOOP IF MOD(i, 2) 0 THEN CONTINUE; -- 跳过偶数 END IF; DBMS_OUTPUT.PUT_LINE(奇数 || i); END LOOP;在没有CONTINUE的老版本里通常用IF嵌套把循环体包一层或者用GOTO配合标签跳转又丑又难维护。如果你的数据库还停在10g该考虑升级了。4. 游标处理结果集的核心工具4.1 显式游标的基本流程游标是PL/SQL里处理多行查询结果的核心机制。显式游标的标准四步曲声明、打开、提取、关闭。CREATE OR REPLACE PROCEDURE print_emp_list ( p_dept_id IN NUMBER ) IS CURSOR c_emp IS SELECT emp_id, emp_name, salary FROM emp WHERE dept_id p_dept_id; v_emp_id emp.emp_id%TYPE; v_emp_name emp.emp_name%TYPE; v_salary emp.salary%TYPE; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO v_emp_id, v_emp_name, v_salary; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_id || || v_emp_name || || v_salary); END LOOP; CLOSE c_emp; EXCEPTION WHEN OTHERS THEN IF c_emp%ISOPEN THEN CLOSE c_emp; END IF; RAISE; END; /显式游标的基本写法就是这样。%NOTFOUND、%FOUND、%ISOPEN、%ROWCOUNT这四个游标属性是必须掌握的。%ROWCOUNT非常好用可以在循环里统计当前处理了多少行LOOP FETCH c_emp INTO v_emp_id, v_emp_name; EXIT WHEN c_emp%NOTFOUND; v_count : c_emp%ROWCOUNT; END LOOP;一个注意事项是游标在异常情况下可能没被关闭所以异常处理块里一定要判断%ISOPEN并关闭游标这是一个非常容易被人忽略的细节时间长了就会出现ORA-01000: maximum open cursors exceeded的报错。4.2 游标FOR循环与批量处理游标FOR循环是我日常用得最多的游标写法它直接把声明、打开、提取、关闭全都封装了CREATE OR REPLACE PROCEDURE print_emp_list ( p_dept_id IN NUMBER ) IS BEGIN FOR r_emp IN ( SELECT emp_id, emp_name, salary FROM emp WHERE dept_id p_dept_id ) LOOP DBMS_OUTPUT.PUT_LINE(r_emp.emp_id || || r_emp.emp_name || || r_emp.salary); END LOOP; END; /你不用显式声明游标也不用OPEN、FETCH、CLOSEOracle在循环内部自动完成这一切。循环结束后游标自动关闭连异常处理里都不用管关闭游标的事省心很多。游标FOR循环里可以直接用r_emp.列名的方式访问当前行的所有列配合%ROWTYPE的好处是哪怕SELECT的列变了代码也不用大改。批量处理这块我再说一个和游标结合的高效做法——用游标收集主键然后批量更新CREATE OR REPLACE PROCEDURE batch_update_salary ( p_dept_id IN NUMBER, p_rate IN NUMBER ) IS TYPE t_emp_ids IS TABLE OF NUMBER; v_emp_ids t_emp_ids; BEGIN SELECT emp_id BULK COLLECT INTO v_emp_ids FROM emp WHERE dept_id p_dept_id; FORALL i IN v_emp_ids.FIRST..v_emp_ids.LAST UPDATE emp SET salary salary * p_rate WHERE emp_id v_emp_ids(i); COMMIT; END; /BULK COLLECT和FORALL是Oracle批处理的黄金搭档它们能大幅减少SQL引擎和PL/SQL引擎之间的上下文切换。比如往10万行数据逐条UPDATE用单行游标可能要跑几分钟用FORALL批量更新几秒就完成。这也是我在性能调优里最常做的一个改动。5. 动态SQL灵活应对不确定的需求5.1 EXECUTE IMMEDIATE的前世今生动态SQL解决的是SQL语句本身在编译时不确定、需要运行时拼出来的场景。比如表名、列名由参数传入或者查询条件完全动态组合。PL/SQL中最常用的动态SQL语句就是EXECUTE IMMEDIATE。CREATE OR REPLACE PROCEDURE dynamic_dml ( p_table_name IN VARCHAR2, p_emp_id IN NUMBER ) IS v_sql VARCHAR2(1000); BEGIN v_sql : UPDATE || p_table_name || SET status DONE WHERE emp_id :1; EXECUTE IMMEDIATE v_sql USING p_emp_id; DBMS_OUTPUT.PUT_LINE(受影响行数 || SQL%ROWCOUNT); END; /这里有个核心点动态SQL里用绑定变量:1、:2而不是直接把值拼进字符串里。这一点极其重要能防SQL注入还能让Oracle复用SQL执行计划性能上有天壤之别。USING后面按顺序传入绑定变量的实际值。这个顺序不能乱个数必须匹配否则运行时直接报错。动态SQL里返回单行值可以用INTO子句v_sql : SELECT COUNT(*) FROM || p_table_name || WHERE status :1; EXECUTE IMMEDIATE v_sql INTO v_count USING DONE;返回多行记录时EXECUTE IMMEDIATE就搞不定了得配合游标TYPE t_ref_cursor IS REF CURSOR; v_cursor t_ref_cursor; v_emp_id NUMBER; v_emp_name VARCHAR2(50); v_sql : SELECT emp_id, emp_name FROM emp WHERE dept_id :1; OPEN v_cursor FOR v_sql USING p_dept_id; LOOP FETCH v_cursor INTO v_emp_id, v_emp_name; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_id || || v_emp_name); END LOOP; CLOSE v_cursor;这种REF CURSOR也是把结果集返回给Java等外部程序的标准玩法。JDBC里接收REF CURSOR直接映射成ResultSet非常顺滑。5.2 绑定变量动态SQL防性能灾难的钥匙关于绑定变量我多说几句因为这是很多性能事故的根源。很多老程序员习惯把变量直接拼到SQL字符串里-- 不好的写法 v_sql : SELECT * FROM emp WHERE emp_id || p_emp_id;这样做功能上没错但Oracle每次执行这句SQL时因为字符串变了值不同在共享池里找不到可复用的执行计划必须做硬解析。高并发下共享池迅速膨胀甚至引发library cache lock争用整个库都能被拖垮。改成绑定变量之后v_sql : SELECT * FROM emp WHERE emp_id :1; EXECUTE IMMEDIATE v_sql INTO v_emp USING p_emp_id;不管p_emp_id的取值是什么SQL语句的文本始终是同一串Oracle可以复用执行计划。这个差异在每秒钟执行几百次的系统里就是性能的两极分化。我调过的一个核心接口就是把拼字符串改成绑定变量CPU占用率直接降了40%左右。表名、列名这种对象名不能绑定只能拼进去。但拼之前一定要做白名单校验比如限定表名在预定义的集合里否则就有SQL注入风险。5.3 动态SQL里我常用的几个技巧处理NULL值的时候动态SQL容易踩坑。绑定变量传NULL是没问题的但如果拼写SQL时把NULL拼成了字符串NULL那就麻烦了。比如-- 错误拼法当p_status为NULL时 v_sql : WHERE status || p_status; -- 变成 WHERE status NULL正确做法是判断后在SQL文本里写IS NULL。这个判断逻辑最好提前写好不要在SQL字符串里去处理。调试动态SQL有一个我常用的笨办法把拼出来的SQL用DBMS_OUTPUT打印出来然后复制到SQL窗口单独执行。这样能快速定位是语法问题还是逻辑问题DBMS_OUTPUT.PUT_LINE(v_sql);另外如果服务端字符集和客户端字符集不一致动态SQL里拼中文条件时偶尔会出现乱码问题。排查时要先看数据库的NLS_CHARACTERSET参数再有针对性地在拼SQL前做字符转换。这种情况不多见但碰上会非常头疼。6. 异常处理让代码优雅地失败6.1 异常处理的基本结构存储过程里的异常处理某种意义上比业务逻辑本身还重要。一个没做异常处理的存储过程在生产环境出问题时会留下一堆看不懂的错误堆栈而做了异常处理的能直接告诉你哪里错了、数据是什么状态。BEGIN -- 业务逻辑 INSERT INTO orders(...) VALUES(...); COMMIT; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(订单号重复); WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(发生未知错误 || SQLERRM); RAISE; -- 重新抛出异常让调用方感知 END;Oracle会按顺序匹配异常处理器匹配到第一个就执行然后离开异常处理块。WHEN OTHERS必须放在最后它捕获所有未匹配的异常。异常处理里常用的几个内置函数SQLCODE获取错误代码比如-1、-2291、-1407SQLERRM获取错误消息文本DBMS_UTILITY.FORMAT_ERROR_BACKTRACE获取错误发生位置的回溯信息我最近几年写存储过程异常处理里都会记一条日志方便排查问题。如果用了WHEN OTHERS我基本都会加RAISE把异常重新抛出否则调用方根本不知道过程到底失败了没有。这个细节非常关键——很多系统的故障排查困难就是因为存储过程把异常全吞了外面看到一切正常数据却没更新。6.2 预定义异常与自定义异常Oracle有一堆预定义异常常用的几个包括异常名SQLCODE说明NO_DATA_FOUNDORA-01403SELECT INTO无数据返回TOO_MANY_ROWSORA-01422SELECT INTO返回多行DUP_VAL_ON_INDEXORA-00001唯一约束冲突VALUE_ERRORORA-06502数值或类型转换错误ZERO_DIVIDEORA-01476除数为零TIMEOUT_ON_RESOURCEORA-00051资源等待超时LOGIN_DENIEDORA-01017登录失败NOT_LOGGED_ONORA-01012未连接数据库还有一个技巧是把业务可能特有的ORA错误关联成命名异常用PRAGMA EXCEPTION_INITDECLARE e_foreign_key_violation EXCEPTION; PRAGMA EXCEPTION_INIT(e_foreign_key_violation, -2291); BEGIN DELETE FROM dept WHERE dept_id 100; EXCEPTION WHEN e_foreign_key_violation THEN DBMS_OUTPUT.PUT_LINE(该部门存在关联数据无法删除); END; /PRAGMA EXCEPTION_INIT的作用是把一个Oracle错误号和一个自定义异常名绑定。这样你就不用老查ORA-02291是什么意思代码也更可读。我一般在写涉及外键删除、唯一约束这类逻辑时都会用这种方式预定义几个业务异常名。主动抛异常可以用RAISE和RAISE_APPLICATION_ERRORIF v_balance v_amount THEN RAISE_APPLICATION_ERROR(-20001, 余额不足当前余额 || v_balance); END IF;RAISE_APPLICATION_ERROR可以在存储过程里主动报错错误号范围是-20000到-20999这是预留给用户定义的。配合异常处理可以实现非常精细的业务校验逻辑。调用方不管是Java还是别的存储过程抓到异常后看到的就是你自己写的业务提示而不是一串莫名其妙的ORA错误。7. 一个综合示例核心业务单据处理7.1 场景设计与需求梳理纸上谈兵说够了来看一个相对完整的综合示例。假设我们要写一个订单审批通过的存储过程需求大概是这样根据订单号查询订单基本信息校验订单当前状态必须是待审批否则报业务错误校验订单金额不能超过客户授信额度更新订单状态为已通过更新客户的累计已用授信额度向操作日志表插入一条记录全部成功提交任一步失败回滚这个场景覆盖了参数传递、变量声明、SELECT INTO、游标用于日志插入判断、异常处理、事务控制、动态SQL拼接日志表名等几乎所有常见写法。7.2 核心代码实现与逐步解读CREATE OR REPLACE PROCEDURE approve_order ( p_order_id IN NUMBER, p_operator IN VARCHAR2, p_remark IN VARCHAR2 DEFAULT 审批通过 ) IS -- 订单记录变量 v_order orders%ROWTYPE; -- 客户信息 v_cust_limit customers.credit_limit%TYPE; v_cust_used customers.used_credit%TYPE; -- 游标用于查询客户已有授信使用记录 CURSOR c_credit IS SELECT used_credit FROM customers WHERE cust_id v_order.cust_id; -- 业务异常 e_status_error EXCEPTION; PRAGMA EXCEPTION_INIT(e_status_error, -20001); e_credit_error EXCEPTION; PRAGMA EXCEPTION_INIT(e_credit_error, -20002); BEGIN -- 1. 查询订单 SELECT * INTO v_order FROM orders WHERE order_id p_order_id; -- 2. 校验状态 IF v_order.status PENDING THEN RAISE_APPLICATION_ERROR(-20001, 订单状态不是待审批当前状态 || v_order.status); END IF; -- 3. 查询客户授信额度 SELECT credit_limit, NVL(used_credit, 0) INTO v_cust_limit, v_cust_used FROM customers WHERE cust_id v_order.cust_id; -- 4. 校验授信额度 IF v_cust_used v_order.total_amount v_cust_limit THEN RAISE_APPLICATION_ERROR(-20002, 授信额度不足已用额度 || v_cust_used || 本单金额 || v_order.total_amount || 授信限额 || v_cust_limit); END IF; -- 5. 更新订单状态 UPDATE orders SET status APPROVED, approve_time SYSDATE, approve_by p_operator WHERE order_id p_order_id; -- 6. 更新客户已用额度 UPDATE customers SET used_credit NVL(used_credit, 0) v_order.total_amount WHERE cust_id v_order.cust_id; -- 7. 插入操作日志展示动态表名拼接的典型场景 EXECUTE IMMEDIATE INSERT INTO order_log_ || TO_CHAR(SYSDATE, YYYYMM) || (log_id, order_id, action, operator, remark, log_time) || VALUES (:1, :2, :3, :4, :5, SYSDATE) USING seq_log_id.NEXTVAL, p_order_id, APPROVE, p_operator, p_remark; -- 8. 提交 COMMIT; DBMS_OUTPUT.PUT_LINE(订单审批完成订单号 || p_order_id); EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20003, 订单或客户信息不存在); WHEN e_status_error THEN ROLLBACK; RAISE; WHEN e_credit_error THEN ROLLBACK; RAISE; WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(审批失败错误 || SQLCODE || || SQLERRM); RAISE; END approve_order; /整个过程其实没有哪个语句是冷门的但组合起来就是一个完整的业务闭环。几个关键细节NVL(used_credit, 0)处理了NULL值问题这是数据库开发里最常见的坑之一PRAGMA EXCEPTION_INIT把-20001、-20002绑定成明确的业务异常名动态SQL写日志表每月一张表避免了单表数据膨胀但这需要DBA事前建好当月的日志表否则会报ORA-00942每步都做ROLLBACK保证数据完整性这个模板可以直接套用到大多数审批、确认、过账类业务里。换张表、换几个字段就能用。8. 常见问题与排查技巧实录8.1 编译相关的问题速查表存储过程开发里我总结了一张高频问题速查表直接拿去排查效率很高错误信息常见原因排查思路PLS-00103语法错误常见于缺少分号、关键字拼错看错误行号往前一行找PLS-00201对象或表不存在检查对象名、当前模式权限PLS-00363表达式不能作为赋值目标看看是不是给IN参数赋值了ORA-06550PL/SQL编译错误后面通常会跟具体错误行PLS-00428INTO子句缺失SELECT语句忘了写INTOPLS-00487引用变量类型不匹配检查声明类型和赋值类型遇到编译错误时先看第一个错误别管后面的错误。Oracle的编译器经常因为第一个错误产生一串连锁误报。我见过新同事盯着第5个错误查半天其实只要把第1个错误修好后面全都没了。还有一个常见问题是标识符太长Oracle 12.2之前限制标识符最长30个字符之后是128个。变量名起得太长老版本直接编译失败。所以我个人习惯变量名控制在30字符内虽然现在的新版本放宽了但迁移兼容性还是要留一手。8.2 运行时错误与数据异常排查运行时错误比编译错误难查得多。我列几个高频案例ORA-01000: maximum open cursors exceeded这个错误出现时先查是不是游标泄漏。游标未关闭、循环里反复打开游标或者代码里用了PL/SQL无显式关闭的隐式游标其实PL/SQL隐式游标一般会自动关闭但显式游标必须手动关。排查时可以用SELECT * FROM V$OPEN_CURSOR查看当前会话打开的游标再对代码做逐条审查。ORA-01403: no data found最常见的运行时异常。SELECT INTO查不到行时触发。我习惯的做法是在SELECT INTO前先做一个COUNT判断或者直接在异常处理里接住NO_DATA_FOUND两种方式都行。尤其要注意这个异常如果没被捕获会一路往外抛导致整个批处理中断。ORA-06502: PL/SQL numeric or value error类型转换或长度超限。这个错误通常伴随CHAR_TO_VARCHAR2、VALUE_ERROR之类提示。排查思路是查变量长度定义和实际传入数据长度。有一个很隐蔽的坑当字符串拼接超过VARCHAR2变量长度时报的也是这个错误。所以我在拼长的错误消息时会把错误消息截断或者用CLOB变量。ORA-01422: exact fetch returns more than requested number of rowsSELECT INTO返回多行时触发。很多时候是因为主键失效、数据重复或者查询条件写得不够精确。排查时把这个SELECT单独拿出来执行一下看看返回几行通常很快就知道了。8.3 我的几个避坑习惯下面这几个习惯是我从大量生产事故里总结出来的真的建议照做。第一所有SELECT INTO必须配异常处理。哪怕你觉得这表一定有一条数据也要配。生产环境的数据永远是超出想象的字段为空、数据被删除、并发产生重复任何一种情况都可能发生。这是个防御性编程习惯成本极低收益极大。第二凡是有UPDATE、DELETE的地方先SELECT验证数据存在性。我写的批量任务里基本是先查后改模式先SELECT计数如果不存在就说没数据不执行更新存在才更新。这样可以避免把更新0行当成更新成功。第三COMMIT的时机要想清楚。存储过程里到底什么时候COMMIT这是有讲究的。如果是独立过程内部COMMIT没问题。但如果这个过程会被另一个大事务调用内部COMMIT就会破坏外部事务的原子性。一个稳妥的约定是除非明确知道存储过程是独立使用的否则不要在里面COMMIT把COMMIT的控制权交给调用方。我还见过一种做法是在过程里用自治事务PRAGMA AUTONOMOUS_TRANSACTION来做日志记录这样日志的COMMIT不会影响主事务的回滚。但自治事务是个双刃剑用多了会让数据一致性逻辑变得复杂我建议只在确实需要独立提交的日志场景里用。第四DBMS_OUTPUT不要过度使用。生产环境的批处理任务里大量的DBMS_OUTPUT输出会拖慢执行速度。调试的时候无所谓上线前一定要清掉或者用日志表替代。有个规则叫开发期日志、生产期统一走审计表我深以为然。8.4 性能优化方向存储过程的性能问题我见过最多的集中在三个方向上下文切换。PL/SQL引擎和SQL引擎之间每切换一次都有开销逐行UPDATE 10万次和FORALL批量更新10万行性能差距可以达到两个数量级。所以能用集合操作、FORALL的坚决不要逐行。隐式转换。WHERE条件里写了WHERE emp_id 100虽然Oracle能自动把字符串转成数字但索引可能就用不上了。实际生产中就是因为这些看似无害的隐式转换导致全表扫描慢查询一个接一个。写WHERE时字段端和值端的类型保持严格一致。SQL重复解析。动态SQL里不用绑定变量、变量没有复用执行计划都会导致硬解析。我在前面已经详细讲过了这里再敲一次警钟。另外还有一个常见的性能问题——在PL/SQL里做大量字符串拼接。VARCHAR2变量在循环里反复拼接如果超过了4000字节限制会报ORA-06502。遇到这种情况要么改用CLOB要么换一种写法。我写过一次按月拼接大段导出文本的过程最后用CLOB变量才搞定。结尾这篇东西我写了好几个小时把这十几年用Oracle存储过程的压箱底经验都翻出来了。说句掏心窝的话存储过程写得好不好很多时候不在于你背了多少语句而在于你有没有把这些语句放在正确的场景里用。变量声明用%TYPE、动态SQL用绑定变量、游标用FOR循环、异常用自定义错误码——这些都是我从一个个故障单里学出来的。如果你现在正在学Oracle存储过程我的建议很简单先把这篇里的基础语句敲一遍然后找个真实的业务场景比如自己模拟一个订单审批逻辑从头写一个完整的存储过程。宁可功能简单也要保证异常处理和事务控制是规范的。写完之后过两周再回来看自己的代码你会发现自己进步得特别快。最后再分享一个小技巧每次写存储过程前先画一遍数据流转的草图哪怕是在纸上画几个框也行。想清楚数据从哪来、处理成什么样、往哪去、失败怎么办再动手写代码。有了这个习惯你写的存储过程从第一天开始就不会是能跑就行的水平。