Zusammengesetzte Datentypen

Zusammengesetzte Datentypen
Arbeit mit
zusammengesetzten
Datentypen
• Typen:
– PL/SQL RECORDS
– PL/SQL TABELLEN
• Enthalten interne Komponenten
• Sind wiederverwendbar
Copyright © Oracle Corporation, 1998. All rights reserved.
PL/SQL Records
• Müssen eine oder mehrere Komponenten von
einfachen Variablen, RECORDs oder Feldern
enthalten.
• Sind in der Struktur ähnlich den Records in 3GL
• Sind nicht das gleiche wie die Tupel in der DBTabelle.
• Behandeln mehrere Felder als logische Einheit
SQL2 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Erzeugen eines PL/SQL Records
Syntax
TYPE type_name IS RECORD
(field_declaration[, field_declaration]…);
identifier
type_name;
Felddeklaration für die gleiche Struktur
field_name {field_type | variable%TYPE
| table.column%TYPE | table%ROWTYPE}
[[NOT NULL] {:= | DEFAULT} expr]
• Sind geeignet zum Holen von Tupeln aus der
Tabelle.
SQL3 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL4 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Erzeugen eines PL/SQL Records
Deklariere eine Variable zum Speichern
des Namens, Jobs und Gehalts für einen
neuen Angestellten.
Beispiel
...
TYPE emp_record_type IS RECORD
(ename
VARCHAR2(10),
job
VARCHAR2(9),
sal
NUMBER(7,2));
emp_record
emp_record_type;
...
SQL5 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Das %ROWTYPE Attribut
PL/SQL Record Struktur
Feld1 (Datentyp)
Feld2 (Datentyp)
Feld3 (Datentyp)
Feld1 (Datentyp)
Feld2 (Datentyp)
Feld3 (Datentyp)
empno number(4)
ename varchar2(10)
job varchar2(9)
Beispiel
SQL6 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Vorteile von %ROWTYPE
• Deklariere eine Variable entsprechend
einer Anzahl von Spalten in einer DBTabelle oder View.
• Anzahlen und Datentypen der
entsprechenden Spalten müssen nicht
bekannt sein.
• Der Name ist: tabellenname%ROWTYPE
• Die Anzahl und Datentypen der
entsprechenden Spalten könnte
geändert worden sein.
• Die Felder im Record nehmen die
Namen und Datentypen der Spalten der
Tabelle oder View an.
SQL7 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
• Günstig zum Wiederauffinden eines
Tupels mit dem SELECT Statement.
SQL8 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
%ROWTYPE Attribut - Beispiel
Deklariere eine Variable zum Speichern
der gleichen Information über eine
Abteilung, die in der DEPT-Tabelle
gespeichert ist.
dept_record
dept%ROWTYPE;
Deklariere eine Variable zum Speichern
der gleichen Information über einen
Angestellten, der in der EMP-Tabelle
gespeichert ist.
emp_record
PL/SQL Tabellen
• sind zusammengesetzt aus zwei
Komponenten:
– Datentyp des Primärschlüssels ist
BINARY_INTEGER
– Die Spalten sind vom einfachen oder
RECORD-Datentyp
• Dynamisches Erhöhen, da keine
Constraints bestehen.
emp%ROWTYPE;
SQL9 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Erzeugen einer PL/SQL Tabelle
Syntax
TYPE type_name IS TABLE OF
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
[INDEX BY BINARY_INTEGER];
identifier
type_name;
Deklariere eine PL/SQL Variable zum
Speichern eines Namens.
Beispiel
...
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
...
SQL11 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL10 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
PL/SQL Tabellen Strukturen
Primary Key
Spalte
...
...
1
Jones
2
Smith
3
Maduro
...
...
BINARY_INTEGER
einfach
SQL12 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Erzeugen einer PL/SQL Tabelle
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE hiredate_table_type IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
ename_table
ename_table_type;
hiredate_table hiredate_table_type;
BEGIN
ename_table(1) := 'CAMERON';
hiredate_table(8) := SYSDATE + 7;
IF ename_table.EXISTS(1) THEN
INSERT INTO ...
...
END;
SQL13 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
PL/SQL Tabelle für Records
• Eine Tabellenvariable mit dem %ROWTYPE
Attribut wird definiert.
• eine PL/SQL Variable wird zum Speichern
der Abteilungsinformationen deklariert.
Beispiel
DECLARE
TYPE dept_table_type IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_table dept_table_type;
-- Jedes Tupel der dept-Tabelle ist ein Satz
SQL14 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Cursor mit Parameter
Syntax
Das erweiterte Konzept für
Explizite Cursor
CURSOR cursor_name
[(parameter_name datatype, ...)]
IS
select_statement;
• Die Parameterwerte werden in den
Cursor eingefügt, wenn der Cursor offen
ist und die Anfrage ausgeführt wird.
• Cursor können mehrfach für unterschiedliche Sätze geöffnet werden.
Copyright © Oracle Corporation, 1998. All rights reserved.
SQL16 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Cursor mit Parameter
Eingeben der Abteilungsnummer und der
Tätigkeit in die WHERE-Klausel.
Beispiel
Behandlung von Ausnahmen
DECLARE
CURSOR c1
(v_deptno NUMBER, v_job VARCHAR2) IS
SELECT
empno, ename
FROM
emp
WHERE
deptno = v_deptno
AND
job = v_job;
BEGIN
OPEN c1(10, 'CLERK');
...
SQL17 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Copyright © Oracle Corporation, 1998. All rights reserved.
Ausnahmebehandlung
Behandlung der
Ausnahme
Ausnahme
tritt auf
Ausnahme
wird
behandelt
Ausnahme weiterleiten
DECLARE
DECLARE
BEGIN
BEGIN
EXCEPTION
EXCEPTION
END;
END;
Ausnahmetypen
• Vordefiniert im Oracle Server
• Nicht-vordefiniert im Oracle
Server
Ausnahme
tritt auf
• Benutzerdefiniert
}
Explizite
Behandlung
Ausnahme
wird nicht
behandelt
Ausnahme wird
weitergeleitet
SQL19 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Implizit
behandelt
SQL20 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Behandlung von Ausnahmen
Behandlung der Ausnahmen
Syntax
EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
statement1;
statement2;
. . .
[WHEN exception3 [OR exception4 . . .] THEN
statement1;
statement2;
. . .]
[WHEN OTHERS THEN
statement1;
statement2;
. . .]
SQL21 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Behandlung vordefinierter
Oracle Server Errors
• Einige vordefinierte Ausnahmen:
– NO_DATA_FOUND
– TOO_MANY_ROWS
– INVALID_CURSOR
– ZERO_DIVIDE
– DUP_VAL_ON_INDEX
• WHEN OTHERS ist die letzte Klausel.
• Das Schlüsselwort EXCEPTION startet
eine Ausnahme-Behandlungs-Routine.
• Verschiedene Ausnahmebehandlungen
sind erlaubt.
• Nur eine Routine hat gearbeitet bevor
der Block verlassen wurde.
SQL22 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
Vordefinierte Ausnahmen
Syntax
BEGIN SELECT ... COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
statement1;
statement2;
WHEN TOO_MANY_ROWS THEN
statement1;
WHEN OTHERS THEN
statement1;
statement2;
statement3;
END;
• Werden über den Namen angesprochen
SQL23 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved
SQL24 basierend auf OAI-Kurs Copyright © Oracle Corporation, 1998. All rights reserved