martinbulinski.de

Die Arbeit mit PL/SQL scheint auf den ersten Blick kompliziert. Tatsächlich steht aber ein mächtiges Konzept dahinter, um mehrere Werte auf einmal zu bearbeiten.

Wie Records werden auch PL/SQL Tabellen in zwei Schritten erzeugt. Erst muss die Tabellenstruktur deklariert werden mit Hilfe des TYPE Statements. Im zweiten Schritt muß eine Variable des eben angelegten Tabellentypen deklariert, bzw. instanziiert werden.

Die Deklaration erfolgt mit

TYPE names IS TABLE OF varchar2(20) [NOT NULL]
  INDEX BY BINARY_INTEGER ;

Dies erzeugt einen Tabellentypen, der 20stellige VARCHAR2-Werte aufnimmt. Das NOT NULL ist optional. Die Datentypen in einem Tabellentypen können alle skalaren Typen sein, aber auch Records (womöglich ein Record, dessen Feldkomponente wieder eine Tabelle ist? Damit läßt sich ganz schön was anstellen…).
Auch Verankerungen mit ROWTYPE sind zulässig.

Um eine Variable des Typen zu instanziieren, nutzen wir

-- <table_name> <table_type>; 
-- in unserem Beispiel also 
customer_names names; 

ACHTUNG! Seit der ORACLE-Version 9i ist es auch möglich, als Index-Kriterium Zeichenketten zu verwenden, so daß Sie mit Hilfe eines Strings auf eine Zeile der Tabelle zugreifen können (deshalb spricht man jetzt auch von associative arrays).

TYPE names IS TABLE OF varchar2 [NOT NULL]
  INDEX BY VARCHAR2;

Weiterhin wichtig ist, dass Sie die Indizierungsform durchaus weglassen können, also z.B.

TYPE names IS TABLE OF varchar2 [NOT NULL];

Dann handelt es sich aber nicht mehr um eine PL/SQL-Tabelle, sondern um eine nested table, die sich an bestimmten Stellen anders als eine PL/SQL-Tabelle verhält.
Eine nested table hat eine Entsprechung in ORACLEs SQL, eine PL/SQL-Tabelle ist ein PL/SQL-Konstrukt, das in der SQL-Welt nicht existiert.

Zeilen in PL/SQL Tabellen

Wir erinnern uns, dass wir auf Zeilen in einer PL/SQL Tabelle mit Hilfe eines Index zugreifen. Die Syntax hierzu sieht wie folgt aus:

<table_name>(<primary_key_value>) 

Der muss kompatibel zum Datentyp BINARY_INTEGER sein (seit 9i sind auch Zeichenketten möglich).

Die folgenden Beispiele sind allesamt zulÄssig. Wir nutzen automatische Typkonvertierung, Zahlen mit Nachkommaanteil werden gerundet.

employee_tab (20) := 'Maier';
employee_tab(-20) := 'Müller';
employee_tab('11'||'3') := 'Schmidt'; 
employee_tab(11.73) := 'Wagner';

Auf die gleiche Art und Weise können wir auch wieder aus einer Tabelle lesen. Was passiert aber, wenn eine referenzierte Zeile nicht existiert? Eine Zeile existiert erst, wenn ihr ein Wert zugewiesen wurde (Dieser Wert kann natürlich auch NULL sein). Wenn Sie versuchen, eine Zeile anzusprechen, die nicht existiert, erhalten Sie eine NO_DATA_FOUND Ausnahme.

PL/SQL Tabellen füllen und leeren

Es gibt drei verschiedene Arten, PL/SQL Tabellen zu füllen.

• Direkte Wertzuweisung
• Iterative Wertzuweisung
• Aggregierte Wertzuweisung

Direkte Zuweisung meint die Form, die wir zuvor genutzt haben, also:

employee_tab(1) := 'Morris'; 

Die iterative Zuweisung erfolgt z.B. in Form einer Schleife. Meist wird hier wohl die FOR-CURSOR-LOOP genutzt, um die Daten aus einer SQL Tabelle in eine PL/SQL Tabelle zu übernehmen.

declare
i := 0;
for rec in (select ename from emp) loop
  mytab(i) := rec.ename;
  i := i + 1;
end loop;

Eine wesentlich schnellere Form ist das sogenannte BULK COLLECT:

 select ename bulk collect into mytab from emp;

Aggregierte Zuweisung meint die Zuweisung einer gesamten Tabelle an eine andere Tabelle. Diese muß typkompatibel sein. So wie bei Records ist das Vergleichen zweier Tabellen auf diese Art nicht möglich, hier muß Element für Element verglichen werden.

neue_employee_tab := employee_tab; 

Es ist nicht möglich, alle Zeilen einer Tabelle zu entfernen oder gar zu “droppen”. Zwar können wir den einzelnen Zeilen einer Tabelle den Wert NULL zuweisen, aber die Zeile existiert weiter, eben gefüllt mit dem NULL Wert.

Um eine Tabelle zu leeren und den verwendeten Speicher freizugeben, können wir einen Trick anwenden:
Wir erzeugen eine leere PL/SQL Tabelle, die typkompatibel zur leerenden Tabelle ist und nutzen die aggregierte Zuweisung, um der zu leerenden Tabelle die leere Tabelle zuzuweisen.

zu_leerende_tab := leere_tab; 

Seit PL/SQL Version 2.3 kann aber auch das DELETE Statement genutzt werden.

PL/SQL Tabellenfunktionen

Es gibt für die Arbeit mit PL/SQL Tabellen eine Reihe vordefinierter Prozeduren und Funktionen:

• Die DELETE Anweisung (die also ausnahmsweise keine Funktion ist)
• Die COUNT Funktion
• Die FIRST Funktion
• Die NEXT Funktion
• Die LAST Funktion
• Die EXISTS Funktion
• Die PRIOR Funktion

