9. Einführung in PL/SQL Motivation für PL/SQL Aufbau von ...home.edvsz.fh-osnabrueck.de/skleuker/WS12_DB/Datenbanksysteme… · Minr NUMBER(5), Name VARCHAR(10), Gehalt NUMBER(4),

Datenbanksysteme 198Prof. Dr. Stephan Kleuker

9. Einführung in PL/SQL

• Motivation für PL/SQL• Aufbau von PL/SQL-Programmen• Alternativen• Steuerung des Programmablaufs

• Records• Ausnahmebehandlung


Einwurf

• Oracle nutzt Transaktionssteuerung (wie alle echten Datenbanken)

• Nutzer haben Gefühl, dass sie individuell arbeiten• Vorstellung: Nutzer arbeiten auf lokalen Kopien• erst mit Befehl COMMIT;werden Änderungen

endgültig übernommen• kommen mehrere Nutzer in Konflikt (gleiches Datum

bearbeiten), findet DB Lösung (z. B. ein Nutzer muss warten)

• Fazit: Nach INSERT-Befehlen soll COMMIT;stehen


Erweiterung relationaler Datenbanken

• Einbettung von SQL in prozedurale oder objektorientierte Wirtssprachen (embedded SQL); meistens C, C++, oder Java (JDBC)

• Erweiterung von SQL um prozedurale Elemente innerhalb der SQL-Umgebung, PL/SQL (Procedurallanguage extensions to SQL)

• Vorteile von PL/SQL: Bessere Integration der prozeduralen Elemente in die Datenbank; Nutzung in Prozeduren, Funktionen und Triggern


Warum Erweiterung von SQL sinnvoll ist

• keine prozeduralen Konzepte in SQL (Schleifen, Verzweigungen, Variablendeklarationen)

• viele Aufgaben nur umständlich über Zwischentabellen oder überhaupt nicht in SQL zu realisieren.– Transitive Hülle

• Programme repräsentieren anwendungsspezifisches Wissen, das nicht in der Datenbank enthalten ist


Warum PL/SQL generell interessant ist

• PL/SQL erhöht die Funktionalität und Mächtigkeit von SQL-Anfragen

• Obwohl PL/SQL Oracle-spezifisch ist, können viele zentralen Ideen (z.B. Cursor) auch genutzt werden, wenn sie SQL in andere Programmiersprachen einbetten (z. B. Transact SQL für MS Server)

• Für Informatiker ist es uninteressant, welche Programmiersprache sie können, sie müssen aber die Fähigkeit haben, sich schnell in eine beliebige Sprache einzuarbeiten; PL/SQL ist ein gutes Beispiel, diese Fähigkeit zu prüfen


Anmerkungen zu PL/SQL in dieser Veranstaltung

• PL/SQL wird schrittweise erweitert, wir betrachten nur Basiskonzepte (z.B. zum Erstellen von Triggern)

• PL/SQL ist eine prozedurale Sprache, die sich an Ada anlehnt (z.B. strenge Typisierung), C -Kenntnisse helfen aber auch

• PL/SQL hat einen „objekt-basierten“-Ansatz, erlaubt die Aufteilung der Software in Packages

• In Oracle existieren einige Built-In-Packages, die hier nur am Rand betrachtet werden

• Achtung: PL/SQL wird kontinuierlich erweitert, ab und zu klappt nicht das, was einem logisch erscheint


Einschub: SEQUENCE und DUAL

• In Oracle gibt es keine Auto-Inkrement-Funktion, ma n kann aber spezielle Zähler (Sequenzen) definieren

• CREATE SEQUENCE mi INCREMENT BY 1;

definiert eine Sequenz mi , die den Startwert 1 hat, • durch den Aufruf mi.NEXTVAL , wird der Wert von mi zurück

gegeben und um eins erhöht• Nutzungsbeispiel:

INSERT INTO Angestellte VALUES(mi.NEXTVAL,‘Meier‘,30);

INSERT INTO Angestellte VALUES(mi.NEXTVAL,‘Mücke‘,25);

• Will man Informationen über verschiedene Werte von Oracle erfahren, kann man dazu die Dummy-Tabelle DUALnutzen

• Beispiel: Anfrage einer Sequenz mit ErhöhungSELECT mi.NEXTVAL FROM DUAL;


Einstieg ohne DB (Hello World)

CREATE OR REPLACE PROCEDURE Hello IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World');

END Hello;

