在Oracle SQL Developer 1.5中打印Oracle Sys_refcursor [英] Printing Oracle Sys_refcursor in Oracle SQL Developer 1.5

问题描述

我正在尝试执行返回sys_refcursor作为输出的过程.步骤是PROCEDURE GET_EMPLOYEEs(P_ID in NUMBER, P_OUT_CURSOR OUT SYS_REFCURSOR);

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);

我在SQL Developer 1.5中编写了下面的匿名块，它的执行正常，但是当我尝试打印光标时，出现错误.游标返回emp_name，salary和其他列.

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.

set serveroutput on;
declare
result sys_refcursor;
begin
emp.emp360_utils.GET_EMPLOYEEs(222334,result); 
dbms_output.put_line(result); // Error here
end;

错误是

PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

已更新:游标已迭代，但仍显示为对变量dummycursor的无效引用"的错误.

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;

推荐答案

您将需要遍历ref游标，并为其中的每一行打印出各个字段.在更新的版本中，您需要将游标读取到局部标量变量中，而不是另一个ref游标中:

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:

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;
/

我猜想lsn是一个数字，如果不是，则将其声明为正确的类型.如果游标返回的列不止一列，那么您将需要为它们中的每一个声明局部变量，并将它们全部取入其中，即使您仅显示其中之一.

I've guessed lsn is 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.

如果只想显示它，则可以使用绑定变量来执行此操作(在当前版本中选中并返回1.5.0):

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):

variable result refcursor

begin
  emp.emp360_utils.GET_EMPLOYEEs(222334, :result); 
end;
/

print result

请注意， 不是在declare块中；它是一个SQL Developer命令，而不是PL/SQL命令. 与print 一样，尽管两者都仅在SQL * Plus中进行了说明文档.还要注意在块内:result开头的冒号，表明它是一个绑定变量，而不是本地PL/SQL变量.

Note that the variable command is not in the declare block; 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 :result within the block, which indicates that it is a bind variable, not a local PL/SQL variable.

这篇关于在Oracle SQL Developer 1.5中打印Oracle Sys_refcursor的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！