Oracle函数参数如何传递数组？

在 Oracle 中，并没有像 Java、C# 或 PL/SQL 表类型那样原生意义上的“数组”数据类型，我们可以通过多种方式来模拟和实现数组的功能,并将其作为参数传递给函数或存储过程。

以下是三种最常用和最实用的方法,按推荐顺序排列：

使用 VARRAY (可变数组)

VARRAY 是一个存储固定数量元素的有序集合，它是一个对象类型,在使用前需要先定义。

定义 VARRAY 类型

你需要在包（PACKAGE）中定义一个 VARRAY 类型，通常在包的规范（PACKAGE SPEC）中定义,这样整个数据库会话都可以使用它。

-- 创建一个包，用于定义我们需要的数组类型
CREATE OR REPLACE PACKAGE pkg_array_types AS
    -- 定义一个最多包含1000个VARCHAR2(4000)元素的VARRAY类型
    TYPE t_string_array IS VARRAY(1000) OF VARCHAR2(4000);
    -- 你也可以定义其他类型的数组，比如数字
    -- TYPE t_number_array IS VARRAY(1000) OF NUMBER;
END pkg_array_types;
/

在函数中使用 VARRAY 参数

你可以创建一个函数，并声明一个 VARRAY 类型的参数。

CREATE OR REPLACE FUNCTION fn_process_string_array (
    p_string_array IN pkg_array_types.t_string_array
) RETURN VARCHAR2
IS
    v_result VARCHAR2(4000) := 'Processed items: ';
    v_item   VARCHAR2(4000);
BEGIN
    -- 使用 FIRST 和 LAST 遍历数组
    FOR i IN p_string_array.FIRST .. p_string_array.LOOP
        v_item := p_string_array(i);
        v_result := v_result || v_item || ', ';
    END LOOP;
    -- 移除最后的逗号和空格
    IF v_result LIKE '%, ' THEN
        v_result := SUBSTR(v_result, 1, LENGTH(v_result) - 2);
    END IF;
    RETURN v_result;
END fn_process_string_array;
/

如何调用这个函数

调用时，你需要先构造一个 VARRAY 类型的实例。

-- 调用函数
SELECT fn_process_string_array(
    pkg_array_types.t_string_array('Apple', 'Banana', 'Cherry', 'Date')
) AS result
FROM DUAL;

输出结果:

Processed items: Apple, Banana, Cherry, Date

优点:

• 类型安全: 在 PL/SQL 代码中，你可以直接操作它，就像操作原生数组一样（arr(i)）。
• 结构清晰: 是面向对象的方式,易于理解和管理。

缺点:

• 调用稍显复杂: 需要先构造类型实例。
• 元素数量有限: 定义时需要指定最大数量（VARRAY(1000)）。

使用 ASSOCIATIVE ARRAY (关联数组 / PL/SQL 表)

关联数组是键值对的集合，键可以是整数或字符串，它更像是一个字典或哈希表，对于模拟数组（使用整数键），它非常灵活,因为大小是动态的。

定义关联数组类型

同样,在包中定义。

CREATE OR REPLACE PACKAGE pkg_array_types AS
    -- 定义一个键为整数，值为VARCHAR2的关联数组
    -- PLS_INTEGER 是一个高效的整数类型
    TYPE t_string_table IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
    -- 也可以定义键为字符串的关联数组
    -- TYPE t_string_map IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(100);
END pkg_array_types;
/

在函数中使用关联数组参数

CREATE OR REPLACE FUNCTION fn_process_string_table (
    p_string_table IN pkg_array_types.t_string_table
) RETURN VARCHAR2
IS
    v_result VARCHAR2(4000) := 'Processed items: ';
BEGIN
    -- 使用 FIRST 和 NEXT 遍历关联数组
    -- 注意：关联数组没有 LAST 属性，需要用循环
    DECLARE
        v_idx PLS_INTEGER;
    BEGIN
        v_idx := p_string_table.FIRST;
        WHILE v_idx IS NOT NULL LOOP
            v_result := v_result || p_string_table(v_idx) || ', ';
            v_idx := p_string_table.NEXT(v_idx);
        END LOOP;
    END;
    -- 移除最后的逗号和空格
    IF v_result LIKE '%, ' THEN
        v_result := SUBSTR(v_result, 1, LENGTH(v_result) - 2);
    END IF;
    RETURN v_result;
