KingbaseES PL/SQL参数模式详解：IN、OUT、IN OUT与NOCOPY性能优化

1. 项目概述：深入理解KingbaseES子程序的参数传递机制

在数据库开发领域，尤其是从Oracle生态迁移或进行深度定制的场景下，人大金仓数据库KingbaseES的PL/SQL兼容特性是一个绕不开的核心能力。很多开发者，包括我自己在早期接触时，常常会陷入一个误区：认为只要语法兼容，把存储过程、函数搬过来就能直接跑。但实际踩过几次坑后才发现，参数传递模式（Parameter Mode）的理解深度，直接决定了子程序的健壮性、性能以及代码的可维护性。这不仅仅是知道IN、OUT、IN OUT这三个关键字那么简单，它背后关联着数据流的设计思想、内存的利用效率以及程序与数据库交互的边界。

最近在社区和项目上，看到不少关于子程序调用的错误，比如“数值或值错误”，或者困惑于如何在子程序间高效传递复杂数据。这些问题的根源，往往是对参数模式的选择和使用场景不清晰。本文就想结合我这些年使用KingbaseES（尤其是V8、V9版本）的实际经验，抛开官方手册的条条框框，从一个实践者的角度，把子程序参数模式那点事儿掰开揉碎了讲清楚。我们会聊到每种模式在内存里是怎么运作的，什么时候该用谁，以及那些手册上不会写的、容易翻车的“坑点”。无论你是正在评估国产化替代，还是已经深陷KingbaseES的PL/SQL开发中，希望这些内容能帮你写出更优雅、更高效的数据库端业务逻辑。

2. 核心概念解析：三种参数模式的本质区别

在深入实操之前，我们必须从原理上厘清IN、OUT、IN OUT这三种参数模式的本质。这决定了你如何设计子程序的接口。

2.1 IN模式：只读的输入通道

IN参数是默认模式，也是最常用的一种。你可以把它理解为子程序的一个只读输入值。调用者把数据“传递”给子程序，子程序内部可以读取并使用这个值，但任何对其的修改都只发生在子程序内部的局部副本上，调用者完全感知不到。

关键特性与内部实现：当使用IN模式传递一个参数时，KingbaseES在调用子程序的那一刻，会将实际参数的值（或表达式的结果）拷贝一份，传递给子程序内部的形式参数。这意味着，无论子程序内部对这个形式参数做什么操作，原始的实际参数都安然无恙。对于基本数据类型（如NUMBER、VARCHAR2），这就是一次值拷贝。对于复杂对象（如记录、集合），行为会因具体类型和设置而异，但IN模式保护调用者数据的初衷不变。

典型应用场景：

1. 提供查询条件：例如，根据员工ID查询信息的函数，ID就是典型的IN参数。

   CREATE OR REPLACE FUNCTION get_employee_name(p_emp_id IN NUMBER) RETURN VARCHAR2 IS v_name VARCHAR2(100); BEGIN SELECT emp_name INTO v_name FROM employees WHERE emp_id = p_emp_id; RETURN v_name; END;

2. 传入配置或常量：比如一个计算税率的函数，税率值可以作为IN参数传入。
3. 提供运算的原始数据：如一个数据清洗函数，原始字符串作为IN参数传入，返回清洗后的结果。

注意：虽然IN参数在子程序内不能赋值给IN参数本身（如p_emp_id := 10;会编译报错），但你可以用它的值赋值给其他局部变量。这个“只读”约束是编译期检查的，很好的保证了代码意图的清晰。

2.2 OUT模式：单向的返回管道

OUT参数与IN正好相反，它用于从子程序内部向调用者返回值。在调用时，你传递给OUT参数的实际参数通常是一个未初始化的变量（其初始值会被忽略）。子程序执行过程中，会向这个形式参数写入数据，执行结束后，写入的值会“传递回”给调用者提供的实际参数。

关键特性与内部实现：OUT参数传递的是“引用”或者说“地址”。子程序内部操作的是调用者变量的存储位置。这意味着：

