Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Erzeugen eines Clusters in ORACLE

Blockstruktur von PL/SQL
Zuordnung der Tabellen mit CREATE TABLE unter Angabe des
Clusterschlüssels.
Block Header
CREATE TABLE ‹table›
IS
(‹col› ‹datatype›,
.
. Declaration Section
.
‹col› ‹datatype›) BEGIN
CLUSTER ‹cluster›(‹column-list›); Execution Section
CREATE TABLE CSea EXCEPTION
(Name VARCHAR2(25) PRIMARY KEY,
Exception Section
Depth NUMBER)
CLUSTER Cl_Sea (Name); END;

CREATE TABLE Cgeo_Sea

• Block Header: Art des Objekts (Funktion, Prozedur oder
(Province VARCHAR2(32),
anonym (innerhalb eines anderen Blocks)), und
Country VARCHAR2(4),
Parameterdeklarationen.
Sea VARCHAR2(25))
CLUSTER Cl_Sea (Sea); • Declaration Section: Deklarationen der in dem Block
verwendeten Variablen,
Erzeugen des Clusterschlüsselindexes:
(Dies muß vor dem ersten DML-Kommando geschehen). • Execution Section: Befehlssequenz des Blocks,

CREATE INDEX ‹name› ON CLUSTER ‹cluster›; • Exception Section: Reaktionen auf eventuell auftretende
Fehlermeldungen.
CREATE INDEX ClSeaInd ON CLUSTER Cl_Sea;

Optimierung 144 PL/SQL 146

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Prozedurale Erweiterungen: PL/SQL Prozeduren

• keine prozeduralen Konzepte in SQL (Schleifen, CREATE [OR REPLACE] PROCEDURE ‹proc_name›
Verzweigungen, Variablendeklarationen) [(‹parameter-list›)]
• viele Aufgaben nur umständlich über Zwischentabellen IS ‹pl/sql-body›;
oder überhaupt nicht in SQL zu realisieren. /
– Transitive Hülle. • OR REPLACE: existierende Prozedurdefinition wird
• Programme repräsentieren anwendungsspezifisches überschrieben.
Wissen, das nicht in der Datenbank enthalten ist. • (‹parameter-list›): Deklaration der formalen Parameter:
(‹variable› [IN|OUT|IN OUT] ‹datatype›,
.
.
Erweiterungen .
‹variable› [IN|OUT|IN OUT] ‹datatype›)
• Einbettung von SQL in prozedurale Wirtssprachen
• IN, OUT, IN OUT: geben an, wie die Prozedur/Funktion auf
(embedded SQL); meistens Pascal, C, C++, oder
den Parameter zugreifen kann (Lesen, Schreiben, beides).
neuerdings auch Java (JDBC),
• Default: IN.
• Erweiterung von SQL um prozedurale Elemente innerhalb
der SQL-Umgebung, PL/SQL (Procedural language • Bei OUT und IN OUT muß beim Aufruf eine Variable
extensions to SQL). angegeben sein, bei IN ist auch eine Konstante erlaubt.

• Vorteile von PL/SQL: Bessere Integration der prozeduralen • ‹datatype›: alle von PL/SQL unterstützten Datentypen;
Elemente in die Datenbank; Nutzung in Prozeduren, ohne Längenangabe (VARCHAR2 anstelle VARCHAR2(20)).
Funktionen und Triggern. • ‹pl/sql-body› enthält die Definition der Prozedur in
• benötigt für Objektmethoden. PL/SQL.

PL/SQL 145 PL/SQL 147

 Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Funktionen

Analog, zusätzlich wird der Datentyp des Ergebnisses Beispiel: Prozedur

