如何在PL/SQL存储过程中不使用OUT参数返回多条记录？

嘿，这个问题我太有共鸣了！之前做高并发系统的时候，用REF CURSOR当OUT参数确实遇到过性能瓶颈，尤其是频繁调用的时候，会话上下文切换和额外的网络传输开销挺头疼的。下面给你几个我实际项目里验证过的解决方案，都能避开OUT参数返回多条记录：

1. 管道化表函数（Pipelined Table Functions） #

这是我最常用的方案，尤其是处理大量数据的时候。它的核心是逐行返回数据，不用把所有结果都加载到内存里，能有效降低内存占用和性能损耗，而且可以直接在SQL语句中调用，非常灵活。

实现步骤： #

首先定义自定义的记录类型和对应的表类型，然后编写管道化函数：

-- 定义单个记录的类型
CREATE OR REPLACE TYPE emp_record_type AS OBJECT (
    emp_id NUMBER,
    emp_name VARCHAR2(100),
    salary NUMBER
);
/

-- 定义存储多条记录的表类型
CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_record_type;
/

-- 创建管道化表函数
CREATE OR REPLACE FUNCTION get_employee_details(p_dept_id NUMBER) 
RETURN emp_table_type PIPELINED
IS
    -- 定义游标查询目标数据
    CURSOR emp_cursor IS
        SELECT employee_id, first_name || ' ' || last_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
    v_emp emp_record_type;
BEGIN
    -- 遍历游标，逐行返回数据
    FOR emp_rec IN emp_cursor LOOP
        v_emp := emp_record_type(emp_rec.employee_id, emp_rec.first_name || ' ' || last_name, emp_rec.salary);
        PIPE ROW(v_emp); -- 将当前行推送到结果集中
    END LOOP;
    RETURN;
END;
/

使用方式： #

直接用TABLE()函数将结果转换为可查询的数据集：

SELECT * FROM TABLE(get_employee_details(30));

这种方式的优势在于，数据是流式返回的，不会一次性占用大量内存，而且SQL层面可以直接过滤、排序，和普通表查询的体验一致。

2. 返回集合类型（嵌套表/VARRAY） #

如果你的数据量不大，这种方案实现起来更简单。它会把所有查询结果打包成一个集合对象返回，适合小批量数据场景。

实现步骤： #

同样先定义类型（可以复用上面的emp_record_type和emp_table_type），然后编写普通函数：

CREATE OR REPLACE FUNCTION get_employees(p_dept_id NUMBER) 
RETURN emp_table_type
IS
    v_emp_table emp_table_type := emp_table_type(); -- 初始化集合
    CURSOR emp_cursor IS
        SELECT employee_id, first_name || ' ' || last_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        v_emp_table.EXTEND; -- 扩展集合容量
        -- 将当前记录添加到集合中
        v_emp_table(v_emp_table.LAST) := emp_record_type(emp_rec.employee_id, emp_rec.first_name || ' ' || last_name, emp_rec.salary);
    END LOOP;
    RETURN v_emp_table;
END;
/

使用方式： #

和管道化函数一样，用TABLE()函数查询：

SELECT * FROM TABLE(get_employees(30));

注意：如果数据量很大，这种方案会把所有数据加载到内存中，可能导致内存溢出，所以只适合小数据量场景。

3. 隐式结果集（Oracle 12c及以上版本） #

如果你用的是Oracle 12c或更高版本，这个方案最省心！它允许存储过程直接返回结果集，不需要定义任何额外类型，也不用OUT参数，底层处理比REF CURSOR更高效。

实现方式： #

CREATE OR REPLACE PROCEDURE get_employee_details(p_dept_id NUMBER)
IS
    v_cursor SYS_REFCURSOR;
BEGIN
    OPEN v_cursor FOR
        SELECT employee_id, first_name || ' ' || last_name AS emp_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
    -- 直接返回结果集
    DBMS_SQL.RETURN_RESULT(v_cursor);
    
    -- 更简化的写法（Oracle 12.1+支持）：直接执行SELECT语句即可返回结果
    -- SELECT employee_id, first_name || ' ' || last_name AS emp_name, salary
    -- FROM employees
    -- WHERE department_id = p_dept_id;
END;
/

使用方式： #

直接调用存储过程，客户端会自动收到结果集：

EXEC get_employee_details(30);

这种方式省去了类型定义的麻烦，而且Oracle对隐式结果集的优化更好，性能比传统的REF CURSOR OUT参数要高。

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