Weitere Beispiele zu Cursor Expressions

Cursor Expressions sind so übel nicht!

Sie erlauben äußerst dynamische Konstrukte, die selbst mit analytischen Funktionen nicht so einfach realisierbar sind:

Beispiel:

Hier nutze ich eine Funktion is_related_to, der ich die Bedindung, sprich das SELECT, das bestimmte Mitarbeiter ermitteln soll, beim Aufruf übergeben kann. Die Funktion sorgt für eine Aneinanderreihung der Werte als kommaseparierte Liste.

Das mächtige an der Funktion ist, dass mir die kommaseparierte Liste komplett dynamisch anhand meines Select-Statements zusammengebaut wird.

 

SQL> col related format a60
SQL> -- Wer sind die Mitarbeiter, die in der gleichen Abteilung arbeiten wie Scott
SQL> select ename, is_related_to (cursor(select ename from emp where deptno=x.deptno)) related 
        from emp x where ename = 'SCOTT';
ENAME      RELATED
---------- ------------------------------------------------------------
SCOTT      SMITH,JONES,SCOTT,ADAMS,FORD

SQL> -- Wer sind die Mitarbeiter, die den gleichen Beruf haben wie Scott
SQL> select ename, is_related_to (cursor(select ename from emp where job=x.job)) related 
        from emp x where ename = 'SCOTT';
ENAME      RELATED
---------- ------------------------------------------------------------
SCOTT      SCOTT,FORD

SQL> -- Wer sind die Mitarbeiter, die das gleiche Gehalt haben wie Scott
SQL> select ename, is_related_to (cursor(select ename from emp where sal=x.sal)) related 
        from emp x where ename = 'SCOTT';
ENAME      RELATED
---------- ------------------------------------------------------------
SCOTT      SCOTT,FORD

SQL> -- Wer sind die Mitarbeiter, die den gleichen Vorgesetzten haben
SQL> select ename, is_related_to (cursor(select ename from emp where mgr=x.mgr)) related 
        from emp x where ename = 'SCOTT';
ENAME      RELATED
---------- ------------------------------------------------------------
SCOTT      SCOTT,FORD

SQL> -- Wer sind die Mitarbeiter, die weniger verdienen als Scott
SQL> select ename, is_related_to (cursor(select ename from emp where sal<x.sal)) related 
        from emp x where ename = 'SCOTT';
ENAME      RELATED
---------- ------------------------------------------------------------
SCOTT      SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,TURNER,ADAMS,JAMES,MILLER

Um so etwas zu realisieren, könnte man folgende Implementierung mit Hilfe von Cursor Expression nutzen:

 

create or replace function is_related_to ( mycur sys_refcursor) return varchar2 is
  dummy varchar2(20);
  erg_String varchar2(200);
begin
    loop
      fetch mycur into dummy;
      exit when mycur%notfound;
      erg_string := erg_string||dummy||',';
    end loop;
    return (substr(erg_string,1,length(erg_string)-1));    
end;
/

Benutzung von Cursor Expression

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

Dynamisches SQL mit REF Cursor

Was ist ein 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.

Dynamisches SQL mit EXECUTE IMMEDIATE

Bisher waren wir in unseren PL/SQL Programmen auf statisches SQL angewiesen. Eine gewisse Dynamik brachte der Cursor, der mit Hilfe von Parametern die SELECT Anweisung innerhalb bestimmter Grenzen anpassen konnte. Aber auch das ist unbefriedigend. Und spätestens, wenn Sie ein DDL-Kommando innerhalb von PL/SQL ausführen wollen, sind wir komplett hilflos.Gründe für den Einsatz von dynamischem SQL gibt es viele: Vielleicht steht der Name der Tabelle, aus der selektiert werden soll, erst zur Laufzeit fest, oder mehr noch, der Name der Tabelle soll als Argument beim Aufruf einer Prozedur übergeben werden. Dynamisches SQL kann benutzt werden, um alle meine Tabellen zu löschen. Besitze ich 50 Tabellen und will diese loswerden, tippe ich nicht 50mal drop table ein, sondern schreibe mir einen PL/SQL-Block, der das für mich übernimmt. Dynamisch.

Eigentlich müßte der Artikel „Dynamische Programmierung“ heißen, denn es ist nicht nur dynamisches SQL, sondern auch dynamisches PL/SQL möglich. Früher hat man dynamisches SQL ausschliesslich über das Package DBMS_SQL realisieren können, heute ist es einfacher denn je: EXECUTE IMMEDIATE.

 

Ein paar Dinge vorweg:

  • die Kommandos, die wir mit EXECUTE IMMEDIATE ausführen, sind ganz normal eingebettet in unsere Transaktion. Führen wir ein dynamisches DML-Statement aus, muss es committed werden, damit andere die Änderung sehen. Führen wir dynamisches DDL aus, wird automatisch committed, auch das, was wir vorher mit DML-Anweisungen gemacht haben.
  • Führen wir dynamisches SQL aus, terminieren wir unseren Befehl NICHT mit Semikolon
  • Führen wir dynamisches PL/SQL aus, terminieren wir wie gehabt jede Anweisung mit Semikolon, inklusive END;
  • Hüten Sie sich, einen SQL-Befehl mit Hilfe von String-Konkatenation zusammenzustellen. Erstens gibt es Probleme mit den Datentypen, zweitens könnten Hacker Ihren Code mit SQL-Injection-Versuchen aushebeln, drittens holen Sie nicht das Beste an Performance raus. Nutzen Sie stattdessen Bindevariablen.

Jetzt aber los:

EXECUTE IMMEDIATE ist eine ausführbare Anweisung, sie kann also nur im ausführbaren Teil eines Blocks oder im Fehlerbehandlungsteil stehen. EXECUTE IMMEDIATE wird gefolgt von einer Zeichenkette, entweder statisch in einfachen Hochkommata, oder in Form einer Variablen.

declare
  cmd_string varchar2(200) := 'delete from scott.emp';
begin
  execute immediate cmd_string;
  commit;
  execute immediate 'delete from scott.dept';
  rollback;
end;

Wenn ich einen Befehl ausführen kann, der in einer Variablen steht und ich den Wert der Variablen ja selber bestimmen kann, dann geht auch das hier. Ein Block, der alle meine Tabellen dropt (Achtung, DDL committed automatisch, also Vorsicht beim Ausprobieren)

begin
  for rec in (select table_name from user_tables) loop
     execute immediate 'drop table '||rec.table_name||' cascade constraints';
  end loop;
end;

Die Technik, Befehle durch einfache Variablen-Konkatenierung wie oben zusammenzusetzen, hat drei große Nachteile:

  1. Wir müssen über Datentypen nachdenken.
  2. das SQL-Kommando wird durch den SQL-Interpreter immer wieder neu geparst,  sobald der Befehl „anders“ aussieht als ein schon mal ausgeführter. Und anders aussehen heißt, sobald der Befehlsstring sich von einem bereits ausgeführten unterscheidet.
  3. Wenn man die Stringkonkatenation ungeschickt anstellt, implementiert man mögliche Sicherheitslücken. Und zuguterletzt muss man dem Aufrufer klarmachen, dass er Zeichenketten in einer Zeichenkette mit doppelten Hochkommata übergeben muss, also z.B. execute del_employee(“’SCOTT“‘)

Diese drei Nachteile möchte ich an einer stored procedure demonstrieren. Sie hat die Aufgabe, das Gehalt eines Mitarbeiters, dessen Name übergeben wird, um 10% anzuheben. Um genau zu sehen, was passiert, lasse ich das auszuführende Statement auch noch mal auf den Bildschirm schreiben.

