循环调用存储过程如何收集所有REF CURSOR结果并校验响应码？

1. 关于返回结果的问题 #

是的，当前的OUTER_SP逻辑执行N次MAIN_SP后，只会返回最后一次调用的REF CURSOR结果。因为每次循环调用MAIN_SP时，都是对同一个OUT参数P_REF_CURSOR重新执行OPEN ... FOR操作，之前打开的游标会被覆盖，最终只有最后一次的结果能被外部获取到。

2. 解决方案：收集所有结果并检查错误 #

要实现收集所有调用结果、检查错误并返回拼接后的游标，需要借助自定义类型的集合来暂存数据，具体步骤和代码如下：

第一步：定义存储结果的自定义类型 #

先在包级别或当前Schema下定义匹配MAIN_SP返回结构的记录类型，以及该记录的表类型：

-- 定义匹配MAIN_SP返回字段的记录类型
TYPE T_MAIN_RESULT IS RECORD(
    V_SUCCESSFUL_COUNT NUMBER,
    V_RESPONSE_CODE NUMBER
);
-- 定义记录的表类型，用于存储多次调用的结果集合
TYPE T_MAIN_RESULT_LIST IS TABLE OF T_MAIN_RESULT;

第二步：修改OUTER_SP的逻辑 #

修改后的OUTER_SP会用临时游标接收单次调用结果，存入集合并检查错误，最后将集合数据转为输出游标返回：

PROCEDURE OUTER_SP(PARAM1 IN NUMBER,
                   P_REF_ID_LIST IN TABLE OF VARCHAR2,
                   P_REF_CURSOR OUT REF CURSOR)
IS
    L_SINGLE_RESULT  T_MAIN_RESULT;       -- 接收单次MAIN_SP的结果
    L_ALL_RESULTS    T_MAIN_RESULT_LIST := T_MAIN_RESULT_LIST(); -- 存储所有结果的集合
    L_HAS_ERROR      BOOLEAN := FALSE;    -- 标记是否存在错误（V_RESPONSE_CODE > 0）
    L_TEMP_CURSOR    REF CURSOR;          -- 临时游标，避免覆盖输出游标
BEGIN
    FOR i IN 1 .. P_REF_ID_LIST.COUNT LOOP
        -- 调用MAIN_SP，用临时游标接收单次结果
        MAIN_SP(PARAM1, P_REF_ID_LIST(i), L_TEMP_CURSOR);
        
        -- 读取临时游标数据到变量，用完及时关闭游标
        FETCH L_TEMP_CURSOR INTO L_SINGLE_RESULT;
        CLOSE L_TEMP_CURSOR;
        
        -- 将单次结果添加到集合
        L_ALL_RESULTS.EXTEND;
        L_ALL_RESULTS(L_ALL_RESULTS.LAST) := L_SINGLE_RESULT;
        
        -- 检查是否存在错误，更新标记
        IF L_SINGLE_RESULT.V_RESPONSE_CODE > 0 THEN
            L_HAS_ERROR := TRUE;
        END IF;
    END LOOP;

    -- 可选：如果需要，根据错误标记执行后续逻辑（比如抛出异常）
    -- IF L_HAS_ERROR THEN
    --     RAISE_APPLICATION_ERROR(-20001, "存在至少一次调用失败");
    -- END IF;

    -- 将集合中的所有结果转为REF CURSOR返回
    OPEN P_REF_CURSOR FOR
        SELECT * FROM TABLE(L_ALL_RESULTS);
END;

关键说明 #

• 使用临时游标L_TEMP_CURSOR避免直接覆盖输出游标，保证每次调用的结果都能被读取；
• 通过集合L_ALL_RESULTS暂存所有调用结果，最后用TABLE()函数将集合转换为关系型数据集，通过输出游标返回；
• 用L_HAS_ERROR变量全程跟踪是否存在错误，后续可根据业务需求添加异常抛出、日志记录等逻辑。

内容的提问来源于stack exchange，提问作者gene b.