如何在不使用系统对象的情况下将Oracle PL/SQL集合导入SYS_REFCURSOR

首先咱们先拆解你遇到的两个核心问题：

1. 第一个示例只返回最后一行：
   你代码里的问题是没递增IDX变量，两次赋值都给了VAR_TEMP(0)同一个元素，最后值被覆盖成2，而且只查询了这一个元素，自然只返回一行。正确填充集合需要每次赋值后递增索引，或者用集合的EXTEND方法（嵌套表场景）。

2. 使用TABLE(VAR_TEMP)报错：
   SQL引擎无法识别PL/SQL中定义的局部集合类型（比如你用TYPE ... IS TABLE OF ... INDEX BY PLS_INTEGER创建的关联数组），TABLE()函数只能作用于全局定义的集合类型（用CREATE TYPE创建的）或者Oracle内置系统类型，所以直接用会抛出ORA-00902: invalid datatype错误。

下面给你两种不需要创建全局系统对象的解决方案：

方案1：使用会话级临时表 #

会话级临时表仅在当前会话存在，不会污染系统全局对象，非常适配这种场景：

CREATE OR REPLACE PROCEDURE SP_TEST (TEST_cursor OUT SYS_REFCURSOR)
IS
    TYPE TEMP_RECORD IS RECORD(
        entries NUMBER,
        name VARCHAR2(50),
        update_col VARCHAR2(200) -- 避免用UPDATE关键字做字段名
    );
    TYPE TEMP_TABLE IS TABLE OF TEMP_RECORD INDEX BY PLS_INTEGER;
    VAR_TEMP TEMP_TABLE;
    IDX PLS_INTEGER;
BEGIN
    -- 正确填充集合数据
    IDX := 0;
    VAR_TEMP(IDX).entries := 1;
    VAR_TEMP(IDX).name := 'Name 1';
    VAR_TEMP(IDX).update_col := 'Update 1';
    
    IDX := IDX + 1;
    VAR_TEMP(IDX).entries := 2;
    VAR_TEMP(IDX).name := 'Name 2';
    VAR_TEMP(IDX).update_col := 'Update 2';

    -- 创建会话级临时表（仅第一次执行时创建，后续忽略已存在错误）
    BEGIN
        EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_sp_test (
            entries NUMBER,
            name VARCHAR2(50),
            update_col VARCHAR2(200)
        ) ON COMMIT PRESERVE ROWS';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -955 THEN -- 忽略表已存在的错误
                RAISE;
            END IF;
    END;

    -- 清空临时表，避免会话残留数据
    EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_sp_test';

    -- 遍历集合插入临时表
    IDX := VAR_TEMP.FIRST;
    WHILE IDX IS NOT NULL LOOP
        INSERT INTO temp_sp_test (entries, name, update_col)
        VALUES (VAR_TEMP(IDX).entries, VAR_TEMP(IDX).name, VAR_TEMP(IDX).update_col);
        IDX := VAR_TEMP.NEXT(IDX);
    END LOOP;

    -- 打开游标返回数据
    OPEN TEST_cursor FOR
        SELECT entries, name, update_col AS update FROM temp_sp_test;
END SP_TEST;
/

方案说明： #

• 临时表temp_sp_test是会话隔离的，其他会话看不到当前会话的数据，也不会互相干扰。
• ON COMMIT PRESERVE ROWS确保提交后数据仍然保留，直到会话结束。

方案2：动态SQL拼接UNION ALL（完全无对象依赖） #

如果你连临时表都不想创建，可以用动态SQL把集合中的每个元素拼接成SELECT ... FROM DUAL的语句，再用UNION ALL连接起来：

CREATE OR REPLACE PROCEDURE SP_TEST (TEST_cursor OUT SYS_REFCURSOR)
IS
    TYPE TEMP_RECORD IS RECORD(
        entries NUMBER,
        name VARCHAR2(50),
        update_col VARCHAR2(200)
    );
    TYPE TEMP_TABLE IS TABLE OF TEMP_RECORD INDEX BY PLS_INTEGER;
    VAR_TEMP TEMP_TABLE;
    IDX PLS_INTEGER;
    v_sql VARCHAR2(32767);
BEGIN
    -- 填充集合数据
    IDX := 0;
    VAR_TEMP(IDX).entries := 1;
    VAR_TEMP(IDX).name := 'Name 1';
    VAR_TEMP(IDX).update_col := 'Update 1';
    
    IDX := IDX + 1;
    VAR_TEMP(IDX).entries := 2;
    VAR_TEMP(IDX).name := 'Name 2';
    VAR_TEMP(IDX).update_col := 'Update 2';

    -- 拼接动态SQL语句
    v_sql := '';
    IDX := VAR_TEMP.FIRST;
    WHILE IDX IS NOT NULL LOOP
        IF v_sql IS NOT NULL THEN
            v_sql := v_sql || ' UNION ALL ';
        END IF;
        -- 转义字符串中的单引号，避免SQL语法错误
        v_sql := v_sql || 'SELECT ' || VAR_TEMP(IDX).entries || ' AS entries, ' ||
                 '''' || REPLACE(VAR_TEMP(IDX).name, '''', '''''') || ''' AS name, ' ||
                 '''' || REPLACE(VAR_TEMP(IDX).update_col, '''', '''''') || ''' AS update FROM DUAL';
        IDX := VAR_TEMP.NEXT(IDX);
    END LOOP;

    -- 打开游标执行动态SQL，空集合时返回空游标
    IF v_sql IS NOT NULL THEN
        OPEN TEST_cursor FOR v_sql;
    ELSE
        OPEN TEST_cursor FOR SELECT * FROM DUAL WHERE 1 = 0;
    END IF;
END SP_TEST;
/

方案说明： #

• 不需要创建任何数据库对象，完全在PL/SQL内部处理。
• 注意要转义字符串中的单引号（用REPLACE把'替换成''），否则会导致SQL语法错误。
• 如果集合很大，拼接后的SQL长度可能超过VARCHAR2(32767)的限制，这种情况下建议用临时表方案。

内容的提问来源于stack exchange，提问作者D.J.