如何将Oracle参考游标提取到表变量中? [英] How to fetch Oracle reference cursor into table variable?

问题描述

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

I am trying to load data from reference cursor into a table variable (or array), the reference cursor works if the table variable is based on existingtable %Rowtype but my reference cursor gets loaded by joining multiple tables so let me try to demonstrate an example what i am trying to do and some one can help me

--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

不确定我想念的是什么，在此先感谢您的帮助

Not sure what i am missing, Thanks in advance for your help

推荐答案

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

The name of variable in code above misleaded you. Your variable outtable is in table type. It isn't possible to fetch record data into table of records, but you can fetch it into record itself.

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语句:

Update: If you want to fetch all data for better performance your application you need to use BULK COLLECT statement:

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语句的内存消耗要比不使用内存大得多.

Note: memory consumption with the BULK statement is much more than without.

了解并开始学习时要记住的最重要的事情 利用诸如BULK COLLECT之类的功能，那就是没有 免费午餐.几乎总是需要在某处进行权衡. 与BULK COLLECT一样，需要折衷解决 性能增强功能运行速度更快，但消耗更多 内存."( Oracle杂志)

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. (问汤姆)

But if you are just fetching and processing the rows - a row at a time there is no needs in BULK statement, just use the cursor FOR LOOP. (Ask Tom)

这篇关于如何将Oracle参考游标提取到表变量中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！