oracle - 如何将Oracle引用游标提取到表变量中？

Question

我正在尝试将数据从引用游标加载到表变量(或数组)中，如果表变量基于现有表％Rowtype，则引用游标可以工作，但我的引用游标得到通过连接多个表来加载，所以让我尝试举一个例子来演示我正在尝试做什么，有人可以帮助我

--created table
create table SAM_TEMP(
    col1 number null,
    col2 varchar(100) null
);

--created procedure which outputs results from that table

CREATE OR REPLACE
PROCEDURE SP_OUT_RefCur_PARAM(
    C_RESULT OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN C_RESULT FOR 
        SELECT COL1,COL2
        FROM SAM_TEMP;
END SP_OUT_RefCur_PARAM;

--seeing the output works like this
DECLARE 
    REFCUR SYS_REFCURSOR;   
    outtable SAM_TEMP%rowtype ;  
BEGIN 
    SP_OUT_RefCur_PARAM(REFCUR);
    LOOP
        FETCH REFCUR INTO outtable;
        EXIT WHEN REFCUR%NOTFOUND;
        dbms_output.put_line(outtable.col1);
    END LOOP;
    CLOSE REFCUR;
END;


--but when i try to run below script it is giving error,i think i am missing something
DECLARE 
    REFCUR SYS_REFCURSOR;   
    TYPE REFTABLETYPE IS RECORD (COL1 NUMBER, COL2  VARCHAR(100));
    TYPE TABLETYPE IS TABLE OF REFTABLETYPE;
    outtable TABLETYPE; 
BEGIN 
    SP_OUT_RefCur_PARAM(REFCUR);
    LOOP
        FETCH REFCUR INTO outtable;
        EXIT WHEN REFCUR%NOTFOUND;
        dbms_output.put_line(outtable.col1);
    END LOOP;
    CLOSE REFCUR;
END;

错误报告:

ORA-06550 line 9, column 21:
PLS-00597 expression 'OUTTABLE' in the INTO list is of wrong type
ORA-06550 line 9, column 3:
PL/SQL SQL Statement ignored
ORA-06550 line 11, column 32:
PLS-00302 component 'COL1' must be declared

不确定我缺少什么，提前感谢您的帮助

Answer 1

上面代码中的变量名称误导了您。您的变量 outtable 是 table 类型。无法将记录数据提取到记录表中，但您可以将其提取到记录本身中。

DECLARE 
    REFCUR SYS_REFCURSOR;   
    TYPE RECORDTYPE IS RECORD (COL1 NUMBER, COL2  VARCHAR(100));
    outtable RECORDTYPE; 
BEGIN 
    SP_OUT_RefCur_PARAM(REFCUR);
    LOOP
        FETCH REFCUR INTO outtable;
        EXIT WHEN REFCUR%NOTFOUND;
        dbms_output.put_line(outtable.col1);
    END LOOP;
    CLOSE REFCUR;
END;

更新:如果您想获取所有数据以获得更好的应用程序性能，您需要使用 BULK COLLECT 语句:

DECLARE 
    REFCUR SYS_REFCURSOR;   
    TYPE RECORDTYPE IS
        RECORD (COL1 NUMBER, COL2  VARCHAR(100));
    TYPE TABLETYPE IS
        TABLE OF REFTABLETYPE
        INDEX BY PLS_INTEGER;
    outtable TABLETYPE; 
BEGIN 
    SP_OUT_RefCur_PARAM(REFCUR);
    LOOP
        FETCH REFCUR INTO BULK COLLECT outtable;
        EXIT WHEN outtable.COUNT = 0;

        FOR indx IN 1 .. outtable.COUNT 
        LOOP
            dbms_output.put_line(outtable(indx).col1);;
        END LOOP;
    END LOOP;
    CLOSE REFCUR;
END;

注意:使用 BULK 语句时的内存消耗比不使用时要多得多。

The most important thing to remember when you learn about and start to take advantage of features such as BULK COLLECT is that there is no free lunch. There is almost always a trade-off to be made somewhere. The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory." (Oracle Magazine)

但是，如果您只是获取和处理行 - 一次一行，则不需要 BULK 语句，只需使用游标 FOR LOOP 即可。 (Ask Tom)