1. 在子程序内部，OUT参数在入口处的初始值是NULL（或该类型的默认值），与调用者传入的变量初始值无关。你必须先在子程序内为其赋值，然后才能读取它（否则可能读到NULL）。
2. 子程序内部对OUT参数的修改，直接作用于调用者的变量上。

典型应用场景：

1. 返回多个值：PL/SQL函数只能返回一个标量值或一个记录，但过程可以通过多个OUT参数返回多个独立的结果。

   CREATE OR REPLACE PROCEDURE get_employee_details( p_emp_id IN NUMBER, o_name OUT VARCHAR2, o_salary OUT NUMBER, o_dept OUT VARCHAR2 ) IS BEGIN SELECT emp_name, salary, dept_name INTO o_name, o_salary, o_dept FROM employees e JOIN departments d ON e.dept_id = d.dept_id WHERE e.emp_id = p_emp_id; END;

2. 返回游标引用：用于返回一个结果集供调用者进一步处理。
3. 返回操作状态或错误码：除了主业务数据，额外通过OUT参数返回执行状态标志。

实操心得：使用OUT参数时，务必在子程序内部逻辑的所有可能分支（包括异常处理块）中都考虑对其的赋值。一个未赋值的OUT参数返回给调用者，可能会导致调用者收到意外的NULL值，进而引发类似“ORA-06502: PL/SQL: numeric or value error”的错误（在KingbaseES中错误号可能不同，但语义类似）。这常发生在查询未找到数据时，没有在NO_DATA_FOUND异常处理中为OUT参数设置默认值。

2.3 IN OUT模式：双向的读写通道

IN OUT参数结合了前两者的特性，它既允许调用者向子程序输入一个初始值，也允许子程序修改该值并将修改后的结果返回给调用者。你可以把它想象成一个可读写的共享变量。

关键特性与内部实现：对于IN OUT参数，KingbaseES通常采用“传值-传址”结合或“传址”的方式。一种常见的实现是：调用时，将实际参数的值拷贝到子程序的形参（完成IN的部分）；子程序执行完毕返回时，再将形参的最终值拷贝回实际参数（完成OUT的部分）。对于大型数据结构，这可能会有效率开销。因此，它适用于需要基于输入进行原地修改并返回的场景。

典型应用场景：

1. 原地修改和返回：例如，一个将字符串中所有字母转换为大写的子程序，输入输出是同一个变量。

   CREATE OR REPLACE PROCEDURE capitalize_string(p_str IN OUT VARCHAR2) IS BEGIN p_str := UPPER(p_str); END;

2. 累加或累计操作：比如一个过程，接收一个计数器IN OUT参数，每次调用将其递增。
3. 复杂对象的逐步构建：传入一个部分初始化的复杂记录或集合，在子程序中补充内容后返回。

避坑指南：IN OUT参数虽然灵活，但滥用会降低代码的可读性和可预测性。调用者需要知道传入的变量值可能会被改变。如果只是需要基于输入计算一个新值，更推荐使用IN参数+函数返回值或OUT参数的模式，这样意图更清晰。另外，在嵌套调用或并发环境下，对IN OUT参数的修改需要格外小心，避免产生意外的副作用。

3. 参数模式的实战应用与代码示例

理解了原理，我们通过一系列具体的代码示例，来看看如何在实际开发中运用这些参数模式，并解析其中的关键点。

3.1 基础调用语法与示例

首先，我们创建一个包含三种参数模式的过程，并演示如何调用。

