如何将SYS_REFCURSOR数据插入表？求避免重复SELECT的优化方案

你的核心需求是消除对TABLE1的两次重复查询，减少数据库的重复计算与IO开销，以下是两种实用的优化方案：

方案1：利用临时表暂存结果 #

通过全局临时表存储TABLE1的查询结果，后续基于临时表完成游标返回和插入操作，仅需查询TABLE1一次：

CREATE OR REPLACE PROCEDURE TEST(CUR OUT SYS_REFCURSOR)
IS
BEGIN
    -- 提前创建全局临时表（仅需执行一次，无需在存储过程中重复创建）
    -- CREATE GLOBAL TEMPORARY TABLE TMP_TABLE1
    -- ON COMMIT PRESERVE ROWS
    -- AS SELECT * FROM TABLE1 WHERE 1=0;

    -- 唯一一次查询TABLE1并写入临时表
    INSERT INTO TMP_TABLE1 SELECT * FROM TABLE1;

    -- 基于临时表打开输出游标
    OPEN CUR FOR SELECT * FROM TMP_TABLE1;

    -- 将临时表数据插入目标表TABLE2
    INSERT INTO TABLE2 SELECT * FROM TMP_TABLE1;
END;
/

注：全局临时表是会话隔离的，数据仅对当前会话可见，会话结束后会自动清理（或事务提交后，取决于创建时的ON COMMIT设置），不会影响其他业务。

方案2：使用PL/SQL集合+BULK COLLECT #

通过BULK COLLECT将查询结果一次性加载到内存集合中，再通过集合生成返回游标、批量插入目标表，同样仅查询TABLE1一次：

CREATE OR REPLACE PROCEDURE TEST(CUR OUT SYS_REFCURSOR)
IS
    -- 定义与TABLE1结构完全匹配的记录和集合类型
    TYPE TABLE1_REC IS RECORD (
        -- 需对应TABLE1的所有字段，示例：
        id NUMBER,
        name VARCHAR2(100),
        create_date DATE
        -- 其他字段...
    );
    TYPE TABLE1_TAB IS TABLE OF TABLE1_REC;
    v_table_data TABLE1_TAB;
BEGIN
    -- 一次性查询TABLE1数据到集合
    SELECT * BULK COLLECT INTO v_table_data FROM TABLE1;

    -- 将集合转换为游标返回
    OPEN CUR FOR SELECT * FROM TABLE(v_table_data);

    -- 批量插入TABLE2，比单条插入效率更高
    FORALL i IN 1..v_table_data.COUNT
        INSERT INTO TABLE2 VALUES v_table_data(i);
END;
/

注：若TABLE1数据量极大，可通过分批FETCH避免内存溢出：

CREATE OR REPLACE PROCEDURE TEST(CUR OUT SYS_REFCURSOR)
IS
    CURSOR c_table1 IS SELECT * FROM TABLE1;
    TYPE TABLE1_TAB IS TABLE OF c_table1%ROWTYPE;
    v_table_data TABLE1_TAB;
    v_full_data TABLE1_TAB; -- 存储全量数据用于返回游标
BEGIN
    OPEN c_table1;
    LOOP
        -- 每次批量获取1000条数据
        FETCH c_table1 BULK COLLECT INTO v_table_data LIMIT 1000;
        EXIT WHEN v_table_data.COUNT = 0;

        -- 批量插入TABLE2
        FORALL i IN 1..v_table_data.COUNT
            INSERT INTO TABLE2 VALUES v_table_data(i);
        
        -- 累加全量数据
        v_full_data := v_full_data MULTISET UNION ALL v_table_data;
    END LOOP;
    CLOSE c_table1;

    -- 返回全量数据的游标
    OPEN CUR FOR SELECT * FROM TABLE(v_full_data);
END;
/

方案对比 #

• 临时表方案：适合超大数据量场景，内存占用低，但依赖临时表资源；
• 集合方案：纯内存操作速度更快，数据量较小时性能最优，大数据量需配合分批处理。

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