martinbulinski.de

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