1. Zeile: Definition einer Prozedur (Parameter im nächsten Schritt)

2.-4. Zeile: Rumpf der Prozedur mit Ausgabebefehl

Ausführung: EXECUTE Hello;


Anmerkung zur SW -Entwicklung

DBMS_OUTPUT.PUT_LINE('...') ist Prozedur eines Zusatzpakets DBMS_OUTPUT und kann bei inhaltlicher Fehlersuche hilfreich sein


Aufbau einer PL/SQL-Prozedur oder Funktion

CREATE OR REPLACE PROCEDURE<PName> (<Parameter>) IS

<Variablenname> <VariablenTyp>BEGIN

<PL/SQL-Programmteile und SQL-Anfragen>EXCEPTION

<Behandlung von Ausnahmen (optionaler Anteil)>END;

CREATE OR REPLACE FUNCTION<FName> (<Parameter>) RETURN<Ergebnistyp> IS

... (wie oben, aber mindestens ein Befehl RETURN<Ergebnis>)


Beispiel-Datenbank

-- einfache VerwaltungstabelleCREATE TABLE ANGESTELLTE(

Minr NUMBER(5),Name VARCHAR(10),Gehalt NUMBER(4),PRIMARY KEY (MiNr)

);-- einfache KontrolltabelleCREATE TABLE PROTOKOLL(

WER VARCHAR(10),WAS VARCHAR(10),WO VARCHAR(15),WEN VARCHAR(12),WANN DATE

);-- Zähler für Minr (ohne Test auf Obergrenze)CREATE SEQUENCE mi INCREMENT BY 1;


Einfache Einfüge-Prozedur

CREATE OR REPLACE PROCEDURE EIN0

(N IN VARCHAR, G IN NUMBER) IS

BEGIN

INSERT INTO ANGESTELLTE VALUES(mi.NEXTVAL,N,G);

END;

• Prozeduren und Funktionen können Parameter haben• für Parameter ist die Bearbeitungsart ( IN OUT INOUT )

anzugeben, bei Funktionen nur IN (ist default)• bei VARCHARund NUMBER-Parametern darf keine Formatangabe

(Länge) übergeben werden• In SQL-Statements können (einfache, d.h. keine Tabell en)

Variablen referenziert werden


Einfüge-Prozedur mit Protokoll

CREATE OR REPLACE PROCEDURE EIN1 (N IN VARCHAR, G IN NUMBER) IS

mi_Nummer NUMBER;/* USER_USERS ist Oracle-Tabelle mit Informationen über

den aktuellen Nutzer (Name im Attribut USERNAME) */NUTZER USER_USERS.USERNAME%TYPE;

BEGINSELECT mi.NEXTVAL

INTO mi_NummerFROM DUAL;

INSERT INTO ANGESTELLTE VALUES(mi_Nummer,N,G);SELECT USER_USERS.USERNAME

INTO NUTZERFROM USER_USERS;

INSERT INTO PROTOKOLL VALUES(NUTZER,'EINFUEGEN', 'ANGESTELLTE',mi_Nummer,SYSDATE);

END;

Datenbanksysteme 211

Ausführung

SELECT * FROM Angestellte;

SELECT * FROM Protokoll;

EXECUTE EIN1('ich',42);

SELECT * FROM Angestellte;

SELECT * FROM Protokoll;

Prof. Dr. Stephan Kleuker

Keine Zeilen gewähltKeine Zeilen gewähltanonymer Block abgeschlossen

MINR NAME GEHALT----- ---------- ------

1 ich 42

WER WAS WO WEN WANN ---------- ---------- ------------ ---- --------SKLEUKER EINFUEGEN ANGESTELLTE 1 19.11.12


SQL in PL/SQL

• In PL/SQL sind grundsätzlich alle SQL DML-Befehle (SELECT, INSERT, UPDATE, DELETE ) erlaubt

• für DDL-Befehle ( CREATE, DROP) muss Zusatzpaket benutzt werden (hier nicht betrachtet)

• SELECT-Befehle müssen zusätzlich die INTO-Zeile haben und müssen genau ein Ergebnis liefern

• Mit Tabelle.Spaltenname %TYPEkann direkt auf den Typen einer Tabellenspalte zugegriffen werden

• Abfragen von Funktionswerten oder Sequenz-Werten werden immer auf die Dummy-Tabelle DUALbezogen

• USER_USERSist eine der Systemtabellen


Alternativen

