martinbulinski.de

Die PL/SQL-Welt ist gross, in den vorangegangenen Lektionen habt ihr einen grossen Teil dieser Welt kennengelernt und damit einen grossen Teil des täglichen Handwerkzeugs vermittelt bekommen. Langsam wird es speziell. Cursor Expressions sind bspw. ein Feature, das man nicht alltäglich benutzt.

Eine Cursor-Expression ist ein Cursor, der Teil eines anderen Cursor ist. Er ist damit ein idealer Kandidat, um 1:n-Beziehungen speicherplatzsparend aufzulösen und z.B. an einen Aufrufer zurückzuliefern. Nehmen wir folgendes Beispiel: Ich benötige eine Prozedur, die mir für eine gegebene Mitarbeiternummer den dazugehörigen Namen zurückliefert (mittlerweile für alle hier ein leichtes Spiel). Aber sie soll mir auch die Kollegen des Mitarbeiters zurückliefern. Am liebsten in Form eines Cursors, schliesslich sollen die Schnittstellen schlank bleiben.

Ich stelle mir den Aufruf ungefähr so vor:

declare
 kollegennamen_cur sys_refcursor;
 mitarbeitername emp.ename%TYPE;
 kollegenname emp.ename%TYPE;

begin
 gib_name(7902,mitarbeitername,kollegennamen_cur);
 dbms_output.put_line(mitarbeitername);
 loop
 fetch kollegennamen_cur into kollegenname;
  exit when kollegennamen_cur%NOTFOUND;
  dbms_output.put_line(kollegenname);
 end loop;
 end;
/

Nichts leichter als das. Wir kennen die dafür notwendige Technik ja bereits. Dann müsste nämlich die aufgerufene Prozedur implementiert werden wie folgt:

create or replace procedure gib_name 
    (mitnr number, 
     mitname out emp.ename%TYPE, 
     kollegen_cur out sys_refcursor) is
  begin
   select ename into mitname from emp where empno=mitnr;
   open kollegen_cur for select ename from emp where deptno =
                                 (select deptno from emp where ename = mitname)
                                 and ename <> mitname;
end;
/

Gut, das funktioniert,Bulinski. So what? Wo ist jetzt der Benefit? Schauen wir uns mal folgende Idee an, die Prozedur zu implementieren:

create or replace procedure gib_name     
(mitnr number,      
 mitname out emp.ename%TYPE,      
 kollegen_cur out sys_refcursor) is  
   cursor c1 is select ename, cursor(select ename from emp 
                       where e.deptno=deptno 
                       and e.ename <> ename) kollegen
  from emp e;
begin   
  open c1;
  fetch c1 into mitname, kollegen_cur;
end;
/

Schauen wir uns den Wunsch an: Wir wollen einen Mitarbeiternamen erhalten und einen Cursor auf die Kollegen des Mitarbeiters. Der Alias "kollegen" wäre hier nicht unbedingt notwendig, soll aber zeigen, dass es sich einmal um ein Element handelt, nämlich den Mitarbeiternamen, im Falle der Kollegen aber um eine Menge. Der Cursor C1 liefert also sowohl einen skalaren Wert zurück wie auch eine Menge, bekannt gemacht durch die Funktion CURSOR.

Also müssen wir beim Fetch auch entspr. Variablen bereitstellen. Einmal eine skalare Variable, um den Mitarbeiternamen aufzunehmen, einmal einen REF Cursor, um einen Pointer auf dessen Kollegen aufzunehmen.

Na dann müsste ja der Aufrufer gar nicht verändert werden. Im letzten Beispiel liefern wir ja im Grunde das gleiche zurück: einen skalaren Wert und einen Pointer auf eine Menge von Werten.

Wer jetzt meint, das ginge, der irrt... mehr später...