PL SQL For循环Sys_RefCursor [英] PL SQL For Loop Sys_RefCursor

问题描述

我正在使用Oracle 12c.在PL/SQL中，我可以这样做

I'm using Oracle 12c. In PL/SQL I can do this

set serveroutput on
declare
begin
  for x in (select 1 as y from dual) loop
    dbms_output.put_line(x.y);
  end loop;
end;

我也可以这样做...

I can also do this...

set serveroutput on
declare
  cursor c1 is
    select 1 as y from dual;
begin
  for x in c1 loop
    dbms_output.put_line(x.y);
  end loop;
end;

到目前为止，太好了.但是我可以使用sys_refcursor来做到这一点吗?我知道我可以使用fetch/while循环来做到这一点，但更喜欢for循环语法(我认为这样更干净)...

So far, so good. But can I do this with a sys_refcursor? I am aware I could do it with a fetch/while loop but prefer the for loop syntax (I think it is a lot cleaner)...

set serveroutput on
declare
  cur sys_refcursor;
begin
  cur := Package.GetData(1234);
  fetch cur into y;
  while cur%FOUND loop
    dbms_output.put_line(y);
    fetch cur into y;
  end loop;
end;

我想做...

set serveroutput on
declare
  cur sys_refcursor;
begin
  cur := PACKAGE.GetData(1234); -- This returns a sys_refcursor
  for x in cur loop
    dbms_output.put_line(x.y);
  end loop;
end;

Error report -
ORA-06550: line 5, column 16:
PLS-00221: 'cur' is not a procedure or is undefined

是否存在用于通过sys_refcursor进行循环的机制(而不是获取/while循环)?也许我不知道的关于12c的新事物...?

Is there a mechanism to for loop through the sys_refcursor (rather than the fetch into/while loop)? Perhaps something new-fangled in 12c that I don't know about...?

推荐答案

SYS_REFCURSOR仅仅是预先声明的弱引用游标.没有这样的机制可以在不提取的情况下循环遍历sys_refcursor.同样，您也无法将弱反射器与普通光标进行比较，并且它们的工作方式有所不同.这是一个缓冲区空间，用于临时保存结果.当您在PLSQL块中运行以下语句时，PLSQL引擎无法理解它为PLSQL变量并引发错误

SYS_REFCURSOR is merely a pre-declared weak ref cursor. There is no such mechanism to loop through sys_refcursor without fetching. Also you cannot compare a weak refcursor with normal cursor and they work differently. It's a buffer space which is allocated to hold the result temporarily. When you run the below statement in PLSQL block, PLSQL engine doesnot understand it a PLSQL variable and throws the error

针对x的当前循环

for x in cur loop

PLS-00221: 'CUR' is not a procedure or is undefined

此外，下面的语句也将失败，因为如果您引用了SYS_REFCURSOR，则没有为Package定义OUT参数.

Apart the below statement will also fail since you didnot defined the OUT paramater to the Package if its retruning a SYS_REFCURSOR.

cur:= PACKAGE.GetData(1234);

cur := PACKAGE.GetData(1234);

您可以获取SYS_REFCURSOR的内容，然后将其显示如下:

You can fetch the content of the SYS_REFCURSOR and then display it as below:

declare
  a  SYS_REFCURSOR;
  v_emp_id  employee.emp_id%type;
begin         
  --- This is a procedure with OUT parameter as SYS_REFCURSOR
  dynmc_selec(emp_output=>a);

  loop
    FETCH a INTO v_emp_id;
    EXIT WHEN a%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp_id );

  end loop;
end;

这篇关于PL SQL For循环Sys_RefCursor的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！