如何向对象方法声明引用游标参数？

Question

我对 PL/SQL 有点陌生，需要看起来有点像这样的东西：

create type base as object (
  unused number,
  member procedure p( c in ref cursor )
) not final;

create type child1 under base (
  overriding member procedure p( c in ref cursor ) as
    t table1%rowtype
  begin
    fetch c into t;
    -- process table1 row
  end;
);

create type child2 under base (
  overriding member procedure p( c in ref cursor ) as
    t table2%rowtype
  begin
    fetch c into t;
    -- process table2 row
  end;
);

procedure generic_handler( o in base, c in ref cursor ) as
begin
  o.p( c );
end;

o1 child1 := child1(0)
o2 child2 := child2(0)

c ref cursor
open c for select * from table1;
generic_handler( o1, c );

open c for select * from table2;
generic_handler( o2, c );

基本上，我需要一个通用例程，它知道如何执行与表无关的操作，将特定于表的任务委托给派生类。

上述采用“引用光标”的对象方法无法编译 - 编译器表示“需要定义光标”。 因此，我当然已经尝试过“键入 generic_cursor 作为引用光标”，但无法编译它。

当我试图追踪将引用游标传递给对象方法的语法时，我几乎什么也没发现。 这让我觉得也许我正在尝试做一些愚蠢的事情。

我想做的事情有意义吗？ 如果是这样，我错过了什么？ 在哪里可以定义 generic_cursor 以便我可以将其用作对象方法参数类型？

Answer 1

一旦你解决了语法错误，你的代码就可以工作了。

SQL> create or replace type base as object
  2  (  unused number
  3      ,  member procedure p( c in sys_refcursor )
  4  )
  5  not final;
  6  /

Type created.

SQL>
SQL> create or replace type child1 under base (
  2      overriding member procedure p( c in sys_refcursor )
  3  );
  4  /

Type created.

SQL> create or replace type body child1 as
  2      overriding member procedure p( c in sys_refcursor )
  3          as
  4              t dept%rowtype;
  5          begin
  6              loop
  7                  fetch c into t;
  8                  exit when c%notfound;
  9                  dbms_output.put_line('dname='||t.dname);
 10              end loop;
 11          end;
 12  end;
 13  /

Type body created.

SQL>
SQL> create or replace type child2 under base (
  2      overriding member procedure p( c in sys_refcursor )
  3   );
  4  /

Type created.

SQL> create or replace type body child2 as
  2      overriding member procedure p( c in sys_refcursor )
  3          as
  4              t emp%rowtype;
  5          begin
  6              loop
  7                  fetch c into t;
  8                  exit when c%notfound;
  9                  dbms_output.put_line('ename='||t.ename);
 10              end loop;
 11          end;
 12  end;
 13  /

Type body created.

SQL>
SQL>
SQL> create or replace procedure generic_handler
  2          ( o in out base, c in sys_refcursor )
  3          as
  4  begin
  5      o.p( c );
  6  end;
  7  /

Procedure created.

SQL>
SQL> set serveroutput on size unlimited
SQL>
SQL> declare
  2      o1 child1 := child1(0);
  3      o2 child2 := child2(0);
  4      rc sys_refcursor;
  5  begin
  6      open rc for select * from dept where deptno = 10;
  7      o1.p(rc);
  8      open rc for select * from emp where deptno = 10;
  9      o2.p(rc);
 10  end;
 11  /
dname=ACCOUNTING
ename=BOEHMER
ename=SCHNEIDER
ename=KISHORE

PL/SQL procedure successfully completed.

SQL>

当您是新手时，Oracle 文档很难理解。 我认为在您的情况下，您需要知道 面向对象的内容与常规 PL/SQL 信息。 每当您遇到困难时，您可能都需要检查两者。