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 where e.empno=mitnr;
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...

Es will partout nicht gelingen, eine cursor expression als OUT Argument an den Aufrufer zurückzugeben. (Wer eine Lösung hat, nur her damit!).

Das ist meiner Ansicht nach kein Auskriterium für Cursor Expressions. Sie erlauben ja immer noch sehr viel saubereren Code. Ein Workaround ist z.B.

create or replace type emp_name_Tab is table of varchar2(20);
/
create or replace procedure gib_name (mitnr emp.empno%TYPE, 
                                                      mitname out emp.ename%TYPE, 
                                                      kollegen_Tab out emp_name_tab) is
kollegen sys_refcursor;
cursor c1 is
    select y.ename, 
    cursor (select ename from emp x where x.deptno=y.deptno and x.ename<>y.ename) 
    from emp y where y.empno=mitnr;
begin
    open c1;
    fetch c1 into mitname, kollegen;
    fetch kollegen bulk collect into kollegen_Tab;
end;
/

declare
    mitname emp.ename%TYPE;
    kollegen_Tab emp_name_tab;
    kollegen_string varchar2(2000);
begin
    gib_name(7803,mitname, kollegen_tab);
    dbms_output.put_line('Mitarbeiter: '||mitname);
    for i in kollegen_Tab.first .. kollegen_Tab.last loop
        kollegen_String := kollegen_string ||kollegen_tab(i)||',';
    end loop;
    dbms_output.put_line(substr('Kollegen: '||kollegen_String,1,length(kollegen_string)+9));
end;
/ 

Wo liegen nun praktische Verwendungsmöglichkeiten von Cursor-Expressions?

Naja, Abstraktion ist sicherlich ein erstes Argument. Denn ich kann zwar (nach meiner Kenntnis) keine Cursor Expression zurückliefern, aber sehr wohl einer Funktion übergeben. Beispiel:

Vorstellbar ist natürlich eine Funktion quersumme, die wie folgt benutzt wird:

declare
  summe number;
begin
  select sum(sal) into summe from emp where deptno=10;
  dbms_output.put_line(quersumme(summe));
end;
/

Wieviel schöner wäre allerdings eine Funktion, die wie folgt aufgerufen wird:

begin
 dbms_output.put_line(quersumme(cursor( select sal from emp where deptno=10 )));
end;
/

Oder noch besser:

select quersumme(cursor(select sal from emp where deptno=10)) from dual;

Sie müssen sich registrieren, um den Artikel ganz zu lesen