CREATE TABLE Gehaltsklassen(CREATE TABLE Gehaltsklassen(CREATE TABLE Gehaltsklassen(CREATE TABLE Gehaltsklassen(klasse VARCHAR(1),klasse VARCHAR(1),klasse VARCHAR(1),klasse VARCHAR(1),anzahl NUMBER(3)anzahl NUMBER(3)anzahl NUMBER(3)anzahl NUMBER(3)

););););CREATE OR REPLACE PROCEDURE EIN (N IN VARCHAR, G IN NUMBER) IS CREATE OR REPLACE PROCEDURE EIN (N IN VARCHAR, G IN NUMBER) IS CREATE OR REPLACE PROCEDURE EIN (N IN VARCHAR, G IN NUMBER) IS CREATE OR REPLACE PROCEDURE EIN (N IN VARCHAR, G IN NUMBER) IS BEGINBEGINBEGINBEGININSERT INTO ANGESTELLTE VALUES(INSERT INTO ANGESTELLTE VALUES(INSERT INTO ANGESTELLTE VALUES(INSERT INTO ANGESTELLTE VALUES(mi.NEXTVALmi.NEXTVALmi.NEXTVALmi.NEXTVAL,N,G);,N,G);,N,G);,N,G);IF G>90IF G>90IF G>90IF G>90THEN UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='A';THEN UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='A';THEN UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='A';THEN UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='A';

DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse A eingefuegt');ELSIF G>60ELSIF G>60ELSIF G>60ELSIF G>60THEN THEN THEN THEN BEGINBEGINBEGINBEGINUPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='B';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='B';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='B';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='B';DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegt');DBMS_OUTPUT.PUT_LINE('In Klasse B eingefuegt');

END; END; END; END; ELSEELSEELSEELSEUPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='C';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='C';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='C';UPDATE Gehaltsklassen SET anzahl=anzahl+1 WHERE klasse='C';

END IF; END IF; END IF; END IF; END;END;END;END;

INSERT INTO Gehaltsklassen VALUES('A',0);INSERT INTO Gehaltsklassen VALUES('A',0);INSERT INTO Gehaltsklassen VALUES('A',0);INSERT INTO Gehaltsklassen VALUES('A',0);INSERT INTO Gehaltsklassen VALUES('B',0);INSERT INTO Gehaltsklassen VALUES('B',0);INSERT INTO Gehaltsklassen VALUES('B',0);INSERT INTO Gehaltsklassen VALUES('B',0);INSERT INTO Gehaltsklassen VALUES('C',0);INSERT INTO Gehaltsklassen VALUES('C',0);INSERT INTO Gehaltsklassen VALUES('C',0);INSERT INTO Gehaltsklassen VALUES('C',0);


Strukturierung des Ablaufs

• Alternativen werden durch die IF-THEN-ELSIF-ELSIF-...-ELSE-END IF Konstruktion beschrieben

• Wird eine Bedingung nach „undefiniert“ ausgewertet, wird in den ELSE-Zweig gesprungen

• Grundsätzlich können in PL/SQL-Rümpfen weitere PL/SQL-Blöcke definiert werden

• weitere Blöcke dienen zur Strukturierung der Programme und sind hilfreich bei der Reaktion auf Ausnahmen (Fehlerfälle)

• Es gibt die Anweisung NULL; (steht für den leeren Befehl)

• Die „üblichen“ Regeln für Sichtbarkeiten von Variablen werden übernommen (sollten bei guter Programmierung keine Rolle spielen)


Schleifen (Beispiele)

CREATE OR REPLACE PROCEDURE FREMD0(Anzahl NUMBER, Firma VARCHAR, Gehalt NUMBER) IS

BEGINFOR i IN 1 .. AnzahlLOOP

EIN(Firma || i, Gehalt); END LOOP;

END;

CREATE OR REPLACE PROCEDURE FREMD(Anzahl NUMBER, Firma VARCHAR, Gehalt NUMBER) ISi INTEGER DEFAULT 1;

BEGINWHILE i<=Anzahl LOOP

EIN(Firma || i, Gehalt);i:=i+1;

END LOOP;END;


Übersicht - Schleifen

• Simple LOOP: LOOP ... END LOOP;

• WHILE LOOP:WHILE <bedingung> LOOP ... END LOOP;

• Numeric FOR LOOP:FOR <loop_index>

IN [REVERSE] <Anfang> .. <Ende>

LOOP … END LOOP;

• Die Variable <loop_index> wird dabei automatisch als INTEGER deklariert.

