1. 从一次“诡异”的数据更新说起为什么参数模式如此重要最近在做一个数据迁移后的校验项目用到了人大金仓KingbaseES数据库。我写了一个PL/SQL子程序目的是根据传入的员工ID更新其部门信息并返回更新前的旧部门名称。最初的代码大概是这样的CREATE OR REPLACE PROCEDURE update_employee_dept( p_emp_id IN NUMBER, p_new_dept_id IN NUMBER, p_old_dept_name OUT VARCHAR2 ) AS v_temp_name VARCHAR2(100); BEGIN -- 先查出旧的部门名 SELECT dept_name INTO v_temp_name FROM departments d JOIN employees e ON d.dept_id e.dept_id WHERE e.emp_id p_emp_id; -- 赋值给OUT参数 p_old_dept_name : v_temp_name; -- 更新员工部门 UPDATE employees SET dept_id p_new_dept_id WHERE emp_id p_emp_id; COMMIT; DBMS_OUTPUT.PUT_LINE(更新成功旧部门为 || p_old_dept_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(未找到员工ID: || p_emp_id); END;看起来逻辑清晰对吧但在调用时我却遇到了一个让人困惑的情况。我这样调用它DECLARE v_old_dept VARCHAR2(100); BEGIN update_employee_dept(1001, 5, v_old_dept); DBMS_OUTPUT.PUT_LINE(调用结束获得的旧部门是 || v_old_dept); END;理论上程序块应该先打印子程序内部的“更新成功...”再打印外部的“调用结束...”。但实际运行时外部的DBMS_OUTPUT.PUT_LINE语句有时会报错提示v_old_dept变量未初始化或为空。更奇怪的是子程序内部的更新和提交确实成功了但外部就是拿不到那个p_old_dept_name的值。这个问题困扰了我一阵子。后来我才意识到我掉进了一个关于参数传递模式的经典“坑”里。我对IN、OUT、IN OUT这三种模式的理解还停留在“传入、传出、既传又出”的表面却忽略了KingbaseES以及Oracle PL/SQL在底层处理这些参数时的具体机制和约束。这个“诡异”的现象恰恰是理解参数模式关键差异的绝佳入口。参数模式绝非简单的数据流向标识它直接关系到程序的正确性、性能甚至是事务边界和异常处理逻辑。对于从其他数据库如MySQL、PostgreSQL迁移过来或者主要使用应用程序如Java、Python进行数据库交互的开发者来说PL/SQL的这种参数模式是需要特别关注的一个知识领域。2. 庖丁解牛深入理解IN、OUT、IN OUT三种参数模式要解决上面那个问题我们必须像庖丁解牛一样把三种参数模式里里外外剖析清楚。很多人觉得这很简单但真正涉及到值传递、引用传递、初始状态、异常回滚时细节就决定了成败。2.1 IN模式只读的输入通道IN参数是默认模式也是最常用、最直观的一种。它就像一个单向阀门只允许数据从调用环境流向子程序内部。核心特性与工作原理只读性在子程序内部IN参数被当作常量constant对待。你可以读取它的值但绝对不能给它赋值。任何试图修改IN参数的操作都会在编译时直接报错。传递机制默认情况下KingbaseES采用按值传递pass by value。这意味着调用时实参的值会被复制一份传递给子程序内的形参。形参和实参在内存中是两个独立的副本。因此即使在子程序内部对形参进行操作当然只读也完全不会影响外部的实参变量。默认值IN参数可以指定默认值例如p_emp_id IN NUMBER DEFAULT 0。这在参数可选时非常有用能简化调用。一个关键但常被忽略的细节NOCOPY提示符。虽然默认是按值传递但KingbaseES支持Oracle的NOCOPY提示符。你可以这样声明p_large_data IN NOCOPY CLOB。NOCOPY是一个编译器提示Hint它建议编译器采用按引用传递pass by reference。这意味着形参和实参指向内存中的同一个地址避免了大型数据如CLOB、集合类型复制的开销。注意NOCOPY只是提示编译器在某些情况下如参数带有NOT NULL约束、是游标变量等可能会忽略它。另外按引用传递会带来副作用如果在子程序内发生了未处理的异常即使程序回滚通过IN NOCOPY参数修改的实参值也可能已被部分改变因为直接操作了原内存地址这破坏了参数的“只读”语义需要谨慎使用。对于普通的NUMBER、VARCHAR2类型通常不需要也不建议使用NOCOPY。典型应用场景提供查询条件如存储过程中的查询条件参数。提供配置项或控制标志。传入不需要修改的基准数据。2.2 OUT模式单向的写入通道OUT参数是数据输出的主要通道之一另一个是函数返回值。它的行为与IN参数相反。核心特性与工作原理只写性在调用开始时在子程序内部OUT形参在刚进入子程序时被视为未初始化的变量。它的初始值是NULL对于标量类型或空对于复合类型。你必须先在子程序内部为其赋值然后才能读取它。如果未赋值就直接读取可能会得到NULL或不可预知的值。传递机制OUT参数本质上是按引用传递的。子程序结束时形参的最终值会“写回”到调用处的实参中。但这里有一个非常重要的前置动作在子程序开始执行时实参的值会被传入子程序吗答案是否定的。对于OUT参数实参的初始值对子程序是不可见的。子程序只负责向这个“内存地址”写入新值。对实参的要求调用OUT参数时实参必须是一个变量variable而不能是常量如5或表达式如salary * 1.1因为需要有一个地址来接收回写的数据。这就解释了开篇案例的问题根源在我的update_employee_dept过程中p_old_dept_name是OUT模式。当我调用时传入的实参是v_old_dept。在过程内部我正确地对p_old_dept_name进行了赋值p_old_dept_name : v_temp_name;。所以过程内部的打印语句能正确输出。 但是在过程外部的匿名块中我紧接着打印v_old_dept。这里的关键在于子程序内部的DBMS_OUTPUT.PUT_LINE语句和COMMIT语句与参数值的传递和回写是两回事。参数值的回写发生在子程序正常结束到达END或执行RETURN时。如果子程序内部发生了异常并被捕获处理然后子程序仍然正常结束那么OUT参数的值仍然会回写。我的代码看起来符合这个条件。 然而有一种隐蔽的情况如果子程序内部对OUT参数赋值后在后续代码中比如COMMIT前后又发生了某些运行时错误非显式声明的异常或者与外部会话环境存在某些交互问题可能导致输出缓冲区DBMS_OUTPUT的内容与参数回写动作在客户端工具如ksql、PL/SQL Developer中表现的时序出现错觉让人误以为参数没传出来。实际上更稳妥的测试方法是在外部匿名块中在调用子程序后使用一个独立的SELECT语句或另一个PUT_LINE来验证变量值而不是依赖子程序内部的打印顺序做判断。一个更清晰的验证DECLARE v_result VARCHAR2(100) : ‘初始化值’; BEGIN DBMS_OUTPUT.PUT_LINE(‘调用前: ‘ || v_result); your_procedure_with_out_param(v_result); -- 假设这个过程会修改OUT参数 DBMS_OUTPUT.PUT_LINE(‘调用后: ‘ || v_result); -- 这里查看是否被修改 END;2.3 IN OUT模式双向的数据通道IN OUT参数结合了IN和OUT的特性是最灵活但也最容易误用的一种模式。核心特性与工作原理可读可写在子程序内部IN OUT形参就像一个已经初始化过的局部变量你可以读取它的传入值也可以修改它修改后的值将在子程序结束时传回给实参。传递机制默认情况下IN OUT参数采用按值传递。这有点反直觉但确实是这样的实参的值被复制给形参子程序对形参的修改在结束时将形参的最终值再次复制回实参。这意味着存在两次数据复制。同样可以使用NOCOPY提示符来建议编译器使用按引用传递以提升大数据的性能。对实参的要求和OUT参数一样实参必须是一个变量。一个经典的使用场景编写一个“自增”或“状态翻转”过程。CREATE OR REPLACE PROCEDURE toggle_status( p_id IN NUMBER, p_status IN OUT VARCHAR2 ) AS BEGIN IF p_status ‘ACTIVE’ THEN p_status : ‘INACTIVE’; ELSIF p_status ‘INACTIVE’ THEN p_status : ‘ACTIVE’; ELSE p_status : ‘UNKNOWN’; END IF; -- 可以基于新的状态做其他操作 UPDATE some_table SET status p_status WHERE id p_id; END; /调用时DECLARE v_current_status VARCHAR2(10) : ‘ACTIVE’; BEGIN toggle_status(100, v_current_status); DBMS_OUTPUT.PUT_LINE(‘新状态: ‘ || v_current_status); -- 输出 ‘INACTIVE’ END;重要陷阱IN OUT参数的初始值由于IN OUT参数是“传入传出”所以你必须为它提供一个有意义的初始值。如果你调用子程序时传入的实参变量是NULL那么子程序内部得到的初始值就是NULL。如果你的逻辑没有妥善处理NULL情况就可能出错。例如上面的toggle_status过程如果传入的p_status初始就是NULL那么所有IF判断都为假最终会执行ELSE分支将其设为‘UNKNOWN’。三种模式对比总结表特性INOUTIN OUT数据流向调用者 - 子程序子程序 - 调用者调用者 - 子程序子程序内可读是且只读是但需先赋值是读取传入值子程序内可写否编译错误是必须赋值是可修改传递机制默认按值传递按引用传递按值传递支持NOCOPY是避免大数据复制是是避免两次复制实参要求常量、变量、表达式必须为变量必须为变量形参初始值实参的副本值NULL未初始化实参的副本值主要用途提供输入数据、条件返回计算结果、状态修改传入的变量、累加器3. 实战中的“坑”与最佳实践如何正确选择和使用参数模式理解了理论我们来看看实战中如何避开陷阱做出最佳选择。很多开发中的“灵异事件”都源于参数模式的误用。3.1 模式选择决策指南什么时候用什么选择哪种模式首先取决于你的设计意图其次要考虑性能和代码清晰度。首选IN模式如果参数仅仅用于向子程序提供信息子程序不会改变它那么毫无争议地使用IN模式。这是最安全、意图最清晰的方式。即使未来子程序逻辑变更IN参数也能保证调用者的数据不会被意外修改。何时用OUT何时用函数返回值使用OUT参数当子程序需要返回多个独立的值时。函数只能返回一个标量或一个复合类型而过程可以通过多个OUT参数返回多个值。当需要返回一个游标引用REF CURSOR时通常使用OUT参数。当子程序的主要目的是执行一个动作如更新、删除同时附带返回一些状态信息时使用过程PROCEDURE配合OUT参数更符合语义。使用函数RETURN当子程序的主要目的是计算并返回一个单一结果时。例如计算税率、验证密码强度等。这符合数学上“函数”的概念代码可读性更强。函数可以直接在SQL语句中调用前提是函数是“纯净的”即没有DML操作等副作用而带OUT参数的过程不能。示例对比— 方案A使用过程OUT参数 CREATE OR REPLACE PROCEDURE get_employee_info( p_emp_id IN NUMBER, p_name OUT VARCHAR2, p_salary OUT NUMBER, p_dept OUT VARCHAR2 ) AS ... — 调用 DECLARE v_name VARCHAR2(50); v_sal NUMBER; v_dept VARCHAR2(50); BEGIN get_employee_info(1001, v_name, v_sal, v_dept); END; — 方案B使用函数返回记录类型 CREATE TYPE emp_info_rec AS OBJECT ( name VARCHAR2(50), salary NUMBER, department VARCHAR2(50) ); CREATE OR REPLACE FUNCTION get_employee_info_fn( p_emp_id IN NUMBER ) RETURN emp_info_rec AS ... — 调用 DECLARE v_info emp_info_rec; BEGIN v_info : get_employee_info_fn(1001); END;方案B在返回多个相关数据时更优雅但需要预先定义类型。方案A更直接灵活尤其在返回字段不确定或动态时。谨慎使用IN OUT模式IN OUT模式通常用于以下场景修改传入的变量如之前toggle_status的例子或者一个累加器过程。传递大型对象并修改例如传入一个大的CLOB进行内容处理处理完再传回。此时强烈建议配合NOCOPY使用。实现“引用”语义当你希望子程序直接操作调用者的某个复杂数据结构如嵌套表时。一个常见的误用是将IN OUT当作“可选的OUT”来用即调用者不关心传入值只关心输出值。这是错误的。你应该使用OUT模式并在子程序内部忽略其初始的NULL状态。使用IN OUT会给调用者强加一个“必须提供有意义的初始值”的负担降低了接口的易用性。3.2 性能考量NOCOPY的得与失对于IN、IN OUT参数当传递大型数据结构如包含数千元素的集合VARRAY、NESTED TABLE或大的LOB时按值传递的复制开销会非常显著影响性能。使用NOCOPY的优点减少内存拷贝直接传递指针极大提升性能尤其在大数据量和频繁调用时。避免ORA-06502错误在某些场景下按值传递大型集合可能因为内存不足或超出PL/SQL子程序参数的大小限制而抛出值错误VALUE_ERRORNOCOPY可以避免这个问题。使用NOCOPY的风险与限制副作用Side Effects这是最大的风险。如果子程序因异常而中断通过IN OUT NOCOPY修改的实参可能处于一种“部分被修改”的不确定状态。因为修改是直接作用于原内存地址的无法自动回滚。CREATE OR REPLACE PROCEDURE risky_proc( p_data IN OUT NOCOPY MY_ARRAY_TYPE ) IS BEGIN p_data(1) : ‘new value’; — 直接修改了原数据 — 假设这里发生了一个不可预见的异常 RAISE SOME_EXCEPTION; END;调用后即使过程异常退出p_data(1)可能已经被改为‘new value’了。编译器可能忽略NOCOPY只是一个提示。在以下情况编译器为了确保语义正确性会忽略NOCOPY参数带有NOT NULL约束。参数是游标变量REF CURSOR。参数在调用时需要隐式类型转换。子程序被远程调用通过数据库链接。子程序被作为并行查询的一部分调用。别名问题Aliasing如果同一个实参通过多个NOCOPY参数传入或者在子程序内部NOCOPY参数和局部变量指向了同一块内存就会产生别名导致逻辑混乱难以调试。最佳实践建议对于简单的标量类型NUMBER,VARCHAR2,DATE不要使用NOCOPY。复制开销微乎其微而副作用风险得不偿失。对于大型的集合类型或LOB考虑使用NOCOPY。但在使用前必须确保子程序的异常处理非常完备能在发生异常时将数据恢复到一致状态或者调用者能接受数据被部分修改的风险。仔细检查是否存在别名问题。将NOCOPY的使用限制在私有子程序或包内部避免在公共API中暴露以减少不可控的风险。3.3 异常处理与参数状态参数模式与异常处理紧密相关理解它们之间的交互能帮你写出更健壮的程序。OUT和IN OUT参数在异常发生时的行为如果子程序因未处理的异常而退出异常传播到调用者那么对于按值传递的参数默认的IN OUT以及带NOCOPY的IN实参的值不会被改变。因为修改发生在形参这个副本上而副本随着子程序栈的销毁而消失了。如果子程序因未处理的异常而退出对于按引用传递的参数默认的OUT以及带NOCOPY的IN OUT实参可能已经被改变对于OUT它从NULL被初始化了不OUT参数在子程序入口时实参值不被传入但子程序内部对其形参的赋值会直接作用到实参地址。如果异常发生在赋值之后那么实参就已经被修改了。一个演示案例CREATE OR REPLACE PROCEDURE proc_with_exception( p_in_val IN NUMBER, p_out_val OUT NUMBER, p_inout_val IN OUT NUMBER ) AS BEGIN DBMS_OUTPUT.PUT_LINE(‘Start. p_inout_val’ || p_inout_val); p_out_val : 100; — 修改OUT参数 p_inout_val : 200; — 修改IN OUT参数 DBMS_OUTPUT.PUT_LINE(‘After assignment.’); RAISE_APPLICATION_ERROR(-20001, ‘模拟异常’); — 抛出异常 EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘Exception caught locally.’); RAISE; — 再次抛出 END; / DECLARE v_out NUMBER : 1; v_inout NUMBER : 2; BEGIN v_inout : 2; BEGIN proc_with_exception(10, v_out, v_inout); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘Outer block caught: ‘ || SQLERRM); END; DBMS_OUTPUT.PUT_LINE(‘v_out’ || v_out || ‘, v_inout’ || v_inout); END;运行这个块输出可能是Start. p_inout_val2 After assignment. Exception caught locally. Outer block caught: ORA-20001: 模拟异常 v_out100, v_inout2分析p_out_val是OUT模式按引用传递。在子程序中p_out_val : 100已经执行所以即使发生异常v_out也被改成了100。p_inout_val是IN OUT模式默认按值传递。在子程序中p_inout_val : 200修改的是形参副本。由于异常最终传播到了外部子程序非正常结束形参副本的值200没有被复制回实参v_inout。所以v_inout仍然是2。如果p_inout_val被声明为IN OUT NOCOPY NUMBER那么它也是按引用传递异常发生后v_inout的值可能会是200取决于异常发生和传递的精确时机这就产生了不确定性。给你的经验在编写带有OUT或IN OUT NOCOPY参数的子程序时必须在子程序内部妥善处理所有可能的异常。要么在异常处理块中将输出参数设置为某个错误状态值要么确保在发生异常前不修改这些参数。最安全的做法是在子程序开头将OUT或IN OUT参数的值先保存到局部变量中所有逻辑都基于局部变量进行只有在所有操作都确认成功完成后再将局部变量的值赋给输出参数。这类似于一种“两阶段提交”的思想能最大程度保证数据一致性。4. 高级话题与兼容性考量从热词看实际应用结合你提供的网络热词我们可以看到很多实际问题都围绕着参数模式展开。比如java.sql.sqlexception:ora-06502:pl/sql:number or value error这个错误经常就发生在参数传递或处理过程中。4.1 与编程语言如Java/JDBC的交互这是企业应用中最常见的场景。Java程序通过JDBC调用KingbaseES的存储过程或函数如何正确地传递IN、OUT、IN OUT参数JDBC中的调用方式对于存储过程使用CallableStatement。IN参数使用setXXX()方法设置。OUT参数必须使用registerOutParameter()方法先注册参数的类型执行后再用getXXX()方法获取。IN OUT参数结合两者先setXXX()再registerOutParameter()最后getXXX()。一个典型的Java调用示例// 假设有一个过程PROCEDURE proc_emp(emp_id IN NUMBER, emp_name OUT VARCHAR2) String sql “{call proc_emp(?, ?)}”; try (CallableStatement cstmt connection.prepareCall(sql)) { // 设置IN参数 cstmt.setInt(1, 1001); // 注册OUT参数的类型 cstmt.registerOutParameter(2, Types.VARCHAR); // 执行 cstmt.execute(); // 获取OUT参数的值 String name cstmt.getString(2); System.out.println(“Employee Name: “ name); }常见错误与解决ORA-06502: PL/SQL: numeric or value error原因1Java端设置的IN参数类型或值与PL/SQL形参声明不匹配。例如PL/SQL中参数是VARCHAR2(10)Java传了一个超过10字符的字符串。原因2PL/SQL过程中对OUT参数赋值时值超过了其声明的长度或精度。原因3IN OUT参数在PL/SQL内部被使用如参与运算时其传入的值为NULL而运算不支持NULL。解决仔细检查参数定义在PL/SQL中对可能为NULL的输入进行NVL处理在Java端进行数据校验和截断。忘记注册OUT参数会导致执行时错误或获取不到值。获取OUT参数的顺序或索引错误getXXX()方法的索引应与registerOutParameter()的索引一致。4.2 参数默认值与重载KingbaseES的PL/SQL支持为IN参数设置默认值这增加了灵活性。CREATE OR REPLACE PROCEDURE search_orders( p_customer_id IN NUMBER DEFAULT NULL, p_start_date IN DATE DEFAULT SYSDATE - 30, p_status IN VARCHAR2 DEFAULT ‘SHIPPED’ ) AS ...调用时可以省略有默认值的参数BEGIN search_orders; — 使用所有默认值 search_orders(p_customer_id 101); — 仅指定客户ID search_orders(p_status ‘PENDING’, p_start_date SYSDATE - 7); — 使用命名表示法顺序无关 END;参数模式与重载Overloading在包PACKAGE中子程序可以重载即多个子程序同名但参数列表不同。参数模式是签名的一部分。CREATE OR REPLACE PACKAGE my_pkg AS PROCEDURE process_data(p_data IN NUMBER); — 版本1 PROCEDURE process_data(p_data IN OUT VARCHAR2); — 版本2参数模式不同可以重载 — PROCEDURE process_data(p_data OUT NUMBER); — 版本3如果只有模式不同与版本1可能造成调用歧义需谨慎。 END my_pkg;编译器根据调用时实参的数据类型和模式来决定调用哪个版本。但需要注意如果两个重载过程的区别仅仅在于IN和OUT/IN OUT模式不同调用时可能会因为歧义而失败尤其是在使用命名表示法时。4.3 记录类型与集合类型的参数传递当参数是复杂类型如自定义的记录RECORD或集合TABLE时行为需要特别注意。记录类型RECORD可以作为IN、OUT、IN OUT参数传递。传递的是整个记录的值。CREATE TYPE emp_rectype AS OBJECT (id NUMBER, name VARCHAR2(50)); CREATE OR REPLACE PROCEDURE handle_emp(p_emp IN emp_rectype) AS ...集合类型NESTED TABLE,VARRAY这些类型通常数据量较大。作为IN参数按值传递整个集合会被复制性能开销大。务必考虑使用IN NOCOPY。作为OUT参数按引用传递。在子程序内部你需要初始化这个集合如p_list : my_table_type();然后扩展EXTEND并赋值。作为IN OUT参数默认按值传递两次复制性能极差。几乎总是应该使用IN OUT NOCOPY。一个集合参数的示例CREATE OR REPLACE TYPE num_list IS TABLE OF NUMBER; CREATE OR REPLACE PROCEDURE filter_numbers( p_input_list IN NOCOPY num_list, p_output_list OUT NOCOPY num_list ) AS v_idx PLS_INTEGER; BEGIN p_output_list : num_list(); — 初始化OUT集合 IF p_input_list IS NOT NULL AND p_input_list.COUNT 0 THEN v_idx : p_input_list.FIRST; WHILE v_idx IS NOT NULL LOOP IF p_input_list(v_idx) 10 THEN — 过滤条件 p_output_list.EXTEND; p_output_list(p_output_list.LAST) : p_input_list(v_idx); END IF; v_idx : p_input_list.NEXT(v_idx); END LOOP; END IF; END;4.4 从“热词”看真实问题排查你提供的热词中pl/sql develope破解、s7-200smart子程序设置密码、如何解密这些虽然与KingbaseES参数模式不直接相关但反映了开发者在进行数据库编程和集成时的常见诉求——寻找工具和解决权限、加密问题。而java.sql.sqlexception:ora-06502和博途中编写温度转换子程序fc则直接指向了跨语言调用和子程序逻辑编写。对于ORA-06502错误结合参数模式的排查思路应该是定位错误行错误信息中的ORA-06512: at line X给出了子程序内部出错的行号。检查参数赋值查看该行代码中所有涉及的变量和参数特别是OUT、IN OUT参数是否发生了值越界字符串超长、数字超精度、类型不匹配或对NULL值的非法运算。检查参数传递如果是从Java等外部程序调用检查JDBC中设置的参数类型、长度是否与PL/SQL定义一致。使用简单日志在子程序关键点使用DBMS_OUTPUT.PUT_LINE输出参数的值或使用更专业的日志工具观察参数的实际传递过程。对于像“温度转换子程序”这样的逻辑在定义参数时就要想清楚温度值是输入IN吗转换后的结果是输出OUT还是直接作为函数返回值转换公式是否需要额外的参数如单位IN VARCHAR2把这些数据流向用正确的参数模式固定下来是写出清晰、可复用子程序的第一步。参数模式是PL/SQL编程的基石之一。它看似简单却串联起了数据封装、性能优化和异常处理等多个核心主题。理解并正确运用IN、OUT、IN OUT以及NOCOPY这样的高级特性能让你编写的存储过程、函数更加健壮、高效和易于维护。下次当你设计一个子程序接口时不妨多花一分钟思考一下每个参数真正的意图这能省下未来无数小时调试的时间。 PL/SQL参数模式详解：IN、OUT、IN OUT的区别与实战避坑指南
1. 从一次“诡异”的数据更新说起为什么参数模式如此重要最近在做一个数据迁移后的校验项目用到了人大金仓KingbaseES数据库。我写了一个PL/SQL子程序目的是根据传入的员工ID更新其部门信息并返回更新前的旧部门名称。最初的代码大概是这样的CREATE OR REPLACE PROCEDURE update_employee_dept( p_emp_id IN NUMBER, p_new_dept_id IN NUMBER, p_old_dept_name OUT VARCHAR2 ) AS v_temp_name VARCHAR2(100); BEGIN -- 先查出旧的部门名 SELECT dept_name INTO v_temp_name FROM departments d JOIN employees e ON d.dept_id e.dept_id WHERE e.emp_id p_emp_id; -- 赋值给OUT参数 p_old_dept_name : v_temp_name; -- 更新员工部门 UPDATE employees SET dept_id p_new_dept_id WHERE emp_id p_emp_id; COMMIT; DBMS_OUTPUT.PUT_LINE(更新成功旧部门为 || p_old_dept_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(未找到员工ID: || p_emp_id); END;看起来逻辑清晰对吧但在调用时我却遇到了一个让人困惑的情况。我这样调用它DECLARE v_old_dept VARCHAR2(100); BEGIN update_employee_dept(1001, 5, v_old_dept); DBMS_OUTPUT.PUT_LINE(调用结束获得的旧部门是 || v_old_dept); END;理论上程序块应该先打印子程序内部的“更新成功...”再打印外部的“调用结束...”。但实际运行时外部的DBMS_OUTPUT.PUT_LINE语句有时会报错提示v_old_dept变量未初始化或为空。更奇怪的是子程序内部的更新和提交确实成功了但外部就是拿不到那个p_old_dept_name的值。这个问题困扰了我一阵子。后来我才意识到我掉进了一个关于参数传递模式的经典“坑”里。我对IN、OUT、IN OUT这三种模式的理解还停留在“传入、传出、既传又出”的表面却忽略了KingbaseES以及Oracle PL/SQL在底层处理这些参数时的具体机制和约束。这个“诡异”的现象恰恰是理解参数模式关键差异的绝佳入口。参数模式绝非简单的数据流向标识它直接关系到程序的正确性、性能甚至是事务边界和异常处理逻辑。对于从其他数据库如MySQL、PostgreSQL迁移过来或者主要使用应用程序如Java、Python进行数据库交互的开发者来说PL/SQL的这种参数模式是需要特别关注的一个知识领域。2. 庖丁解牛深入理解IN、OUT、IN OUT三种参数模式要解决上面那个问题我们必须像庖丁解牛一样把三种参数模式里里外外剖析清楚。很多人觉得这很简单但真正涉及到值传递、引用传递、初始状态、异常回滚时细节就决定了成败。2.1 IN模式只读的输入通道IN参数是默认模式也是最常用、最直观的一种。它就像一个单向阀门只允许数据从调用环境流向子程序内部。核心特性与工作原理只读性在子程序内部IN参数被当作常量constant对待。你可以读取它的值但绝对不能给它赋值。任何试图修改IN参数的操作都会在编译时直接报错。传递机制默认情况下KingbaseES采用按值传递pass by value。这意味着调用时实参的值会被复制一份传递给子程序内的形参。形参和实参在内存中是两个独立的副本。因此即使在子程序内部对形参进行操作当然只读也完全不会影响外部的实参变量。默认值IN参数可以指定默认值例如p_emp_id IN NUMBER DEFAULT 0。这在参数可选时非常有用能简化调用。一个关键但常被忽略的细节NOCOPY提示符。虽然默认是按值传递但KingbaseES支持Oracle的NOCOPY提示符。你可以这样声明p_large_data IN NOCOPY CLOB。NOCOPY是一个编译器提示Hint它建议编译器采用按引用传递pass by reference。这意味着形参和实参指向内存中的同一个地址避免了大型数据如CLOB、集合类型复制的开销。注意NOCOPY只是提示编译器在某些情况下如参数带有NOT NULL约束、是游标变量等可能会忽略它。另外按引用传递会带来副作用如果在子程序内发生了未处理的异常即使程序回滚通过IN NOCOPY参数修改的实参值也可能已被部分改变因为直接操作了原内存地址这破坏了参数的“只读”语义需要谨慎使用。对于普通的NUMBER、VARCHAR2类型通常不需要也不建议使用NOCOPY。典型应用场景提供查询条件如存储过程中的查询条件参数。提供配置项或控制标志。传入不需要修改的基准数据。2.2 OUT模式单向的写入通道OUT参数是数据输出的主要通道之一另一个是函数返回值。它的行为与IN参数相反。核心特性与工作原理只写性在调用开始时在子程序内部OUT形参在刚进入子程序时被视为未初始化的变量。它的初始值是NULL对于标量类型或空对于复合类型。你必须先在子程序内部为其赋值然后才能读取它。如果未赋值就直接读取可能会得到NULL或不可预知的值。传递机制OUT参数本质上是按引用传递的。子程序结束时形参的最终值会“写回”到调用处的实参中。但这里有一个非常重要的前置动作在子程序开始执行时实参的值会被传入子程序吗答案是否定的。对于OUT参数实参的初始值对子程序是不可见的。子程序只负责向这个“内存地址”写入新值。对实参的要求调用OUT参数时实参必须是一个变量variable而不能是常量如5或表达式如salary * 1.1因为需要有一个地址来接收回写的数据。这就解释了开篇案例的问题根源在我的update_employee_dept过程中p_old_dept_name是OUT模式。当我调用时传入的实参是v_old_dept。在过程内部我正确地对p_old_dept_name进行了赋值p_old_dept_name : v_temp_name;。所以过程内部的打印语句能正确输出。 但是在过程外部的匿名块中我紧接着打印v_old_dept。这里的关键在于子程序内部的DBMS_OUTPUT.PUT_LINE语句和COMMIT语句与参数值的传递和回写是两回事。参数值的回写发生在子程序正常结束到达END或执行RETURN时。如果子程序内部发生了异常并被捕获处理然后子程序仍然正常结束那么OUT参数的值仍然会回写。我的代码看起来符合这个条件。 然而有一种隐蔽的情况如果子程序内部对OUT参数赋值后在后续代码中比如COMMIT前后又发生了某些运行时错误非显式声明的异常或者与外部会话环境存在某些交互问题可能导致输出缓冲区DBMS_OUTPUT的内容与参数回写动作在客户端工具如ksql、PL/SQL Developer中表现的时序出现错觉让人误以为参数没传出来。实际上更稳妥的测试方法是在外部匿名块中在调用子程序后使用一个独立的SELECT语句或另一个PUT_LINE来验证变量值而不是依赖子程序内部的打印顺序做判断。一个更清晰的验证DECLARE v_result VARCHAR2(100) : ‘初始化值’; BEGIN DBMS_OUTPUT.PUT_LINE(‘调用前: ‘ || v_result); your_procedure_with_out_param(v_result); -- 假设这个过程会修改OUT参数 DBMS_OUTPUT.PUT_LINE(‘调用后: ‘ || v_result); -- 这里查看是否被修改 END;2.3 IN OUT模式双向的数据通道IN OUT参数结合了IN和OUT的特性是最灵活但也最容易误用的一种模式。核心特性与工作原理可读可写在子程序内部IN OUT形参就像一个已经初始化过的局部变量你可以读取它的传入值也可以修改它修改后的值将在子程序结束时传回给实参。传递机制默认情况下IN OUT参数采用按值传递。这有点反直觉但确实是这样的实参的值被复制给形参子程序对形参的修改在结束时将形参的最终值再次复制回实参。这意味着存在两次数据复制。同样可以使用NOCOPY提示符来建议编译器使用按引用传递以提升大数据的性能。对实参的要求和OUT参数一样实参必须是一个变量。一个经典的使用场景编写一个“自增”或“状态翻转”过程。CREATE OR REPLACE PROCEDURE toggle_status( p_id IN NUMBER, p_status IN OUT VARCHAR2 ) AS BEGIN IF p_status ‘ACTIVE’ THEN p_status : ‘INACTIVE’; ELSIF p_status ‘INACTIVE’ THEN p_status : ‘ACTIVE’; ELSE p_status : ‘UNKNOWN’; END IF; -- 可以基于新的状态做其他操作 UPDATE some_table SET status p_status WHERE id p_id; END; /调用时DECLARE v_current_status VARCHAR2(10) : ‘ACTIVE’; BEGIN toggle_status(100, v_current_status); DBMS_OUTPUT.PUT_LINE(‘新状态: ‘ || v_current_status); -- 输出 ‘INACTIVE’ END;重要陷阱IN OUT参数的初始值由于IN OUT参数是“传入传出”所以你必须为它提供一个有意义的初始值。如果你调用子程序时传入的实参变量是NULL那么子程序内部得到的初始值就是NULL。如果你的逻辑没有妥善处理NULL情况就可能出错。例如上面的toggle_status过程如果传入的p_status初始就是NULL那么所有IF判断都为假最终会执行ELSE分支将其设为‘UNKNOWN’。三种模式对比总结表特性INOUTIN OUT数据流向调用者 - 子程序子程序 - 调用者调用者 - 子程序子程序内可读是且只读是但需先赋值是读取传入值子程序内可写否编译错误是必须赋值是可修改传递机制默认按值传递按引用传递按值传递支持NOCOPY是避免大数据复制是是避免两次复制实参要求常量、变量、表达式必须为变量必须为变量形参初始值实参的副本值NULL未初始化实参的副本值主要用途提供输入数据、条件返回计算结果、状态修改传入的变量、累加器3. 实战中的“坑”与最佳实践如何正确选择和使用参数模式理解了理论我们来看看实战中如何避开陷阱做出最佳选择。很多开发中的“灵异事件”都源于参数模式的误用。3.1 模式选择决策指南什么时候用什么选择哪种模式首先取决于你的设计意图其次要考虑性能和代码清晰度。首选IN模式如果参数仅仅用于向子程序提供信息子程序不会改变它那么毫无争议地使用IN模式。这是最安全、意图最清晰的方式。即使未来子程序逻辑变更IN参数也能保证调用者的数据不会被意外修改。何时用OUT何时用函数返回值使用OUT参数当子程序需要返回多个独立的值时。函数只能返回一个标量或一个复合类型而过程可以通过多个OUT参数返回多个值。当需要返回一个游标引用REF CURSOR时通常使用OUT参数。当子程序的主要目的是执行一个动作如更新、删除同时附带返回一些状态信息时使用过程PROCEDURE配合OUT参数更符合语义。使用函数RETURN当子程序的主要目的是计算并返回一个单一结果时。例如计算税率、验证密码强度等。这符合数学上“函数”的概念代码可读性更强。函数可以直接在SQL语句中调用前提是函数是“纯净的”即没有DML操作等副作用而带OUT参数的过程不能。示例对比— 方案A使用过程OUT参数 CREATE OR REPLACE PROCEDURE get_employee_info( p_emp_id IN NUMBER, p_name OUT VARCHAR2, p_salary OUT NUMBER, p_dept OUT VARCHAR2 ) AS ... — 调用 DECLARE v_name VARCHAR2(50); v_sal NUMBER; v_dept VARCHAR2(50); BEGIN get_employee_info(1001, v_name, v_sal, v_dept); END; — 方案B使用函数返回记录类型 CREATE TYPE emp_info_rec AS OBJECT ( name VARCHAR2(50), salary NUMBER, department VARCHAR2(50) ); CREATE OR REPLACE FUNCTION get_employee_info_fn( p_emp_id IN NUMBER ) RETURN emp_info_rec AS ... — 调用 DECLARE v_info emp_info_rec; BEGIN v_info : get_employee_info_fn(1001); END;方案B在返回多个相关数据时更优雅但需要预先定义类型。方案A更直接灵活尤其在返回字段不确定或动态时。谨慎使用IN OUT模式IN OUT模式通常用于以下场景修改传入的变量如之前toggle_status的例子或者一个累加器过程。传递大型对象并修改例如传入一个大的CLOB进行内容处理处理完再传回。此时强烈建议配合NOCOPY使用。实现“引用”语义当你希望子程序直接操作调用者的某个复杂数据结构如嵌套表时。一个常见的误用是将IN OUT当作“可选的OUT”来用即调用者不关心传入值只关心输出值。这是错误的。你应该使用OUT模式并在子程序内部忽略其初始的NULL状态。使用IN OUT会给调用者强加一个“必须提供有意义的初始值”的负担降低了接口的易用性。3.2 性能考量NOCOPY的得与失对于IN、IN OUT参数当传递大型数据结构如包含数千元素的集合VARRAY、NESTED TABLE或大的LOB时按值传递的复制开销会非常显著影响性能。使用NOCOPY的优点减少内存拷贝直接传递指针极大提升性能尤其在大数据量和频繁调用时。避免ORA-06502错误在某些场景下按值传递大型集合可能因为内存不足或超出PL/SQL子程序参数的大小限制而抛出值错误VALUE_ERRORNOCOPY可以避免这个问题。使用NOCOPY的风险与限制副作用Side Effects这是最大的风险。如果子程序因异常而中断通过IN OUT NOCOPY修改的实参可能处于一种“部分被修改”的不确定状态。因为修改是直接作用于原内存地址的无法自动回滚。CREATE OR REPLACE PROCEDURE risky_proc( p_data IN OUT NOCOPY MY_ARRAY_TYPE ) IS BEGIN p_data(1) : ‘new value’; — 直接修改了原数据 — 假设这里发生了一个不可预见的异常 RAISE SOME_EXCEPTION; END;调用后即使过程异常退出p_data(1)可能已经被改为‘new value’了。编译器可能忽略NOCOPY只是一个提示。在以下情况编译器为了确保语义正确性会忽略NOCOPY参数带有NOT NULL约束。参数是游标变量REF CURSOR。参数在调用时需要隐式类型转换。子程序被远程调用通过数据库链接。子程序被作为并行查询的一部分调用。别名问题Aliasing如果同一个实参通过多个NOCOPY参数传入或者在子程序内部NOCOPY参数和局部变量指向了同一块内存就会产生别名导致逻辑混乱难以调试。最佳实践建议对于简单的标量类型NUMBER,VARCHAR2,DATE不要使用NOCOPY。复制开销微乎其微而副作用风险得不偿失。对于大型的集合类型或LOB考虑使用NOCOPY。但在使用前必须确保子程序的异常处理非常完备能在发生异常时将数据恢复到一致状态或者调用者能接受数据被部分修改的风险。仔细检查是否存在别名问题。将NOCOPY的使用限制在私有子程序或包内部避免在公共API中暴露以减少不可控的风险。3.3 异常处理与参数状态参数模式与异常处理紧密相关理解它们之间的交互能帮你写出更健壮的程序。OUT和IN OUT参数在异常发生时的行为如果子程序因未处理的异常而退出异常传播到调用者那么对于按值传递的参数默认的IN OUT以及带NOCOPY的IN实参的值不会被改变。因为修改发生在形参这个副本上而副本随着子程序栈的销毁而消失了。如果子程序因未处理的异常而退出对于按引用传递的参数默认的OUT以及带NOCOPY的IN OUT实参可能已经被改变对于OUT它从NULL被初始化了不OUT参数在子程序入口时实参值不被传入但子程序内部对其形参的赋值会直接作用到实参地址。如果异常发生在赋值之后那么实参就已经被修改了。一个演示案例CREATE OR REPLACE PROCEDURE proc_with_exception( p_in_val IN NUMBER, p_out_val OUT NUMBER, p_inout_val IN OUT NUMBER ) AS BEGIN DBMS_OUTPUT.PUT_LINE(‘Start. p_inout_val’ || p_inout_val); p_out_val : 100; — 修改OUT参数 p_inout_val : 200; — 修改IN OUT参数 DBMS_OUTPUT.PUT_LINE(‘After assignment.’); RAISE_APPLICATION_ERROR(-20001, ‘模拟异常’); — 抛出异常 EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘Exception caught locally.’); RAISE; — 再次抛出 END; / DECLARE v_out NUMBER : 1; v_inout NUMBER : 2; BEGIN v_inout : 2; BEGIN proc_with_exception(10, v_out, v_inout); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘Outer block caught: ‘ || SQLERRM); END; DBMS_OUTPUT.PUT_LINE(‘v_out’ || v_out || ‘, v_inout’ || v_inout); END;运行这个块输出可能是Start. p_inout_val2 After assignment. Exception caught locally. Outer block caught: ORA-20001: 模拟异常 v_out100, v_inout2分析p_out_val是OUT模式按引用传递。在子程序中p_out_val : 100已经执行所以即使发生异常v_out也被改成了100。p_inout_val是IN OUT模式默认按值传递。在子程序中p_inout_val : 200修改的是形参副本。由于异常最终传播到了外部子程序非正常结束形参副本的值200没有被复制回实参v_inout。所以v_inout仍然是2。如果p_inout_val被声明为IN OUT NOCOPY NUMBER那么它也是按引用传递异常发生后v_inout的值可能会是200取决于异常发生和传递的精确时机这就产生了不确定性。给你的经验在编写带有OUT或IN OUT NOCOPY参数的子程序时必须在子程序内部妥善处理所有可能的异常。要么在异常处理块中将输出参数设置为某个错误状态值要么确保在发生异常前不修改这些参数。最安全的做法是在子程序开头将OUT或IN OUT参数的值先保存到局部变量中所有逻辑都基于局部变量进行只有在所有操作都确认成功完成后再将局部变量的值赋给输出参数。这类似于一种“两阶段提交”的思想能最大程度保证数据一致性。4. 高级话题与兼容性考量从热词看实际应用结合你提供的网络热词我们可以看到很多实际问题都围绕着参数模式展开。比如java.sql.sqlexception:ora-06502:pl/sql:number or value error这个错误经常就发生在参数传递或处理过程中。4.1 与编程语言如Java/JDBC的交互这是企业应用中最常见的场景。Java程序通过JDBC调用KingbaseES的存储过程或函数如何正确地传递IN、OUT、IN OUT参数JDBC中的调用方式对于存储过程使用CallableStatement。IN参数使用setXXX()方法设置。OUT参数必须使用registerOutParameter()方法先注册参数的类型执行后再用getXXX()方法获取。IN OUT参数结合两者先setXXX()再registerOutParameter()最后getXXX()。一个典型的Java调用示例// 假设有一个过程PROCEDURE proc_emp(emp_id IN NUMBER, emp_name OUT VARCHAR2) String sql “{call proc_emp(?, ?)}”; try (CallableStatement cstmt connection.prepareCall(sql)) { // 设置IN参数 cstmt.setInt(1, 1001); // 注册OUT参数的类型 cstmt.registerOutParameter(2, Types.VARCHAR); // 执行 cstmt.execute(); // 获取OUT参数的值 String name cstmt.getString(2); System.out.println(“Employee Name: “ name); }常见错误与解决ORA-06502: PL/SQL: numeric or value error原因1Java端设置的IN参数类型或值与PL/SQL形参声明不匹配。例如PL/SQL中参数是VARCHAR2(10)Java传了一个超过10字符的字符串。原因2PL/SQL过程中对OUT参数赋值时值超过了其声明的长度或精度。原因3IN OUT参数在PL/SQL内部被使用如参与运算时其传入的值为NULL而运算不支持NULL。解决仔细检查参数定义在PL/SQL中对可能为NULL的输入进行NVL处理在Java端进行数据校验和截断。忘记注册OUT参数会导致执行时错误或获取不到值。获取OUT参数的顺序或索引错误getXXX()方法的索引应与registerOutParameter()的索引一致。4.2 参数默认值与重载KingbaseES的PL/SQL支持为IN参数设置默认值这增加了灵活性。CREATE OR REPLACE PROCEDURE search_orders( p_customer_id IN NUMBER DEFAULT NULL, p_start_date IN DATE DEFAULT SYSDATE - 30, p_status IN VARCHAR2 DEFAULT ‘SHIPPED’ ) AS ...调用时可以省略有默认值的参数BEGIN search_orders; — 使用所有默认值 search_orders(p_customer_id 101); — 仅指定客户ID search_orders(p_status ‘PENDING’, p_start_date SYSDATE - 7); — 使用命名表示法顺序无关 END;参数模式与重载Overloading在包PACKAGE中子程序可以重载即多个子程序同名但参数列表不同。参数模式是签名的一部分。CREATE OR REPLACE PACKAGE my_pkg AS PROCEDURE process_data(p_data IN NUMBER); — 版本1 PROCEDURE process_data(p_data IN OUT VARCHAR2); — 版本2参数模式不同可以重载 — PROCEDURE process_data(p_data OUT NUMBER); — 版本3如果只有模式不同与版本1可能造成调用歧义需谨慎。 END my_pkg;编译器根据调用时实参的数据类型和模式来决定调用哪个版本。但需要注意如果两个重载过程的区别仅仅在于IN和OUT/IN OUT模式不同调用时可能会因为歧义而失败尤其是在使用命名表示法时。4.3 记录类型与集合类型的参数传递当参数是复杂类型如自定义的记录RECORD或集合TABLE时行为需要特别注意。记录类型RECORD可以作为IN、OUT、IN OUT参数传递。传递的是整个记录的值。CREATE TYPE emp_rectype AS OBJECT (id NUMBER, name VARCHAR2(50)); CREATE OR REPLACE PROCEDURE handle_emp(p_emp IN emp_rectype) AS ...集合类型NESTED TABLE,VARRAY这些类型通常数据量较大。作为IN参数按值传递整个集合会被复制性能开销大。务必考虑使用IN NOCOPY。作为OUT参数按引用传递。在子程序内部你需要初始化这个集合如p_list : my_table_type();然后扩展EXTEND并赋值。作为IN OUT参数默认按值传递两次复制性能极差。几乎总是应该使用IN OUT NOCOPY。一个集合参数的示例CREATE OR REPLACE TYPE num_list IS TABLE OF NUMBER; CREATE OR REPLACE PROCEDURE filter_numbers( p_input_list IN NOCOPY num_list, p_output_list OUT NOCOPY num_list ) AS v_idx PLS_INTEGER; BEGIN p_output_list : num_list(); — 初始化OUT集合 IF p_input_list IS NOT NULL AND p_input_list.COUNT 0 THEN v_idx : p_input_list.FIRST; WHILE v_idx IS NOT NULL LOOP IF p_input_list(v_idx) 10 THEN — 过滤条件 p_output_list.EXTEND; p_output_list(p_output_list.LAST) : p_input_list(v_idx); END IF; v_idx : p_input_list.NEXT(v_idx); END LOOP; END IF; END;4.4 从“热词”看真实问题排查你提供的热词中pl/sql develope破解、s7-200smart子程序设置密码、如何解密这些虽然与KingbaseES参数模式不直接相关但反映了开发者在进行数据库编程和集成时的常见诉求——寻找工具和解决权限、加密问题。而java.sql.sqlexception:ora-06502和博途中编写温度转换子程序fc则直接指向了跨语言调用和子程序逻辑编写。对于ORA-06502错误结合参数模式的排查思路应该是定位错误行错误信息中的ORA-06512: at line X给出了子程序内部出错的行号。检查参数赋值查看该行代码中所有涉及的变量和参数特别是OUT、IN OUT参数是否发生了值越界字符串超长、数字超精度、类型不匹配或对NULL值的非法运算。检查参数传递如果是从Java等外部程序调用检查JDBC中设置的参数类型、长度是否与PL/SQL定义一致。使用简单日志在子程序关键点使用DBMS_OUTPUT.PUT_LINE输出参数的值或使用更专业的日志工具观察参数的实际传递过程。对于像“温度转换子程序”这样的逻辑在定义参数时就要想清楚温度值是输入IN吗转换后的结果是输出OUT还是直接作为函数返回值转换公式是否需要额外的参数如单位IN VARCHAR2把这些数据流向用正确的参数模式固定下来是写出清晰、可复用子程序的第一步。参数模式是PL/SQL编程的基石之一。它看似简单却串联起了数据封装、性能优化和异常处理等多个核心主题。理解并正确运用IN、OUT、IN OUT以及NOCOPY这样的高级特性能让你编写的存储过程、函数更加健壮、高效和易于维护。下次当你设计一个子程序接口时不妨多花一分钟思考一下每个参数真正的意图这能省下未来无数小时调试的时间。