create or replace procedure 
gehaltserhoehung (mitname scott.emp.ename%TYPE) is
  stmt varchar2(200);
begin
  stmt := 'update scott.emp set sal = sal * 1.1 '||
          'where ename = '||mitname;
  dbms_output.put_line(stmt);
  execute immediate stmt;
end;
/

So, jetzt die Nachteile dieser Art der Programmierung:

Datentypprobleme

Grund 1 ist einfach nachzuweisen. Mit einem DESCRIBE sehen wir die Schnittstelle der Procedure, sie sagt eindeutig, wir sollen ein Argument mitname vom Typ VARCHAR2(10) übergeben. Ich probiers und bin enttäuscht.

SQL> exec gehaltserhoehung('ALLEN')
update scott.emp set sal = sal * 1.1 where ename = ALLEN
BEGIN gehaltserhoehung('ALLEN'); END;
*
FEHLER in Zeile 1:
ORA-00904: "ALLEN": ung³ltiger Bezeichner
ORA-06512: in "SYSTEM.GEHALTSERHOEHUNG", Zeile 8
ORA-06512: in Zeile 1

Zum Glück habe ich mir das Statement selbst auch noch mal auf den Bildschirm schreiben lassen und so erkenne ich das Dilemma: Ich habe zwar eine Stringvariable übergeben, aber beim Konkatenieren gehen die einfachen Hochkomma verloren, sie markieren ja lediglich den Beginn und das Ende einer Stringkonstante. Bei der Übergabe an den SQL-Interpreter sind die Hochkomma aber zwingend notwendig, denn in meinem Beispiel glaubt der SQL-Interpreter, ALLEN wäre ein Ausdruck oder eine Spalte der Tabelle, nein, es muss ja auch heissen ‚ALLEN‘.

Wir wissen, dass ein Hochkomma in einem String dargestellt werden kann, indem man es einfach doppelt angibt, also

SQL> exec gehaltserhoehung(' ''ALLEN'' ')
update scott.emp set sal = sal * 1.1 where ename =  'ALLEN'

PL/SQL-Prozedur erfolgreich abgeschlossen.

Schön wäre, auf derlei Feinheiten keine Rücksicht nehmen zu müssen.

Schlechte Performance

Für den Grund 2 „schlechte Performance“ muss ich weiter ausholen: saloop ausgedrückt, parst der SQL-Interpreter ein SQL-Statement immer dann, wenn es nicht bereits vorgeparst im RAM des Servers steht. Und das kostet natürlich Zeit. Und die will man einsparen. Um zu sehen, welche Statements schon vorgeparst im RAM stehen, kann man die DataDictionary-View v$sqlarea abfragen. Hier stehen gerade für das Thema Tuning eine Reihe wichtiger Informationen zur Verfügung. Wir beschränken uns auf die Anzahl Ausführungen, die Spalte executions. Nehmen wir also an, ich hätte bereits drei Mitarbeitern ein neues Gehalt verpaßt, dann könnte da stehen:

SQL> col sql_text format a60 word
SQL> col executions format 99 heading EXEC
SQL> select sql_Text, executions from v$sqlarea
  2  where sql_text like '%update scott.emp%';
SQL_TEXT                                                     EXEC
------------------------------------------------------------ ----
update scott.emp set sal = sal * 1.1 where ename = 'KING'       1
update scott.emp set sal = sal * 1.1 where ename = 'JAMES'      1
select sql_Text, executions from v$sqlarea where sql_text       1
like '%update scott.emp%'
update scott.emp set sal = sal * 1.1 where ename = 'ALLEN'      1

Hier kann man erkennen, dass nicht das gleiche Statement immer wieder ausgeführt wurde, sondern das der Parser drei verschiedene Statements erkannt hat, obwohl sich diese ja eigentlich nur im WHERE-Teil unterscheiden. Der Ausführungplan, der im Rahmen des Parsens erstellt wird, ist wahrscheinlich auch immer der gleiche: Entweder erfolgt ein Zugriff mit Hilfe eines Index oder eben ein Full-Table-Scan. In solchen Situationen kann man Parse-Zeiten sparen und das Statement verwenden, das bereits geparst wurde. Der Schlüssel dazu sind sogenannte Bindevariablen.

Sicherheitslücken / SQL-Injection

Grund 3, die oben gezeigte Prozedur nicht auf diese Art und Weise zu implementieren. An diesem Beispiel ist das auch schnell erklärt. Ein Hacker, aber auch jemand, der allen eine 10%ige Gehaltserhöhung verpassen möchte, könnte die Prozedur wie folgt ausführen:

SQL> exec gehaltserhoehung(' ''ALLEN'' or 1=1')
update scott.emp set sal = sal * 1.1 where ename =  'ALLEN' or 1=1
PL/SQL-Prozedur erfolgreich abgeschlossen.

Anhand der Ausgabe von DBMS_OUTPUT sehen wir, was passiert ist. Die WHERE-Bedingung wurde „ausgehebelt“. Na, das war nicht die Intension des Programmierers, denn beim Aufruf wurde eine Bedingung in den Where-Teil des Update-Kommandos „eingeschmuggelt“, die immer TRUE ergibt und damit alle Datensätze updated. Das gleiche könnte passieren, wenn z.B. nur bestimmte Buchungsbelege mit einer angegebenen Belegnummer gelöscht werden sollen, beim Aufruf mit Hilfe dieser Technik dann aber alle Belege – vielleicht aller Geschäftsjahre – gelöscht werden. Sie werden es nicht glauben: auch hier helfen oben erwähnte Bindevariablen.

Bindevariablen in EXECUTE IMMEDIATE

Bindevariablen lösen alle oben genannten Probleme und werden wie folgt verwendet. Eine Bindevariable wird in einem SQL-Statement mit einem Doppelpunkt vor dem Variablennamen gekennzeichnet. Der Parser parst das Statement mit der Variablen, die Variablenersetzung durch den konkreten Wert erfolgt erst vor der Ausführung. Das heißt, der Parser erkennt am stets gleichen Variablennamen, dass er das Statement schon mal übersetzt hat und benutzt den im RAM stehenden Ausführungsplan. Wenn Bindevariablen benutzt werden, müssen wir uns über Datentypen und Wertübergaben nicht mehr kümmern. Die korrekte Ersetzung der Variablen durch die Konstante erfolgt automatisch. Wir brauchen also nicht mehr Strings in zweifache Hochkommata schreiben, denn allein der Typ String sorgt für korrekte Ersetzung. Und aus dem gleichen Grund ist auch SQL-Injection kaum mehr möglich. Richtig hätte ich oben genannte Prozedur wie folgt implementiert, um den Problemen aus dem Weg zu gehen:

Die Using-Klausel

create or replace procedure 
gehaltserhoehung (mitname scott.emp.ename%TYPE) is
begin
  execute immediate 'update scott.emp set sal = sal * 1.1'||
  'where ename = :bindvar' using mitname ;
end;
/

Die Variable :bindvar habe ich nach eigenem Gusto benannt, wichtig ist der Teil USING. Hier wird AUSSERHALB des Strings, der das SQL-Statement enthält, festgelegt, welcher tatsächliche Wert bei der Ausführung an die Stelle des „Platzhalter“ :bindvar tritt.