angegeben:
• Einfache Prozedur: PL/SQL-Body enthält nur SQL-Befehle
CREATE [OR REPLACE] FUNCTION ‹funct_name›
[(‹parameter-list›)]
RETURN ‹datatype› Informationen über Länder sind über mehrere Relationen
IS ‹pl/sql body›; verteilt.
/ CREATE OR REPLACE PROCEDURE InsertCountry
• PL/SQL-Funktionen werden mit (name VARCHAR2, code VARCHAR2, area NUMBER, pop NUMBER,
gdp NUMBER, inflation NUMBER, pop_growth NUMBER)
RETURN ‹ausdruck›;
IS
verlassen. Jede Funktion muß mindestens ein
BEGIN
RETURN-Statement im ‹body› enthalten.
INSERT INTO Country (Name,Code,Area,Population)
• Eine Funktion darf keine Seiteneffekte auf die Datenbasis VALUES (name,code,area,pop);
haben. INSERT INTO Economy (Country,GDP,Inflation)
VALUES (code,gdp,inflation);
Wichtig: nach dem Semikolon noch ein Vorwärtsslash (“/”), um
INSERT INTO Population (Country,Population_Growth)
die Deklaration auszuführen!!!
VALUES (code,pop_growth);
Im Falle von “... created with compilation errors”: END;
SHOW ERRORS; /
EXECUTE InsertCountry
ausgeben lassen.
(’Lummerland’, ’LU’, 1, 4, 50, 0.5, 0.25);
Prozeduren und Funktionen können mit DROP
PROCEDURE/FUNCTION ‹name› gelöscht werden.

PL/SQL 148 PL/SQL 150

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Funktion
Prozeduren und Funktionen
• Einfache Funktion: Einwohnerdichte eines Landes
• Aufruf von Prozeduren im PL/SQL-Skript:
‹procedure› (arg1,...,argn); CREATE OR REPLACE FUNCTION Density
(wenn ein formaler Parameter als OUT oder INOUT (arg VARCHAR2)
angegeben ist, muss das Argument eine Variable sein) RETURN number
• Aufruf in von Prozeduren in SQLPlus: IS
execute ‹procedure› (arg1,...,argn); temp number;
BEGIN
• Verwendung von Funktionen in PL/SQL:
SELECT Population/Area
... ‹function› (arg1,...,argn) ...
INTO temp
wie in anderen Programmiersprachen.
FROM Country
WHERE code = arg;
Die system-eigene Tabelle DUAL wird verwendet um das RETURN temp;
Ergebnis freier Funktionen ausgeben zu lassen. END;
SELECT ‹function› (arg1,...,argn) /
FROM DUAL; SELECT Density(’D’)
FROM dual;

PL/SQL 149 PL/SQL 151

 Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Zuweisung an Variablen
PL/SQL-Variablen und Datentypen.
• “klassisch” innerhalb des Programms:
Deklaration der PL/SQL-Variablen in der Declaration Section: a := b;

‹variable› ‹datatype› [NOT NULL] [DEFAULT ‹value›]; • Zuweisung des (einspaltigen und einzeiligen!) Ergebnisses
.
. einer Datenbankanfrage an eine PL/SQL-Variable:
.
‹variable› ‹datatype› [NOT NULL] [DEFAULT ‹value›]; SELECT ...
INTO ‹PL/SQL-Variable›
FROM ...
Einfache Datentypen:
BOOLEAN: TRUE, FALSE, NULL, Beispiel:
BINARY_INTEGER, PLS_INTEGER: Ganzzahlen mit Vorzeichen. the_name [Link]%TYPE
.
.
NATURAL, INT, SMALLINT, REAL, . . . : Numerische Datentypen. .
SELECT name
anzahl NUMBER DEFAULT 0; INTO the_name
name VARCHAR2(30); FROM country
WHERE name=’Germany’;

PL/SQL 152 PL/SQL 154

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

PL/SQL-Datentypen: Records

Ein RECORD enthält mehrere Felder, entspricht einem Tupel in

