在 Oracle SQL Developer 1.5 中打印 Oracle Sys_refcursor
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24570157/
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
Printing Oracle Sys_refcursor in Oracle SQL Developer 1.5
提问by Sanjana
I am trying to execute the procedure which returns a sys_refcursor as output. The procedure is PROCEDURE GET_EMPLOYEEs(P_ID in NUMBER, P_OUT_CURSOR OUT SYS_REFCURSOR);
我正在尝试执行返回 sys_refcursor 作为输出的过程。程序是 PROCEDURE GET_EMPLOYEEs(P_ID in NUMBER, P_OUT_CURSOR OUT SYS_REFCURSOR);
I wrote the below anonymous block in SQL Developer 1.5 and its executing fine,but when I try to print the cursor, I am getting an error. The cursor returns emp_name,salary and other columns.
我在 SQL Developer 1.5 中编写了以下匿名块并且它执行正常，但是当我尝试打印游标时，出现错误。游标返回 emp_name、salary 等列。
set serveroutput on;
declare
result sys_refcursor;
begin
emp.emp360_utils.GET_EMPLOYEEs(222334,result);
dbms_output.put_line(result); // Error here
end;
The error is
错误是
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
UPDATED: Iterated for cursor,but still getting error as "Invalid reference to variable dummycursor".
更新：针对游标进行迭代，但仍然出现“对变量虚拟游标的引用无效”的错误。
set serveroutput on;
declare
dummycursor sys_refcursor;
result sys_refcursor;
begin
emp.emp360_utils.GET_EMPLOYEEs(222334,result);
LOOP
fetch result into dummycursor;
EXIT when result%notfound;
dbms_output.putline(dummycursor.lsn);
end loop;
end;
回答by Alex Poole
You would need to loop over the ref cursor and for each row in it, print out the individual fields. In your updated version you need to fetch the cursor into local scalar variables, not another ref cursor:
您需要遍历 ref 游标，并为其中的每一行打印出各个字段。在您更新的版本中，您需要将游标提取到本地标量变量中，而不是另一个引用游标：
set serveroutput on;
declare
result sys_refcursor;
lsn number; -- guessing the data type
begin
emp.emp360_utils.GET_EMPLOYEEs(222334,result);
loop
fetch result into lsn; -- and other columns if needed
exit when result%notfound;
dbms_output.put_line(lsn);
end loop;
end;
/
I've guessed lsnis a number, if not then declare that as the right type. If the cursor returns more than one column then you will need to declare local variables for each of them and fetch them all into those, even if you're only displaying one of them.
我猜lsn是一个数字，如果不是，则将其声明为正确的类型。如果游标返回不止一列，那么您需要为每一列声明局部变量并将它们全部提取到那些中，即使您只显示其中之一。
If you just want to display it then you can use a bind variable to do this instead (checked in the current version and back to 1.5.0):
如果您只想显示它，那么您可以使用绑定变量来执行此操作（在当前版本中检查并返回到 1.5.0）：
variable result refcursor
begin
emp.emp360_utils.GET_EMPLOYEEs(222334, :result);
end;
/
print result
Note that the variablecommandis notin the declareblock; it is a SQL Developer command, not a PL/SQL command. As is print, though both are only documented in the SQL*Plus docs. And also note the colon at the start of :resultwithin the block, which indicates that it is a bind variable, not a local PL/SQL variable.
注意，该variable命令是不是在declare块; 它是 SQL Developer 命令，而不是 PL/SQL 命令。由于是print，虽然双方都只是在SQL * Plus的文档记录。还要注意:result块内开头的冒号，这表明它是一个绑定变量，而不是本地 PL/SQL 变量。
回答by Alex78191
You can execute procedure using Runbutton in package source
您可以使用Run包源中的按钮
执行过程
and view cursor content in tab Output variables
并在选项卡中查看光标内容 Output variables
