ORACLE und PL/SQL

5
Zusammengesetzte Datentypen
5.1
5.2
5.3
5.4
Records .......................................................................................... 5-4
5.1.1 Implizite Typdeklaration mit %ROWTYPE ............................. 5-6
5.1.2 Referenzierung eines Records .......................................... 5-6
PL/SQL Tables ............................................................................... 5-8
5.2.1 Deklaration einer PL/SQL Table ........................................ 5-8
Referenzierung einer PL/SQL-Tabelle.......................................... 5-10
5.3.1 PL/SQL-Tabellen-Methoden ............................................ 5-10
Bulk Binds..................................................................................... 5-12
5.4.1 Fehlerbehandlung............................................................ 5-16
1.2.066 / 4053
5-1
5
5
Zusammengesetzte Datentypen
Zusammengesetzte Datentypen
Variablen eines zusammengesetzten Datentyps können im Gegensatz
zu skalaren Datentypen mehr als einen Wert enthalten. Man unterscheidet:
• TABLE
• NESTED TABLE
• VARRAY
• RECORD
Die beiden gebräuchlichsten sind TABLE und RECORD, auf die im Weiteren noch genauer eingegangen wird.
TABLE, NESTED TABLE und VARRAY werden auch unter dem Überbegriff Collections zusammengefaßt, da bei ihnen im Gegensatz zu RECORDs alle Elemente vom gleichen Datentyp sein müssen.
Eine TABLE wird auch als PL/SQL-Tabelle oder Index-By-Tabelle bezeichnet, eine NESTED TABLE auch als PL/SQL-Version8-Tabelle.
VArrays ähneln den Arrays anderer Programmiersprachen, sind aber
nur eindimensional. Auf sie wird hier nicht näher eingegangen.
Nested Tables ähneln Tables, können aber im Gegensatz zu diesen in
Feldern einer Datenbankspalte gespeichert werden, wenn sie mit
CREATE TYPE in der Datenbank definiert wurden. Auch auf sie wird
nicht näher eingegangen.
5-2
1.2.066 / 4053
Zusammengesetzte Datentypen
5
Zusammengesetzte Datentypen
5
ƒ beinhalten mehr als einen Wert
ƒ man unterscheidet:
ƒ TABLE
ƒ NESTED TABLE
ƒ VARRAY
ƒ RECORD
ƒ TABLES, NESTED TABLES und VARRAYS werden auch als Collections bezeichnet
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 2
5-3
5
5.1
Zusammengesetzte Datentypen
Records
Records beinhalten einzelne, aber inhaltlich zusammengehörige Elemente, die als Felder bezeichnet werden. Jedes Feld kann einen unterschiedlichen Datentyp aufweisen, wobei skalare Datentypen, Records
und Tables erlaubt sind. Sie eignen sich besonders zur Aufnahme eines
Datensatzes aus einer Datenbanktabelle.
Die Deklaration einer Record-Variablen geschieht in zwei Schritten:
• Zuerst wird der Record-Typ deklariert:
TYPE typname IS RECORD (felddeklaration1
[, felddeklaration2,...];
felddeklaration steht dabei für die Deklaration des einzelnen Feldes und
entspricht in der Syntax einer Variablendeklaration:
feldname datentyp [NOT NULL] [DEFAULT | := wert];
Beispiel:
TYPE dept_rec_type is RECORD (deptnumber number(2),
dname dept.dname%TYPE,
loc varchar2(13));
• Im zweiten Schritt wird dann eine Variable dieses Datentyps deklariert:
variable recordtyp;
Defaultwerte oder NOT NULL sind hier nicht zulässig, da dies bei einem
Record nur für einzelne Felder möglich ist.
Beispiel:
dept_rec dept_rec_type;
5-4
1.2.066 / 4053
Zusammengesetzte Datentypen
5
Records
ƒ
ƒ
ƒ
ƒ
5
setzen sich aus einzelnen Feldern zusammen, die logisch zusammengehören
jedes Feld kann unterschiedliche Datentypen haben
eignen sich besonders zur Verarbeitung von Zeilen einer Datenbanktabelle
Deklaration einer Variable in zwei Schritten
ƒ Typdeklaration
ƒ Variablendeklaration
ƒ Beispiel:
TYPE dept_rec_type is RECORD
(
deptnumber NUMBER(2),
dname dept.dname%TYPE,
loc VARCHAR2(13)
);
dept_rec dept_rec_type;
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 3
5-5
5
5.1.1
Zusammengesetzte Datentypen
Implizite Typdeklaration mit %ROWTYPE
Mit %ROWTYPE wird die Struktur einer Datenbanktabelle komplett übernommen. Dadurch kann die Variablendeklaration in einem Schritt erfolgen:
variable tabellenname%ROWTYPE;
Beispiel:
emp_rec emp%ROWTYPE;
dept_rec scott.dept%ROWTYPE;
Die Variable emp_rec enthält nun die Felder empno, ename, job, mgr,
hiredate, sal, comm und deptno mit den entsprechenden Datentypen und spiegelt damit die Tabelle emp komplett wider. (Das Feld empno ist allerdings nicht NOT NULL!).
5.1.2
Referenzierung eines Records
Ein einzelnes Feld in einem Record wird mit der Punktnotation angesprochen in der Form
recordname.feldname
Beispiele:
emp_rec.empno := 4711;
IF emp_rec.ename = 'KING' THEN ....
IF emp_rec.comm IS NULL THEN ...
Ein kompletter Record kann nicht verglichen werden (weder mit IS
NULL noch mit Vergleichsoperatoren)!
Die Felder eines Records müssen einzeln zugewiesen werden. Eine
Zuweisung der Form dept_rec := 10, 'SALES', 'BOSTON'; ist
nicht zulässig. Ausnahme: Falls ein Record mit %ROWTYPE deklariert
wurde, kann eine komplette Zeile eingelesen werden mit
SELECT * INTO emp_rec
FROM emp WHERE ROWNUM = 1;
5-6
1.2.066 / 4053
Zusammengesetzte Datentypen
5
Implizite Typdeklaration
5
ƒ %ROWTYPE
ƒ spiegelt die komplette Struktur einer Tabelle in einem Record wider
ƒ ermöglicht die Variablendeklaration in einem Schritt
ƒ Referenzierung eines Records
ƒ ein kompletter Record kann nicht referenziert werden, nur einzelne Felder:
recordname.feldname
ƒ Records können nicht verglichen werden
ƒ Referenzierung erfolgt mit Punktnotation
www.unilog.integrata.de
www.unilog-integrata.de
4053 / 1.2.036
Folie 4
Implizite Typdeklaration (f)
5
ƒ Beispiele:
emp_rec emp%ROWTYPE;
emp_rec.deptno := 40;
IF emp_rec.comm IS NULL THEN ...
SELECT * INTO emp_rec
FROM emp WHERE ...
SELECT empno, ename INTO
emp_rec.empno,
emp_rec.ename
FROM emp WHERE...
IF emp_rec.deptno = dept_rec.deptnumber
THEN ...
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 5
5-7
5
5.2
Zusammengesetzte Datentypen
PL/SQL Tables
PL/SQL-Tabellen sind nicht zu verwechseln mit Datenbanktabellen. Sie
existieren nur temporär im Hauptspeicher während der Abarbeitung eines PL/SQL-Konstrukts. Eine PL/SQL-Tabelle besteht aus zwei Komponenten, die Spalten entsprechen (aber nicht benannt werden können):
• Die erste Spalte hat den Datentyp BINARY_INTEGER und dient der
Identifizierung der einzelnen Elemente in der Tabelle (eine Art Primärschlüssel).
• Die zweite Spalte dient der Aufnahme von Werten und stellt die eigentliche Variable dar. Seit Version 9i kann hier auch ein Record
verwendet werden.
Die Größe einer PL/SQL-Tabelle braucht (im Gegensatz zu Varrays)
nicht festgelegt werden, eine Table kann also dynamisch wachsen. Alle
in einer Variablen dieses Typs gespeicherten Werte müssen den gleichen Datentyp (denjenigen der zweiten Spalte) haben.
5.2.1
Deklaration einer PL/SQL Table
Eine PL/SQL Table wird immer in zwei Schritten deklariert:
• Deklaration des Typs:
TYPE typ_name IS TABLE OF datentyp [NOT NULL]
INDEX BY BINARY_INTEGER;
Als Datentyp sind alle skalaren Datentypen und Records zulässig. Auch
die Attribute %TYPE und %ROWTYPE sind erlaubt. Tables werden nicht initialisiert (auch nicht, wenn sie als NOT NULL deklariert werden).
• Deklaration einer Variablen dieses Typs:
variable tabletyp;
Beispiele:
TYPE num_table_type IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
empno_table num_table_type;
TYPE emp_rec_table_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_table emp_rec_table_type;
5-8
1.2.066 / 4053
Zusammengesetzte Datentypen
5
PL/SQL-Tables
5
ƒ sind Datenbanktabellen nachgebildet
ƒ enthalten eine Art "Primärschlüssel" vom Typ
BINARY_INTEGER
ƒ
ƒ
ƒ
ƒ
enthalten eine unbenannte Spalte zur Aufnahme von Werten
können nur Werte des gleichen Datentyps (skalar oder Record) speichern
werden in zwei (bzw. drei) Schritten deklariert
wachsen dynamisch
www.unilog.integrata.de
www.unilog-integrata.de
4053 / 1.2.036
Folie 6
PL/SQL-Tables (f)
5
ƒ Beispiel:
TYPE num_table_type IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
empno_table num_table_type;
TYPE emp_rec_table_type
IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_table
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
emp_rec_table_type;
4053 / 1.2.036
Folie 7
5-9
5
5.3
Zusammengesetzte Datentypen
Referenzierung einer PL/SQL-Tabelle
Die einzelnen Elemente einer PL/SQL-Tabelle werden über ihren Index
angesprochen in der Form:
variablenname(i)
wobei i dem Wert in der Primärschlüsselspalte entspricht. i muss nicht
bei 1 beginnen und kann auch negativ sein. Lücken sind zulässig. Bei
einem Record-Datentyp werden die Felder wieder mit der Punktnotation
nach Angabe des Index angesprochen.
Beispiele:
v_empno := emp_table(3);
IF emp_table(2).empno = 4711 THEN ...
SELECT * INTO emp_table(4) FROM emp WHERE empno = 7788;
emp_table(4).ename := 'Scotty';
5.3.1
PL/SQL-Tabellen-Methoden
PL/SQL-Tabellen-Methoden sind vordefinierte Prozeduren oder Funktionen, die auf PL/SQL-Tabellen angewendet werden können und mit
Punktnotation aufgerufen werden in der Form:
tablename.methodenname[(Parameter)]
Verfügbare Methoden:
EXISTS(n):
gibt TRUE zurück, wenn das Element mit Index n
existiert, sonst FALSE
COUNT:
gibt die Anzahl der Elemente einer Table zurück
FIRST / LAST:
Gibt den niedrigsten bzw. höchsten Index einer
Table zurück oder NULL, wenn die Table leer ist.
PRIOR(n) / NEXT(n): Gibt den Index zurück, der vor (PRIOR) bzw.
nach (NEXT) n in der Table liegt
5-10
DELETE:
löscht alle Elemente der Table
DELETE(n):
löscht das Element mit Index n
DELETE(m, n):
löscht alle Elemente mit Index zwischen m und n
1.2.066 / 4053
Zusammengesetzte Datentypen
Referenzierung einer PL/SQL-Tabelle
ƒ
ƒ
ƒ
ƒ
5
5
Elemente werden über Index angesprochen
Als Index sind beliebige BINARY_INTEGER-Werte zulässig
Lücken im Index sind zulässig
PL/SQL-Tabellen-Methoden
ƒ werden mit Punktnotation aufgerufen
ƒ verfügbar:
ƒ EXISTS(n)
ƒ COUNT
ƒ FIRST / LAST
ƒ PRIOR / NEXT
ƒ DELETE
www.unilog.integrata.de
www.unilog-integrata.de
4053 / 1.2.036
Folie 8
Referenzierung einer PL/SQL-Tabelle (f)
5
ƒ Beispiele:
SELECT * INTO emp_table(1)
FROM emp WHERE empno = 7788;
emp_table(7).empno := 4711;
FOR i IN 1.. emp_table.COUNT LOOP
IF emp_table.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(emp_table(i).
ename);
ELSE
DBMS_OUTPUT.PUT_LINE('Index ' ||
i || ' noch frei');
END IF;
END LOOP;
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 9
5-11
5
5.4
Zusammengesetzte Datentypen
Bulk Binds
Eine Möglichkeit des Bulk-Select der Form
SELECT ... BULK COLLECT INTO...
ist auch mit Cursorn (s. Kapitel 6) möglich in der Form
FETCH ... BULK COLLECT INTO...
Auch hier sind als Variablen nur Collections zulässig.
Bulk Binds können auch als Ersatz für FOR-Schleifen mit DMLAnweisungen (INSERT, UPDATE, DELETE), die den Schleifenzähler referenzieren, verwendet werden. Dazu ist die Klausel FORALL nötig.
Syntax:
FORALL zaehler IN Untergrenze .. Obergrenze
DML-Befehl;
Hierbei handelt es sich nicht um ein Schleifenkonstrukt; die Performance wird deutlich verbessert (ca. Faktor 10), da nicht mehr ständig zwischen PL/SQL-Anweisung (FOR ... LOOP) und SQL-Anweisung (DMLBefehl) hin- und hergewechselt werden muss.
Beim Schleifenzähler muss es sich um den Index einer Collection (z.B.
PL/SQL-Table) handeln.
Voraussetzung ist außerdem, dass der DML-Befehl die Collection mit
dem Index zaehler referenziert, beispielsweise in der WHERE-Bedingung
(bei UPDATE oder DELETE) oder in der VALUES-Klausel (bei INSERT).
Die FORALL-Klausel betrifft grundsätzlich nur einen (unmittelbar folgenden) Befehl, und nur hier kann der Schleifenzähler referenziert werden.
Die Gesamtzahl der durch den DML-Befehl betroffenen Zeilen kann in
gewohnter Weise mit SQL%ROWCOUNT abgefragt werden; im Zusammenhang mit FORALL gibt es darüber hinaus als zusätzliches Attribut
SQL%BULK_ROWCOUNT(zaehler),
mit dessen Hilfe abgefragt werden kann, wie viele Zeilen bei einer vorgegebenen Zählerzahl betroffen waren. SQL%BULK_ROWCOUNT ist nicht
zulässig in Verbindung mit einem INSERT-Befehl.
Einzelheiten zur Variablendeklaration und -verwendung im nächsten
Beispiel (PL/SQL-TABLE) finden Sie in Kapitel 5.
5-12
1.2.066 / 4053
Zusammengesetzte Datentypen
5
Bulk Binds
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
5
erhöhen Performance
nur in Verbindung mit einer Collection
Klausel BULK COLLECT INTO für SELECT und FETCH-Anweisungen
Klausel FORALL für DML-Anweisungen
Collection muss im DML-Befehl referenziert werden
SQL%BULK_ROWCOUNT
www.unilog.integrata.de
www.unilog-integrata.de
4053 / 1.2.036
Folie 10
Bulk Binds (f)
5
ƒ Beispiel:
DECLARE
TYPE nr_liste IS TABLE OF
emp.empno%TYPE;
nr nr_liste;
num number;
BEGIN
SELECT empno BULK COLLECT
INTO nr FROM emp
WHERE deptno = 20;
FORALL i IN nr.FIRST .. nr.LAST
UPDATE emp
SET sal = sal* 1.1
WHERE empno = nr(i);
END;
/
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 11
5-13
5
Zusammengesetzte Datentypen
Beispiel:
DECLARE
TYPE
nr_liste IS TABLE OF
emp.empno%TYPE;
nr nr_liste;
num number;
BEGIN
SELECT empno BULK COLLECT
INTO nr FROM emp
WHERE deptno = 20;
FORALL i IN nr.FIRST .. nr.LAST
UPDATE emp
SET sal = sal* 1.1
WHERE empno = nr(i);
END;
/
5-14
1.2.066 / 4053
Zusammengesetzte Datentypen
1.2.066 / 4053
5
5-15
5
5.4.1
Zusammengesetzte Datentypen
Fehlerbehandlung
Da nach Auftreten eines Fehlers die übrigen DML-Befehle des Arrays
nicht mehr durchgeführt werden, gibt es die Möglichkeit, mit SAVE EXCEPTIONS alle Befehle komplett durchlaufen zu lassen. Die Exception
wird erst hinterher ausgelöst (und muss, im Gegensatz zur Beschreibung in der ORACLE-Doku, auch abgefangen werden).
Neu eingeführt wurde dazu auch das Cursor-Attribut
SQL%BULK_EXCEPTIONS. Mit ihm kann abgefragt werden
• wie viele Fehler aufgetreten sind
• in welchen Durchläufen Fehler aufgetreten sind
• welche Fehler aufgetreten sind
Beispiel:
DECLARE
type table_type IS TABLE OF emp.empno%TYPE;
v_table table_type;
count_errors NUMBER;
BEGIN
SELECT empno BULK COLLECT INTO v_table from emp;
FOR i IN 1..5 LOOP
v_table(i*2):= v_table(i*2) +1;
END LOOP;
FORALL i IN v_table.first..v_table.last SAVE EXCEPTIONS
INSERT INTO emp (empno) VALUES(v_table(i));
EXCEPTION
WHEN OTHERS THEN
count_errors := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Anzahl der Fehler: '||count_errors);
FOR i in 1..count_errors LOOP
DBMS_OUTPUT.PUT_LINE('Fehler in Runde'||
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE('Aufgetretener Fehler '||
SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
END LOOP;
END;
Der hier ständig auftretende Fehler ist eine Verletzung des Primary Key
(ORA-00001). Als Fehlercode wird also nur die Nummer allein (ohne
führende Nullen und ohne ORA-) ausgegeben.
Ohne SAVE EXCEPTIONS würde die Ausführung bereits für i=1 unterbrochen und in den Exception-Teil verzweigt werden.
5-16
1.2.066 / 4053
Zusammengesetzte Datentypen
5
Fehlerbehandlung
ƒ
5
Erweiterung zu
FORALL (SAVE EXCEPTIONS)
DECLARE
TYPE table_type IS
TABLE OF emp.empno%TYPE;
v_table table_type;
BEGIN
SELECT empno BULK COLLECT INTO v_table FROM emp;
FOR i IN 1..5 LOOP
v_table( i * 2 ):=
v_table( i * 2 ) + 1;
END LOOP;
FORALL i IN
v_table.first..v_table.last
SAVE EXCEPTIONS
INSERT INTO emp (empno)
VALUES(v_table(i));
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 12
5-17
5
5-18
Zusammengesetzte Datentypen
1.2.066 / 4053