der Datenbasis:
TYPE city_type IS RECORD
anchored Typdeklaration
(Name [Link]%TYPE,
Country VARCHAR2(4),
Angabe einer PL/SQL-Variablen, oder Tabellenspalte (!) deren
Province VARCHAR2(32),
Typ man übernehmen will:
Population NUMBER,
‹variable› ‹variable’›%TYPE Longitude NUMBER,
[NOT NULL] [DEFAULT ‹value›]; Latitude NUMBER);
oder
the_city city_type;
‹variable› ‹table›.‹col›%TYPE
[NOT NULL] [DEFAULT ‹value›];
• cityname [Link]%TYPE anchored Typdeklaration für Records
• %TYPE wird zur Compile-Time bestimmt.
Records mit Tabellenzeilen-Typ deklarieren: %ROWTYPE:
‹variable› ‹table-name›%ROWTYPE;

Äquivalent zu oben:
the_city city%ROWTYPE;

PL/SQL 153 PL/SQL 155

 Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Zuweisung an Records

• Aggregierte Zuweisung: zwei Variablen desselben

Record-Typs:
‹variable› := ‹variable’›; PL/SQL-Datentypen: PL/SQL Tables
• Feldzuweisung: ein Feld wird einzeln zugewiesen:
Zusätzlich built-in-Funktionen und -Prozeduren:
‹[Link]› := ‹variable›|‹value›;
‹variable› := ‹pl/sql-table-name›.‹built-in-function›;
• SELECT INTO: Ergebnis einer Anfrage, die nur ein einziges oder
Tupel liefert: ‹pl/sql-table-name›.‹built-in-procedure›;
SELECT ...
• COUNT (fkt): Anzahl der belegten Zeilen.
INTO ‹record-variable›
plz_table.count = 2
FROM ... ;
• EXISTS (fkt): TRUE falls Tabelle nicht leer.
the_country country%ROWTYPE • DELETE (proc): Löscht alle Zeilen einer Tabelle.
.
.
. • FIRST/LAST (fkt): niedrigster/höchster belegter Indexwert.
SELECT * plz_table.first = 33334
INTO the_country
FROM country • NEXT/PRIOR(n) (fkt): Gibt ausgehend von n den
WHERE name=’Germany’; nächsthöheren/nächstniedrigen belegten Indexwert.
plz_table.next(33334) = 79110

Vergleich von Records:

Beim Vergleich von Records muß jedes Feld einzeln verglichen
werden.

PL/SQL 156 PL/SQL 158

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

SQL-Statements in PL/SQL

• DML-Kommandos INSERT, UPDATE, DELETE sowie SELECT

PL/SQL-Datentypen: PL/SQL Tables INTO-Statements.
• Diese SQL-Anweisungen dürfen auch PL/SQL-Variablen
Array-artige Struktur, eine Spalte mit beliebigem Datentyp (also enthalten.
auch RECORD), normalerweise mit BINARY_INTEGER indiziert.
• Befehle, die nur ein einziges Tupel betreffen können mit
TYPE ‹type› IS TABLE OF ‹datatype› RETURNING Werte an PL/SQL-Variablen zurückgeben:
[INDEX BY BINARY_INTEGER];
UPDATE ... SET ... WHERE ...
‹var› ‹type›; RETURNING ‹expr-list›
plz_table_type IS TABLE OF [Link]%TYPE INTO ‹variable-list›;
INDEX BY BINARY_INTEGER;
Z.B. Row-ID des betroffenen Tupels zurückgeben:
plz_table plz_table_type;
DECLARE rowid ROWID;
• Adressierung: ‹var›(1) BEGIN
.
plz_table(79110):= Freiburg; .
.
plz_table(33334):= Kassel; INSERT INTO Politics (Country,Independence)
• sparse: nur die Zeilen gespeichert, die Werte enthalten. VALUES (Code,SYSDATE)
RETURNING ROWID
Tabellen können auch als Ganzes zugewiesen werden INTO rowid;
.
.
.
andere_table := plz_table;
END;
• DDL-Statements in PL/SQL nicht direkt unterstützt:
DBMS_SQL-Package.
PL/SQL 157 PL/SQL 159
 Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kontrollstrukturen Cursorbasierter Datenbankzugriff

