1. 从一次存储过程调试说起参数传递的“哑谜”最近在帮团队排查一个金仓数据库KingbaseES的性能问题一个原本在Oracle上运行良好的报表存储过程迁移到金仓的Oracle兼容模式下后执行效率变得异常低下。经过层层剥离最终定位到问题出在一个看似不起眼的地方存储过程参数的模式设置。开发同事在迁移时为了图省事将几个原本定义为OUT模式的参数改成了IN OUT想着“反正都能传值出去”。就是这个小小的改动导致了执行计划的天差地别。这件事让我意识到无论是Oracle原生环境还是像金仓这样提供高度兼容性的国产数据库对于存储过程或函数中IN、OUT、IN OUT这三种参数模式的理解绝不能停留在“能传值进去”或“能返回值出来”的浅层认知。它们背后是数据库引擎截然不同的数据处理逻辑、内存管理方式和性能优化策略。尤其是在进行数据库迁移或异构系统集成时参数模式的误用就像埋下了一颗“性能地雷”平时可能相安无事一旦数据量上来或并发增高就会瞬间引爆。今天我们就以金仓数据库的Oracle兼容模式为背景彻底拆解这三种参数模式。我会结合实际的代码案例、执行计划分析和性能测试数据告诉你它们到底有什么区别在什么场景下该用哪一种以及像开头提到的那个案例错误的用法会带来怎样具体的影响。无论你是正在从Oracle向金仓迁移还是单纯想深入理解PL/SQL或KingbasePL/SQL的参数机制这篇文章都能给你带来直接的实操参考。2. 参数模式的核心数据流方向与内存操作揭秘在深入代码之前我们必须先建立正确的概念模型。IN、OUT、IN OUT这三个关键字定义的不是参数的数据类型而是数据在调用者Caller和被调用程序单元Callee如存储过程、函数之间的流动方向以及内存操作方式。这是理解所有后续行为和性能差异的基石。2.1 IN模式只读的输入通道IN是默认的模式。当你定义一个参数为IN时你是在告诉数据库“这个值由调用者提供在过程内部你只能读取它不能修改它。”底层原理与行为传值调用Call by Value在大多数情况下数据库引擎会将调用者提供的实际参数Actual Parameter的值复制一份到被调用过程的形式参数Formal Parameter所在的内存空间。这个过程发生在过程执行之前。只读属性在过程内部任何试图对IN参数进行赋值:的操作都会引发编译错误。这保证了数据的原始性不被意外篡改。性能考量由于是值拷贝对于基本数据类型如NUMBER, VARCHAR2开销很小。但对于大型对象如CLOB、用户自定义的复杂记录类型或集合拷贝整个值可能会产生显著的内存和CPU开销。不过现代数据库优化器在某些情况下会对大型对象采用“写时复制”或引用传递等优化但逻辑上仍保证其只读性。适用场景提供查询条件如根据员工ID查询信息。提供配置或控制参数如分页大小、排序字段。任何不需要在过程内部改变其值的输入数据。金仓Oracle兼容模式下的示例与验证-- 创建一个使用IN参数的存储过程 CREATE OR REPLACE PROCEDURE proc_in_demo( p_emp_id IN NUMBER, -- IN模式输入员工ID p_emp_info OUT VARCHAR2 ) AS v_salary NUMBER; BEGIN -- 尝试修改IN参数这将导致编译错误 -- p_emp_id : p_emp_id 1; -- 此行若取消注释编译时会报错ORA-06550 / 灵蜂类似错误 -- 正确读取IN参数的值 SELECT emp_name || , || job INTO p_emp_info FROM employees WHERE employee_id p_emp_id; -- 后续可以使用p_emp_id进行其他只读操作 DBMS_OUTPUT.PUT_LINE(查询的ID是: || p_emp_id); END; /调用这个过程时你只需要为p_emp_id提供一个值这个值传入后就被“锁定”了。2.2 OUT模式单向的写入通道OUT模式与IN完全相反。它表示“调用者不需要也无法提供这个参数的初始值它的值完全由被调用过程内部计算并填充然后返回给调用者。”底层原理与行为传址调用Call by Reference的“空盒子”调用时传递给过程的是一个“空盒子”未初始化的变量地址。过程执行前这个盒子里的值是NULL或未定义。只写属性在调用者视角调用者在调用前为OUT参数提供的任何值都是无效的过程内部根本看不到。过程内部必须对其赋值否则在过程成功返回后调用者看到的该参数值仍然是NULL。必须赋值在过程内部OUT参数在逻辑路径上必须被至少赋值一次否则可能引发VALUE_ERROR或返回NULL。适用场景返回过程的执行结果、状态码或错误消息。返回通过计算或查询得到的单个值。返回游标引用REF CURSOR。金仓Oracle兼容模式下的示例与陷阱CREATE OR REPLACE PROCEDURE proc_out_demo( p_dept_id IN NUMBER, p_avg_salary OUT NUMBER, p_emp_count OUT NUMBER ) AS BEGIN -- p_avg_salary和p_emp_count在进入过程时是NULL或未初始化 -- 我们必须为它们赋值 SELECT AVG(salary), COUNT(*) INTO p_avg_salary, p_emp_count FROM employees WHERE department_id p_dept_id; -- 如果查询没有结果SELECT...INTO会抛出NO_DATA_FOUND异常。 -- 为了避免OUT参数未被赋值通常需要异常处理。 EXCEPTION WHEN NO_DATA_FOUND THEN p_avg_salary : 0; p_emp_count : 0; -- 或者也可以抛出异常但必须确保在抛出前OUT参数已处于确定状态虽然调用者可能捕获不到。 END; / -- 调用示例 DECLARE v_avg_sal NUMBER; v_count NUMBER; BEGIN -- 注意v_avg_sal和v_count作为实参传入但它们的初始值比如这里没赋值是NULL对过程proc_out_demo不可见。 proc_out_demo(80, v_avg_sal, v_count); DBMS_OUTPUT.PUT_LINE(部门80平均工资: || v_avg_sal || , 人数: || v_count); END; /一个常见的陷阱是调用者误以为可以给OUT参数传入一个初始值供过程使用。这是错误的。在过程内部OUT参数初始状态与调用者传入的实参值无关。2.3 IN OUT模式双向的读写通道IN OUT模式结合了前两者的特点也是最容易产生混淆和性能问题的地方。它表示“调用者提供一个初始值过程可以读取并修改这个值修改后的结果会返回给调用者。”底层原理与行为传址调用Call by Reference的“有内容的盒子”调用时传递给过程的是一个装有初始值的“盒子”的地址。过程内部直接对这个盒子里的数据进行读写操作。读写属性过程内部既可以读取其值也可以赋予新值。过程结束后盒子里的最终内容就是返回给调用者的值。性能与副作用由于是地址传递避免了大型数据的值拷贝看起来效率很高。但这也带来了“副作用”Side Effect——过程内部对参数的修改直接影响调用者的变量。这有时是期望的但有时会导致难以调试的问题。更重要的是在金仓或Oracle中优化器对IN OUT参数的处理可能比单纯的IN或OUT更保守因为它必须假设参数值可能在过程内任何时刻被改变这可能会阻碍某些查询优化如子查询展开、谓词推进。适用场景原地修改需要对传入的复杂数据结构如数组、嵌套表进行原地修改并返回。如果使用IN传入再OUT返回一个新对象会产生两次拷贝开销。累加或迭代计算例如传入一个计数器或累加器在过程内部更新它。返回多个状态一个参数既作为输入条件又作为输出标志。但这种情况应谨慎使用通常用单独的IN和OUT参数更清晰。金仓Oracle兼容模式下的示例与警示-- 一个使用IN OUT进行字符串拼接的简单例子 CREATE OR REPLACE PROCEDURE append_title( p_name IN OUT VARCHAR2, p_title IN VARCHAR2 DEFAULT 工程师 ) AS BEGIN -- 读取并修改IN OUT参数 p_name : p_name || ( || p_title || ); END; / DECLARE v_emp_name VARCHAR2(100) : 张三; BEGIN append_title(v_emp_name, 高级); DBMS_OUTPUT.PUT_LINE(v_emp_name); -- 输出张三 (高级) append_title(v_emp_name); -- 使用默认标题 DBMS_OUTPUT.PUT_LINE(v_emp_name); -- 输出张三 (高级) (工程师) END; /这个例子展示了IN OUT的便利性。但让我们看一个反面教材这正是文章开头那个性能问题的简化版-- 假设有一个根据复杂条件查询并汇总数据的业务过程 CREATE OR REPLACE PROCEDURE complex_report( p_start_date IN DATE, p_end_date IN DATE, p_department_ids IN OUT SYS.ODCINUMBERLIST, -- 误用部门ID列表本应只是输入条件 p_total_amount OUT NUMBER ) AS CURSOR cur_emp IS SELECT /* 这里可能无法有效优化 */ e.employee_id, e.salary, d.department_name FROM employees e JOIN departments d ON e.department_id d.department_id WHERE e.hire_date BETWEEN p_start_date AND p_end_date AND d.department_id MEMBER OF p_department_ids; -- 优化器可能因为p_department_ids是IN OUT而不敢做激进优化 -- ... 复杂的计算逻辑可能还会修改p_department_ids BEGIN p_total_amount : 0; FOR rec IN cur_emp LOOP -- 某些业务逻辑... p_total_amount : p_total_amount rec.salary; -- 危险操作可能基于某些条件清空或修改输入列表 -- IF some_condition THEN -- p_department_ids : SYS.ODCINUMBERLIST(); -- 清空列表 -- END IF; END LOOP; END; /这里p_department_ids本应是一个纯粹的输入条件IN模式。将其定义为IN OUT首先在语义上不清晰调用者会疑惑这个列表为什么可能被修改其次最关键的是它向优化器传递了一个错误信号“这个参数可能在过程执行中被改变”。这可能导致优化器无法在编译时确定p_department_ids的内容从而不敢将MEMBER OF等集合操作进行有效的优化转换比如将其展开为一系列OR条件最终选择了更保守、更低效的执行计划。3. 三种模式的对比与选型决策矩阵理解了原理我们通过一个表格进行直观对比这能帮助你在设计时快速做出正确选择。特性维度IN 模式OUT 模式IN OUT 模式数据流向调用者 - 过程过程 - 调用者调用者 - 过程过程内部只读必须写入通常可读可写调用前实参必须提供有效值可以提供值但过程内不可见视为未初始化必须提供有效值调用后实参保持不变被赋予过程内部计算出的新值被更新为过程内部修改后的值底层传递方式通常为传值Copy传址Reference传址Reference性能一般情况简单类型开销小大对象需注意拷贝成本开销小仅传递地址开销小仅传递地址对优化器影响最小。优化器可以安全地基于其值进行优化。无直接影响。潜在影响大。优化器需考虑其值可能被修改可能阻碍优化。代码清晰度高。意图明确是输入。高。意图明确是输出。较低。意图模糊既是输入又是输出增加了理解复杂度。典型使用场景查询条件、配置参数、常量输入。返回结果、状态码、游标。原地修改的复杂数据结构、累加器、历史遗留接口兼容。选型决策指南首选IN只要参数值在过程内部不需要被修改就坚定不移地使用IN模式。这是最安全、最清晰、对优化器最友好的选择。次选OUT当参数纯粹用于从过程内部向外传递信息时使用OUT。确保在过程的所有退出路径包括异常处理上都对其进行了赋值。慎用IN OUT仅在以下情况考虑使用性能关键路径需要原地修改一个非常大的数据结构如数万元素的集合且复制成本不可接受。模拟引用语义实现类似“交换两个变量值”这样的操作。遵守特定接口维护或集成一个已定义的、使用了IN OUT的现有接口。内部状态传递在递归或需要维护跨调用状态的特定算法中。核心原则能用IN和OUT组合实现的就不要用IN OUT。将输入和输出分离是提高代码可读性、可维护性和可优化性的关键。4. 金仓Oracle兼容模式下的特殊考量与实战踩坑金仓数据库在兼容Oracle的PL/SQL语法和大部分行为上做得相当不错但在参数处理的一些边角场景和性能表现上仍有需要特别注意的地方。4.1 默认值与NOCOPY提示的兼容性默认值三种参数模式都可以指定默认值DEFAULT或:。但对于OUT和IN OUT参数指定默认值仅在过程内部有意义。调用时如果对应实参被省略对于OUT/IN OUT传入的仍然是一个未初始化的变量对OUT或一个缺失的实参通常会导致错误而不是那个默认值。默认值仅在过程内部当你想基于参数的初始状态对于IN OUT做判断但又允许调用者不提供时有用但这种情况极其罕见且容易混淆强烈不建议对OUT/IN OUT使用默认值。NOCOPY提示这是Oracle PL/SQL中的一个性能优化提示用于OUT和IN OUT参数建议编译器采用“传引用”而非“传值”的方式尽管OUT/IN OUT本身已是传址但涉及复杂类型时编译器有时会进行写时拷贝等保护性复制。语法是parameter_name [IN | OUT | IN OUT] NOCOPY datatype。金仓的兼容情况金仓数据库的Oracle兼容模式通常支持NOCOPY语法。但在其内部实现中对于集合、记录等复杂类型即使不加NOCOPY其传递方式也可能已经是高效的引用方式。添加NOCOPY提示可以看作是一种明确的意图声明但在金仓中性能提升可能不如在Oracle某些场景下明显。NOCOPY的风险使用NOCOPY时如果过程执行失败并回滚由于参数是引用传递实参变量可能已经被部分修改处于一种“不确定”的状态。这违背了事务的原子性。因此仅在对性能有极致要求且能接受参数状态在异常时可能不完整的场景下使用。-- 金仓下使用NOCOPY的示例 CREATE OR REPLACE PROCEDURE process_large_list( p_data_list IN OUT NOCOPY SYS.ODCIVARCHAR2LIST -- 使用NOCOPY提示 ) AS BEGIN FOR i IN 1..p_data_list.COUNT LOOP p_data_list(i) : UPPER(p_data_list(i)); END LOOP; END; /4.2 参数传递的异常处理与原子性这是一个高级但重要的话题。当存储过程抛出未处理的异常时对于IN参数无影响调用者的实参保持不变。对于OUT参数在过程异常退出前如果已经对OUT参数进行了赋值这些赋值是否会保留给调用者在Oracle和金仓的标准行为下如果异常被传播到调用者而未在过程内部捕获则所有对OUT和IN OUT参数的修改都会被回滚调用者看到的实参值保持不变或为NULL。但如果过程内部捕获了异常并处理后再抛出或进行其他操作则情况会复杂化。对于IN OUT参数同上如果异常未处理修改通常被回滚。但如果使用了NOCOPY则有可能出现部分修改已写入实参的情况取决于具体实现和异常发生点。实战建议在过程内部进行严谨的异常处理。如果需要对OUT/IN OUT参数赋值后可能发生的异常进行处理并在处理完后重新抛出或返回错误状态务必在异常处理块中也将这些参数设置为一个明确的错误状态值以保证调用者获得确定性的结果。4.3 从网络热词看常见混淆与错误分析提供的热词可以发现很多问题都与参数模式的误解或不当使用有关your access token could not be refreshed.../communications link failure...这类连接超时或令牌失效错误虽然不直接是参数模式问题但在编写重试或状态维护的存储过程时如果用来传递连接状态或令牌的参数模式设计不当例如该用IN OUT维护状态却用了IN和OUT分开导致状态丢失会加剧这类问题的处理复杂度。为何博途程序块fc,fb中的定时器要封装成inout参数这是一个来自工业自动化西门子PLC编程的问题但其思想相通。将定时器封装为IN OUT在TIA Portal中是为了在函数块多次调用间保持定时器的状态。这正体现了IN OUT的核心用途在多次调用间传递和维持一个可变的状态。在金仓/Oracle中如果你想写一个递归的过程或者一个需要记住上次调用状态的过程比如分页游标可能会考虑使用IN OUT参数来传递状态变量。Codex ran out of room.../CUDA out of memory这些是资源耗尽错误。误用IN OUT导致优化器失效可能产生巨大的中间结果集如未优化的笛卡尔积从而消耗大量临时表空间或PGA内存间接引发此类“Out of Memory”问题。一个本该使用IN列表进行高效过滤的查询因为参数模式设为IN OUT可能迫使数据库对全表数据进行处理。idea中build out输出乱码这提醒我们当使用OUT参数返回字符串时特别是可能包含多字节字符如中文时要确保数据库字符集、客户端NLS_LANG设置对于Oracle客户端、以及应用层编码的一致性否则返回的OUT值可能会出现乱码。4.4 性能问题排查实例复盘回到开头的案例我们是如何排查并最终定位到是IN OUT参数导致性能问题的现象迁移后的存储过程在测试环境小数据量下正常在生产环境大数据量下耗时从秒级飙升到分钟级。初步排查检查了SQL语句、索引、表统计信息均无异常。对比Oracle原库和金仓新库的执行计划发现金仓下关键查询的连接方式从“NESTED LOOPS”变成了“HASH JOIN”且多出了一个全表扫描。深入分析使用金仓的EXPLAIN PLAN工具或SET AUTOEXPLAIN ON查看存储过程内SQL的执行计划。发现一个使用MEMBER OF查询集合的语句其执行计划异常简单且低效。怀疑点该语句的过滤条件是一个IN OUT的集合参数。查阅金仓文档并回忆参数模式特性怀疑优化器无法“窥视”PeekIN OUT参数的内容因此无法在编译时优化MEMBER OF。验证将存储过程中该IN OUT参数改为IN模式因为过程内部确实没有修改该集合的需求。重新编译过程。结果再次执行耗时恢复至秒级。检查执行计划优化器成功将MEMBER OF转换为了高效的IN LIST查询并选择了正确的索引和连接方式。这个案例的教训是参数模式不仅关乎数据流更是给优化器的重要提示。IN模式说“这个值很稳定放心优化。”IN OUT模式说“这个值可能随时会变请保守一点。”5. 最佳实践与迁移建议基于以上分析总结出在金仓Oracle兼容模式下使用参数模式的最佳实践声明显式化永远不要依赖默认模式即不写模式默认为IN。即使它是IN也明确写上IN。这提高了代码的可读性和可维护性。-- 好 PROCEDURE my_proc(p_id IN NUMBER, p_result OUT VARCHAR2); -- 不好 PROCEDURE my_proc(p_id NUMBER, p_result VARCHAR2); -- p_result意图不明单一职责原则一个参数尽量只承担一种职责。输入就用IN输出就用OUT。避免使用IN OUT除非你有非常充分的理由如性能瓶颈或状态保持。迁移时仔细审核从Oracle向金仓迁移存储过程代码时要逐一检查每个参数的模型。确认每个IN OUT参数是否真的有必要。如果过程内部只是读取它请改为IN如果只是写入它请考虑改为OUT并在调用前准备好初始值如果需要的话通过另一个IN参数传入。注意金仓可能对某些复杂数据类型如自定义对象、高级集合类型的IN OUT传递实现细节与Oracle有细微差别需要进行功能测试。善用记录类型和集合当需要返回多个相关值时不要定义一堆单独的OUT参数而是定义一个RECORD类型或OBJECT类型用一个OUT参数返回。这样更清晰也减少了参数个数。CREATE OR REPLACE PACKAGE emp_pkg AS TYPE emp_info_rec IS RECORD ( name employees.emp_name%TYPE, salary employees.salary%TYPE, dept_name departments.dept_name%TYPE ); PROCEDURE get_emp_details(p_id IN NUMBER, p_info OUT emp_info_rec); END emp_pkg;性能测试对于性能关键的存储过程如果必须使用IN OUT处理大型数据或者怀疑参数模式影响了性能请务必进行A/B测试。分别用IN/OUT组合和IN OUT实现相同逻辑在真实数据量下对比执行时间和资源消耗。文档化如果由于历史原因或特定优化需求不得不使用IN OUT参数请在过程头部的注释中明确说明原因以及该参数在调用前后状态的变化避免其他开发者误用。参数模式是存储过程设计的基石之一。正确的选择能让代码清晰高效错误的选择则可能埋下维护的陷阱和性能的隐患。在金仓这样的国产数据库大步前进逐步替代国外产品的今天深入理解这些底层细节写出规范、高效、可移植的代码对于我们每一位开发者而言都是一项值得投入的核心技能。下次编写或审查存储过程时不妨多花一分钟思考一下这个参数到底该用IN、OUT还是IN OUT 金仓数据库存储过程参数模式详解：IN、OUT、IN OUT的性能差异与选型指南
1. 从一次存储过程调试说起参数传递的“哑谜”最近在帮团队排查一个金仓数据库KingbaseES的性能问题一个原本在Oracle上运行良好的报表存储过程迁移到金仓的Oracle兼容模式下后执行效率变得异常低下。经过层层剥离最终定位到问题出在一个看似不起眼的地方存储过程参数的模式设置。开发同事在迁移时为了图省事将几个原本定义为OUT模式的参数改成了IN OUT想着“反正都能传值出去”。就是这个小小的改动导致了执行计划的天差地别。这件事让我意识到无论是Oracle原生环境还是像金仓这样提供高度兼容性的国产数据库对于存储过程或函数中IN、OUT、IN OUT这三种参数模式的理解绝不能停留在“能传值进去”或“能返回值出来”的浅层认知。它们背后是数据库引擎截然不同的数据处理逻辑、内存管理方式和性能优化策略。尤其是在进行数据库迁移或异构系统集成时参数模式的误用就像埋下了一颗“性能地雷”平时可能相安无事一旦数据量上来或并发增高就会瞬间引爆。今天我们就以金仓数据库的Oracle兼容模式为背景彻底拆解这三种参数模式。我会结合实际的代码案例、执行计划分析和性能测试数据告诉你它们到底有什么区别在什么场景下该用哪一种以及像开头提到的那个案例错误的用法会带来怎样具体的影响。无论你是正在从Oracle向金仓迁移还是单纯想深入理解PL/SQL或KingbasePL/SQL的参数机制这篇文章都能给你带来直接的实操参考。2. 参数模式的核心数据流方向与内存操作揭秘在深入代码之前我们必须先建立正确的概念模型。IN、OUT、IN OUT这三个关键字定义的不是参数的数据类型而是数据在调用者Caller和被调用程序单元Callee如存储过程、函数之间的流动方向以及内存操作方式。这是理解所有后续行为和性能差异的基石。2.1 IN模式只读的输入通道IN是默认的模式。当你定义一个参数为IN时你是在告诉数据库“这个值由调用者提供在过程内部你只能读取它不能修改它。”底层原理与行为传值调用Call by Value在大多数情况下数据库引擎会将调用者提供的实际参数Actual Parameter的值复制一份到被调用过程的形式参数Formal Parameter所在的内存空间。这个过程发生在过程执行之前。只读属性在过程内部任何试图对IN参数进行赋值:的操作都会引发编译错误。这保证了数据的原始性不被意外篡改。性能考量由于是值拷贝对于基本数据类型如NUMBER, VARCHAR2开销很小。但对于大型对象如CLOB、用户自定义的复杂记录类型或集合拷贝整个值可能会产生显著的内存和CPU开销。不过现代数据库优化器在某些情况下会对大型对象采用“写时复制”或引用传递等优化但逻辑上仍保证其只读性。适用场景提供查询条件如根据员工ID查询信息。提供配置或控制参数如分页大小、排序字段。任何不需要在过程内部改变其值的输入数据。金仓Oracle兼容模式下的示例与验证-- 创建一个使用IN参数的存储过程 CREATE OR REPLACE PROCEDURE proc_in_demo( p_emp_id IN NUMBER, -- IN模式输入员工ID p_emp_info OUT VARCHAR2 ) AS v_salary NUMBER; BEGIN -- 尝试修改IN参数这将导致编译错误 -- p_emp_id : p_emp_id 1; -- 此行若取消注释编译时会报错ORA-06550 / 灵蜂类似错误 -- 正确读取IN参数的值 SELECT emp_name || , || job INTO p_emp_info FROM employees WHERE employee_id p_emp_id; -- 后续可以使用p_emp_id进行其他只读操作 DBMS_OUTPUT.PUT_LINE(查询的ID是: || p_emp_id); END; /调用这个过程时你只需要为p_emp_id提供一个值这个值传入后就被“锁定”了。2.2 OUT模式单向的写入通道OUT模式与IN完全相反。它表示“调用者不需要也无法提供这个参数的初始值它的值完全由被调用过程内部计算并填充然后返回给调用者。”底层原理与行为传址调用Call by Reference的“空盒子”调用时传递给过程的是一个“空盒子”未初始化的变量地址。过程执行前这个盒子里的值是NULL或未定义。只写属性在调用者视角调用者在调用前为OUT参数提供的任何值都是无效的过程内部根本看不到。过程内部必须对其赋值否则在过程成功返回后调用者看到的该参数值仍然是NULL。必须赋值在过程内部OUT参数在逻辑路径上必须被至少赋值一次否则可能引发VALUE_ERROR或返回NULL。适用场景返回过程的执行结果、状态码或错误消息。返回通过计算或查询得到的单个值。返回游标引用REF CURSOR。金仓Oracle兼容模式下的示例与陷阱CREATE OR REPLACE PROCEDURE proc_out_demo( p_dept_id IN NUMBER, p_avg_salary OUT NUMBER, p_emp_count OUT NUMBER ) AS BEGIN -- p_avg_salary和p_emp_count在进入过程时是NULL或未初始化 -- 我们必须为它们赋值 SELECT AVG(salary), COUNT(*) INTO p_avg_salary, p_emp_count FROM employees WHERE department_id p_dept_id; -- 如果查询没有结果SELECT...INTO会抛出NO_DATA_FOUND异常。 -- 为了避免OUT参数未被赋值通常需要异常处理。 EXCEPTION WHEN NO_DATA_FOUND THEN p_avg_salary : 0; p_emp_count : 0; -- 或者也可以抛出异常但必须确保在抛出前OUT参数已处于确定状态虽然调用者可能捕获不到。 END; / -- 调用示例 DECLARE v_avg_sal NUMBER; v_count NUMBER; BEGIN -- 注意v_avg_sal和v_count作为实参传入但它们的初始值比如这里没赋值是NULL对过程proc_out_demo不可见。 proc_out_demo(80, v_avg_sal, v_count); DBMS_OUTPUT.PUT_LINE(部门80平均工资: || v_avg_sal || , 人数: || v_count); END; /一个常见的陷阱是调用者误以为可以给OUT参数传入一个初始值供过程使用。这是错误的。在过程内部OUT参数初始状态与调用者传入的实参值无关。2.3 IN OUT模式双向的读写通道IN OUT模式结合了前两者的特点也是最容易产生混淆和性能问题的地方。它表示“调用者提供一个初始值过程可以读取并修改这个值修改后的结果会返回给调用者。”底层原理与行为传址调用Call by Reference的“有内容的盒子”调用时传递给过程的是一个装有初始值的“盒子”的地址。过程内部直接对这个盒子里的数据进行读写操作。读写属性过程内部既可以读取其值也可以赋予新值。过程结束后盒子里的最终内容就是返回给调用者的值。性能与副作用由于是地址传递避免了大型数据的值拷贝看起来效率很高。但这也带来了“副作用”Side Effect——过程内部对参数的修改直接影响调用者的变量。这有时是期望的但有时会导致难以调试的问题。更重要的是在金仓或Oracle中优化器对IN OUT参数的处理可能比单纯的IN或OUT更保守因为它必须假设参数值可能在过程内任何时刻被改变这可能会阻碍某些查询优化如子查询展开、谓词推进。适用场景原地修改需要对传入的复杂数据结构如数组、嵌套表进行原地修改并返回。如果使用IN传入再OUT返回一个新对象会产生两次拷贝开销。累加或迭代计算例如传入一个计数器或累加器在过程内部更新它。返回多个状态一个参数既作为输入条件又作为输出标志。但这种情况应谨慎使用通常用单独的IN和OUT参数更清晰。金仓Oracle兼容模式下的示例与警示-- 一个使用IN OUT进行字符串拼接的简单例子 CREATE OR REPLACE PROCEDURE append_title( p_name IN OUT VARCHAR2, p_title IN VARCHAR2 DEFAULT 工程师 ) AS BEGIN -- 读取并修改IN OUT参数 p_name : p_name || ( || p_title || ); END; / DECLARE v_emp_name VARCHAR2(100) : 张三; BEGIN append_title(v_emp_name, 高级); DBMS_OUTPUT.PUT_LINE(v_emp_name); -- 输出张三 (高级) append_title(v_emp_name); -- 使用默认标题 DBMS_OUTPUT.PUT_LINE(v_emp_name); -- 输出张三 (高级) (工程师) END; /这个例子展示了IN OUT的便利性。但让我们看一个反面教材这正是文章开头那个性能问题的简化版-- 假设有一个根据复杂条件查询并汇总数据的业务过程 CREATE OR REPLACE PROCEDURE complex_report( p_start_date IN DATE, p_end_date IN DATE, p_department_ids IN OUT SYS.ODCINUMBERLIST, -- 误用部门ID列表本应只是输入条件 p_total_amount OUT NUMBER ) AS CURSOR cur_emp IS SELECT /* 这里可能无法有效优化 */ e.employee_id, e.salary, d.department_name FROM employees e JOIN departments d ON e.department_id d.department_id WHERE e.hire_date BETWEEN p_start_date AND p_end_date AND d.department_id MEMBER OF p_department_ids; -- 优化器可能因为p_department_ids是IN OUT而不敢做激进优化 -- ... 复杂的计算逻辑可能还会修改p_department_ids BEGIN p_total_amount : 0; FOR rec IN cur_emp LOOP -- 某些业务逻辑... p_total_amount : p_total_amount rec.salary; -- 危险操作可能基于某些条件清空或修改输入列表 -- IF some_condition THEN -- p_department_ids : SYS.ODCINUMBERLIST(); -- 清空列表 -- END IF; END LOOP; END; /这里p_department_ids本应是一个纯粹的输入条件IN模式。将其定义为IN OUT首先在语义上不清晰调用者会疑惑这个列表为什么可能被修改其次最关键的是它向优化器传递了一个错误信号“这个参数可能在过程执行中被改变”。这可能导致优化器无法在编译时确定p_department_ids的内容从而不敢将MEMBER OF等集合操作进行有效的优化转换比如将其展开为一系列OR条件最终选择了更保守、更低效的执行计划。3. 三种模式的对比与选型决策矩阵理解了原理我们通过一个表格进行直观对比这能帮助你在设计时快速做出正确选择。特性维度IN 模式OUT 模式IN OUT 模式数据流向调用者 - 过程过程 - 调用者调用者 - 过程过程内部只读必须写入通常可读可写调用前实参必须提供有效值可以提供值但过程内不可见视为未初始化必须提供有效值调用后实参保持不变被赋予过程内部计算出的新值被更新为过程内部修改后的值底层传递方式通常为传值Copy传址Reference传址Reference性能一般情况简单类型开销小大对象需注意拷贝成本开销小仅传递地址开销小仅传递地址对优化器影响最小。优化器可以安全地基于其值进行优化。无直接影响。潜在影响大。优化器需考虑其值可能被修改可能阻碍优化。代码清晰度高。意图明确是输入。高。意图明确是输出。较低。意图模糊既是输入又是输出增加了理解复杂度。典型使用场景查询条件、配置参数、常量输入。返回结果、状态码、游标。原地修改的复杂数据结构、累加器、历史遗留接口兼容。选型决策指南首选IN只要参数值在过程内部不需要被修改就坚定不移地使用IN模式。这是最安全、最清晰、对优化器最友好的选择。次选OUT当参数纯粹用于从过程内部向外传递信息时使用OUT。确保在过程的所有退出路径包括异常处理上都对其进行了赋值。慎用IN OUT仅在以下情况考虑使用性能关键路径需要原地修改一个非常大的数据结构如数万元素的集合且复制成本不可接受。模拟引用语义实现类似“交换两个变量值”这样的操作。遵守特定接口维护或集成一个已定义的、使用了IN OUT的现有接口。内部状态传递在递归或需要维护跨调用状态的特定算法中。核心原则能用IN和OUT组合实现的就不要用IN OUT。将输入和输出分离是提高代码可读性、可维护性和可优化性的关键。4. 金仓Oracle兼容模式下的特殊考量与实战踩坑金仓数据库在兼容Oracle的PL/SQL语法和大部分行为上做得相当不错但在参数处理的一些边角场景和性能表现上仍有需要特别注意的地方。4.1 默认值与NOCOPY提示的兼容性默认值三种参数模式都可以指定默认值DEFAULT或:。但对于OUT和IN OUT参数指定默认值仅在过程内部有意义。调用时如果对应实参被省略对于OUT/IN OUT传入的仍然是一个未初始化的变量对OUT或一个缺失的实参通常会导致错误而不是那个默认值。默认值仅在过程内部当你想基于参数的初始状态对于IN OUT做判断但又允许调用者不提供时有用但这种情况极其罕见且容易混淆强烈不建议对OUT/IN OUT使用默认值。NOCOPY提示这是Oracle PL/SQL中的一个性能优化提示用于OUT和IN OUT参数建议编译器采用“传引用”而非“传值”的方式尽管OUT/IN OUT本身已是传址但涉及复杂类型时编译器有时会进行写时拷贝等保护性复制。语法是parameter_name [IN | OUT | IN OUT] NOCOPY datatype。金仓的兼容情况金仓数据库的Oracle兼容模式通常支持NOCOPY语法。但在其内部实现中对于集合、记录等复杂类型即使不加NOCOPY其传递方式也可能已经是高效的引用方式。添加NOCOPY提示可以看作是一种明确的意图声明但在金仓中性能提升可能不如在Oracle某些场景下明显。NOCOPY的风险使用NOCOPY时如果过程执行失败并回滚由于参数是引用传递实参变量可能已经被部分修改处于一种“不确定”的状态。这违背了事务的原子性。因此仅在对性能有极致要求且能接受参数状态在异常时可能不完整的场景下使用。-- 金仓下使用NOCOPY的示例 CREATE OR REPLACE PROCEDURE process_large_list( p_data_list IN OUT NOCOPY SYS.ODCIVARCHAR2LIST -- 使用NOCOPY提示 ) AS BEGIN FOR i IN 1..p_data_list.COUNT LOOP p_data_list(i) : UPPER(p_data_list(i)); END LOOP; END; /4.2 参数传递的异常处理与原子性这是一个高级但重要的话题。当存储过程抛出未处理的异常时对于IN参数无影响调用者的实参保持不变。对于OUT参数在过程异常退出前如果已经对OUT参数进行了赋值这些赋值是否会保留给调用者在Oracle和金仓的标准行为下如果异常被传播到调用者而未在过程内部捕获则所有对OUT和IN OUT参数的修改都会被回滚调用者看到的实参值保持不变或为NULL。但如果过程内部捕获了异常并处理后再抛出或进行其他操作则情况会复杂化。对于IN OUT参数同上如果异常未处理修改通常被回滚。但如果使用了NOCOPY则有可能出现部分修改已写入实参的情况取决于具体实现和异常发生点。实战建议在过程内部进行严谨的异常处理。如果需要对OUT/IN OUT参数赋值后可能发生的异常进行处理并在处理完后重新抛出或返回错误状态务必在异常处理块中也将这些参数设置为一个明确的错误状态值以保证调用者获得确定性的结果。4.3 从网络热词看常见混淆与错误分析提供的热词可以发现很多问题都与参数模式的误解或不当使用有关your access token could not be refreshed.../communications link failure...这类连接超时或令牌失效错误虽然不直接是参数模式问题但在编写重试或状态维护的存储过程时如果用来传递连接状态或令牌的参数模式设计不当例如该用IN OUT维护状态却用了IN和OUT分开导致状态丢失会加剧这类问题的处理复杂度。为何博途程序块fc,fb中的定时器要封装成inout参数这是一个来自工业自动化西门子PLC编程的问题但其思想相通。将定时器封装为IN OUT在TIA Portal中是为了在函数块多次调用间保持定时器的状态。这正体现了IN OUT的核心用途在多次调用间传递和维持一个可变的状态。在金仓/Oracle中如果你想写一个递归的过程或者一个需要记住上次调用状态的过程比如分页游标可能会考虑使用IN OUT参数来传递状态变量。Codex ran out of room.../CUDA out of memory这些是资源耗尽错误。误用IN OUT导致优化器失效可能产生巨大的中间结果集如未优化的笛卡尔积从而消耗大量临时表空间或PGA内存间接引发此类“Out of Memory”问题。一个本该使用IN列表进行高效过滤的查询因为参数模式设为IN OUT可能迫使数据库对全表数据进行处理。idea中build out输出乱码这提醒我们当使用OUT参数返回字符串时特别是可能包含多字节字符如中文时要确保数据库字符集、客户端NLS_LANG设置对于Oracle客户端、以及应用层编码的一致性否则返回的OUT值可能会出现乱码。4.4 性能问题排查实例复盘回到开头的案例我们是如何排查并最终定位到是IN OUT参数导致性能问题的现象迁移后的存储过程在测试环境小数据量下正常在生产环境大数据量下耗时从秒级飙升到分钟级。初步排查检查了SQL语句、索引、表统计信息均无异常。对比Oracle原库和金仓新库的执行计划发现金仓下关键查询的连接方式从“NESTED LOOPS”变成了“HASH JOIN”且多出了一个全表扫描。深入分析使用金仓的EXPLAIN PLAN工具或SET AUTOEXPLAIN ON查看存储过程内SQL的执行计划。发现一个使用MEMBER OF查询集合的语句其执行计划异常简单且低效。怀疑点该语句的过滤条件是一个IN OUT的集合参数。查阅金仓文档并回忆参数模式特性怀疑优化器无法“窥视”PeekIN OUT参数的内容因此无法在编译时优化MEMBER OF。验证将存储过程中该IN OUT参数改为IN模式因为过程内部确实没有修改该集合的需求。重新编译过程。结果再次执行耗时恢复至秒级。检查执行计划优化器成功将MEMBER OF转换为了高效的IN LIST查询并选择了正确的索引和连接方式。这个案例的教训是参数模式不仅关乎数据流更是给优化器的重要提示。IN模式说“这个值很稳定放心优化。”IN OUT模式说“这个值可能随时会变请保守一点。”5. 最佳实践与迁移建议基于以上分析总结出在金仓Oracle兼容模式下使用参数模式的最佳实践声明显式化永远不要依赖默认模式即不写模式默认为IN。即使它是IN也明确写上IN。这提高了代码的可读性和可维护性。-- 好 PROCEDURE my_proc(p_id IN NUMBER, p_result OUT VARCHAR2); -- 不好 PROCEDURE my_proc(p_id NUMBER, p_result VARCHAR2); -- p_result意图不明单一职责原则一个参数尽量只承担一种职责。输入就用IN输出就用OUT。避免使用IN OUT除非你有非常充分的理由如性能瓶颈或状态保持。迁移时仔细审核从Oracle向金仓迁移存储过程代码时要逐一检查每个参数的模型。确认每个IN OUT参数是否真的有必要。如果过程内部只是读取它请改为IN如果只是写入它请考虑改为OUT并在调用前准备好初始值如果需要的话通过另一个IN参数传入。注意金仓可能对某些复杂数据类型如自定义对象、高级集合类型的IN OUT传递实现细节与Oracle有细微差别需要进行功能测试。善用记录类型和集合当需要返回多个相关值时不要定义一堆单独的OUT参数而是定义一个RECORD类型或OBJECT类型用一个OUT参数返回。这样更清晰也减少了参数个数。CREATE OR REPLACE PACKAGE emp_pkg AS TYPE emp_info_rec IS RECORD ( name employees.emp_name%TYPE, salary employees.salary%TYPE, dept_name departments.dept_name%TYPE ); PROCEDURE get_emp_details(p_id IN NUMBER, p_info OUT emp_info_rec); END emp_pkg;性能测试对于性能关键的存储过程如果必须使用IN OUT处理大型数据或者怀疑参数模式影响了性能请务必进行A/B测试。分别用IN/OUT组合和IN OUT实现相同逻辑在真实数据量下对比执行时间和资源消耗。文档化如果由于历史原因或特定优化需求不得不使用IN OUT参数请在过程头部的注释中明确说明原因以及该参数在调用前后状态的变化避免其他开发者误用。参数模式是存储过程设计的基石之一。正确的选择能让代码清晰高效错误的选择则可能埋下维护的陷阱和性能的隐患。在金仓这样的国产数据库大步前进逐步替代国外产品的今天深入理解这些底层细节写出规范、高效、可移植的代码对于我们每一位开发者而言都是一项值得投入的核心技能。下次编写或审查存储过程时不妨多花一分钟思考一下这个参数到底该用IN、OUT还是IN OUT