Dynamisches SQL mit REF Cursor

Nun, wir haben die „normalen“ Cursor kennengelernt. Sie werden deklariert und dabei wird ihnen ein statisches SQL-Statement zugewiesen (das parametrisiert werden kann). Der Cursor ist dabei ein Pointer auf einen Speicherbereich, in dem die selektierten Daten liegen. Die Einschränkungen des „normalen“ Cursors sind:

• Kein dynamisches Zusammensetzen des SQL-Statements möglich (ausser mit Parametern), das SQL-Statement muss im Deklarationsteil ausformuliert werden
• Keine Möglichkeit, den „Speicherbereich“ z.B. in Form einer Funktion an einen Aufrufer zurückzugeben

Ein REF Cursor hingegen ist ein Pointer auf einen Speicherbereich, in dem die selektierten Datensätze liegen. Vorteile:

• Das Select des REF Cursor kann zur Ausführungszeit dynamisch zusammengesetzt werden
• Der REF Cursor (Pointer) kann an einen Aufrufer zurückgegeben werden

Der Benefit führt zu mehr Transparenz und Kapselung für den Aufrufer. Der Aufrufer erhält einen Pointer auf einen Speicherbereich, von dem er nicht weiss (wissen muss), wie die darin enthaltenen Datensätze tatsächlich ermittelt worden sind (Ortstransparenz, Kapselung). Gleichzeitig hat der Aufrufer die eigene Entscheidungskompetenz, wieviel Daten er tatsächlich aus dem Cursor abrufen will, bzw. wie er ihn bearbeiten will.

Beispiel 1: Nehmen wir an, eine Funktion hat die Aufgabe, Mitarbeiter aus der EMP-Tabelle an einen Aufrufer zurückzureichen. Natürlich kann die Funktion eine gefüllte PL/SQL-Tabelle zurückreichen, die entsprechend der Parametrisierung (z.B. nur eine bestimmte Abteilung) gefüllt ist. Nachteil: Der Speicherbedarf wächst (vielleicht gibt es 1000 Mitarbeiter in einer Abteilung, PL/SQL benötigt dann eine große Variable), obwohl der Aufrufer nur bestimmte Mitarbeiter aus der Abteilung braucht. Blöd.

Beispiel 2: Der Aufrufer benötigt Daten und die aufgerufene Funktion muss entscheiden, woher diese Daten zu beschaffen sind. Die Funktion bestimmt das SELECT-Statement und damit die zu lesende Tabelle oder sogar das Schema und liefert dem Aufrufer einen Pointer auf diese Datenmenge zurück. Der Aufrufer hat keine Ahnung, woher die Daten stammen, er benutzt sie lediglich.

In meinem Programm für den SQLDUMP unter ORACLE benutze ich z.B. den REF CURSOR, um dem Aufrufer die Möglichkeit zu geben, die Tabelle zu bestimmen, aus der er die Daten entladen will. Ein normaler Cursor ist hier einfach nicht möglich, weil dann ja bereits zum Deklarationszeitpunkt das Select voll ausformuliert werden müsste. Natürlich hätte hier auch ein EXECUTE IMMEDIATE verwendet werden können, aber genau da kristallisiert sich der Unterschied heraus: Ein EXECUTE IMMEDIATE füllt im besten Fall eine PL/SQL-Tabelle und liefert diese als Parameter zurück (erhöhter Speicherbedarf), während ein REF CURSOR als Pointer auf einen Speicherbereich an den Aufrufer zurückgeliefert werden kann.

Stark oder Schwach?

ORACLE unterscheidet die schwachen (weak) von den starken (strong) REF Cursor. Der RÜckgabewert eines schwachen REF Cursor ist zur Compile-Zeit unbekannt. Dafür haben wir den Benefit, während des Ablaufs des Programms das SELECT-Statement dynamisch zusammenzustellen, ohne uns Gedanken über den Rückgabetyp zu machen. Starke REF Cursor müssen beim Typisieren auch den Rückgabetyp enthalten. Das Select, das der REF Cursor ausführt, muss sich an diesen Rückgabetyp halten. Warum sollte man nun starke REF Cursor verwenden, wenn doch schwache mehr Dynamik zu lassen. Starke REF Cursor werden ein klitzekleines bisschen schneller ausgeführt als schwache. Messung vom Tom Kyte ergeben einen Performance-Vorteil von ca. 7%. Ich finde zwei andere Argumente viel wichtiger, starke REF Cursor zu verwenden: da bei einem starken REF Cursor der Rückgabetyp schon zur Compile-Zeit bekannt ist, kann erstens bei einem Describe auch dieser Typ eingesehen werden. Zweitens würden Fehler beim Fetch aus diesem Cursor bereits zur Compile-Zeit aufgedeckt werden.

Nun aber der Code

