Oracle PL/SQL：编写返回关联数组的函数及调用方法（含过程调用）

关联数组（也叫索引表）是PL/SQL中常用的集合类型，下面一步步说明如何声明这类类型、编写返回关联数组的函数，以及在不同场景下调用它。

1. 声明共享的关联数组类型 #

要让函数和调用它的程序单元（比如存储过程）都能识别关联数组类型，最方便的方式是在包规范中声明该类型，这样类型可以跨程序单元共享：

CREATE OR REPLACE PACKAGE emp_collection_types IS
    -- 定义关联数组：键为员工姓名（VARCHAR2），值为薪资（NUMBER）
    TYPE emp_salary_map IS TABLE OF NUMBER
        INDEX BY VARCHAR2(50);
END emp_collection_types;
/

如果只是在单个匿名块中使用，也可以直接在块的DECLARE段声明类型，但这种类型只能在当前块内部访问。

2. 编写返回关联数组的函数 #

接下来在包体中实现返回该关联数组的函数，函数内部可以手动赋值，也可以从数据库表中批量加载数据：

CREATE OR REPLACE PACKAGE BODY emp_collection_types IS
    FUNCTION fetch_employee_salaries RETURN emp_salary_map IS
        v_salary_map emp_salary_map;
    BEGIN
        -- 手动添加测试数据
        v_salary_map('张三') := 8200;
        v_salary_map('李四') := 9600;
        
        -- 从EMP表中批量加载部门10的员工薪资
        FOR emp_rec IN (SELECT ename, sal FROM emp WHERE deptno = 10) LOOP
            v_salary_map(emp_rec.ename) := emp_rec.sal;
        END LOOP;
        
        RETURN v_salary_map;
    END fetch_employee_salaries;
END emp_collection_types;
/

3. 调用函数的场景 #

3.1 在匿名PL/SQL块中调用 #

可以直接调用函数获取关联数组，然后通过键遍历或直接访问指定元素：

DECLARE
    v_result emp_collection_types.emp_salary_map;
    v_current_name VARCHAR2(50);
BEGIN
    -- 调用函数获取关联数组
    v_result := emp_collection_types.fetch_employee_salaries;
    
    -- 遍历关联数组（通过FIRST/NEXT遍历所有键）
    v_current_name := v_result.FIRST;
    WHILE v_current_name IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('员工: ' || v_current_name || ' | 薪资: ' || v_result(v_current_name));
        v_current_name := v_result.NEXT(v_current_name);
    END LOOP;
    
    -- 直接访问指定键的元素（先检查键是否存在避免异常）
    IF v_result.EXISTS('张三') THEN
        DBMS_OUTPUT.PUT_LINE('张三的薪资: ' || v_result('张三'));
    END IF;
END;
/

3.2 在存储过程中调用函数 #

在存储过程中调用的逻辑和匿名块类似，只需在存储过程的声明段定义关联数组变量，再调用函数赋值即可：

CREATE OR REPLACE PROCEDURE process_salary_data IS
    v_emp_salaries emp_collection_types.emp_salary_map;
    v_emp_name VARCHAR2(50);
BEGIN
    -- 调用函数获取关联数组
    v_emp_salaries := emp_collection_types.fetch_employee_salaries;
    
    -- 业务处理：比如打印薪资或执行更新操作
    v_emp_name := v_emp_salaries.FIRST;
    WHILE v_emp_name IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('处理员工: ' || v_emp_name || ', 当前薪资: ' || v_emp_salaries(v_emp_name));
        -- 这里可添加自定义业务逻辑，比如调整薪资、记录日志等
        v_emp_name := v_emp_salaries.NEXT(v_emp_name);
    END LOOP;
END process_salary_data;
/

-- 执行存储过程
BEGIN
    process_salary_data;
END;
/

注意事项 #

• 关联数组是PL/SQL专属类型，不能在SQL语句中直接使用（比如不能用SELECT * FROM TABLE(函数名)），如果需要在SQL层面操作集合，建议使用嵌套表或VARRAY类型。
• 遍历关联数组时，如果键是不连续的整数或字符串，必须使用FIRST/NEXT方法；如果键是连续整数，也可以用FOR i IN v_result.FIRST .. v_result.LAST循环。
• 访问关联数组元素前，最好用EXISTS(key)方法检查键是否存在，避免触发NO_DATA_FOUND异常。

内容的提问来源于stack exchange，提问作者Chandu