如何将 Oracle 引用游标提取到表变量中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20415107/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fetch Oracle reference cursor into table variable?
提问by sam
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
我正在尝试将数据从引用游标加载到表变量（或数组）中，如果表变量基于现有表 %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;
Error report:
错误报告：
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
不知道我错过了什么，提前感谢您的帮助
回答by Alexander Myshov
The name of variable in code above misleaded you. Your variable outtableis in tabletype. It isn't possible to fetch record data into table of records, but you can fetch it into record itself.
上面代码中的变量名称误导了您。你的变量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;
Update:If you want to fetch all data for better performance your application you need to use BULK COLLECT statement:
更新：如果您想获取所有数据以获得更好的应用程序性能，您需要使用 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;
Note: memory consumption with the BULK statement is much more than without.
注意：使用 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 COLLECT 等功能时，要记住的最重要的事情是没有免费的午餐。几乎总是在某处进行权衡。与许多其他性能增强功能一样，BULK COLLECT 的权衡是“运行速度更快但消耗更多内存”。（甲骨文杂志）
But if you are just fetching and processing the rows - a row at a time there is no needs in BULKstatement, just use the cursor FOR LOOP. (Ask Tom)
但是，如果您只是获取和处理行 - 一次一行，则不需要 inBULK语句，只需使用 cursor FOR LOOP。（问汤姆）
回答by Wernfried Domscheit
Another way to do it is this one:
另一种方法是这样的：
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);
FETCH REFCUR BULK COLLECT INTO outtable;
FOR i in outtable.First..outtable.Last Loop
dbms_output.put_line(outtable(i).col1);
END LOOP;
CLOSE REFCUR;
END;