create or replace package my_package is
   type schwacher_cursortyp is ref cursor;
   type starker_cursortyp is ref cursor return emp%ROWTYPE;
   function gib_schwachen_cursor return schwacher_cursortyp;
   function gib_starken_cursor return starker_cursortyp; 
end;
/
SQL> desc my_package
FUNCTION GIB_SCHWACHEN_CURSOR RETURNS REF CURSOR
FUNCTION GIB_STARKEN_CURSOR RETURNS REF CURSOR
                                RECORD                  OUT
     EMPNO                      NUMBER(4)               OUT
     ENAME                      VARCHAR2(10)            OUT
     JOB                        VARCHAR2(9)             OUT
     MGR                        NUMBER(4)               OUT
     HIREDATE                   DATE                    OUT
     SAL                        NUMBER                  OUT
     COMM                       NUMBER(7,2)             OUT
     DEPTNO                     NUMBER(2)               OUT
SQL>

Hier sieht man sehr schön, dass der Typ des starken Cursors ja bereits zur Compile-Zeit bekannt ist, ich also die Schnittstelle, sprich den Rückgabetypen sehen kann. Ein schwacher Typ kann ja sonst was zurückliefern, der Rückgabetyp wird erst zur Laufzeit festgelegt. Also bleibt ORACLE hier stumm und teilt lediglich mit, da kommt ein Cursor zurück.

Seit der Version 9i muss man diese Funktionen nicht in einer Package kapseln (anders hätte man ja keine Chance, erst den Typ zu deklarieren), sondern wir können einen Built-In-Typen benutzen:

create function gib_refcursor return sys_refcursor is
...
end;
/

Der Typ SYS_REFCURSOR ist der Natur der Sache nach ein schwacher REF Cursor.

Und wie kann so ein REF Cursor nun verwendet werden? Während wir bei einem normalen Cursor ja bereits zum Deklarationszeitpunkt das SELECT-Statement vorgeben, geschieht dies bei einem REF-Cursor zur Laufzeit.

create or replace procedure gib_daten (flag number default 0) is
 type refcurtype is ref cursor;
 myrefcur refcurtype;
 helper varchar2(20);
begin
  if flag = 0 then
  open myrefcur for select dname from dept;
 else
  open myrefcur for select ename from emp;
 end if;
 loop
  fetch myrefcur into helper;
  exit when myrefcur%NOTFOUND;
  dbms_output.put_line(helper);
 end loop;
end;
/

Diese Prozedur schreibt also je nach Aufruf entweder Abteilungsnamen oder Mitarbeiternamen auf den Bildschirm. Ein normaler Cursor könnte dies nicht. Baer mit EXECUTE IMMEDIATE hätte ich das doch auch realisieren können? Damit sind wir bei einem weiteren Benefit: In den seltensten Fällen wollen wir ja Daten auf den Bildschirm schreiben. Für gewöhnlich wollen wir Daten verarbeiten. Würde ich eine Funktion schreiben, die mir das Ergebnis eines Selects zurückliefert, müsste ich erst die Daten in eine PL/SQL-Tabelle schreiben und die gefüllte Tabelle zurückliefern. Bei grossen Datenmengen ein ziemlich grosses Übergabe-Argument. Mit dem REF Cursor habe ich die Chance, nicht die Daten, sondern lediglich den Pointer auf die Daten zurückzuliefern. Wie mit dem Pointer umgegangen wird, entscheidet dann der Aufrufer. Ein Pointer ist ein kleines Übergabe-Argument. Das könnte dann so aussehen:

create or replace function gib_werte (arg number) return sys_refcursor is
  myrefcur sys_refcursor;
begin
  if arg = 0 then
   open myrefcur for select ename from emp;
  else
   open myrefcur for select dname from dept;
  end if;
  return (myrefcur);
end;
/

Der Aufrufer entscheidet jetzt allein, wie er mit den Daten umgehen will. Vielleicht braucht er ja nur den ersten Datensatz. Das könnte so aussehen:

declare
  mycur sys_refcursor;
  wert varchar2(20);
begin
  mycur := gib_werte(0);
  fetch mycur into wert;
  dbms_output.put_line(wert);
  mycur := gib_werte(1);
  fetch mycur into wert;
  dbms_output.put_line(wert);
end;
/

Dieser Block liefert einmal den ersten Mitarbeiternamen aus der Menge aller, dann den ersten Abteilungsnamen aus der Menge aller zurück.

Ich habe einen REF Cursor benutzt, um ein SQLDUMP in ORACLE zu implementieren, also eine Prozedur, die die Daten einer Tabelle in Semikolon-separierter Form zurückliefert. Dazu soll der Aufrufer den Namen des Tabelle übergeben, das SQL-Statement konstruiert die Prozedur allein. Siehe hier.