如何在存储过程单个OUT参数中返回多个REF CURSOR

单个SYS_REFCURSOR无法同时指向多个独立结果集，以下是几种适配你需求的实现方案：

方案1：固定数量查询——多OUT游标参数 #

如果查询数量固定，直接为每个查询定义独立的SYS_REFCURSOR输出参数：

CREATE OR REPLACE PROCEDURE multiple_cursor_out_proc (
    p_in        VARCHAR2,
    p_cur1      OUT SYS_REFCURSOR,
    p_cur2      OUT SYS_REFCURSOR,
    -- 按需添加对应数量的游标参数
    p_curn      OUT SYS_REFCURSOR
)
AS
    v_sql_1   CLOB := 'select * from table_1';
    v_sql_2   CLOB := 'select * from table_2';
    -- ...
    v_sql_n   CLOB := 'select * from table_n';
BEGIN
    OPEN p_cur1 FOR v_sql_1;
    OPEN p_cur2 FOR v_sql_2;
    -- ...
    OPEN p_curn FOR v_sql_n;
END;
/

JDBC调用时，依次注册每个OUT参数为OracleTypes.CURSOR类型，分别获取对应结果集即可。

方案2：任意数量查询——动态执行+JDBC多结果集遍历 #

如果查询数量不固定，无需定义OUT参数，直接在存储过程中通过DBMS_SQL执行所有动态SQL，JDBC原生支持遍历多个结果集：

CREATE OR REPLACE PROCEDURE multiple_cursor_out_proc (
    p_in        VARCHAR2
)
AS
    v_sql_list  DBMS_SQL.VARCHAR2A;
    v_cursor    INTEGER;
    v_rows      INTEGER;
BEGIN
    -- 根据输入参数动态构造SQL列表（示例为固定添加，实际可按需生成）
    v_sql_list(1) := 'select * from table_1';
    v_sql_list(2) := 'select * from table_2';
    -- ... 可添加任意数量SQL语句

    FOR i IN v_sql_list.FIRST .. v_sql_list.LAST LOOP
        v_cursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_cursor, v_sql_list(i), DBMS_SQL.NATIVE);
        v_rows := DBMS_SQL.EXECUTE(v_cursor);
        DBMS_SQL.CLOSE_CURSOR(v_cursor);
    END LOOP;
END;
/

对应的Java JDBC处理代码：

CallableStatement cs = conn.prepareCall("{call multiple_cursor_out_proc(?)}");
cs.setString(1, "你的输入值");
boolean hasResults = cs.execute();
int resultSetIndex = 0;

while (hasResults) {
    ResultSet rs = cs.getResultSet();
    resultSetIndex++;
    // 处理第resultSetIndex个独立结果集
    while (rs.next()) {
        // 读取数据逻辑
    }
    rs.close();
    hasResults = cs.getMoreResults();
}
cs.close();

方案3：兼容低版本——带标识列的管道函数（可选） #

如果你的Oracle版本不支持游标集合，且允许给结果集添加标识字段，可通过管道函数将所有结果集合并为一个带区分标识的集合，JDBC读取后再拆分：

-- 定义结果行类型（需覆盖所有查询的字段，或用SYS.ANYDATA做通用类型）
CREATE OR REPLACE TYPE unified_result_row AS OBJECT (
    result_set_id   NUMBER,
    col1            VARCHAR2(100),
    col2            NUMBER,
    col3            DATE
);
/

CREATE OR REPLACE TYPE unified_result_table AS TABLE OF unified_result_row;
/

CREATE OR REPLACE FUNCTION multiple_result_func(p_in VARCHAR2) RETURN unified_result_table PIPELINED
AS
    v_result_id NUMBER := 1;
    v_cur       SYS_REFCURSOR;
    v_col1      VARCHAR2(100);
    v_col2      NUMBER;
    v_col3      DATE;
BEGIN
    -- 处理第一个查询
    OPEN v_cur FOR 'select col1, col2, col3 from table_1';
    LOOP
        FETCH v_cur INTO v_col1, v_col2, v_col3;
        EXIT WHEN v_cur%NOTFOUND;
        PIPE ROW(unified_result_row(v_result_id, v_col1, v_col2, v_col3));
    END LOOP;
    CLOSE v_cur;
    v_result_id := v_result_id + 1;

    -- 处理第二个查询
    OPEN v_cur FOR 'select col1, col2, col3 from table_2';
    LOOP
        FETCH v_cur INTO v_col1, v_col2, v_col3;
        EXIT WHEN v_cur%NOTFOUND;
        PIPE ROW(unified_result_row(v_result_id, v_col1, v_col2, v_col3));
    END LOOP;
    CLOSE v_cur;
    -- ... 处理更多查询

    RETURN;
END;
/

JDBC读取后，通过result_set_id字段区分不同的原始结果集。

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