• EXIT [WHEN <bedingung>] : LOOP Verlassen (ist aber schlechter Programmierstil)


Zugriffsrechte bei Prozeduren / Funktionen

• Benutzungsrechte vergeben:GRANT EXECUTE ON <procedure/function>

TO <user>;

• Prozeduren und Funktionen werden jeweils mit den Zugriffsrechten des Besitzers ausgeführt

• d.h. der Nutzer kann die Prozedur/Funktion auch dann aufrufen, wenn er kein Zugriffsrecht auf die dabei benutzten Tabellen hat

• Rechtesteuerung in späteren VL

• Anmerkung: Sieht man PL/SQL als serverseitige Programmierung, ist dies ein zentrales Argument für PL/SQL


Records

• Ein RECORDenthält mehrere Felder, entspricht einem Tupel in der Datenbasis:TYPE Citytype IS RECORD(

Name City.Name%TYPE,

Country VARCHAR(4),

Province VARCHAR(32),

Population NUMBER,

Longitude NUMBER,

Latitude NUMBER

);

• Nutzung (Deklaration einer Variablen diesen Typs):theCity Citytype;

• Semantisch verhalten sich RECORD wie struct in C (k ein direkter Vergleich, direkte Zuweisung möglich, bei Zuweisung werden Werte kopiert)


Etwas Semantikanalyse (1/2)

CREATE OR REPLACE PROCEDURE RecordTest ISTYPE T1 IS RECORD(

X NUMBER,Y NUMBER

);TYPE T2 IS RECORD(

X NUMBER,Y NUMBER

); A T1;B T1 DEFAULT A;C T2;

BEGINA.x:=1;A.y:=2;-- DBMS_OUTPUT.PUT_LINE(A); geht nichtDBMS_OUTPUT.PUT_LINE('A.x= '||A.x);DBMS_OUTPUT.PUT_LINE('A.y= '||A.y);DBMS_OUTPUT.PUT_LINE('B.x= '||B.x);DBMS_OUTPUT.PUT_LINE('B.y= '||B.y);-- DBMS_OUTPUT.PUT_LINE(B.y); liefert leere Zeile!

Ausgabe:A.x= 1A.y= 2B.x=B.y=


Etwas Semantikanalyse (2/2)

B.x:=1;B.y:=2;-- IF A=B ist verbotenIF A.x=B.x AND A.y=B.y

THEN DBMS_OUTPUT.PUT_LINE('A gleich B');ELSE DBMS_OUTPUT.PUT_LINE('A ungleich B');

END IF;

A:=B;B.x:=2;IF A.x=B.x AND A.y=B.y

THEN DBMS_OUTPUT.PUT_LINE('A gleich B');ELSE DBMS_OUTPUT.PUT_LINE('A ungleich B');

END IF;

-- nicht erlaubt C:=A;END;

Ausgabe:A gleich BA ungleich B


Ausnahmen (1/5)

• Declaration Section: Deklaration (der Namen) benutzerdefinierter Exceptions.[DECLARE] <exception_name> EXCEPTION;

• Exceptions können dann an beliebigen Stellen des PL/SQL-Blocks durch RAISE ausgelöst werden. IF <condition>

THEN RAISE <exception_name>;

• Exception Section: Definition der beim Auftreten einer Exception auszuführenden Aktionen.WHEN <exception_name>

THEN <PL/SQL-Statement>;

WHEN OTHERS THEN <PL/SQL-Statement>;


Ausnahmen (2/5)

• auslösen einer Exception• entsprechende Aktion der WHEN-Klausel ausführen• innersten Block verlassen (oft Anwendung von anon ymenBlöcken sinnvoll)

PROCEDURE

BEGIN

EXCEPTION

END

ohneException

mitgefangenerException

ohnegefangeneException

PROCEDURE

BEGIN

EXCEPTION

END

PROCEDURE

BEGIN

EXCEPTION

END


Ausnahmen (3/5)

CREATE OR REPLACE FUNCTION noHeinz(name VARCHAR) RETURN VARCHAR IS

heinz EXCEPTION;BEGIN

IF name='Heinz'THEN

RAISE heinz;ELSE

RETURN name;END IF;

END;/

CREATE OR REPLACE PROCEDURE heinzTest ISheinz EXCEPTION;BEGIN

DBMS_OUTPUT.PUT_LINE(noHeinz('Egon'));DBMS_OUTPUT.PUT_LINE(noHeinz('Heinz'));DBMS_OUTPUT.PUT_LINE(noHeinz('Udo'));

