oracle 如何使用记录来循环引用游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9385142/
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 use record to loop a ref cursor?
提问by Alex Tang
I want to write PL/SQL to test a function in a package. The package defines a cursor type
我想编写 PL/SQL 来测试包中的函数。包定义了一个游标类型
TYPE ref_cursor IS REF CURSOR;
I want to define a record based on that type.
我想根据该类型定义一条记录。
My code is:
我的代码是：
DECLARE
cur PACKAGE_NAME.ref_cursor;
rec cur%ROWTYPE;
why is last line not correct?
为什么最后一行不正确？
回答by Justin Cave
You can't define a record type based on a weakly-typed REF CURSOR. Since the cursor type defined in the package can be used to return data from an arbitrary query with arbitrary columns, the PL/SQL compiler can't determine an appropriate record type to fetch the data into.
您不能根据弱类型的 REF CURSOR 定义记录类型。由于包中定义的游标类型可用于从具有任意列的任意查询返回数据，因此 PL/SQL 编译器无法确定适当的记录类型来获取数据。
If you know the actual data being returned from the function, you could declare a record of that type to fetch the data into. For example, if I declare a function that returns a weakly-typed cursor type but I know that the cursor really returns a cursor based on the EMPtable, I can fetch the data into an EMP%ROWTYPErecord (note that SYS_REFCURSORis a system-defined weakly-typed REF CURSOR type)
如果您知道函数返回的实际数据，则可以声明该类型的记录以将数据提取到其中。例如，如果我声明了一个返回弱类型游标类型的函数，但我知道该游标确实返回了一个基于EMP表的游标，我可以将数据提取到一个EMP%ROWTYPE记录中（注意这SYS_REFCURSOR是一个系统定义的弱类型游标REF CURSOR 类型）
create or replace function f1
return sys_refcursor
is
l_rc sys_refcursor;
begin
open l_rc
for select *
from emp;
return l_rc;
end;
declare
l_rc sys_refcursor;
l_emp emp%rowtype;
begin
l_rc := f1;
loop
fetch l_rc into l_emp;
exit when l_rc%notfound;
dbms_output.put_line( l_emp.empno );
end loop;
end;
