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