• IF THEN - [ELSIF THEN] - [ELSE] - END IF, Zeilenweiser Zugriff auf eine Relation aus einem
• verschiedene Schleifen: PL/SQL-Programm.

• Simple LOOP: LOOP ... END LOOP; Cursordeklaration in der Declaration Section:

• WHILE LOOP: CURSOR ‹cursor-name› [(‹parameter-list›)]

WHILE ‹bedingung› LOOP ... END LOOP; IS
‹select-statement›;
• Numeric FOR LOOP:
FOR ‹loop_index› IN • (‹parameter-list›): Parameter-Liste,
[REVERSE] ‹Anfang› .. ‹Ende› • nur IN als Übergaberichtung erlaubt.
LOOP ... END LOOP;
• Zwischen SELECT und FROM auch PL/SQL-Variablen und
Die Variable ‹loop_index› wird dabei automatisch als
PL/SQL-Funktionen. PL/SQL-Variablen können ebenfalls in
INTEGER deklariert.
den WHERE-, GROUP- und HAVING-Klauseln verwendet
• EXIT [WHEN ‹bedingung›]: LOOP verlassen. werden.
• den allseits beliebten GOTO-Befehl mit Labels:
‹‹label_i›› ... GOTO label_j;
Beispiel
• NULL-Werte verzweigen immer in den ELSE-Zweig.
Alle Städte in dem in der Variablen the_country angegebenen
• GOTO: nicht von außen in ein IF-Konstrukt, einen LOOP, oder Land:
einen lokalen Block hineinspringen, nicht von einem
DECLARE CURSOR cities_in
IF-Zweig in einen anderen springen.
(the_country [Link]%TYPE)
• hinter einem Label muß immer mindestens ein IS SELECT Name
ausführbares Statement stehen; FROM City
• NULL Statement. WHERE Country=the_country;
PL/SQL 160 PL/SQL 162

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Cursore
Geschachtelte Blöcke • OPEN ‹cursor-name›[(‹argument-list›)];

Innerhalb der Execution Section werden anonyme Blöcke zur Erzeugt mit dem gegebenen SELECT-Statement eine
Strukturierung verwendet. Hier wird die Declaration Section mit virtuelle Tabelle mit einem “Fenster”, das über einem Tupel
DECLARE eingeleitet (es gibt keinen Block Header): stehen kann und schrittweise vorwärts bewegt wird. Mit
OPEN wird die Anfrage ausgeführt und der Cursor
BEGIN initialisiert:
-- Befehle des äußeren Blocks --
DECLARE OPEN cities_in (’D’);
-- Deklarationen des inneren Blocks
BEGIN OPEN
Name
-- Befehle des inneren Blocks FETCH
END; Bonn
FETCH
-- Befehle des äußeren Blocks -- Kiel
FETCH
END; Hamburg
..
.

PL/SQL 161 PL/SQL 163

 Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Cursore

• FETCH ‹cursor-name› INTO ‹record-variable›; oder

FETCH ‹cursor-name› INTO ‹variable-list›;
bewegt den Cursor auf die nächste Zeile des Ergebnisses
der Anfrage und kopiert diese in die angegebene Cursor FOR LOOP
Record-Variable oder Variablenliste.
Diese wird kann z.B. mit ‹cursor-name›%ROWTYPE mit dem FOR ‹record_index› IN ‹cursor-name›
Record-Typ des Cursors definiert werden: LOOP ... END LOOP;
‹variable› ‹cursor-name›%ROWTYPE; • ‹record_index› wird dabei automatisch als Variable vom
• CLOSE ‹cursor-name›; schließt einen Cursor. Typ ‹cursor-name›%ROWTYPE deklariert,
• ‹record_index› immer von einem Record-Type – ggf.
einspaltig.
Beispiel
• Es wird automatisch ein OPEN ausgeführt,
DECLARE CURSOR cities_in
(the_country [Link]%TYPE) • bei jeder Ausführung des Schleifenkörpers wird
IS SELECT Name automatisch ein FETCH ausgeführt,
FROM City • → Schleifenkörper enthält i.a. keinen FETCH-Befehl,
WHERE Country=the_country;
• am Ende wird automatisch ein CLOSE ausgeführt,
city_in cities_in%ROWTYPE;
• Spalten müssen explizit adressiert werden.
BEGIN
OPEN cities_in (’D’);
FETCH cities_in INTO city_in;
CLOSE cities_in;
END;
PL/SQL 164 PL/SQL 166

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Cursore