Prüfen wir unsere drei Probleme. Datentypprobleme: Sind verschwunden. Wir rufen die Prozedur auf, übergeben ganz normal einen String. Das dieser im SQL-Statement in einfachhen Hochkomma stehen muss, weiss PL/SQL anhand des Datentypen. Dadurch verflüchtigt sich auch das Problem mit SQL-Injection, denn es gibt bspw. keinen Datensatz, wo in der Spalte ename der Wert ‚Allen or 1=1‘ steht. Und zuguterletzt profitieren wir von reduzierten Parseaktivitäten.

SQL> exec gehaltserhoehung('ALLEN')
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> exec gehaltserhoehung('''ALLEN''')
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> exec gehaltserhoehung('''ALLEN'' or 1=1')
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> exec gehaltserhoehung('SMITH')
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> select sql_Text, executions from v$sqlarea
  2  where sql_text like '%update scott.emp%';
SQL_TEXT                                                     EXEC
------------------------------------------------------------ ----
update scott.emp set sal = sal * 1.1where ename = :bindvar      4
select sql_Text, executions from v$sqlarea where sql_text       1
like '%update scott.emp%'

Erläuterung: Das erste Statement erhöht das Gehalt von ALLEN, das zweite updated nichts, denn es gibt keinen Mitarbeiter ‚ALLEN‘ (mit Hochkomma), genausowenig wie es einen Mitarbeiter ALLEN or 1=1 gibt. SMITH ist wiederum ein Mitarbeiter, der in der Tabelle existiert, sein Gehalt wird um 10% erhöht. Zuguterletzt werfen wir wieder einen Blick in v$sqlarea und erkennen, dass der Parser sagt, viermal wurde das Statement mit Bindevariable ausgeführt. Einmal hat er es geparst, dreimal konnte er die vorgeparste Form verwenden. Na, das nenn‘ ich mal einen Gewinn!

Was, wenn ich mehrere Bindevariablen verwenden möchte? Kein Problem, wir zählen sie im USING-Teil auf in der Reihenfolge, in der sie entsprechend der Position im SQL-Statement verwendet werden sollen. Alle Mitarbeiter in der Abteilung 10 deren Namen mit A beginnt, werden gelöscht.

begin
execute immediate 'delete from scott.emp where deptno=:var1 '||
      'and ename like :var2' using 10, 'A%';
end;
/

Nun ja, das ist ja erst die halbe Wahrheit, den dynamisches SQL bedeutet nicht nur, Variablen für DML-Statement zu übergeben, sondern wir wollen vielleicht ein SELECT-Statement dynamisch zusammenstellen und die Ergebnismenge in PL/SQL übernehmen. Die Technik bleibt die gleiche, wir nutzen Bindevariablen.

Die INTO-Klausel

declare
  abtnr number :=10;
  summe number; 
begin
  execute immediate' select sum(sal) from scott.emp where deptno=:var1' 
         into summe using abtnr;
end;

Hier ist lediglich wichtig, sich zu merken, dass erst die INTO-Klausel kommt und dann eine eventuelle USING-Klausel. Aber wir müssen uns auch hier Gedanken machen, was passiert, wenn das Statement mehr als einen Datensatz zurückliefert. Obiges Beispiel klappt wieder nur, weil die Gruppenfunktion genau einen Wert liefert und der perfekt in meine skalare Variable passt.

BULK COLLECT INTO-Klausel

Wenn es darum geht, mehrere Zeilen eines dynamischen Select zurückzuerhalten, brauchen wir einen Datentyp, der mehr als eine Zeile aufnehmen kann. Zum Beispiel die PL/SQL-Tabelle. Unsere Syntax ändert sich nur wenig: Anstelle von INTO tritt ein BULK COLLECT INTO.

create or replace procedure 
zeig_namen (abtnr scott.emp.deptno%TYPE) is
  type namentabtyp is table of scott.emp.ename%TYPE index by binary_integer;
  namentab namentabtyp;
begin
  execute immediate 'select ename from scott.emp '||
  'where deptno = :bindvar' 
  bulk collect into namentab
  using abtnr;
  for i in namentab.first .. namentab.last loop
    dbms_output.put_line(namentab(i));
  end loop;
end;
/

Diese Prozedur zeigt alle Mitarbeiternamen einer Abteilung auf dem Bildschirm. Es wird eine PL/SQL-Tabelle definiert, die dann mit bulk collect gefüllt wird. Im Anschluss wird der Inhalt der PL/SQL-Tabelle auf dem Bildschirm in einer Schleife angezeigt.

Der Vollständigkeit halber sei an dieser Stelle erwähnt, dass es auch noch eine RETURNING Klausel gibt. Das wird aber derart selten verwendet, dass ich hier auf eine Erläuterung verzichte.

Arbeiten mit VARRAYs

Ein Varray ist ein weiterer Datentyp, der sowohl in der SQL- wie auch PLSQL-Welt von ORACLE existiert. Nochmal: PL/SQL-Tabellen gibt es nur in PLSQL, nested tables und varrays gibt es auch in SQL, so daß sich die beiden letzteren eignen, Daten dauerhaft in der Datenbank zu speichern.Der Unterschied zwischen Varray und nested table ist ein einfacher: Eine nested table hat alle Charaktereigenschaften einer normalen Tabelle, es handelt sich um eine Menge. Ein Varray hingegen ist eher zu vergleichen mit einer verketteten Liste. Die Position des Elements im Varray ist fix und wird mit Hilfe eines Index referenziert.

Weiter wichtig ist, dass wir in einer nested table keine obere Grenze für die Anzahl Werte haben, wogegen bei einem Array bereits beim Anlegen des TYPS(!) – und nicht erst beim Anlegen der Variablen – die maximale Anzahl von Listenelementen bestimmen müssen.

Ein Varray ist also sinnvoll, wenn Sie eine maximale Obergrenze von Elementen kennen, die das Varray aufnehmen soll. So weiss ich, dass bei einer Lottoziehung (6 aus 49) maximal sechs Zahlen ermittelt werden. Das spricht für den Einsatz eines Varrays. Dagegen spricht, dass andere Lottogesellschaften vielleicht mehr als sechs Zahlen ziehen und mein Datenmodell dort keine Gültigkeit mehr hat. Ein Varray ist die richtige Lösung, wenn es auch auf die Position des Listenelements in der Liste ankommt. Für stochastische Analysen ist es womöglich interessant zu wissen, in welcher Reihenfolge die Zahlen gezogen wurden. Eine nested table kennt keine Reihenfolge, damit könnte ich diese Forderung nicht abbilden. Ein anderes Beispiel: Eine Namensliste, in der die Vornamen einer Person entsprechend der Eintragung im Personalausweis aufgelistet werden sollen (erster Rufname, zweiter Rufname etc.), auch hier muss ich ein Varray nutzen, nur dort kann die Reihenfolge abgebildet werden.

Ähnlich, wie bei nested tables, müssen auch varrays initialisiert werden, bevor sie verwendet werden können. Ein Beispiel, in dem so ziemlich alles schiefgeht:

declare
  type namenarraytyp is varray(4) of varchar2(20);
  n1_array namenarraytyp;
  n2_array namenarraytyp := namenarraytyp('Hans','Detlef','Juergen',
                                               'Walther','Otto');
  n3_array namenarraytyp := namenarraytyp();
begin
  n1_array(1) := 'Helmut';
  n3_array(1) := 'Willi';
end;
/

Die Typdeklaration sieht maximal vier-elementige Namenslisten vor. n1_array wird deklariert, aber nicht initialisiert. Das ist noch kein Fehler, wir können diese Variable aber nicht nutzen. n2_array wird initialisiert, aber bei der Initialisierung habe ich fünf Elemente angegeben. Soviel kann das VArray nicht aufnehmen. Es wird die Ausnahme SUBSCRIPT_BEYOND_COUNT ausgelöst. n3_array wird bei der Deklaration initialisiert, die Liste enthält aber keine Elemente.

Das interessante passiert im ausführbaren Teil: Beide Anweisungen führen zu einem Fehler. Die Wertzuweisung an n1_array liefert die Ausnahme COLLECTION_IS_NULL, weil wir auf ein nicht initialisiertes VArray zugreifen,  die Wertzuweisung an n3_array liefert wieder ein SUBSCRIPT_BEYOND_COUNT. Das VArray wurde bei der Deklaration bereits initialisiert, aber trotzdem kann ich nichts zuweisen. Das ist ein wichtiger Unterschied z.B. gegenüber einer PL/SQL-Tabelle. Das VArray ist zwar in der Lage, bis zu vier Strings aufzunehmen, aber es existiert ja noch gar kein erstes Listenelement, in dem ich ‚Willi‘ speichern möchte. Aha! Wir brauchen also eine Methode, um überhaupt erstmal Listenelemente zu erzeugen und das geht mit EXTEND:

declare
  type namenarraytyp is varray(4) of varchar2(20);
  n_array namenarraytyp := namenarraytyp();
begin
  n_array.extend(2);
  n_array(1) := 'Willi';
  
  n_array.extend(2,1);
  dbms_output.put_line(n_array(4));
end;
/

In obigem Beispiel habe ich eine Variable n_array, die eine Liste von Werten aufnehmen kann, mit der Methode EXTEND erzeuge ich nun zwei leere Listenelemente (die haben den Wert NULL), und dann weise ich dem ersten dieser beiden Elemente den Wert ‚Willi‘ zu. Aber was passiert dann? Die zweite Variante EXTEND zu benutzen, ist die neuen Listelemente mit einem Wert zu initialisieren, den ein anderes Listenelement hat. In meinem Beispiel erweitere ich also mit dem zweiten EXTEND mein VArray auf seine maximale Größe von vier Listeinträgen und initialisiere den dritten und vierten Listeintrag mit dem Wert, der im ersten Element steht. Die Ausgabe auf dem Bildschirm zeigt Willi.

Welche Methoden gibt es noch:

  • COUNT, zählt die Elemente in einer Liste
  • DELETE, löscht alle Elemente einer Liste
  • EXISTS(x), prüft, ob das x-te Listenelement existiert
  • TRIM(x), entfernt die x letzten Elemente der Liste, versucht man Elemente zu entfernen, die gar nicht existieren, wird die Ausnahme SUBSCRIPT_BEYOND_COUNT ausgelöst.

Genau wie nested tables kann man auch VArrays als Typen innerhalb von ORACLE SQL anlegen und so Spalten von Tabellen diesen Typen zuweisen. Ein Select funktioniert genauso wie bei einer nested table mit Hilfe der TABLE-Funktion. Wollten wir ein Varray jedoch in irgendeiner Art und Weise verändern (so eine Art Insert, Update oder Delete einzelner Listenelemente), müssten wir Zugriff auf die oben genannten Methoden haben. Diese aber existieren in SQL nicht, sondern nur in PL/SQL. Ergo: Um VArrays zu ändern, muss erst das VArray aus der Tabelle in ein PL/SQL-Programm gelesen werden, dort verändert werden und dann wieder zurückgeschrieben werden. Dies ist ein erheblicher Mehraufwand, der bei Änderungen am Datenbestand zu Performance-Einbußen führt. Geht es in erster Linie um Select-Performance, können wir mit VArrays (genauso wie mit nested tables) ja notwendige Joins von zwei Tabellen verhindern, was durchaus zu Performance-Gewinnen führen kann.

Gegenüber dem Einsatz von Nested Tables (die eher zu einer Performance-Verschlechterung geführt haben), ist der Zugriff auf varrays in einer Tabelle interessant: Hier müssen intern nicht zwei Tabellen gejoint werden, sondern das varray ist wirklich Teil des Datensatzes. Greift ORACLE also auf den Datensatz zu steht gleichzeitig auch das Varray zur Verfügung. Damit ergeben sich – allerdings geringe – Vorteile gegenüber dem Join zweier Tabellen. Nachteil: Nur ein Select könnte mit dem Einsatz von Varrays beschleunigt werden, ein Update, Insert und Delete läuft viel langsamer. Warum? Nun, wir haben zum Verändern eines Varrays, anders als bei nested tables, keine DML Operationen zur Verfügung. Wir müssen erst das Varray lesen, dann die Elemente mit PL/SQL ändern, um das veränderte Varray wieder abzuspeichern. Hier ist also der Einsatz von PL/SQL unbedingt nötig, wogegen ich mich bei der Arbeit mit nested tables auch auf ORACLEs SQL-Syntax beschränken kann.

 

Nested tables mit SQL bearbeiten

Um von nested tables zu profitieren, muss nicht unbedingt PL/SQL mit an Bord. Im Gegenteil: wann immer man auf PL/SQL verzichten kann, sollte man dies allein aus Performance-Gründen tun (mit einigen Ausnahmen).Nested tables verhalten sich in ORACLE SQLs grundsätzlich so wie normale Tabellen. Schliesslich sind sie das ja auch: die Datensätze haben keine Reihenfolge, sie sind lückenlos und können in ihrer Anzahl beliebig wachsen. Die nested table wird wie eine „normale“ ORACLE-Tabelle gespeichert, allerdings können wir nicht direkt darauf zugreifen, sondern benötigen die Tabelle, deren Spalte die nested table ist.

Ein Beispiel:

SQL> create type telefonlistentyp is table of varchar2(20);
  2  /
Typ wurde erstellt.
SQL> create table mitarbeiter 
  2     (name varchar2(20), telefon telefonlistentyp)
  3     nested table telefon store as telefontab tablespace users;
Tabelle wurde erstellt.
SQL> insert into mitarbeiter values 
2       ('Susi',telefonlistentyp('030/12345','030/45678'));
SQL> insert into mitarbeiter values 
2       ('Helga',telefonlistentyp('040/4321','040/8765'));

Selects auf nested tables

Beim Selektieren behalten wir im Hinterkopf, dass die Spalte telefon selbst wieder eine Tabelle ist. Ein ordinäres SELECT * FROM MITARBEITER zeigt, dass es sich um einen komplexen Datentyp handelt: wir sehen den Konstruktor (den Typ) und die enthaltenen Daten. Sieht nicht schön aus.

Weil die Spalte telefon eine nested table ist, können wir die darin enthaltenen Daten wie eine normale Tabelle anzeigen mit folgendem select. Wichtig ist, dass der Tabellenausdruck nur eine nested table zurückliefern darf.

select * from
table(select telefon from mitarbeiter
        where name = 'Susi');

 

Um alle Telefonnummern von allen Mitarbeitern zu erhalten, benötigen wir eine andere Technik, die als „unnesting collections“ bezeichnet wird: Wir müssen die Tabelle dazu mit der nested table joinen:

SQL> select name, ttab.* from mitarbeiter mtab, table(mtab.telefon) ttab;
NAME                 COLUMN_VALUE
-------------------- --------------------
Susi                 030/12345
Susi                 030/45678
Helga                040/4321
Helga                040/8765

 

Es handelt sich hierbei wirklich um einen Join, es gilt also das typische Verhalten bzgl. Inner und outer joins. Hier ein Beispiel:

SQL> insert into mitarbeiter values 
2       ('Helmut',telefonlistentyp());
SQL> insert into mitarbeiter values 
2       ('Willi',null);
SQL> insert into mitarbeiter values 
        ('Jochen',telefonlistentyp(null));
SQL> select name, ttab.* from mitarbeiter mtab, 
2       table(mtab.telefon) ttab;
NAME                 COLUMN_VALUE
-------------------- --------------------
Susi                 030/12345
Susi                 030/45678
Helga                040/4321
Helga                040/8765
Jochen

 

Susi und Helga sind klar, Jochen wird ausgegeben, denn er hat eine Telefonnummer: NULL. Für Willi ist keine Telefonliste hinterlegt, und Helmuts Telefonliste ist leer. Sollen diese Mitarbeiternamen trotzdem ausgegeben werden, muß ein OUTER JOIN her. Syntax:

SQL> select name, ttab.* from mitarbeiter mtab, table(mtab.telefon)(+) ttab;

 

Hierbei ist interessant, dass die neue Join-Syntax mit dem Schlüsselwort JOIN nach meiner Kenntnis nicht unterstützt wird, sondern die „alte“ (+)-Notation verwendet werden muss. Beim Versuch, die neue Join-Syntax zu verwenden, stoßen wir auf die in meinem Beispiel nicht vorhandene Verknüpfungsbedingung, die im ON, bzw. USING Teil des Joins benutzt werden muß. Verwenden wir anstelle dessen einen Cross-Join, erhalten wir auch nicht das gewünschte Ergebnis. Anstelle dessen würden alle Telefonnummern mit allen Namen verknüpft werden.

Update auf nested tables

Bei einem Update müssen wir immer unterscheiden, ob wir die Spalte, die eine nested table ist, updaten wollen oder ob wir Inhalte der nested table updaten wollen. Der erste Fall ist simpel, wir updaten die Spalte und verwenden wieder den Konstruktor, – sprich Datentyp -, um der nested-table-Spalte eine neue nested table zuzuweisen (auweia, ich hoffe, ich drücke mich verständlich aus). Beispiel: elga zieht von Hamburg nach München:

SQL> update mitarbeiter 
  2     set telefon = telefonlistentyp('089/12345','089/6789')
  3     where name = 'Helga';

Der zweite Fall, nämlich das verändern einer Telefonnummer vom Helga, ist syntaktisch schon wieder anders. Wir müssen wieder im Hinterkopf behalten, dass es sich bei einer nested table auch um eine ordinäre Tabelle handelt. Und diese gilt es zu referenzieren. Nehmen wir also an, eine von Helgas Telefonnummern ändert sich von München nach Stuttgart:

SQL> update table(select telefon from mitarbeiter where name = 'Helga')
  2  set column_value='0711/12345' where column_value='089/12345';

Insert auf nested tables

Nun denn, das nächste Statement: Wie sorge ich dafür, dass Helga auch unter einer dritten Telefonnummer erreichbar ist. Wieder gilt, wir fügen nicht in die Mitarbeitertabelle einen Datensatz ein, sondern in die Spalte, die Teil der Mitarbeitertabelle ist. Dabei muss natürlich klar sein, zu welchem Datensatz die nested table gehört. In meinem Beispiel ist Helga jetzt auch wieder in Berlin erreichbar.

SQL> insert into table(select telefon from mitarbeiter where name = 'Helga') 
values ('030/12334');

Delete auf nested tables

Und damit sind wir wieder beim letzten der DML-Statements. Um aus einer nested table einen Datensatz zu entfernen, gilt im Prinzip das gleiche wie oben: wir löschen keinen Datensatz der übergeordneten Tabelle, sondern nur einen der untergeordneten, der nested table. Daher das gleiche Spiel wie bisher:

SQL> delete from table(select telefon from mitarbeiter where name = 'Helga') 
         where column_value like '089/%';

Zählen der Zeilen in nested tables

Um die Anzahl Datensätze/Werte in einer nested table zu zählen, gibt es zwei Möglichkeiten: Zum einen steht uns natürlich Standard-SQL, und damit auch die COUNT-Funktion zu Verfügung. Jochen hat eine Telefonnummer (die null ist, aber mitgezählt wird), Willi hat keine Telefonliste, und Helmut hat eine, die keine Datensätze enthält. Darum liefert COUNT auf Willis und Helmuts Telefonnummern jeweils numerisch null.

SQL> select count(*) from 
   2    table(select telefon from mitarbeiter
   3            where name='Jochen');  
COUNT(*)
----------
         1
SQL> select count(*) from 
   2    table(select telefon from mitarbeiter
   3            where name='Willi');
COUNT(*)
----------
         0

Eine zweite Variante ist, die Funktion CARDINALITY zu benutzen, die die Anzahl Elemente einer nested table zählt. Das Ergebnis unterscheidet sich aber vom COUNT, denn CARDINALITY liefert bei einer leeren nested table (wie bei Helmut) numerisch null, ist jedoch gar keine nested table vorhanden (wie bei Willi), liefert sie NULL.

SQL> select name, cardinality(telefon) from mitarbeiter;
NAME                 CARDINALITY(TELEFON)
-------------------- --------------------
Susi                                    2
Helga                                   2
Helmut                                  0
Willi
Jochen                                  1

 

Nehmen wir an, ein Mitarbeiter hat die gleiche Telefonnummer mehrfach bekommen, wir wollen nur das einfache Auftreten zählen. Gibt es eine Art DISTINCT auch bei nested tables? Ja, hier benutzen wir die Operation SET, um aus einer nested table mit mehrfach vorkommenden Werten eine „echte“ Menge zu machen und doppelte Werte zu verwerfen.

SQL> select name, cardinality(set(telefon)) from mitarbeiter;

Bei Performance-Vegleichen Nested Table vs. zwei „normalen“ Tabellen liess sich allerdings kein Performance-Gewinn feststellen, sondern eher ein Verlust an Performance beim Einsatz von nested tables. Allerdings wurde nicht mit Indexes experimentiert. Ich hätte erwartet, dass die Performance zumindest gleich bleibt, denn schliesslich macht ORACLE intern ja einen Join auf die Tabelle die die nested table Spalte enthält. Aber nein, es wurde sogar schlechter, obwohl der Ausführungsplan gleich war (HASH JOIN in Ermengelung eines Index).

Insofern bleibt die nested table wohl vorerst ein Modellierungs- und Abstraktionselement, ohne dass ein echter Vorteil beim Einsatz von nested tables rausspringt. Beim Einsatz von PL/SQL-Programmen, die die nested tables verarbeiten sollen, sehe ich das wieder anders: Hier existieren interessante Methoden, um Mengenoperationen mit Programmvariablen vorzunehmen. Damit erhält der Mengen-Gedanke auch Einzug in PL/SQL.

Nested tables in der Datenbank

PL/SQL-Variablen sind nett, sie sind sogar in gewissem Sinn persistent, wenn sie in einer Package „global“ definiert sind. Diese Persistenz gilt aber nur für die Dauer der Session.
Während skalare Variabeln ja durchaus auch als Spalten einer Tabelle dauerhaft in der Datenbank gespeichert werden können, gilt dies aber spätestens für PL/SQL-Tabellen (associative arrays) nicht mehr, für diesen Datentyp gibt es keine Entsprechung in ORACLE’s SQL.Und da kommen die nested tables wieder ins Spiel: Eine nested table kann auch in der Spalte einer Datenbanktabelle gespeichert werden. Im Grunde widerspricht das den Anforderungen der 3. Normalform, kann aber trotzdem durchaus Sinn machen.

Als erstes müssen wir den Typen in ORACLE’s SQL bekanntmachen.

SQL> create type telefonlistentyp is table of varchar2(20);
  2  /

Nun kann eine Tabelle angelegt werden, deren Spalte eine nested table ist. Dabei ist wichtig, dass die Daten in der Spalte vom Typ nested table (die ja selber eine Menge ist) wieder in einer eigenen ORACLE-Tabelle gespeichert wird. Den Namen dieser Tabelle muss man angeben, kann aber direkt nicht darauf zugreifen.

SQL> create table mitarbeiter 
  3  (name varchar2(20), telefon telefonlistentyp)
  2  nested table telefon store as telefontab tablespace users;
SQL> desc telefontab
 Name                                      Null?    Typ
 ------------------------------------ -------- ------------
 COLUMN_VALUE                                    VARCHAR2(20)

Genauso wie innerhalb von PL/SQL, benötigen wir auch in SQL den Konstruktor, um eine Telefonliste zu hinterlegen. Beachten Sie wieder folgenden Unterschied:

SQL> insert into mitarbeiter values 
	('Helmut', null);
SQL> insert into mitarbeiter values 
	('Helga',telefonlistentyp(null));
SQL> insert into mitarbeiter values 
	('Willi',telefonlistentyp());
SQL> insert into mitarbeiter values 
	('Susi',telefonlistentyp('030/12345','030/45678');

Helmut hat keine Telefonliste, Helga hat eine Telefonliste mit einer undefinierten Telefonnummer, und Willi hat eine leere Telefonliste. Susi hat zwei Telefonnummern.

Dieser unterschiedliche Behandlung schlägt sich auch in PL/SQL nieder: Im ersten Schritt verarbeiten wir ganz normal die Telefonnummern von Susi.

declare
 liste_susi telefonlistentyp;
begin
 select telefon into liste_susi from mitarbeiter 
       where name = 'Susi';
 for i in liste_susi.first .. liste_susi.last loop
   dbms_output.put_line(liste_susi(i));
 end loop;
end;
/

Genauso könnten wir auch mit Helgas Telefonliste verfahren, hier würde die Schleife einmal durchlaufen und NULL auf dem Bildschirm ausgegeben. Was aber, wenn wir Helmuts und Willis Telefonnummern ermitteln wollen?

Würde das gleiche Programm ausgeführt, um Helmuts Telefonnummern zu ermitteln, gibt es Ärger, denn Helmut hat keine Telefonnummernliste, oder anders gesagt, die Telefonnummernliste ist nicht initialisiert (wie eine skalare Variable). Entsprechend können wir keine Methoden FIRST und LAST ausführen. Beim Versuch erhalten wir die Meldung

declare
*
FEHLER in Zeile 1:
ORA-06531: Nicht initialisierte Zusammenstellung referenziert
ORA-06512: in Zeile 6

Diesen Fehler können wir umgehen, indem wir vor der Schleife auf NULL abfragen

declare
 liste_helmut telefonlistentyp;
begin
 select telefon into liste_helmut from mitarbeiter 
       where name = 'Helmut';
if list_helmut is not null then
  for i in liste_helmut.first .. liste_helmut.last loop
     dbms_output.put_line(liste_helmut(i));
  end loop;
 end if;
end;
/

Alternativ können wir mit der vordefinierten Ausnahme COLLECTION_IS_NULL auf diese Art von Fehler reagieren.

Und dann bleibt noch der Willi… Willi hat eine leere Telefonliste. Wie geht PL/SQL damit um?

declare
 liste_willi telefonlistentyp;
begin
 select telefon into liste_willi from mitarbeiter where name = 'Willi';
  if liste_willi is not null then
   for i in liste_willi.first .. liste_willi.last loop
    dbms_output.put_line(liste_willi(i));
   end loop;
 end if;
end;
/
declare
*
FEHLER in Zeile 1:
ORA-06502: PL/SQL: numerischer oder Wertefehler
ORA-06512: in Zeile 7

Unsere IF-Abfrage hat nicht geholfen, denn die Telefonliste ist initialisiert, enthält aber keine Werte. Korrekt ist daher, zu prüfen, ob die nested table leer ist oder nicht mit

declare
 liste_willi telefonlistentyp;
begin
 select telefon into liste_willi from mitarbeiter where name = 'Willi';
  if liste_willi is not empty then
   for i in liste_willi.first .. liste_willi.last loop
    dbms_output.put_line(liste_willi(i));
   end loop;
 end if;
end;
/

Halten wir fest, neben der Prüfung, ob ein Select überhaupt einen Datensatz zurückgeliefert hat (exception no_data_found), muß bei der Arbeit mit nested tables auch geprüft werden, ob diese initialisiert ist (if nesttab is [not] null…) und zuguterletzt auch geprüft werden, ob sie Werte enthält (if nesttab is [not] empty).

Nested tables Überblick

Nested Tables sind ein weiterer Datentyp, den es in ORACLEs PL/SQL-Welt gibt. Sie ähneln PL/SQL-Tabellen, verhalten sich dennoch anders. Gerade in Version 10g sind in Bezug auf nested tables einige Features dazugekommen. Es lohnt sich also, genau hinzuschauen:

Halten wir nochmal fest, was wir über PL/SQL-Tabellen wissen:

  • Eine Tabelle mit einer unbenannten Spalte (die aber beliebigen Typ haben kann)
  • Zugriff erfolgt über Index, der seit 9i nicht nur number, sondern auch varchar2 oder date sein kann
  • Index kann negativ und/oder positiv sein
  • Wächst dynamisch
  • Ist lückenbehaftet
  • Unterstützt bulk collect und bulk dml
  • Vergleich von associative arrays ist nicht möglich, nur Vergleich der einzelnen Zeilen

Folgende Code-Abschnitte nutzen PL/SQL-Tabellen

declare 
  type mytabtyp is table of number 
    index by binary_integer; 
  mytab mytabtyp; 
begin   
  mytab(1) := 100;   
  mytab(-1) := 99;   
  mytab(55) := 10;    
  for i in mytab.first .. mytab.last loop 
    if mytab.exist(i) then 	
      dbms_output.put_line(mytab(i)); 
    end if;   
  end loop; 
end;

Im zweiten Beispiel kommt die Bezeichnung associative array zum tragen, denn der Index kann auch Varchar oder Date sein:

Declare 
 type mytabtyp is table of varchar2(20) 
   index by varchar2(3); 
   mytab mytabtyp; 
Begin 
  mytab('DEU') := 'Deutschland'; 	
  mytab('USA') := 'United States';
  mytab('FRA') := 'Frankreich'; 
End;

Nested Tables

Nested Tables sind den PL/SQL-Tabellen recht ähnlich. Hier gelten folgende Richtlinien:

  • Sie haben Entsprechung in ORACLE SQL, ich kann also meine konstruierten nested tables persistent speichern. Das geht mit PL/SQL-Tabellen so einfach nicht
  • Sie müssen über einen Konstruktor initialisiert werden. Nun, das kennt man auch aus der objektorientierten Welt.
  • Sie haben nur einen positiven Index.
  • Sie unterstützen BULK Operationen, genauso wie PL/SQL-Tabellen
  • Sie sind anders als PL/SQL-Tabellen NICHT lückenbehaftet

Der meines Erachtens größte Benefit beim Einsatz von Nested Tables ist, dass es in ORACLEs SQL-Welt den gleichen Datentyp ebenfalls gibt, ich also mit PL/SQL nested tables zusammenbaue und diese in der Datenbank speichern kann.

Die Arbeit mit nested tables

Wie arbeitet man nun mit nested tables? Wie bei PL/SQL-Tabellen müssen wir erst den Typen bekanntmachen und können dann eine Variable des Typs deklarieren. Bei der Deklaration des Typen ist die Regel einfach: Wie eine PL/SQL-Tabelle, nur ohne INDEX-BY-Klausel (das ist dem einen oder anderen vielleicht schon versehentlich passiert und gar nicht aufgefallen. Wie gesagt: Der Unterschied ist klein, aber fein!).

declare
  type mytabtyp is table of varchar2(20);
  mytab mytabtyp;
  ....

Der nächste Schritt ist, eine Wertzuweisung vorzunehmen. Die Variable mytab hat, wie jede andere Variable, den Wert NULL, wenn keine Initialisierung stattfand.
Die Initialisierung erfolgt über einen Konstruktor, der den gleichen Namen wie der Typ hat.
Beachten Sie im untenstehenden Codeabschnitt, dass die nested table NICHT Null ist, wenn wir Werte darin speichern. Selbst wenn es der Wert NULL ist, es steht ja was drin. Folge: Beide IF-Konstrukte sind wahr.

begin  
  -- wenn mytab nicht initialisiert ist, 
  -- initialisiere 
  if mytab is null then
    mytab := mytabtyp('A','B','C');
  end if;
  mytab := mytabtyp(null,null,null,null);
  if mytab is not null then
    dbms_output.put_line('Die NT enthält Werte!');
  end if;
  ...

Operatoren ab Version 10g

Seit Version 10g gibt es eine Reihe von Operatoren, mit denen Sie nested tables verarbeiten können. Auch hier versucht ORACLE, nested tables als Mengen zu begreifen; dementsprechend verhalten sich auch die Operatoren:

DECLARE 
 TYPE nested_typ IS TABLE OF NUMBER; 
  nt1 nested_typ := nested_typ(1,2,3); 
  nt2 nested_typ := nested_typ(3,2,1); 
  nt3 nested_typ := nested_typ(2,3,1,3); 
  nt4 nested_typ := nested_typ(1,2,4);
  answer nested_typ;  
Begin
  answer := nt1 MULTISET UNION nt4;
  -- ergibt (1,2,3,1,2,4)
  -- doppelte Elemente in den Mengen bleiben erhalten
  answer := nt1 MULTISET UNION DISTINCT nt3;  
  -- ergibt (1,2,3)  
  --  doppelte Elemente werden entfernt 
  answer := nt2 MULTISET INTERSECT nt3; 
  -- ergibt (3,2,1)  
  -- schneidet Mengen; nur Elemente, die in beiden
  -- Mengen vorkommen, bleiben erhalten
  -- Hinweis: Käme die 3 in beiden Mengen mehrfach vor, wäre 
  -- sie auch in der Ergebnismenge mehrfach vorhanden
  answer := nt2 MULTISET INTERSECT DISTINCT nt3;
  -- ergibt (3,2,1)         
  -- s.o., nur werden doppelt vorhandene Elemente entfernt 
  answer := SET(nt3);        
  -- ergibt (2,3,1)        
  -- erzeugt eine Menge, doppelte Werte werden 
  -- entfernt  
  answer := nt3 MULTISET EXCEPT nt2;    
  -- (3)         
  -- bildet die Differenz, eine 3 bleibt über, weil sie  
  -- in nt3 zweimal vorkommt 
  answer := nt3 MULTISET EXCEPT DISTINCT nt2;  
  -- ()       
  -- ob die Werte mehrfach vorkommen oder nicht: sie werden
  -- entfernt 
end;

Hier erkennt man recht deutlich, dass es sich um Mengen handelt, und nicht etwa um eine Liste! Behalten Sie das im Hinterkopf. Im folgenden wird es nochmal klar:

Vergleichen von Nested Tables

Seit 10G können nested tables auch miteinander verglichen werden, allerding nur auf Un/Gleichheit.

DECLARE  	
  TYPE nested_typ IS TABLE OF NUMBER;
  nt1 nested_typ := nested_typ(1,2,3);
  nt2 nested_typ := nested_typ(3,2,1);
  nt3 nested_typ := nested_typ(2,3,1,3);
  nt4 nested_typ := nested_typ(1,2,4); 
  answer BOOLEAN;         
  howmany number;
  begin  
  if nt1 = nt2 then … 
  -- ergibt true, auch wenn die Reihenfolge
  -- verschieden ist  
  answer := nt1 IN (nt2,nt3,nt4);
  -- true, weil nt1 aussieht wie nt2   
  answer := nt1 SUBMULTISET OF nt3;
  -- true, weil nt1 eine Teilmenge ist
  answer := nt1 NOT SUBMULTISET OF nt4;
  -- ebenfalls true 
  howmany := CARDINALITY(nt3);
  -- Anzahl Elemente in nt3 
  howmany := CARDINALITY(SET(nt3));
  -- Anzahl distinkter Elemente 
  answer := 4 MEMBER OF nt1;
  -- false, 4 ist kein Element von nt1 
  answer := nt3 IS A SET;
  -- false, denn nt3 enthält doppelte Elemente 
  answer := nt3 IS NOT A SET; 
  -- true, siehe vorher    
  answer := nt1 IS EMPTY; 
  -- false, nt1 enthält Elemente  
  -- (auch wenn diese NULL wären)
  nt1 := NULL;  
  answer := nt1 IS EMPTY;
  -- jetzt true, nt1 enthält keine Elemente (mehr) ...

Trigger

Trigger sind spezielle PL/SQL Konstrukte ähnlich den Prozeduren. Während eine Prozedur allerdings explizit durch einen anderen Block über einen Prozeduraufruf gestartet wird, wird ein Trigger automatisch ausgeführt, wenn ein “triggerndes” Ereignis eintritt.

Diese Ereignisse können DML-Statements sein, aber auch DDL-Anweisungen und eine Anzahl von Systemereignissen.
Der Trigger kann vor oder nach dem auslösenden Ereignis ausgeführt werden. Ein Trigger kann ein Row-Level oder Statement-Trigger sein. Ersterer feuert (wird ausgeführt) für jede einzelne Zeile, die durch eine DML-Anweisung verändert wird, letzterer feuert nur einmal für das triggernde Kommando.

Hier sehen Sie die Syntax zum Erzeugen eines Triggers (eine vereinfachte Form):

CREATE [OR REPLACE] TRIGGER <trigger_name>
    {BEFORE|AFTER} {INSTEAD OF} {INSERT|DELETE|UPDATE {OF col}} ON <table_name>
    [FOR EACH ROW [WHEN (<trigger_condition>)]]
    <trigger_body>

Wichtig ist, daß

  • BEFORE und AFTER Trigger nur für Tabellen angelegt werden können. INSTEAD OF Trigger funktionieren nur auf Views. Typischerweise werden sie genutzt, um View Updates zu realisieren, die sonst ja nur unter bestimmten Bedingungen möglich sind.
  • Ereignisse, die einen Trigger auslösen, mit OR kombiniert werden können. Mehr noch, bei UPDATE Triggern können Trigger auch nur bei der Veränderung bestimmter Spalten ausgelöst werden (das Schlüsselwort UPDATE wird dann gefolgt von OF und der Liste der Spaltenattribute).

Hier sind einige Beispiele:

... INSERT ON tab ...
... INSERT OR DELETE OR UPDATE ON tab ...
... UPDATE OF col1, col2 OR INSERT ON tab ... 

ROW-LEVEL vs. Statement-LEVEL

Ist das Schlüsselwort FOR EACH ROW spezifiert, ist der Trigger ein ROWLEVEL Trigger, sonst ein Statement-Level Trigger.

Bei ROWLEVEL Triggern kann eine WHEN Bedingung formuliert werden, die in runden Klammern eingeschlossen sein muß. Die Bedingung muß erfüllt sein, damit der Trigger feuert. Diese Bedingung muß eine PL/SQL Bedingung sein, sie darf kein Subselect enthalten. Vorteil der WHEN-Klausel ist, dass der SQL-Interpreter die Bedingung prüft und, – falls diese nicht erfüllt ist -, die Kontrolle gar nicht erst an den PL/SQL-Interpreter übergibt. Folge ist ein Performancegewinn.

Der trigger_body ist ein PL/SQL-Block, keine Aneinanderreihung von SQL Statements!
ORACLE kennt einige Beschränkungen, was man in Triggern tun darf. Damit sollen Situationen vermieden werden, in denen ein Trigger Aktionen vornimmt, die wiederum einen weiteren Trigger auslösen und so weiter, so dass bspw. in der Regel keine Endlosschleifen entstehen. Diese Restriktionen sind:

  • In einem Row-Level-Trigger darf nicht das gleiche Objekt modifiziert oder gelesen werden, dessen Modifikation den Trigger ausgelöst hat.
  • In einem Statement-Trigger darf keine Tabelle modifiziert oder gelesen werden, die mit der Tabelle, auf der der Trigger liegt, durch ein Fremdschlüsselconstraint verbunden ist und der Trigger durch ein CASCADE ausgelöst wurde.

Um zu zeigen, wie Trigger funktionieren, nutzen wir die folgenden Tabellen:

CREATE TABLE T4 (a INTEGER, b CHAR(10));
CREATE TABLE T5 (c CHAR(10), d INTEGER);

Wir erzeugen einen Trigger, der einen Datensatz in Tabelle T5 einfügt, wenn ein Datensatz in die Tabelle T4 eingefügt wird und dabei der Wert von Spalte a <= 10 ist.

CREATE TRIGGER trig1
    AFTER INSERT ON T4
    FOR EACH ROW
    WHEN (NEW.a <= 10)
    BEGIN
        INSERT INTO T5 VALUES(:NEW.b, :NEW.a);
    END trig1;
/

Die speziellen Variablen NEW und OLD werden benutzt, um die Werte des neuen, bzw. alten Datensatzes zu referenzieren. Bitte beachten Sie, dass im Trigger-Body den Schlüsselworten NEW und OLD ein Doppelpunkt vorangestellt werden muß. In der WHEN Bedingung des Triggers steht kein Doppelpunkt! Wir erzeugen den Trigger mit dem CREATE TRIGGER Statement, durch das Erzeugen wird der jedoch nicht auch ausgeführt.

Nur ein auslösendes Ereignis, wie hier das Einfügen eines Datensatzes in T4, würde den Trigger-Body zur Ausführung bringen.

Informationen aus dem Data Dictionary

Um Informationen über Trigger zu erhalten, nutzen Sie:

select trigger_type, table_name, triggering_event from user_triggers 
where trigger_name = '<trigger_name>';

Um einen Trigger zu löschen:

drop trigger <trigger_name>;

Trigger lassen sich auch deaktivieren, ohne sie löschen zu müssen:

alter trigger <trigger_name> {disable|enable};

Bedenken Sie, dass durch das Löschen der Tabelle alle mit dieser Tabelle verbundenen Trigger mit gelöscht werden.

Der Before- und After-Zeitpunkt

Wozu genau sind nun die Zeitpunkt Before und After gedacht?? Bei ersten Versuchen scheint es völlig unerheblich, ob ein Before- oder After-Trigger genutzt wird. Z.B. findet immer ein Rollback der kompletten Anweisung statt, wenn ein Trigger mit einem Fehler terminiert.
Im Falle eines Row-Triggers ist der Zeitpunkt BEFORE aber auffällig. Denn nur bei einem BEFOREROW-Trigger hat dieser die Möglichkeit, die geänderten Werte z.B. im Zuge eines Updates noch zu verändern.

Folgendes Beispiel verhindert, dass das Gehalt eines Mitarbeiters auf mehr als 5000 geändert werden kann. (ACHTUNG, es handelt sich um einen UPDATE-Trigger, im Falle eines Inserts sind also Gehälter größer 5000 zulässig):

CREATE TRIGGER bu_mitarbeiter
    BEFORE UPDATE ON mitarbeiter
    FOR EACH ROW
    BEGIN
        IF :new.gehalt > 5000 then
            :new.gehalt = 5000;
        END IF;
    END trig1;

Obiges Beispiel ist kein gutes, denn das gleiche hätte man mit einem CHECK-Constraint erreichen können, was weitaus performanter ist, aber man sieht, dass ein Trigger die Werte eines Datensatzes nicht nur untersuchen, sondern auch ändern kann. Wie gesagt, der ROW-Trigger kann dies nur im Falle eines BEFORE-Triggers, also bevor die eigentlich DML-Anweisung ausgeführt wird.

Hat man nun mehrere Trigger, die bei einer DML-Anweisung auf einer Tabelle reagieren, ist die Ausführungsreihenfolge wie folgt:

  • BEFORE-Statement-Trigger wird einmal ausgeführt
  • Für jeden manipulierten Datensatz wird der BEFOREROW-Trigger ausgelöst.
  • Datensatz wird durch die DML-Operation geändert
  • Für jeden manipulierten Datensatz wird der AFTERROW-Trigger ausgelöst.
  • AFTER Statement-Trigger wird einmal ausgeführt

Und wozu können nun diese verschiedenen Zeitpunkte verwendet werden? Z.B. um Trigger miteinander kommunizieren zu lassen!
Nehmen wir folgende Problemstellung an: Es darf maximal die Hälfte der Datensätze, die in einer Tabelle stehen, in einem Delete-Statement gelöscht werden.
Wir müssen also die Anzahl Datensätze vor und nach der Delete-Operation vergleichen, um festzustellen, wieviel nun tatsächlich gelöscht worden sind.
Ein Before-Delete-Trigger soll die Anzahl Datensätze zählen und sich den Wert in einer Package merken. Ein After-Delete-Trigger vergleicht den Wert mit der nun vorhandenen Anzahl Datensätze. Wenn mehr als die Hälfte gelöscht wurde, soll ein Fehler ausgelöst werden.

create package gedaechtnis is
          anzahl_vor_delete number;
     end;
     /
create trigger bd_tab before delete on tab
     begin
        select count(*) into gedaechtnis.anzahl_vor_delete from tab;
     end;
     /
create trigger ad_tab after delete on tab
         anzahl_nach_delete number;
     begin
         select count(*) into anzahl_nach_delete from tab;
         if anzahl_nach_delete < anzahl_vor_delete / 2 then
           raise_application_error(-20001,'Mehr als die Hälfte löschen ist verboten');
         end if;
      end;
      /

DDL-Trigger

Ausserdem gibt es in ORACLE nicht nur Trigger, die auf DML-Ereignisse reagieren, sondern auch solche, die auf DDL– oder gar Systemereignisse (SHUTDOWN, LOGON o.ä.) reagieren. Damit werden Trigger schnell sehr mächtig, man macht sich aber das Debuggen u.U. schwer, da man über die Aktivitäten von Triggern schnell den Überblick verliert. Also setzen Sie Trigger wohlüberlegt und sparsam ein.
Wer mehr wissen will, hinterläßt einen Kommentar, dann erweitere ich den Bereich.