-- 创建一个演示过程 CREATE OR REPLACE PROCEDURE demo_param_modes ( p_in_param IN VARCHAR2, -- IN 参数 p_out_param OUT NUMBER, -- OUT 参数 p_inout_param IN OUT DATE -- IN OUT 参数 ) IS BEGIN DBMS_OUTPUT.PUT_LINE('过程内部 - 接收到 p_in_param: ' || p_in_param); DBMS_OUTPUT.PUT_LINE('过程内部 - p_inout_param 初始值: ' || TO_CHAR(p_inout_param, 'YYYY-MM-DD')); -- p_in_param := '试图修改IN参数'; -- 这行如果取消注释，将会编译错误 p_out_param := LENGTH(p_in_param); -- 给OUT参数赋值 p_inout_param := p_inout_param + 7; -- 修改IN OUT参数，日期加7天 DBMS_OUTPUT.PUT_LINE('过程内部 - 计算后 p_out_param: ' || p_out_param); DBMS_OUTPUT.PUT_LINE('过程内部 - 修改后 p_inout_param: ' || TO_CHAR(p_inout_param, 'YYYY-MM-DD')); END; / -- 匿名块中调用该过程 DECLARE v_input VARCHAR2(20) := 'Hello Kingbase'; v_output NUMBER; v_inout DATE := SYSDATE; BEGIN DBMS_OUTPUT.PUT_LINE('调用前 - v_inout: ' || TO_CHAR(v_inout, 'YYYY-MM-DD')); demo_param_modes(v_input, v_output, v_inout); DBMS_OUTPUT.PUT_LINE('调用后 - v_output: ' || v_output); DBMS_OUTPUT.PUT_LINE('调用后 - v_inout: ' || TO_CHAR(v_inout, 'YYYY-MM-DD')); -- v_input 保持不变 END; /

执行结果分析：调用前，v_inout是当天日期。过程内部打印了IN参数的值和IN OUT参数的初始值，然后为OUT参数赋值（字符串长度），并将IN OUT参数日期加了7天。调用后，可以看到v_output被成功赋值，v_inout的值也被修改了，而v_input保持不变。这个例子清晰地展示了数据流向。

3.2 在函数中使用OUT参数（KingbaseES/PostgreSQL风格）

在Oracle的PL/SQL中，函数通常只通过RETURN返回值。但KingbaseES兼容PostgreSQL语法，允许函数拥有OUT参数，这可以让你从一个函数中返回多个值，这是一种非常实用的特性。

-- 创建一个带有OUT参数的函数 CREATE OR REPLACE FUNCTION get_employee_stats( p_dept_id IN NUMBER, o_emp_count OUT INTEGER, o_avg_salary OUT NUMBER ) RETURN VARCHAR2 -- 主返回值 AS v_dept_name VARCHAR2(50); BEGIN -- 通过OUT参数返回统计信息 SELECT COUNT(*), AVG(salary) INTO o_emp_count, o_avg_salary FROM employees WHERE dept_id = p_dept_id; -- 获取部门名称作为函数返回值 SELECT dept_name INTO v_dept_name FROM departments WHERE dept_id = p_dept_id; -- 防止未找到数据时OUT参数为NULL IF o_emp_count IS NULL THEN o_emp_count := 0; o_avg_salary := 0; END IF; RETURN v_dept_name; EXCEPTION WHEN NO_DATA_FOUND THEN o_emp_count := 0; o_avg_salary := 0; RETURN '部门不存在'; END; / -- 调用带OUT参数的函数 DECLARE v_dept_name VARCHAR2(50); v_count INTEGER; v_avg_sal NUMBER; BEGIN -- 调用方式：将变量传入OUT参数位置 v_dept_name := get_employee_stats(10, v_count, v_avg_sal); DBMS_OUTPUT.PUT_LINE('部门: ' || v_dept_name); DBMS_OUTPUT.PUT_LINE('员工数: ' || v_count); DBMS_OUTPUT.PUT_LINE('平均薪资: ' || ROUND(v_avg_sal, 2)); END; /

注意事项：这种带OUT参数的函数，在SQL语句中直接调用的方式与普通函数不同，通常需要在PL/SQL块内调用。在设计API时，需要明确告知调用者如何使用。

3.3 参数默认值设置

KingbaseES允许为IN参数指定默认值，这极大地提高了子程序的灵活性。OUT和IN OUT参数不能有默认值。

