Inhalt
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) ...