oracle 从过程返回的 REF CURSOR 中获取和批量收集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19562603/
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
Fetch and bulk collect from a REF CURSOR returned by a procedure
提问by Rachcha
I have a stored procedure that has a SYS_REFCURSORas an OUTparameter. The signature is, for example, as follows:
我有一个具有一个存储过程SYS_REFCURSOR作为一个OUT参数。例如，签名如下：
PROCEDURE myProc(p_someID IN INTEGER, p_cursor OUT SYS_REFCURSOR);
I call this procedure from a function, where I have to copy a column named clientIDfrom the p_cursorto a scalar nested table.
我从一个函数调用这个过程，我必须将一个clientID从命名的列复制p_cursor到一个标量嵌套表中。
I am doing as follows:
我正在做如下：
CREATE OR REPLACE FUNCTION myFunction
RETURN sys_refcursor
IS
someID INTEGER := 1234;
myCursor SYS_REFCURSOR;
TYPE t_clientID_nt IS TABLE OF NUMBER(16,0);
clientID_nt t_clientID_nt;
otherID SYS_REFCURSOR;
BEGIN
myProc (someID, myCursor);
FOR i IN myCursor
LOOP
clientID_nt.EXTEND;
clientID_nt (clientID_nt.COUNT) := i.clientID;
END LOOP;
-- Other code that opens the cursor otherID
-- based on the IDs in clientID_nt
...
...
RETURN otherID;
END;
/
When I try to compile this function, the error I get is:
当我尝试编译这个函数时，我得到的错误是：
PLS-00221: 'CLIENTID_NT' is not a procedure or is undefined
and it is at line 11 of the code.
它位于代码的第 11 行。
Any help on how to fetch and bulk collect from such a cursor is greatly appreciated.
非常感谢有关如何从此类游标中获取和批量收集的任何帮助。
回答by Nick Krasnov
It's not allowed to use cursor variables in the forcursor loop (FOR i IN myCursor). You have to fetch from the cursor variable explicitly one row at a time, using FETCH INTOstatement and regular loop statement for instance or use FETCH BULK COLLECT INTOto populate a collection. For instance:
不允许在for游标循环 ( FOR i IN myCursor) 中使用游标变量。您必须一次显式地从游标变量中获取一行，例如 usingFETCH INTO语句和常规循环语句或用于FETCH BULK COLLECT INTO填充集合。例如：
SQL> declare
2 TYPE t_clientID_nt IS TABLE OF dual%rowtype;
3 clientID_nt t_clientID_nt;
4
5 l_cur sys_refcursor;
6
7 procedure OpenAndPopulateCursor(p_cur in out sys_refcursor) is
8 begin
9 open p_cur for
10 select *
11 from dual;
12 end;
13
14 begin
15 OpenAndPopulateCursor(l_cur);
16
17 if l_cur%isopen
18 then
19 fetch l_cur bulk collect into clientID_nt;
20 end if;
21
22 dbms_output.put_line(concat( to_char(clientID_nt.count)
23 , ' record(s) has/have been fetched.'));
24 end;
25 /
1 record(s) has/have been fetched.
PL/SQL procedure successfully completed
