我正试图将数据从引用游标加载到表变量(或数组)中,如果表变量基于existingtable%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
不确定我错过了什么,提前感谢您的帮助
上面代码中的变量名称误导了您。您的变量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语句的内存消耗比不使用要多得多。
当你了解并开始利用BULK COLLECT这样的功能是没有免费午餐。在某个地方几乎总是要进行权衡。与BULK COLLECT的权衡,就像许多其他人一样性能增强功能,是"运行更快但消耗更多"记忆力"(Oracle杂志)
但是,如果您只是获取和处理行(一次一行),则不需要BULK
语句,只需使用光标FOR LOOP
即可。(问汤姆)
另一种方法是:
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;