CREATE OR REPLACE PROCEDURE search_products( p_keyword IN VARCHAR2 DEFAULT NULL, p_category IN VARCHAR2 DEFAULT '%', p_min_price IN NUMBER DEFAULT 0, p_max_price IN NUMBER DEFAULT 999999, p_result OUT SYS_REFCURSOR ) IS BEGIN OPEN p_result FOR SELECT product_id, product_name, price, category FROM products WHERE (p_keyword IS NULL OR product_name LIKE '%' || p_keyword || '%') AND category LIKE p_category AND price BETWEEN p_min_price AND p_max_price ORDER BY price; END; / -- 多种调用方式 DECLARE v_cursor SYS_REFCURSOR; v_rec products%ROWTYPE; BEGIN -- 1. 使用所有默认值（搜索所有产品） search_products(p_result => v_cursor); -- 2. 只提供关键字 search_products('手机', p_result => v_cursor); -- 3. 使用命名参数法，跳过有默认值的参数 search_products(p_category => '电子产品', p_max_price => 5000, p_result => v_cursor); END;

使用默认值的好处：

1. 向后兼容：当需要为子程序增加新的IN参数时，为其设置默认值可以确保所有现有的调用代码无需修改即可继续工作。
2. 简化调用：对于非必填的查询条件，使用默认值（如NULL或%）可以让调用代码更简洁。
3. 推荐使用命名参数调用：当参数较多或只希望覆盖部分默认值时，使用参数名 => 值的语法进行调用，可以使代码意图更清晰，避免因参数顺序错误导致的bug。

4. 高级主题与性能考量

当子程序逻辑变得复杂，或者需要处理大量数据时，参数模式的选择就不仅仅是语法问题，更关系到性能和维护性。

4.1 NOCOPY提示符与性能优化

这是一个非常重要但容易被忽略的高级特性。默认情况下，IN OUT和OUT参数在传递复杂数据类型（如大型集合、记录、对象类型）时，可能涉及两次拷贝：传入时一次，传出时一次。对于数据量大的情况，这会成为性能瓶颈。

NOCOPY是一个编译器提示（Hint），它建议数据库以“传引用”的方式传递OUT和IN OUT参数，从而避免不必要的拷贝，提升性能。

CREATE OR REPLACE PACKAGE big_data_pkg IS TYPE big_array IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER; PROCEDURE process_array_slow (p_data IN OUT big_array); -- 默认，可能拷贝 PROCEDURE process_array_fast (p_data IN OUT NOCOPY big_array); -- 建议传引用 END big_data_pkg; / CREATE OR REPLACE PACKAGE BODY big_data_pkg IS PROCEDURE process_array_slow (p_data IN OUT big_array) IS BEGIN FOR i IN 1..p_data.COUNT LOOP p_data(i) := 'Processed: ' || p_data(i); END LOOP; END; PROCEDURE process_array_fast (p_data IN OUT NOCOPY big_array) IS BEGIN FOR i IN 1..p_data.COUNT LOOP p_data(i) := 'Processed: ' || p_data(i); END LOOP; END; END big_data_pkg; /

为什么是“提示”而非“指令”？数据库优化器在某些情况下可能会忽略NOCOPY，例如：

• 参数是关联数组的索引（如INDEX BY VARCHAR2）。
• 子程序涉及远程过程调用（RPC）。
• 子程序被用于并行查询。
• 参数有别名（Aliasing）问题，即同一个实际参数通过不同途径被子程序访问，传引用可能导致不可预知的结果。

使用NOCOPY的注意事项：

1. 异常行为变化：使用NOCOPY时，如果子程序在执行过程中发生异常，即使异常被捕获并处理，OUT/IN OUT参数可能已经被部分修改。而不使用NOCOPY时，除非过程正常完成，否则参数值不会回传给调用者。这是决定是否使用NOCOPY时需要权衡的关键点。
2. 适用场景：对于大型的PL/SQL表、嵌套表、变长数组（VARRAY）或用户定义的对象类型，在确定没有上述限制且能接受异常行为变化时，使用NOCOPY能带来显著的性能提升。

4.2 参数别名与副作用风险

