重构 PL/SQL：许多具有相同行类型的游标（理论上）

Question

我正在编写一些 PL/SQL，发现自己陷入了重复模式：

cursor c_curs1 is
   select a, b, c
   from (...) big_subquery_1
   where big_subquery_1.a_ind = 'Y'

cursor c_curs2 is
   select a, b, c
   from (...) big_subquery_2
   where big_subquery_2.b_ind = 'R'

cursor c_curs3 is
   select a, b, c
   from (...) big_subquery_3
   where big_subquery_3.c_ind = 'M'

...
type t_curs1_tab is table of c_curs1;
type t_curs2_tab is table of c_curs2;
type t_curs3_tab is table of c_curs3;
...
v_curs1_results t_curs1_tab := t_curs1_tab();
v_curs2_results t_curs2_tab := t_curs2_tab();
v_curs3_results t_curs3_tab := t_curs3_tab();

然后在处理结果时，我有这样的代码：

    open c_curs1;
    fetch c_curs1 bulk collect into v_curs1_results;
    close c_curs1;

    if v_curs1_results.first is not null and v_curs1_results.last is not null then
       for i in v_curs1_results.first .. v_curs1_results.last loop
           /*Do something with field a in the results
             Do something with field b in the results
             Do something with field c in the results*/
           ....
       end loop;
    end if;

处理循环中的代码对于所有游标都是相同的，因为所有 3 个游标都返回 a ,b,c - 唯一的区别是引用哪个光标。我想将其重构为某种通用结果集处理器，但我被困在这里：

procedure sp_process_collection(in_collection t_curs1_tab) is ...

我只能用 v_curs1_results 调用它，不能用 v_curs2_results 调用它code> 或我收到 PLS-00306 类型或参数数量错误... 编译器错误。有没有什么办法可以通用地做到这一点，这样我只需要编写一个集合处理过程？我有这种光标模式（返回相同的三列，始终相同的类型）出现在同一包和处理循环的其他几个部分中，而语义上相同的有时用稍微不同的代码编写。我非常想将处理集中在一个过程中，我只是知道如何在 PL/SQL 中做到这一点。我知道 PL/SQL 没有泛型（我认为这会使 Java/C# 解决方案变得相当微不足道），但我想知道是否有另一种方法可以解决这个我刚刚没有的问题没想到。

（使用Oracle 10g）

Answer 1

如果结果列相同，则没有理由使用三个 TYPE 声明。你只有一种类型。您是否需要该类型的一个或多个变量取决于您是否需要同时保存不同的数据集。

cursor c_curs1 is
   select a, b, c
   from (...) big_subquery_1
   where big_subquery_1.a_ind = 'Y'

cursor c_curs2 is
   select a, b, c
   from (...) big_subquery_2
   where big_subquery_2.b_ind = 'R'

cursor c_curs3 is
   select a, b, c
   from (...) big_subquery_3
   where big_subquery_3.c_ind = 'M'

...
type t_curs_tab is table of c_curs1;
...
v_curs_results t_curs_tab := t_curs_tab();
...
open c_curs1;
fetch c_curs1 bulk collect into v_curs_results;
close c_curs1;
...
open c_curs2;
fetch c_curs2 bulk collect into v_curs_results;
close c_curs2;
...
open c_curs3;
fetch c_curs3 bulk collect into v_curs_results;
close c_curs3;

Answer 2

如果您愿意并且被允许这样做，您可以创建一个 TYPE 来表示任何游标返回的记录。例如：

CREATE TYPE TRIPLE_TYPE IS OBJECT
(
  a NUMBER,
  b NUMBER,
  c NUMBER
);

还有另一种类型来保存 TRIPLE_TYPE 的集合：

CREATE TYPE TRIPLES_TYPE IS TABLE OF TRIPLE_TYPE;

最后，定义游标以返回 TRIPLE_TYPE 记录：

DECLARE

  cursor c_curs1 is
    select TRIPLE_TYPE(a, b, c)
    from (...) big_subquery_1
    where big_subquery_1.a_ind = 'Y';

  triples TRIPLES_TYPE;

BEGIN
  OPEN c_curs1;
  FETCH c_curs1 BULK COLLECT INTO triples;
  CLOSE c_curs1;
END;

此技术允许您编写一个接受 TRIPLE_TYPE 的过程。代码>TRIPLES_TYPE集合：

procedure sp_process_collection(in_collection TRIPLES_TYPE) is ...

Answer 3

您可以使用 REF CURSOR 来完成此操作吗？

类似于：

CREATE PROCEDURE sp_process_cursor(in_cursor SYS_REFCURSOR)

/* you don't need to open the cursor, it's already open */
FETCH in_cursor BULK COLLECT INTO v_cursor_results;
IF v_cursor_results.first is not null and v_cursor_results.last IS NOT NULL THEN
  FOR i in v_cursor_results.FIRST .. v_cursor_results.LAST LOOP
  ...
  END LOOP;
END IF;

您的调用代码需要每个游标调用此过程一次：

OPEN c_curs1;
sp_process_cursor(c_curs1);
CLOSE c_curs1;

OPEN c_curs2;
sp_process_cursor(c_curs2);
...

Answer 4

看起来你必须有 3 个游标，因为我认为每个游标中的“big_subquery”都是不同的。因此，您实际上只需要集中一个过程来处理 a、b 和 c 结果。这里没什么太花哨的，只需定义一个本地过程来执行此操作：

CREATE or replace procedure my_proc IS

  cursor c_curs1 is
   select a, b, c
   from (...) big_subquery_1
   where big_subquery_1.a_ind = 'Y';

  cursor c_curs2 is
   select a, b, c
   from (...) big_subquery_2
   where big_subquery_2.b_ind = 'R';

  cursor c_curs3 is
   select a, b, c
   from (...) big_subquery_3
   where big_subquery_3.c_ind = 'M';

  -- local procedure to handle processing of a,b,c "types"
  procedure process_type(i_type in varchar2) is
  begin
    -- do something great here
  end process_type;

BEGIN
  for rec in c_curs1
  loop
    process_type(rec.a);
    -- do something else
    process_type(rec.b);
    -- do something more
    process_type(rec.c);
    -- do something even more
  end loop;

  for rec in c_curs2
  loop
    -- do processing for this cursor
    -- use process_type like above
  end loop;

  for rec in c_curs3
  loop
    -- do processing for this cursor
    -- use process_type like above
  end loop;

END;

当然，如果您需要为每行一起处理所有 3 种类型，或者类型不同（varchar2、日期和数字），只需修改 process_type 过程传递所有 3 个（a、b 和 c）。例如：

procedure process_type(i_type_a in varchar2, i_type_b in date, i_type_c in number) is
  begin
    -- do something great here
  end process_type;

如果您需要返回值，您可以将其设为函数。