nicht möglich:
OPEN cities_in (’D’); Cursor FOR LOOP
OPEN cities_in (’CH’);
FETCH cities_in INTO ‹variable›; Beispiel: Für jede Stadt in dem gegebenen Land soll eine
Prozedur “request_Info” aufgerufen werden:
• ein parametrisierter Cursor,
DECLARE CURSOR cities_in
• nicht eine Familie von Cursoren! (the_country [Link]%TYPE)
IS SELECT Name
FROM City
Cursore: Attribute
WHERE Country = the_country;
• ‹cursor-name›%ISOPEN: Cursor offen? BEGIN
• ‹cursor-name›%FOUND: Solange ein Cursor bei der letzten the_country:=’D’; % oder sonstwie setzen
FETCH-Operation ein neues Tupel gefunden hat, ist FOR the_city IN cities_in(the_country)
‹cursor-name›%FOUND = TRUE. LOOP
request_Info(the_city.name);
• ‹cursor-name›%NOTFOUND: TRUE wenn man alle Zeilen
END LOOP;
eines Cursors geFETCHt hat.
END;
• ‹cursor-name›%ROWCOUNT: Anzahl der von einem Cursor
bereits gelesenen Tupel.
• nicht innerhalb eines SQL-Ausdrucks.

PL/SQL 165 PL/SQL 167

 Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Cursor FOR LOOP Zugriffsrechte

• SELECT-Anfrage kann auch direkt in die FOR-Klausel
Benutzung von Funktionen/Prozeduren:
geschrieben werden.
• Benutzungsrechte vergeben:
CREATE TABLE big_cities GRANT EXECUTE ON ‹procedure/function› TO ‹user›;
(name VARCHAR2(25)); • Prozeduren und Funktionen werden jeweils mit den
BEGIN Zugriffsrechten des Besitzers ausgeführt.
FOR the_city IN • nach
SELECT Name GRANT EXECUTE ON ‹procedure/function› TO ‹user›;
FROM City
kann dieser User die Prozedur/Funktion auch dann
WHERE Country = the_country
aufrufen, wenn er kein Zugriffsrecht auf die dabei
AND Population > 1000000
benutzten Tabellen hat.
LOOP
INSERT INTO big_cities • Möglichkeit, Zugriffsberechtigungen strenger zu
VALUES (the_city.Name); formulieren als mit GRANT ... ON ‹table› TO ...:
END LOOP; Zugriff nur in einem ganz speziellen, durch die Prozedur
END; oder Funktion gegebenen Kontext.

PL/SQL 168 PL/SQL 170

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Geschachtelte Tabellen unter PL/SQL

Nested_Languages
Country Languages
D German 100
Schreibzugriff via Cursor CH German 65
French 18
Mit WHERE CURRENT OF ‹cursor-name› kann man auf das
Italian 12
zuletzt von dem genannten Cursor geFETCHte Tupel zugreifen:
Romansch 1
UPDATE ‹table-name›
SET ‹set_clause› FL NULL
WHERE CURRENT OF ‹cursor_name›; F French 100
.. ..
DELETE FROM ‹table-name› . .
WHERE CURRENT OF ‹cursor_name›; Nutzung geschachtelter Tabellen in O RACLE nicht ganz
• Dabei bestimmt die Positionierung des Cursors bezüglich unproblematisch:
der Basistabellen den Ort der Änderung (im Gegensatz zu “Bestimme alle Länder, in denen Deutsch gesprochen wird,
View Updates). sowie den Anteil der deutschen Sprache in dem Land”
Eine solche Anfrage muß für jedes Tupel in Nested_Languages
die innere Tabelle untersuchen.
• SELECT THE kann jeweils nur ein Objekt zurückgeben,
• keine Korrelation mit umgebenden Tupeln möglich.
• Verwendung einer (Cursor-)Schleife.
PL/SQL 169 PL/SQL 171
 Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Geschachtelte Tabellen unter PL/SQL

