Refactoring PL/SQL: viele Cursor mit gleichem Zeilentyp (theoretisch)

Question

Ich schreibe gerade PL/SQL und bin in ein sich wiederholendes Muster verfallen:

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

Wenn ich dann die Ergebnisse verarbeite, habe ich einen Code wie diesen:

    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;

Der Code in der Verarbeitungsschleife ist für alle Cursors gleich, da alle 3 Cursors Folgendes zurückgeben a,b,c - der einzige Unterschied besteht darin, welcher Cursor referenziert wird. Ich wollte dies in eine Art von generischen Ergebnismenge Prozessor umgestalten, aber ich bin hier stecken:

procedure sp_process_collection(in_collection t_curs1_tab) is ...

Ich kann dies nur mit v_curs1_results kann ich es nicht nennen mit v_curs2_results oder ich bekomme eine PLS-00306 wrong number of types or arguments... Compiler-Fehler. Gibt es eine Möglichkeit, dies generisch zu tun, so dass ich nur eine Auflistungsbearbeitungsprozedur schreiben muss? Ich habe dieses Muster von Cursor (Rückgabe der gleichen drei Spalten, immer die gleichen Typen) erscheint in mehreren anderen Teilen des gleichen Pakets, und die Verarbeitung Schleife, während semantisch die gleiche ist manchmal mit leicht unterschiedlichen Code geschrieben. Ich würde die Verarbeitung gerne in einer einzigen Prozedur zentralisieren, ich weiß nur nicht, wie ich das in PL/SQL machen soll. Ich weiß, dass PL/SQL keine Generics hat (was ich piense en hätte eine Java/C#-Lösung ziemlich trivial gemacht), aber ich frage mich, ob es eine andere Möglichkeit gibt, dieses Problem anzugehen, an die ich einfach nicht gedacht habe.

(unter Verwendung von Oracle 10g)

Answer 1

Wenn die resultierenden Spalten die gleichen sind, gibt es keinen Grund, drei TYPE-Deklarationen zu haben. Sie haben nur einen TYPE. Ob Sie eine oder mehrere Variablen dieses TYPs benötigen, hängt davon ab, ob Sie die verschiedenen Datensätze gleichzeitig halten müssen.

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

Wenn Sie dazu bereit und berechtigt sind, können Sie eine TYPE um einen von einem der Cursor zurückgegebenen Datensatz darzustellen. Zum Beispiel:

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

Und einen weiteren Typ, der eine Sammlung von TRIPLE_TYPE :

CREATE TYPE TRIPLES_TYPE IS TABLE OF TRIPLE_TYPE;

Schließlich definieren Sie Ihre Cursor so, dass sie Folgendes zurückgeben TRIPLE_TYPE Aufzeichnungen:

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;

Diese Technik erlaubt es Ihnen, eine Prozedur zu schreiben, die eine TRIPLES_TYPE Sammlung:

procedure sp_process_collection(in_collection TRIPLES_TYPE) is ...

Answer 3

Können Sie dies mit einem REF CURSOR tun?

Etwa so:

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;

Ihr aufrufender Code müsste diese Prozedur einmal pro Cursor aufrufen:

OPEN c_curs1;
sp_process_cursor(c_curs1);
CLOSE c_curs1;

OPEN c_curs2;
sp_process_cursor(c_curs2);
...

Answer 4

Scheint, wie Sie 3 Cursors haben MÜSSEN, weil die "big_subquery" in jedem unterschiedlich sind, nehme ich an. Sie müssen also wirklich nur eine Prozedur zentralisieren, um die Verarbeitung von a, b und c Ergebnisse zu behandeln. Nichts allzu Ausgefallenes hier, definieren Sie einfach eine lokale Prozedur, um dies zu tun:

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;

Wenn Sie natürlich alle 3 Typen zusammen für jede Zeile verarbeiten müssen, oder wenn die Typen unterschiedlich sind (varchar2, Datum und Zahl), ändern Sie einfach die process_type-Prozedur, um alle 3 (a, b und c) zu übergeben. Etwa so:

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;

Und wenn Sie einen Rückgabewert benötigen, können Sie daraus eine Funktion machen.