EXCEPTION WHEN heinz THEN

DBMS_OUTPUT.PUT_LINE('Ein Heinz'); WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Wat nu?'); END;/ EXECUTE heinzTest;SELECT noHeinz('Heinz') FROM DUAL;

EgonWat nu?

SELECT noHeinz('Heinz') FROM DUALFEHLER in Zeile 1:ORA-06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahmebedingung (exception)ORA-06512: in „SKLEUKER.NOHEINZ", Zeile 7

1

2

34!


Ausnahmen (4/5)

• Es gibt viele vordefinierte Ausnahmen, die alle abgeprüft und bearbeitet werden können, Beispiele:– NO_DATA_FOUND– TOO_MANY_ROWS– INVALID_CURSOR– ZERO_DIVIDE– DUP_VAL_ON_INDEX

• Ausnahmen können auch direkt ausgelöst werden:RAISE_APPLICATION_ERROR(-20101,

'Kunde nicht kreditwürdig');

• Die Werte dieser Ausnahmen müssen zwischen -21000 und -20000 liegen, Rest ist für Oracle


Ausnahmen (5/5)CREATE OR REPLACE PROCEDURE exTest IS

I INTEGER DEFAULT 0;BEGIN

BEGINI:=I/I;DBMS_OUTPUT.PUT_LINE('Nicht Erreicht');

EXCEPTIONWHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE(''||SQLCODE||'::'||SQLERRM);END;

DBMS_OUTPUT.PUT_LINE(''||SQLCODE||'::'||SQLERRM);RAISE_APPLICATION_ERROR(-20101,'keine Lust mehr');

EXCEPTIONWHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(''||SQLCODE||'::'||SQLERRM);IF SQLCODE=-20101

THENDBMS_OUTPUT.PUT_LINE('stimmt nicht');

END IF;END;/EXECUTE exTest;

-1476::ORA-01476: Divisor ist Null0::ORA-0000: normal, successful completion-20101::ORA-20101: keine Lust mehrstimmt nicht


10. Cursor und Trigger in PL/SQL

• Definition eines Cursor

• Einsatzmöglichkeiten von Cursorn

• Verschiedene Arten von Triggern

Hinweis: Oracle-Bibliothek (als eine Sammlung herunterladbar)


CREATE OR REPLACE PROCEDURE gehaltAnpassen (mi Angestellte.MiNr%TYPE) IS

person Angestellte%ROWTYPE;BEGIN

SELECT *INTO personFROM AngestellteWHERE Angestellte.MiNr=mi;

UPDATE AngestellteSET Gehalt=person.GehaltWHERE Name=person.Name;

END;

RECORD und %ROWTYPE

CREATE TABLE ANGESTELLTE(Minr NUMBER(5),Name VARCHAR(10),Gehalt NUMBER(4),PRIMARY KEY (MiNr)

);

Mit %ROWTYPE erhält man einen RECORD-Typen, der sich genauaus den Attributen der genannten Tabelle zusammense tzt. Variablendieses Typs können dann eine Zeile der Tabelle aufn ehmen


Cursorbasierter DB -Zugriff

• Zeilenweiser Zugriff auf eine Relation aus einem PL/SQL-Programm.

• Cursordeklaration in der Declaration Section:CURSOR <cursor-name> [(<parameter-list>)]

IS

<select-statement>;

• (<parameter-list>): Parameter-Liste, nur IN als Übergaberichtung erlaubt

• Zwischen SELECTund FROM auch PL/SQL-Variablen und PL/SQL-Funktionen. PL/SQL-Variablen können ebenfalls in den WHERE-, GROUP-und HAVING-Klauseln verwendet werden


Beispiel für Cursor-Definition

Country Name

D Cologne

D Hamburg

Erinnerung:Tabelle City (vereinfacht):

Alle Städte in dem in der Variablen theCountry angegebenen Land:

CURSOR cities_in(theCountry Country.Code%TYPE)IS SELECT City.NameFROM CityWHERE City.Country=theCountry;


OPEN

OPEN <cursor-name> [(<argument-list>)];

• das SELECT-Statement des CURSORS wird jetzt ausgeführt• das Ergebnis ist eine virtuelle Tabelle im Speicher• auf diese Tabelle kann nur zeilenweise zugegriffen werden• die aktuelle Zeile wird durch ein FETCH-Kommando ei ngelesen,

