Oracle 19c存储过程中SYS_REFCURSOR使用及ETL优化问询

我正在修订一批旧ETL脚本，原脚本频繁使用CREATE/DROP临时表存储筛选和关联用的数据集，当前运行时长超1.5小时，需尽可能提升性能，数据量通常在1-1000万行，数据源包括非物化视图和表。

我尝试把单个查询整合到包内的存储过程中，通过SYS_REFCURSOR调用不同过程，优化代码组织性以方便评审。但下面的示例代码编译时报错PL/SQL: ORA-00942: table or view does not exist，我清楚这是因为直接引用存储过程而非表导致的。

我的问题分两部分：

1. 这个方案是否合理？如果不合理，存储过程中处理临时数据集的最佳实践是什么？
2. 如何引用存储过程返回的SYS_REFCURSOR结果？我无法访问以SYS_REFCURSOR为OUT参数的测试过程的返回结果。

CREATE OR REPLACE PACKAGE "DAILY_ETL" AS
  PROCEDURE GET_TMP_A (TMP_A OUT SYS_REFCURSOR);
END DAILY_ETL;
/

CREATE OR REPLACE PACKAGE BODY "DAILY_ETL" AS

  PROCEDURE GET_TMP_A (TMP_A OUT SYS_REFCURSOR) AS
    BEGIN
      OPEN TMP_A FOR
        SELECT *
        FROM (
            WITH
              NESTED_TMP_A AS
                (SELECT 
                  THING_A
                FROM RAW_DATA
                ),
              NESTED_TMP_B AS
                (
                SELECT 
                  THING_B
                FROM THING_A
                WHERE BUSINESS_RULES_APPLIED
                )
            SELECT DISTINCT 
              * AS THING_I_WANT_FOR_TMP_A 
            FROM RAW_TABLE
                INNER JOIN NESTED_TMP_B ON 
                  ID = ID
            UNION
            SELECT DISTINCT
              THING_I_WANT_FOR_TMP_A
            FROM NESTED_TMP_A
            WHERE BUSINESS_RULES_APPLIED);
    END GET_TMP_A;

  PROCEDURE GET_TMP_B (TMP_B OUT SYS_REFCURSOR) AS
    BEGIN
      OPEN TMP_B FOR
      SELECT DISTINCT 
        COLUMN_A,
        COLUMN_B
      FROM ROOT_TABLE
      INNER JOIN (GET_TMP_A) AS TMP_A ON
        ROOT_TABLE.ID = TMP_A.ID;
    END GET_TMP_B;
END DAILY_ETL;
/

1. 方案合理性与临时数据集处理最佳实践 #

你当前用SYS_REFCURSOR跨过程关联的方案不合理——SQL引擎无法直接识别PL/SQL的游标对象，这也是编译报错的核心原因。针对你的1-1000万行数据量场景，推荐以下几种最佳实践：

（1）优先用CTE/内嵌视图替代临时表 #

如果原临时表仅用于存储单次查询的中间筛选结果，直接把逻辑合并到主查询的WITH子句（CTE）里。Oracle优化器能全局分析执行计划，避免频繁DDL临时表的开销，还可能生成更优的关联策略。比如你可以把GET_TMP_A的逻辑直接作为CTE嵌入到GET_TMP_B的查询中，不需要拆分存储过程。

（2）用全局临时表（GTT）替代普通临时表 #

如果中间数据集需要多次复用，或者逻辑过于复杂无法合并到单个查询，改用全局临时表（GLOBAL TEMPORARY TABLE）。GTT的结构是永久的，数据是会话级/事务级私有，无需每次CREATE/DROP，能大幅降低DDL带来的性能损耗。创建示例：

CREATE GLOBAL TEMPORARY TABLE GTT_TMP_A (
  ID NUMBER,
  THING_I_WANT_FOR_TMP_A VARCHAR2(100)
) ON COMMIT PRESERVE ROWS; -- 会话级保留数据，可选ON COMMIT DELETE ROWS设为事务级

在存储过程中先清空GTT，再插入数据，后续查询直接关联GTT即可，性能接近普通表。

（3）表函数（Table Function）适配跨PL/SQL与SQL的场景 #

如果必须把PL/SQL生成的数据集暴露给SQL查询，可以用表函数返回自定义集合类型，这样就能在SQL中像表一样引用。步骤如下：

• 先定义自定义类型：

CREATE OR REPLACE TYPE TMP_A_REC AS OBJECT (
  ID NUMBER,
  THING_I_WANT_FOR_TMP_A VARCHAR2(100)
);
CREATE OR REPLACE TYPE TMP_A_TBL AS TABLE OF TMP_A_REC;

• 将GET_TMP_A改为表函数：

FUNCTION GET_TMP_A RETURN TMP_A_TBL PIPELINED AS
BEGIN
  FOR rec IN (
    -- 原GET_TMP_A中的完整查询逻辑
    SELECT DISTINCT ID, THING_I_WANT_FOR_TMP_A
    FROM ...
  ) LOOP
    PIPE ROW(TMP_A_REC(rec.ID, rec.THING_I_WANT_FOR_TMP_A));
  END LOOP;
  RETURN;
END GET_TMP_A;

• 在SQL中引用：

SELECT * FROM TABLE(DAILY_ETL.GET_TMP_A())

注意：1000万级数据量下，表函数可能存在PL/SQL与SQL上下文切换的性能损耗，优先考虑GTT。

2. 访问SYS_REFCURSOR返回结果的方法 #

SYS_REFCURSOR是PL/SQL专属的游标对象，无法直接在SQL中访问，需要通过PL/SQL块读取，或借助工具可视化查看：

（1）在PL/SQL块中读取数据 #

DECLARE
  v_cursor SYS_REFCURSOR;
  v_id NUMBER;
  v_target_col VARCHAR2(100); -- 类型和数量要与游标返回列匹配
BEGIN
  DAILY_ETL.GET_TMP_A(v_cursor);
  LOOP
    FETCH v_cursor INTO v_id, v_target_col;
    EXIT WHEN v_cursor%NOTFOUND;
    -- 处理数据，比如打印或插入到表
    DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Value: ' || v_target_col);
  END LOOP;
  CLOSE v_cursor;
END;
/

（2）在可视化工具中测试 #

如果用SQL Developer、PL/SQL Developer等工具执行存储过程，工具会自动识别OUT参数的SYS_REFCURSOR，执行完成后会弹出窗口展示游标结果。

（3）将游标结果写入表中 #

如果需要在SQL中使用游标数据，可以先把游标内容插入到临时表或普通表：

DECLARE
  v_cursor SYS_REFCURSOR;
BEGIN
  DAILY_ETL.GET_TMP_A(v_cursor);
  -- 先清空目标表（如果需要）
  TRUNCATE TABLE TMP_STORAGE;
  -- 插入数据
  INSERT INTO TMP_STORAGE (ID, TARGET_COL)
  SELECT * FROM TABLE(CAST(v_cursor AS SYS_REFCURSOR));
END;
/

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