PL/SQL:将显式游标转换为ref游标? [英] PL/SQL: convert explicit cursor to ref cursor?

问题描述

我可以将显式游标转换为ref游标吗?我在想类似的东西:

Can I convert an explicit cursor into a ref cursor? I was thinking of something like:

declare
  cursor c is
  select x from tab;

  rc sys_refcursor;

begin
  open c;
  rc:=c;
  close c;
end;
/

我想将ref游标用作过程的输入参数.

I would like to use the ref coursor as an input parameter for a procedure.

我知道我总是可以这样做:

I know I can always do it like this:

OPEN rc FOR select x from tab;

但是我正在重构一些旧代码，为了清楚起见，我希望保留明确的游标定义.

But I'm in the process of refactoring some old code an I would have liked to keep the explicit cursor definitions just for the sake of clarity.

推荐答案

如我的评论中所述，直到Oracle 11g才允许为另一个游标打开sys_refcursor.当您尝试做一些需要使用sys_refcursor的操作时，如下所示:

As mentioned in my comment, opening a sys_refcursor for another cursor is not allowed till Oracle 11g. As you are trying to do something which demands use of sys_refcursor, once way could be like below:

创建类型

CREATE TYPE va IS TABLE OF NUMBER;
/

阻止:

DECLARE
   CURSOR c
   IS
      SELECT employee_id FROM employee;

   rc    SYS_REFCURSOR;
   var   va;
BEGIN
   OPEN c;

   FETCH c BULK COLLECT INTO var;

   CLOSE c;

   OPEN rc FOR SELECT COLUMN_VALUE FROM TABLE (var);
END;
/

您将在此处看到最后，我再次对ref_cursor使用了SELECT语句.就像您不想使用通常的方式一样，我使用了另一种方式.

You would see here that at the end am using again a SELECT statement for ref_cursor. It's just like if you dont want to use usual way, i used an alternative way.

这篇关于PL/SQL:将显式游标转换为ref游标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！