END fn_process_string_table;
/

如何调用这个函数

调用时,直接构造一个关联数组实例。

-- 调用函数
SELECT fn_process_string_table(
    pkg_array_types.t_string_table(
        1 => 'One',
        2 => 'Two',
        3 => 'Three'
    )
) AS result
FROM DUAL;

输出结果:

Processed items: One, Two, Three

优点:

• 大小灵活: 不需要预先定义大小,可以动态增长。
• 性能好: 在 PL/SQL 内部处理速度非常快。

缺点:

• SQL 兼容性差: 这是最大的缺点，你不能直接从 SQL 语句（如 SELECT）中构造一个关联数组并传递给函数，关联数组主要用于 PL/SQL 内部或作为存储过程的 IN OUT 参数，如果你需要从 SQL 中接收一个数组列表,这个方法不适用。

使用 SQL 类型 (对象类型 + 表类型)

这是从 SQL 调用存储过程/函数并传递“数组”数据最推荐的方法，它允许你通过 SQL 语句（如 SELECT 语句）将数据集合传递给 PL/SQL。

定义 SQL 对象类型和表类型

这些类型是作为 SQL 对象创建的，而不仅仅是 PL/SQL 对象。

-- 1. 定义一个对象类型，它代表数组中的一个元素
CREATE OR REPLACE TYPE t_string_element AS OBJECT (
    value VARCHAR2(4000)
);
/
-- 2. 定义一个表类型，这个表将存储上面的对象元素
--    这个表类型就是我们在SQL中使用的“数组”
CREATE OR REPLACE TYPE t_string_table AS TABLE OF t_string_element;
/

在函数中使用 SQL 表类型参数

CREATE OR REPLACE FUNCTION fn_process_sql_table (
    p_string_table IN t_string_table
) RETURN VARCHAR2
IS
    v_result VARCHAR2(4000) := 'Processed items: ';
BEGIN
    -- 使用 TABLE() 函数和表表达式来遍历
    -- 这是处理 SQL 表类型最标准的方式
    FOR rec IN (
        SELECT VALUE(t) AS item_value
        FROM TABLE(p_string_table) t
    ) LOOP
        v_result := v_result || rec.item_value || ', ';
    END LOOP;
    -- 移除最后的逗号和空格
    IF v_result LIKE '%, ' THEN
        v_result := SUBSTR(v_result, 1, LENGTH(v_result) - 2);
    END IF;
    RETURN v_result;
END fn_process_sql_table;
/

如何调用这个函数 (从 SQL)

这是此方法的核心优势，你可以使用 SELECT 语句来构建输入数据。

-- 调用函数，使用集合构造函数
SELECT fn_process_sql_table(
    t_string_table(
        t_string_element('SQL Item 1'),
        t_string_element('SQL Item 2'),
        t_string_element('SQL Item 3')
    )
) AS result
FROM DUAL;

输出结果:

Processed items: SQL Item 1, SQL Item 2, SQL Item 3

优点:

• 完美的 SQL 集成: 可以轻松地从任何 SQL 接口（如 JDBC, ODP.NET, SQL*Plus, SQL Developer）传递数组数据。
• 灵活性高: 非常适合处理来自应用程序的列表数据。
• 类型安全: 是标准的 SQL 类型。

缺点:

• 语法稍显冗长: 需要定义对象类型和表类型,构造函数的语法也更复杂一些。

总结与对比

最终建议

• 如果你的函数/存储过程只被 PL/SQL 代码调用：

  • 如果元素数量固定，使用 VARRAY。
  • 如果元素数量不固定，使用 ASSOCIATIVE ARRAY。

• 如果你的函数/存储过程需要被外部应用程序（如 Java, .NET, Python）通过 SQL 调用，并且需要传递一个列表或数组：

  • 必须使用 SQL Table Type (方法三),这是唯一健壮且标准化的方式。

对于大多数现代应用开发场景，特别是需要与数据库进行交互的应用程序，方法三 (SQL Table Type) 是处理数组参数的首选和最佳实践。