CREATE TABLE tempCountries

(Land VARCHAR2(4),
Sprache VARCHAR2(20), Einschub: Integritätsbedingungen
Anteil NUMBER);
• Spalten- und Tabellenbedingungen
CREATE OR REPLACE PROCEDURE Search_Countries
(the_Language IN VARCHAR2) • Wertebereichsbedingungen (domain constraints),
IS CURSOR countries IS • Verbot von Nullwerten,
SELECT Code
• Uniqueness und Primärschlüssel-Bedingungen,
FROM Country;
BEGIN • CHECK-Bedingungen.
DELETE FROM tempCountries; ! Alles nur als Bedingungen an eine Zeile innerhalb einer
FOR the_country IN countries Tabelle formulierbar.
LOOP
INSERT INTO tempCountries
SELECT the_country.code,Name,Percentage Assertions
FROM THE(SELECT Languages
• Bedingungen, die den gesamten DB-Zustand betreffen.
FROM Nested_Language
WHERE Country = the_country.Code) CREATE ASSERTION ‹name› CHECK (‹bedingung›)
WHERE Name = the_Language; • Diese werden allerdings von O RACLE8 nicht unterstützt.
END LOOP;
⇒ Also muß man sich etwas anderes überlegen.
END;
/
EXECUTE Search_Countries(’German’);
SELECT * FROM tempCountries;
PL/SQL 172 PL/SQL 174

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Trigger

• spezielle Form von PL/SQL-Prozeduren,

• werden beim Eintreten eines bestimmten Ereignisses
ausgeführt.
• Spezialfall aktiver Regeln nach dem
Event-Condition-Action-Paradigma.
• einer Tabelle (oft auch noch einer bestimmten Spalte)
zugeordnet.

• Bis jetzt: Funktionen und Prozeduren werden durch den • Bearbeitung wird durch das Eintreten eines Ereignisses
Benutzer explizit aufgerufen. (Einfügen, Ändern oder Löschen von Zeilen der Tabelle)
ausgelöst (Event).
• Trigger: Ausführung wird durch das Eintreten eines
Ereignisses in der Datenbank angestoßen. • Ausführung von Bedingungen an den Datenbankzustand
abhängig (Condition).
• Action:
• vor oder nach der Ausführung der entsprechenden
aktivierenden Anweisung ausgeführt.
• einmal pro auslösender Anweisung (Statement-Trigger)
oder einmal für jede betroffene Zeile (Row-Trigger)
ausgeführt.
• Trigger-Aktion kann auf den alten und neuen Wert des
gerade behandelten Tupels zugreifen.

PL/SQL 173 PL/SQL 175

 Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Trigger

CREATE [OR REPLACE] TRIGGER ‹trigger-name›

BEFORE | AFTER
{INSERT | DELETE | UPDATE} [OF ‹column-list›]
[ OR {INSERT | DELETE | UPDATE} [OF ‹column-list›]]
Trigger: Beispiel
.
.
. Wenn ein Land neu angelegt wird, wird ein Eintrag in Politics
[ OR {INSERT | DELETE | UPDATE} [OF ‹column-list›]] mit dem aktuellen Jahr erzeugt:
ON ‹table›
CREATE TRIGGER new_Country
[REFERENCING OLD AS ‹name› NEW AS ‹name›]
AFTER INSERT ON Country
[FOR EACH ROW]
FOR EACH ROW
[WHEN (‹condition›)]
BEGIN
‹pl/sql-block›;
INSERT INTO Politics (Country,Independence)
• BEFORE, AFTER: Trigger wird vor/nach der auslösenden VALUES (:[Link],SYSDATE);
Operation ausgeführt. END;
• OF ‹column› (nur für UPDATE) schränkt Aktivierung auf /
angegebene Spalte ein.
INSERT INTO Country (Name,Code)
• Zugriff auf Zeileninhalte vor und nach der Ausführung der
VALUES (’Lummerland’, ’LU’);
aktivierenden Aktion mittels :OLD bzw. :NEW. (Aliasing
durch REFERENCING OLD AS ... NEW AS ...).
SELECT * FROM Politics;
Schreiben in :NEW-Werte nur mit BEFORE-Trigger.
• FOR EACH ROW: Row-Trigger, sonst Statement-Trigger.
• WHEN (‹condition›): zusätzliche Bedingung; OLD und NEW
sind in ‹condition› erlaubt.
PL/SQL 176 PL/SQL 178

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Trigger: Mutating Tables

