如何在多查询与业务逻辑场景下从PL/SQL存储过程返回多行？

当然可以处理这种场景！而且有两种主流方案，我给你拆解清楚，结合你的Java调用需求来推荐：

这是最贴合PL/SQL特性的方案，不需要依赖物理表，直接在内存中处理数据，性能更优，适合数据量中等的场景。

步骤很清晰：

• 先定义自定义记录类型（匹配你最终要返回的字段结构），再基于这个记录类型定义集合类型
• 在存储过程中声明集合变量，通过循环查询、业务逻辑计算等方式，把数据逐条添加到集合里
• 最后把集合转换成SYS_REFCURSOR类型的OUT参数返回，Java端可以直接识别这个游标类型

给你一个完整的代码示例：

CREATE OR REPLACE PROCEDURE get_multi_source_data (
    p_out_cursor OUT SYS_REFCURSOR
) AS
    -- 定义记录类型，对应最终返回的字段
    TYPE result_rec IS RECORD (
        id          NUMBER,
        name        VARCHAR2(100),
        status      VARCHAR2(20),
        source_flag VARCHAR2(10) -- 标记数据来源，方便业务排查
    );
    -- 定义集合类型，用来存储多条记录
    TYPE result_list IS TABLE OF result_rec;
    v_results result_list := result_list(); -- 初始化集合
BEGIN
    -- 第一部分：从table_a获取数据，加上业务逻辑判断
    FOR rec IN (SELECT id, name FROM table_a WHERE active = 'Y') LOOP
        v_results.EXTEND; -- 扩展集合容量
        v_results(v_results.LAST).id := rec.id;
        v_results(v_results.LAST).name := rec.name;
        -- 业务逻辑：根据id判断状态
        v_results(v_results.LAST).status := CASE WHEN rec.id > 100 THEN 'VIP' ELSE 'NORMAL' END;
        v_results(v_results.LAST).source_flag := 'TABLE_A';
    END LOOP;

    -- 第二部分：从table_b获取数据，做不同的业务处理
    FOR rec IN (SELECT emp_id, emp_name FROM table_b WHERE dept_id = 20) LOOP
        v_results.EXTEND;
        v_results(v_results.LAST).id := rec.emp_id;
        v_results(v_results.LAST).name := rec.emp_name;
        v_results(v_results.LAST).status := 'DEPT_20';
        v_results(v_results.LAST).source_flag := 'TABLE_B';
    END LOOP;

    -- 第三部分：手动添加业务逻辑生成的单独数据
    v_results.EXTEND;
    v_results(v_results.LAST).id := 999;
    v_results(v_results.LAST).name := 'Manual Entry';
    v_results(v_results.LAST).status := 'SPECIAL';
    v_results(v_results.LAST).source_flag := 'MANUAL';

    -- 将集合转换为游标，返回给Java
    OPEN p_out_cursor FOR
        SELECT * FROM TABLE(v_results);
EXCEPTION
    WHEN OTHERS THEN
        -- 异常处理：确保游标关闭，避免资源泄漏
        IF p_out_cursor%ISOPEN THEN
            CLOSE p_out_cursor;
        END IF;
        RAISE; -- 抛出异常让Java端捕获
END;
/

如果你的数据量很大，或者需要对中间数据做复杂的SQL操作（比如分组、聚合），可以用全局临时表。它是会话级别的临时存储，每个会话的数据互相独立，不会干扰。

步骤：

1. 先创建全局临时表（只需要创建一次）：

CREATE GLOBAL TEMPORARY TABLE temp_result (
    id          NUMBER,
    name        VARCHAR2(100),
    status      VARCHAR2(20),
    source_flag VARCHAR2(10)
) ON COMMIT DELETE ROWS; -- 提交事务后自动清空数据，适合单次调用

2. 在存储过程中，把各个来源的数据插入临时表，最后通过游标查询临时表返回：

CREATE OR REPLACE PROCEDURE get_multi_source_data_temp (
    p_out_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    -- 清空临时表（可选，因为ON COMMIT DELETE ROWS会保证会话开始时表为空）
    DELETE FROM temp_result;

    -- 插入table_a的处理后数据
    INSERT INTO temp_result (id, name, status, source_flag)
        SELECT id, name, CASE WHEN id > 100 THEN 'VIP' ELSE 'NORMAL' END, 'TABLE_A'
        FROM table_a WHERE active = 'Y';

    -- 插入table_b的数据
    INSERT INTO temp_result (id, name, status, source_flag)
        SELECT emp_id, emp_name, 'DEPT_20', 'TABLE_B'
        FROM table_b WHERE dept_id = 20;

    -- 插入手动生成的数据
    INSERT INTO temp_result VALUES (999, 'Manual Entry', 'SPECIAL', 'MANUAL');

    -- 打开游标返回数据
    OPEN p_out_cursor FOR
        SELECT * FROM temp_result;
EXCEPTION
    WHEN OTHERS THEN
        IF p_out_cursor%ISOPEN THEN
            CLOSE p_out_cursor;
        END IF;
        RAISE;
END;
/

• 集合类型：内存操作，速度快，不需要预先创建对象；但数据量过大时会占用较多会话内存，适合中小数据量场景。
• 全局临时表：支持复杂SQL操作，适合大数据量；但需要预先创建临时表，有磁盘IO开销，不过会话级别的数据隔离性很好。

Java端可以通过CallableStatement来调用存储过程，注册OUT参数为Types.REF_CURSOR即可读取返回的游标数据，示例代码片段：

String procSql = "{call get_multi_source_data(?)}";
try (Connection conn = yourConnectionFactory.getConnection();
     CallableStatement cs = conn.prepareCall(procSql)) {
    // 注册OUT参数，类型为REF_CURSOR
    cs.registerOutParameter(1, Types.REF_CURSOR);
    cs.execute();
    // 获取游标对应的ResultSet
    ResultSet rs = (ResultSet) cs.getObject(1);
    // 遍历结果集
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String status = rs.getString("status");
        // 处理数据...
        System.out.println(String.format("ID: %d, Name: %s, Status: %s", id, name, status));
    }
} catch (SQLException e) {
    // 异常处理
    e.printStackTrace();
}

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