wobei der CURSOR automatisch ein Feld weiter gesetz t wird

• OPEN cities_in('D');Name

Cologne

Hamburg

Munich= virtuell im Speicher

= zugreifbar

FETCH


FETCH• FETCH <cursor-name>

INTO <record-variable>; oder

FETCH <cursor-name> INTO <variable-list>;

• bewegt den Cursor auf die nächste Zeile des Ergebni sses der Anfrage und kopiert diese in die angegebene Record- Variable oder Variablenliste.

• Diese wird kann z.B. mit <cursor-name>%ROWTYPE mit dem Record-Typ des Cursors definiert werden:

<variable> <cursor-name>%ROWTYPE;

• CLOSE <cursor-name>; schließt einen Cursor.• nicht möglich:

OPEN cities_in ('D');

OPEN cities_in ('CH');

FETCH cities_in INTO <variable>;

ein parametrisierter Cursor, nicht eine Familie von Cursoren


Attribute von Cursorn

• <cursor-name>%ISOPEN : Cursor offen?

• <cursor-name>%FOUND : Solange ein Cursor bei der letzten FETCH-Operation ein neues Tupel gefunden hat, ist <cursor-name>%FOUND = TRUE .

• <cursor-name>%NOTFOUND: TRUE wenn man alle Zeilen eines Cursors geFETCHt hat.

• <cursor-name>%ROWCOUNT: Anzahl der von einem Cursor bereits gelesenen Tupel.

• Attribute nicht innerhalb eines SQL-Ausdrucks verwendbar (gehören zu Ablaufsteuerungsmöglichkeiten in PL/SQL)


Cursor – Beispiel (1/2)

-- Nutzung von WHILECREATE OR REPLACE PROCEDURE teureLeute (betr Number ) IS

CURSOR viel (betrag Number) ISSELECT * FROM AngestellteWHERE Angestellte.Gehalt>=betrag;

zeile viel%ROWTYPE;BEGIN

OPEN viel(betr);FETCH viel INTO zeile;WHILE viel%FOUND