• Zeilenorientierte Trigger: immer direkt vor/nach der

Veränderung einer Zeile aufgerufen
Trigger: Beispiel • jede Ausführung des Triggers sieht einen anderen
Datenbestand der Tabelle, auf der er definiert ist, sowie der
Wenn ein Landes-Code geändert wird, pflanzt sich diese Tabellen, die er evtl. ändert
Änderung auf die Relation Province fort:
• ; Ergebnis abhängig von der Reihenfolge der veränderten
CREATE OR REPLACE TRIGGER change_Code Tupel
BEFORE UPDATE OF Code ON Country
O RACLE: Betroffene Tabellen werden während der gesamten
FOR EACH ROW
Aktion als mutating gekennzeichnet, können nicht von Triggern
BEGIN
gelesen oder geschrieben werden.
UPDATE Province
SET Country = :[Link] Nachteil: Oft ein zu strenges Kriterium.
WHERE Country = :[Link]; • Trigger soll auf Tabelle zugreifen auf der er selber definiert
END; ist.
/
– Nur das auslösende Tupel soll von dem Trigger
gelesen/geschrieben werden: Verwendung eines
UPDATE Country
BEFORE-Triggers und der :NEW- und :OLD-Variablen
SET Code = ’UK’
– Es sollen neben dem auslösenden Tupel auch weitere
WHERE Code = ’GB’;
Tupel verwendet werden: Verwendung eines
Statement-orientierten Triggers
• Trigger soll auf andere Tabellen zugreifen: Verwendung von
Statement-Triggern und ggf. Hilfstabellen.

PL/SQL 177 PL/SQL 179

 Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

View Updates und INSTEAD OF-Trigger

INSTEAD OF-Trigger CREATE OR REPLACE TRIGGER InsAllCountry

INSTEAD OF INSERT ON AllCountry
• View Updates: Updates müssen auf Basistabellen FOR EACH ROW
umgesetzt werden. BEGIN
• View-Update-Mechanismen eingeschränkt. INSERT INTO
Country (Name,Code,Population,Area)
• INSTEAD OF-Trigger: Änderung an einem View wird durch
VALUES (:[Link], :[Link],
andere SQL-Anweisungen ersetzt.
:[Link], :[Link]);
CREATE [OR REPLACE] TRIGGER ‹trigger-name› INSERT INTO Economy (Country,Inflation)
INSTEAD OF VALUES (:[Link], :[Link]);
{INSERT | DELETE | UPDATE} ON ‹view› INSERT INTO Population
[REFERENCING OLD AS ‹name› NEW AS ‹name›] (Country, Population_Growth,infant_mortality)
[FOR EACH STATEMENT] VALUES (:[Link], :NEW.Population_Growth,
‹pl/sql-block›; :NEW.infant_mortality);
• Keine Einschränkung auf bestimmte Spalten möglich END;
/
• Keine WHEN-Klausel
• aktualisiert Country, Economy und Population.
• Default: FOR EACH ROW
• Trigger New_Country (AFTER INSERT ON COUNTRY)
aktualisiert zusätzlich Politics.

PL/SQL 180 PL/SQL 182

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Fehlerbehandlung

