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