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;
Machbar ist das z.B. mit folgender Funktion:
Die Funktion erhält einen Cursor, ermittelt die Datensätze (der Typ muss natürlich passen, in diesem Beispiel muss das Select Number-Werte zurückliefern), verarbeitet die Datensätze und liefert die Quersumme zurück.
create or replace function quersumme ( mycur sys_refcursor) return varchar2 is
erg number := 0;
quer number := 0;
dummy number;
begin
loop
fetch mycur into dummy;
exit when mycur%notfound;
erg := erg + dummy;
end loop;
-- Algo für Bildung der Quersumme
while (erg > 0) loop
quer := quer + mod(erg,10);
erg := trunc(erg/10);
end loop;
-- Ende Algo
return (quer);
end;
/
Der Aufruf erfolgt z.B. wie folgt:
SQL> select quersumme(cursor(select 12345 from dual)) from dual; QUERSUMME(CURSOR(SELECT12345FROMDUAL)) ------------------------------------------------------------------ 15
Oder (das ist ja das Ziel der Sache) mit
SQL> select quersumme(cursor(select sal from emp where deptno=10)) from dual; QUERSUMME(CURSOR(SELECTSALFROMEMPWHEREDEPTNO=10)) ----------------------------------------------------------------------------- 20