别名（Aliasing）是指同一个存储位置可以通过多个名称被访问。在使用NOCOPY或处理IN OUT参数时，如果不小心，就可能引发别名问题，导致难以调试的逻辑错误。

CREATE OR REPLACE PROCEDURE risky_swap ( a IN OUT NUMBER, b IN OUT NUMBER ) IS temp NUMBER; BEGIN temp := a; a := b; b := temp; END; / DECLARE x NUMBER := 10; BEGIN risky_swap(x, x); -- 传递同一个变量给两个IN OUT参数！ DBMS_OUTPUT.PUT_LINE('x = ' || x); -- 输出什么？结果是 10，交换无效且混乱。 END; /

在上面的例子中，a和b实际上是同一个变量x的别名。在过程内部，a := b和b := a的操作变得毫无意义，且结果不可预期。虽然这是一个刻意构造的简单例子，但在复杂的代码逻辑中，别名可能通过全局变量、包变量或游标参数等更隐蔽的方式引入。

规避别名问题的建议：

1. 尽量避免让子程序的多个IN OUT/OUT参数指向调用者同一块内存。
2. 谨慎使用包变量（Package Variables）作为实际参数传递给IN OUT/OUT形参，同时又在子程序内部直接引用该包变量。
3. 在代码审查时，关注参数传递的源头，特别是当参数值来源于复杂表达式或函数调用时。

4.3 参数传递方式对异常处理的影响

如前所述，参数传递方式会影响异常发生时的状态。我们通过一个例子来对比：

CREATE OR REPLACE PROCEDURE proc_with_exception ( p_inout_normal IN OUT VARCHAR2, p_inout_nocopy IN OUT NOCOPY VARCHAR2 ) IS BEGIN p_inout_normal := 'Modified Normal'; p_inout_nocopy := 'Modified NOCOPY'; RAISE_APPLICATION_ERROR(-20001, '模拟一个异常！'); END; / DECLARE v_var1 VARCHAR2(100) := 'Original'; v_var2 VARCHAR2(100) := 'Original'; BEGIN BEGIN proc_with_exception(v_var1, v_var2); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('捕获到异常: ' || SQLERRM); END; DBMS_OUTPUT.PUT_LINE('v_var1 (normal): ' || v_var1); DBMS_OUTPUT.PUT_LINE('v_var2 (nocopy): ' || v_var2); END; /

可能的输出：

捕获到异常: ORA-20001: 模拟一个异常！ v_var1 (normal): Original v_var2 (nocopy): Modified NOCOPY

可以看到，使用NOCOPY的参数v_var2在异常发生后，其值已经被修改。而普通方式的v_var1则保持了原值。在设计关键业务逻辑的子程序时，必须仔细考虑这种差异，确保程序的原子性和一致性符合业务要求。

5. 常见问题排查与调试技巧

在实际开发中，关于参数模式的错误和困惑层出不穷。这里我总结了几类最常见的问题和排查思路。

5.1 编译错误与语义错误

5.2 运行时逻辑错误排查

这类错误不会导致编译失败，但会产生错误的业务结果。

• 问题：IN OUT参数值被意外修改

  • 排查：检查子程序内部逻辑，是否在不应修改的地方对IN OUT参数进行了赋值。确认调用者的本意是希望该值被修改。如果不需要回传修改，应改为IN参数。

• 问题：使用NOCOPY后，程序在异常时状态不一致

  • 排查：这是NOCOPY的已知特性。如果业务要求异常时必须回滚所有修改，则应避免对关键参数使用NOCOPY，或者将子程序设计为在异常处理块内将参数重置为传入时的状态（但这需要额外保存初始值）。

• 问题：传递复杂类型（如集合）性能极差

  • 排查：考虑是否为OUT或IN OUT参数添加NOCOPY提示符。同时，评估是否真的需要传递整个大型集合，能否通过游标分批处理或改用临时表共享数据。

5.3 调试与验证技巧