Damit erhalten wir eine Reihe von Informationen über die referenzierte Tabelle, mit der Ausnahme der DELETE Anweisung, die eine Zeile aus einer Tabelle löscht.

Die DELETE Anweisung

Die DELETE Anweisung tut genau das, was wir erwarten: Sie löscht Zeilen in PL/SQL Tabellen. Die Syntax ist allerdings eine andere als in SQL:

table_name.delete -- alle Zeilen löschen
table_name.delete(20) -- 20. Zeile der Tabelle wird entfernt
table_name.delete(20,50)-- Alle Zeilen von 20 bis 50 werden entfernt 

Die COUNT Funktion

Auch diese Funktion ist recht einfach zu merken. Sie liefert die Anzahl Werte in der PL/SQL Tabelle zurück. Zeilen, denen kein Wert zugeordnet ist, werden nicht mitgezählt. Sind keine Zeilen in der Tabelle, liefert COUNT die Zahl 0 zurück.

row_count := table_name.count;

Die FIRST Funktion

Die FIRST Funktion liefert den kleinsten Index zurück, an dem ein Wert eingetragen wurde. Sind keine Elemente (Zeilen) in der PL/SQL Tabelle, liefert FIRST den Wert NULL zurück.

first_row := table_name.first; 

Die NEXT Funktion

Die NEXT Funktion liefert für einen gegebenen Indexwert den nächstgrößeren zurück, an dem ein Wert eingetragen ist. Sie ist sehr nützlich, weil wir ja erwähnt haben, dass eine PL/SQL Tabelle lückenhaft sein kann, die Zeilen also nicht hintereinanderliegen müssen. Das nächste Element nach Zeile 10 kann also 1000 sein. Wenn es keine Werte mehr hinter dem übergebenen Indexwert gibt, liefert NEXT den Wert NULL.

table_name(1):=’Albert’;
table_name(5):=’Jochen’;
next_value := table_name.next(1); 

Im obigen Beispiel erhält die Variable next_value den Wert 5.

Die LAST Funktion

Sie ist das genaue Gegenteil der FIRST Funktion, liefert also den letzten Indexwert der Tabelle zurück, an dem noch ein Element steht. Gibt es keine Elemente in der Tabelle, wird NULL zurückgeliefert. Die Syntax ist:

last_row := table_name.last; 

Die EXISTS Funktion

Die wohl wichtigste Funktion im Umgang mit PL/SQL Tabellen, um den Zugriff auf uninitialisierte Zeilen und damit die NO_DATA_FOUND Ausnahme zu vermeiden: Die EXISTS Funktion liefert TRUE zurück, wenn am gegebenen Index ein Element (auch NULL) eingetragen ist, andernfalls FALSE. Sie kann beispielsweise so verwendet werden:

IF table_name.exists(10) THEN... 

The PRIOR Function

Die PRIOR Funktion ist das Gegenteil der NEXT Funktion. PRIOR liefert den nächstkleineren Indexwert, an dem ein Element steht, für einen gegebenen Indexwert zurück. Sie liefert NULL, wenn es kein Element mit einem kleineren Indexwert gibt.

Beispiel:

prior_value := table_name.prior(100); 

Ein Beispiel für die Arbeit mit PL/SQL Tabellen

Lassen Sie uns nun anschauen, wie PL/SQL Tabellen am einfachsten mit Daten gefüllt werden können. Es ist nicht ganz so einfach wie ein SELECT INTO, um einzelne Spaltenwerte in Variablen zu lesen. Wir müssen den Lesevorgang selbst programmieren.

Wir müssen einen PL/SQL Block schreiben, der die Daten in eine PL/SQL Tabelle übernimmt. Die notwendigen Schritte sind folgende:

• Deklaration eines Cursors, der die Tabelle ausliest, die wir in unseren Block übernehmen wollen.
• Deklaration eines PL/SQL Tabellentyps für jede einzelne Spalte, die der Cursor liest.
• Instanziierung der entspr. PL/SQL Tabellen, die die Daten aufnehmen sollen
• Zuweisen der aus dem Cursor erhaltenen Werte an die PL/SQL Tabellen

Schauen wir uns ein wenig Code an:

DECLARE
  /* Es erfolgt die Deklaration des Cursors, um Datensätze aus der DB zu lesen */
    CURSOR c_employees IS
    SELECT id, name, age, sex, comm_date, salary
    FROM    employees;
  /* Jetzt deklarieren wir den Tabellentyp */
    TYPE local_employees IS TABLE of employees%ROWTYPE
    INDEX BY BINARY_INTEGER;
  /* Jetzt instanziieren wir die Tabelle*/
    local_emp_table local_employees;
  /* Die Variable wird als Datensatz-Zähler genutzt */
    row_number BINARY_INTEGER := 0;
 /* Hier kommt die Action */
BEGIN
  FOR employee_rec in c_employees
  LOOP
    row_number := row_number + 1;
    /* Der komplette Datensatz wird in die Tabelle übernommen */
    local_emp_table(row_number) := employee_rec;
  END LOOP;
END; 

Nun, alternativ können wir eine PL/SQL Tabelle aber auch einfacher füllen:

DECLARE
  TYPE mytabtype IS TABLE OF employees%ROWTYPE 
  INDEX BY BINARY_INTEGER;
  mytab mytabtype;
BEGIN
  SELECT * BULK COLLECT INTO mytab FROM employees;  
END;
/ 

In diesem Beispiel nutzen wir einen impliziten Cursor und übernehmen alle Datensätze in unsere PL/SQL Tabelle mit einem sogenannten BULK COLLECT, das mit der Version PL/SQL-Version 2.3 eingeführt wurde.