• Declaration Section: Deklaration (der Namen)

View Updates und INSTEAD OF-Trigger
benutzerdefinierter Exceptions.
CREATE OR REPLACE VIEW AllCountry AS DECLARE ‹exception› EXCEPTION;
SELECT Name, Code, Population, Area, • Exception Section: Definition der beim Auftreten einer
GDP, Population/Area AS Density, Exception auszuführenden Aktionen.
Inflation, population_growth, WHEN ‹exception›
infant_mortality THEN ‹PL/SQL-Statement›;
FROM Country, Economy, Population WHEN OTHERS THEN ‹PL/SQL-Statement›;
WHERE [Link] = [Link]
AND [Link] = [Link]; • Exceptions können dann an beliebigen Stellen des
PL/SQL-Blocks durch RAISE ausgelöst werden.
INSERT INTO AllCountry IF ‹condition›
(Name, Code, Population, Area, GDP, THEN RAISE ‹exception›;
Inflation, population_growth, infant_mortality)
VALUES (’Lummerland’,’LU’,4,1,0.5,0,25,0);
Fehlermeldung: Über ein Join-View kann nur eine Basistabelle Ablauf
modifiziert werden.
• auslösen einer Exception
• entsprechende Aktion der WHEN-Klausel ausführen
• innersten Block verlassen (oft Anwendung von anonymen
Blöcken sinnvoll)
PL/SQL 181 PL/SQL 183
 Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Weitere PL/SQL-Features
Trigger/Fehlerbehandlung: Beispiel

• Packages: Möglichkeit, Daten und Programme zu kapseln;

• FOR UPDATE-Option bei Cursordeklarationen;
Nachmittags dürfen keine Städte gelöscht werden: • Cursorvariablen;
CREATE OR REPLACE TRIGGER nachm_nicht_loeschen • Exception Handlers;
BEFORE DELETE ON City
• benannte Parameterübergabe;
BEGIN
IF TO_CHAR(SYSDATE,’HH24:MI’) • PL-SQL Built-in Funktionen: Parsing, String-Operationen,
BETWEEN ’12:00’ AND ’18:00’ Datums-Operationen, Numerische Funktionen;
THEN RAISE_APPLICATION_ERROR • Built-in Packages.
(-20101,’Unerlaubte Aktion’);
END IF;
END;
/ • Definition komplexer Transaktionen,
• Verwendung von SAVEPOINTs für Transaktionen,

PL/SQL 184 PL/SQL 186

Praktikum: Datenbankprogrammierung in SQL/ORACLE Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel

CREATE OR REPLACE TRIGGER bla

INSTEAD OF INSERT ON AllCountry
FOR EACH ROW
BEGIN Objekt-Relationale Datenbanksysteme
IF user=’may’
THEN NULL; Integration von relationalen Konzepten und Objektorientierung:
END IF;
• Komplexe Datentypen: Erweiterung des Domain-Konzepts
...
von SQL-2
END;
/ • Abstrakte Datentypen (“Objekttypen”): Objekt-Identität und
Kapselung interner Funktionalität.
INSERT INTO AllCountry • Spezialisierung: Klassenhierarchie; Subtypen als
(Name, Code, Population, Area, GDP, Inflation, Spezialisierung allgemeiner Typen.
population_growth, infant_mortality)
• Tabellen als Subtabellen von Tabellen.
VALUES (’Lummerland’,’LU’,4,1,0.5,0,25,0);
• Funktionen als Bestandteile eines ADT’s oder von
1 Zeile wurde erstellt. Tabellen, oder freie Funktionen.
• Methoden- und Funktionsaufrufe im Rahmen von
SQL> select * from allcountry where Code=’LU’;
SELECT-Ausdrücken.

Es wurden keine Zeilen ausgewaehlt

(aus A. Christiansen, M. Höding, C. Rautenstrauch und
G. Saake, O RACLE 8 effizient einsetzen, Addison-Wesley,
1998)
PL/SQL 185 Objektorientierung in O RACLE 8 187