1. 使用DBMS_OUTPUT进行跟踪：在子程序的关键节点，打印参数的值，这是最直接的调试方法。确保在调用块开头执行SET SERVEROUTPUT ON（或在客户端工具中开启输出）。
2. 单元测试：为重要的子程序编写单元测试脚本，覆盖各种参数组合（正常值、边界值、NULL值）。特别要测试OUT参数在所有分支下的赋值情况。
3. 静态代码分析：利用KingbaseES的PL/Scope（如果支持）或第三方工具，分析代码中参数的读写依赖，发现潜在的别名问题或未初始化的OUT参数。
4. 查看执行计划：对于复杂的、包含SQL的子程序，如果性能不佳，需要检查SQL语句的执行计划，参数模式的选择也可能影响SQL的绑定变量和优化。

6. 设计最佳实践与模式选择指南

根据多年的项目经验，我总结了一些关于参数模式选择的实用原则，这能帮助你在设计子程序接口时做出更优决策。

6.1 模式选择决策树

面对一个参数，可以遵循以下流程决定其模式：

1. 这个参数的值是否仅由调用者提供，子程序只读不写？
   • 是-> 选择IN模式。这是最安全、最清晰的选择。考虑是否设置默认值以增加灵活性。
2. 这个参数的值是否仅由子程序产生并返回给调用者，调用时无需提供有效值？
   • 是-> 选择OUT模式。确保在子程序所有退出路径上都为其赋值。
3. 调用者是否需要提供一个初始值，子程序基于该值计算并返回一个新值，且调用者需要这个新值？
   • 是-> 选择IN OUT模式。慎重考虑是否使用NOCOPY来优化大型数据的性能，并评估异常安全性的影响。
   • 进一步思考：是否可以用“IN参数 + 函数返回值”或“IN参数 + 单独的OUT参数”来替代？这通常能使接口意图更单一、更清晰。

6.2 函数与过程的选择

• 使用函数（FUNCTION）：当操作的核心是计算并返回一个单一的值，且没有显著的副作用（如修改大量表数据、发送邮件等）时。函数可以在SQL语句中直接调用，使用场景更丰富。KingbaseES允许函数有OUT参数，但这会限制其在SQL中的使用，需权衡。
• 使用过程（PROCEDURE）：当操作的主要目的是执行一个动作，可能涉及多个步骤、修改数据、返回多个值（通过多个OUT参数）或返回游标时。过程更侧重于“做事情”。

6.3 关于默认值与重载

• 明智地使用默认值：为IN参数设置合理的默认值，特别是对于非核心的、可选的控制参数。这能显著提升子程序的易用性和向后兼容性。
• 慎用重载（Overloading）：KingbaseES支持包内的子程序重载。你可以创建多个同名但参数列表不同的过程或函数。这用于提供处理不同类型数据的统一接口。但过度使用重载会让代码变得复杂难懂。确保重载的子程序功能在语义上是相似的。

6.4 文档与命名约定

清晰的沟通和约定能减少很多错误。

• 命名约定：许多团队会采用命名前缀来暗示参数模式，例如：
  • p_或i_表示IN参数 (Parameter / Input)
  • o_表示OUT参数 (Output)
  • io_表示IN OUT参数 (Input/Output) 这能让代码读者快速理解参数的用途。
• 注释文档：在子程序头部，使用注释清晰说明每个参数的用途、模式、取值范围、默认值以及是否为NULL。如果使用了NOCOPY，最好也注明原因。

子程序的参数模式是PL/SQL编程的基石之一。从简单的IN/OUT区分，到NOCOPY带来的性能与异常安全的权衡，再到别名问题带来的隐蔽bug，每一个细节都考验着开发者对数据流和程序状态的理解。在KingbaseES数据库开发中，遵循上述原则和实践，不仅能让你写出正确运行的代码，更能写出高效、健壮、易于维护的代码。最后记住一个黄金法则：让子程序的接口尽可能简单、明确，参数模式的选择是达到这一目标的重要手段。当你在设计下一个存储过程或函数时，不妨多花一分钟思考一下参数的模式，这可能会省下未来数小时的调试时间。