LOOPDBMS_OUTPUT.PUT_LINE(zeile.Name||' bekommt zu

viel! (mindestens '||betr||')');FETCH viel INTO zeile;

END LOOP;CLOSE viel;

END;


Cursor FOR LOOP

• FOR <record_index> IN <cursor-name>

LOOP ... END LOOP;

• <record_index> wird dabei automatisch als Variable vom Typ <cursor-name>%ROWTYPEdeklariert,

• <record_index> immer von einem Record-Type –ggf. einspaltig (bei Zugriff beachten).

• es wird automatisch ein OPENausgeführt• bei jeder Ausführung des Schleifenkörpers wird

automatisch ein FETCHausgeführt,• Schleifenkörper enthält i.a. keinen FETCH-Befehl• am Ende wird automatisch ein CLOSEausgeführt• Spalten müssen explizit adressiert werden


Cursor – Beispiel (2/2)

-- Nutzung der CURSOR-FOR-Schleife

CREATE OR REPLACE PROCEDURE teureLeute0(betr Number )

IS

CURSOR viel (betrag Number) IS

SELECT *

FROM Angestellte

WHERE Angestellte.Gehalt>=betrag;

BEGIN

FOR person IN viel(betr)

LOOP

DBMS_OUTPUT.PUT_LINE(person.Name||' verdient

zu viel! (mindestens '||betr||')');

END LOOP;

END;


Aufdatieren an CURSOR -Position

• Um Veränderungen an der aktuellen CURSOR-Position vornehmen zu können, kann die Bedingung WHERE CURRENT OF<CURSOR-Name> genutzt werden, CURSOR muss zum ändern markiert sein

• Beispiel: Gehaltserhöhung um 10% bei allen Leuten deren MiNr kleiner als 100 ist

CREATE OR REPLACE Procedure mehrFuerAlteLeute ISCURSOR alt IS

SELECT *FROM AngestellteWHERE MiNr<100FOR UPDATE;

BEGINFOR person IN alt

LOOPUPDATE Angestellte

SET Gehalt=Gehalt*1.1WHERE CURRENT OF alt;

END LOOP;END;


Trigger (1/2)

• spezielle Form von PL/SQL-Prozeduren

• werden beim Eintreten eines bestimmten Ereignisses ausgeführt

• Spezialfall aktiver Regeln nach dem Event-Condition-Action-Paradigma

• Werden einer Tabelle (oft auch noch einer bestimmten Spalte) zugeordnet

• Bearbeitung wird durch das Eintreten eines Ereignisses (Einfügen, Ändern oder Löschen von Zeilen der Tabelle) ausgelöst (Event)


Trigger (2/2)

• 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 der gerade behandelten Zeile zugreifen.


Syntax von Triggern (1/2)

CREATE [OR REPLACE] TRIGGER <trigger-name>BEFORE | AFTER{INSERT | DELETE | UPDATE} [OF <column-list>][ OR {INSERT | DELETE | UPDATE}

[OF <column-list>]]...[ OR {INSERT | DELETE | UPDATE}

[OF <column- list>]] ON <table>[FOR EACH ROW][WHEN (<condition>)]<pl/sql-block>;


Syntax von Triggern (2/2)

• BEFORE, AFTER: Trigger wird vor/nach der auslösenden Operation ausgeführt

• OF <column> (nur für UPDATE) schränkt Aktivierung auf angegebene Spalte ein

• Zugriff auf Zeileninhalte vor und nach der Ausführung der aktivierenden Aktion mittels :OLDbzw. :NEW. (Aliasing durch REFERENCING OLD AS ... NEW AS ... ).

• Schreiben in :NEW-Werte nur mit BEFORE-Trigger.• FOR EACH ROW: Row -Trigger, sonst Statement-

Trigger.• WHEN (<condition>) : zusätzliche Bedingung :OLD

und :NEWsind in <condition> erlaubt.


Beispiel für Trigger (1/5)

• Wenn ein Landes-Code geändert wird, pflanzt sich diese Änderung auf die Relation Province fort:

CREATE OR REPLACE TRIGGER change_Code

BEFORE UPDATE OF Code

ON Country

FOR EACH ROW

BEGIN

UPDATE Province

SET Country = :NEW.Code

WHERE Country = :OLD.Code;

END;



• Wenn ein Land neu angelegt wird, wird ein Eintrag in Politics mit dem aktuellen Jahr erzeugt:

CREATE TRIGGER new_Country

AFTER INSERT

ON Country

FOR EACH ROW

BEGIN

INSERT INTO Politics (Country,Independence)

VALUES (:NEW.Code,SYSDATE);

END;

• Hinweis: Eventuell benötigte Variablen werden zwisc hen „ FOR

EACH ROW“ und BEGIN nach dem Schlüsselwort DECLARE

definiert



• Nachmittags dürfen keine Städte gelöscht werden:CREATE OR REPLACE TRIGGER nachm_nicht_loeschen

BEFORE DELETE

ON City

BEGIN

IF TO_CHAR(SYSDATE,'HH24:MI')

BETWEEN '12:00' AND '18:00'

THEN RAISE_APPLICATION_ERROR

(-20101,'Unerlaubte Aktion');

END IF;

END;Mit RAISE_APPLICATION_ERRORwird ein Fehler erzeugt, der zum Abbruch führt. Der erste Parameter muss zwischen -21000 und -20000 liegen, der zweiteist der Fehlertext



Niemand darf anfänglich mehr als die „Meiers“ in de r Firma verdienen

CREATE OR REPLACE TRIGGER nichtMehrAlsMeiersBEFORE INSERT ON AngestellteFOR EACH ROWDECLARE

maxMeier NUMBER;BEGIN

SELECT MAX(Gehalt)INTO maxMeierFROM AngestellteWHERE Name='Meier';

IF :NEW.Gehalt>maxMeierTHEN

RAISE_APPLICATION_ERROR(-20111,'Nicht mehr als die Meiers');

END IF;END;



Niemand darf anfänglich mehr als die „Meiers“ in de r Firma verdienenCREATE OR REPLACE TRIGGER nichtMehrAlsMeiersAFTER INSERT ON AngestellteDECLARE

verboten INTEGER;BEGIN

SELECT COUNT(*)INTO verbotenFROM AngestellteWHERE Angestellte.Gehalt > (SELECT MAX(Gehalt)

FROM AngestellteWHERE Name='Meier');

IF verboten > 0THEN

RAISE_APPLICATION_ERROR(-20111,'Nicht mehr als die Meiers');

END IF;END;


FOR EACH ROWoder nichtCREATE TABLE Tr(

X NUMBER,Y NUMBER

);INSERT INTO Tr VALUES (1,3);INSERT INTO Tr VALUES (1,4);INSERT INTO Tr VALUES (1,5);SELECT * FROM Tr;CREATE TRIGGER TrOhneEach

BEFORE UPDATE ON TrBEGIN

DBMS_OUTPUT.PUT_LINE('TrOhneEach');END;

/CREATE TRIGGER TrMitEach

BEFORE UPDATE ON TrFOR EACH ROWBEGIN

DBMS_OUTPUT.PUT_LINE('TrMitEach');END;

/UPDATE TRSET Y=Y+1WHERE X=1;SELECT * FROM Tr;

X Y---------- ----------

1 31 41 5

3 Zeilen ausgewählt.Trigger wurde erstellt.Trigger wurde erstellt.

TrOhneEachTrMitEachTrMitEachTrMitEach

3 Zeilen wurden aktualisiert.X Y

---------- ----------1 41 51 6

3 Zeilen ausgewählt.


Problem der Mutating Tables (1/2)

Trigger können die Inhalte von Tabellen ändern (der Tabelle, auf der sie definiert sind und andere),

• d.h. jede Ausführung des Triggers sieht eventuell einen anderen Datenbestand der Tabelle, auf der er definiert ist, sowie der Tabellen, die er evtl. änd ert

• d.h. Ergebnis abhängig von der Reihenfolge der veränderten Tupel

• ORACLE: Betroffene Tabellen werden während der gesamten Aktion als „mutating“ gekennzeichnet, können nicht erneut von Triggern gelesen oder geschrieben werden

• Nachteil: Oft ein zu strenges Kriterium


Problem der Mutating Tables (2/2)

• Trigger soll auf Tabelle zugreifen, auf der er selbe r definiert ist– nur das auslösende Tupel soll von dem Trigger

gelesen/geschrieben werden: Verwendung eines BEFORE-Triggers und der :NEW- und :OLD -Variablen

– es sollen neben dem auslösenden Tupel auch weitere Tupel verändert werden: Verwendung eines Statement-orientierten Triggers

• Trigger soll auf andere Tabellen zugreifen: Verwendung von Statement-Triggern und ggf. Hilfstabellen


Beispiel (1/4): Aufgabe

• Tabelle speichert Gebote eines Mitglieds (mnr) für eine Ware (ware) als Preis (gebot)

• Forderung: bei neuen Geboten (insert oder update erlaubt) für die gleiche Ware muss das Gebot erhöht werdenCREATE TABLE Gebot(

mnr INTEGER,ware INTEGER,gebot NUMBER(8,2),PRIMARY KEY(mnr,ware,gebot)

);



Beispiel (2/4) : Trigger

CREATE OR REPLACE TRIGGER GEBOTERHOEHEN BEFORE INSERT OR UPDATE ON GEBOT FOR EACH ROW DECLARE

maxi Gebot.gebot%TYPE;BEGIN

SELECT MAX (Gebot.gebot)INTO maxiFROM GebotWHERE Gebot.mnr = :NEW.mnr

AND Gebot.ware = :NEW.ware;IF maxi IS NOT NULL AND maxi >= :NEW.Gebot

THENRAISE_APPLICATION_ERROR(-20900

,'Gebot muss erhoeht werden');END IF;

END;



Beispiel (3/4): Versuch der Anwendung

INSERT INTO Gebot VALUES(42,99,1.00);

1 Zeilen eingefügt.COMMIT;

festgeschrieben.UPDATE Gebot SET gebot = 1.01

WHERE mnr = 42 AND ware = 99;SQL-Fehler: ORA -04091: table ICH.GEBOT is mutating, trigger/function may not see itORA-06512: at "ICH.GEBOTERHOEHEN", line 4ORA-04088: error during execution of trigger 'ICH.GEBOTERHOEHEN'04091. 00000 - "table % s.%s is mutating, trigger/function may not see it"



Beispiel (4/4): Korrektur

CREATE OR REPLACE TRIGGER GEBOTERHOEHEN BEFORE INSERT OR UPDATE ON GEBOT FOR EACH ROW DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;maxi Gebot.gebot%TYPE;

• Korrektur sinnvoll, da nur ursprüngliche Daten gelesen und sonst nichts verändert wird

• auch PRAGMA kann zur Laufzeit scheitern• Erinnerung: Transaktionen mit COMMIT abschließen


Documents

9. Einführung in PL/SQL Motivation für PL/SQL Aufbau von ...home.edvsz.fh-osnabrueck.de/skleuker/WS12_DB/Datenbanksysteme… · Minr NUMBER(5), Name VARCHAR(10), Gehalt NUMBER(4),