Einführung in PL/SQL - home.hs

Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Einführung in PL/SQL
• Procedural Language/Structured Query Language
• Prozedurale Erweiterung der Sprache SQL für Elemente wie
–
–
–
–
Variablen,
Schleifen,
Bedingungen,
Ausnahmebehandlung
Dr. Christian Senger
Einführung PL/SQL
1
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Einführung in PL/SQL
• Code läuft innerhalb der Datenbank ab und ist deshalb sehr
performant
• Zusätzlich große Anzahl an vordefinierten Bibliotheken
(packages) verfügbar.
• Einsatzfelder:
– anonyme Blöcke
– Funktionen und Prozeduren, Methoden
– Trigger
Dr. Christian Senger
Einführung PL/SQL
2
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Couchsurfing
CREATE SEQUENCE schlafplatz_seq
MINVALUE 1
MAXVALUE 99999999
START WITH 1
INCREMENT BY 1
CACHE 10;
INSERT INTO schlafplatz
VALUES(
schlafplatz_seq.nextval,
'Seppl Berlin',
'Berlin', 2, 1);
CREATE TABLE schlafplatz(
schlafplatz_id NUMBER(8,0) NOT NULL,
bezeichnung VARCHAR(255) NULL,
-- der längste Städtename der Welt ist
-- Llanfairpwllgwyngyllgogerychwyrnd
-- robwllllantysiliogogogoch
-- (58 Buchstaben, Ort in Wales)
stadt VARCHAR(60) NOT NULL,
anzahl_plaetze NUMBER(2,0) NOT NULL,
beliebtheit_rang NUMBER(*,0) NOT NULL,
PRIMARY KEY (schlafplatz_id));
Dr. Christian Senger
Einführung PL/SQL
INSERT INTO schlafplatz
VALUES(
schlafplatz_seq.nextval,
'Max',
'Köln', 3, 2);
INSERT INTO schlafplatz
VALUES(
schlafplatz_seq.nextval,
'Kathrinchen',
'Karlsruhe', 1, 3);
INSERT INTO schlafplatz
VALUES(
schlafplatz_seq.nextval,
'Marten',
'Berlin', 2, 4);
3
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Struktur eines PL/SQL Programms
Dr. Christian Senger
Einführung PL/SQL
4
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Datentypen in PL/SQL
•
BINARY_INTEGER
–
•
NUMBER [(Länge, Nachkommastelle)]
–
•
Wertebereich
-2147483647 bis 2147483646
DECLARE
neue_stadt
CHAR(60) := ’Freiburg’;
tabellen_name
CHAR (30);
38 Stellen Genauigkeit
CHAR [(Länge)]
–
maximale Länge von 32767 Bytes
belegte_plätze
NUMBER(10) := 2;
heute
DATE NOT NULL;
BOOLEAN;
•
VARCHAR2 [(Länge)]
ok
•
BOOLEAN
pi
•
DATE
CONSTANT NUMBER := 3.14159;
•
LONG [(Länge)]
•
RAW [(Länge)]
•
ROWID
•
CURSOR
Dr. Christian Senger
CURSOR schlafplatz_cursor IS
SELECT *
FROM schlafplatz
ORDER BY beliebtheit_rang;
Einführung PL/SQL
5
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Datentypen in PL/SQL
•
•
Namen von Tabellen und Spalten
sind als vordefinierte Variable
vorhanden
Attribut %TYPE
liefert Datentyp einer anderen
Variablen oder einer
Tabellenspalte
Spalte: Name,
Tabelle: City
DECLARE
stadt_name
land
•
Attribut %ROWTYPE
liefert Datentyp einer
Tabellenzeile (Zugriff auf einzelne
Felder über „.“-Notation)
•
Attribut %ROWCOUNT
liefert Anzahl von Zeilen die vom
Cursor bisher zurückgegeben
wurden
Dr. Christian Senger
city.name%type;
country%rowtype;
BEGIN
...
stadt_name := city.name;
Einführung PL/SQL
Tabelle: Country
6
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Bildschirmausgabe
•
PL/SQL Programme erzeugen normalerweise keine Bildschirmausgabe
•
spezielles Paket zur Bildschirmausgabe verfügbar (DBMS_OUTPUT)
•
Bildschirmausgabe mit Prozedur: DBMS_OUTPUT.PUT_LINE(<zeichenkette>);
•
Bildschirmausgabe kann in SQLPLUS Sitzung an und ausgeschaltet werden
(Default OFF)
•
Anweisung:
SET SERVEROUTPUT ON | OFF [SIZE <numbytes>]
SET serveroutput ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Willkommen in der Couchsurfer-Vorlesung');
END;
SET serveroutput OFF
Dr. Christian Senger
Einführung PL/SQL
7
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Kontrollstrukturen (Zuweisungen)
–
Zuweisungsoperator :=
SET serveroutput ON
DECLARE
anzahl_plaetze NUMBER(2,0) NOT NULL := 2;
BEGIN
anzahl_plaetze := anzahl_plaetze * 1.5;
DBMS_OUTPUT.PUT_LINE(anzahl_plaetze);
END;
Dr. Christian Senger
Einführung PL/SQL
8
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Kontrollstrukturen (Zuweisungen)
– INTO Operator mit SELECT (wenn genau ein Datensatz zurückgeliefert wird)
DECLARE
anzahl_plaetze_gesamt NUMBER;
BEGIN
SELECT sum(anzahl_plaetze) INTO anzahl_plaetze_gesamt
FROM schlafplatz;
DBMS_OUTPUT.PUT_LINE('Es gibt '||anzahl_plaetze_gesamt||
' Schlafplätze in der Datenbank');
END;
Dr. Christian Senger
Einführung PL/SQL
9
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Kontrollstrukturen (Zuweisungen)
– FETCH ... INTO (Mit Cursor)
FETCH cur_schlafplatz INTO temp_schlafplatz_id, temp_anzahl_plaetze;
Dr. Christian Senger
Einführung PL/SQL
10
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Kontrollstrukturen (bedingte Anweisungen)
-- bedingte Verzweigung
IF <bedingung> THEN
<anweisungen>
END IF;
-- Alternative
IF <bedingung> THEN
<anweisungen>
ELSE
<anweisungen>
END IF;
Dr. Christian Senger
-- mehrere Alternativen
IF <bedingung 1> THEN
<anweisungen>
ELSIF <bedingung 2> THEN
<anweisungen>
ELSIF <bedingung 3> THEN
<anweisungen>
ELSE
<anweisungen>
END IF;
Einführung PL/SQL
11
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Kontrollstrukturen (Schleifen)
-- LOOP … END LOOP
DECLARE
beliebtheit_rang NUMBER := 0;
BEGIN
-- Achtung: Abbruchbedingung muss formuliert werden
LOOP
beliebtheit_rang := beliebtheit_rang + 1;
IF beliebtheit_rang = 50 THEN
DBMS_OUTPUT.PUT_LINE('Die Beliebtheit dieses Schlafplatzes hat '||
beliebtheit_rang||' erreicht!');
EXIT;
END IF;
END LOOP;
END;
-- FOR <zaehlbereich> LOOP ... END LOOP
BEGIN
FOR i IN 1..5 LOOP
INSERT INTO schlafplatz
VALUES (schlafplatz_seq.nextval,'freund'||i,'stadt'||i,0,0);
END LOOP;
END;
Dr. Christian Senger
Einführung PL/SQL
12
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Kontrollstrukturen (Schleifen) II
-- Rückwärts
BEGIN
FOR i in REVERSE 1..5 loop
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
-- WHILE <bed> LOOP ... END LOOP
DECLARE
anzahl_plaetze NUMBER := 0;
beliebtheit_rang NUMBER := 0;
BEGIN
WHILE anzahl_plaetze <= 5 LOOP
beliebtheit_rang := beliebtheit_rang + anzahl_plaetze;
anzahl_plaetze := anzahl_plaetze + 1;
DBMS_OUTPUT.PUT_LINE (
'beliebtheit_rang: '||beliebtheit_rang||' '||
'anzahl_plaetze: '||anzahl_plaetze
);
END LOOP;
END;
Dr. Christian Senger
Einführung PL/SQL
13
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Ausnahmebehandlung
•
Behandlung von Fehlern und
Ausnahmesituationen durch
Exceptionhandler
•
Tritt Ausnahme auf, so wird
normaler Ablauf abgebrochen und
eine Fehlerbehandlung
durchgeführt.
Dr. Christian Senger
•
Fehlerbehandlung erfolgt in
EXCEPTION-Block.
•
Ausnahmen können vordefiniert
oder benutzerdefiniert sein.
Einführung PL/SQL
14
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Ausnahmebehandlung (vordefinierte Ausnahmen)
DECLARE
lieblings_schlafplatz
schlafplatz%rowtype;
stadt_name
VARCHAR(60) := 'Karlsruhe';
BEGIN
SELECT *
INTO lieblings_schlafplatz
FROM schlafplatz
WHERE stadt = stadt_name;
Ausgabe:
Kathrinchen hat 1 Schlafplatz/ Schlafplätze
...
stadt_name
VARCHAR(60) := 'Stuttgart';
...
Ausgabe:
Stadt Stuttgart gibt es nicht
DBMS_OUTPUT.PUT_LINE(
lieblings_schlafplatz.bezeichnung||'
hat '||
lieblings_schlafplatz.anzahl_plaetze||
' Schlafplatz/Schlafplätze'
);
...
stadt_name
VARCHAR(60) := 'Berlin';
...
Ausgabe:
Stadt Berlin gibt es mehrfach
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('Stadt
'||stadt_name||' gibt es nicht');
WHEN too_many_rows THEN
DBMS_OUTPUT.PUT_LINE('Stadt
'||stadt_name||' gibt es es
mehrfach');
END;
Dr. Christian Senger
Einführung PL/SQL
15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
vordefinierte Ausnahmen
Exception
Oracle Error
SQLCODE
ACCESS_INTO_NULL
ORA-06530
-6530
COLLECTION_IS_NULL
ORA-06531
-6531
CURSOR_ALREADY_OPEN
ORA-06511
-6511
DUP_VAL_ON_INDEX
ORA-00001
-1
INVALID_CURSOR
ORA-01001
-1001
INVALID_NUMBER
ORA-01722
-1722
LOGIN_DENIED
ORA-01017
-1017
NO_DATA_FOUND
ORA-01403
100
NOT_LOGGED_ON
ORA-01012
-1012
ORA-06501
Mehr unter http://fara.cs.uni-potsdam.de/~uhlmann/19/ch09.html
PROGRAM_ERROR
-6501
ROWTYPE_MISMATCH
ORA-06504
-6504
STORAGE_ERROR
ORA-06500
-6500
SUBSCRIPT_BEYOND_COUNT
ORA-06533
-6533
SUBSCRIPT_OUTSIDE_LIMIT
ORA-06532
-6532
TIMEOUT_ON_RESOURCE
ORA-00051
-51
TOO_MANY_ROWS
ORA-01422
-1422
VALUE_ERROR
ORA-06502
-6502
ZERO_DIVIDE
ORA-01476
-1476
Dr. Christian Senger
Einführung PL/SQL
16
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Ausnahmebehandlung (eigene Ausnahmen)
DECLARE
exception_zu_allein
EXCEPTION;
lieblings_schlafplatz schlafplatz%rowtype;
stadt_name
VARCHAR(60) := 'Karlsruhe';
BEGIN
SELECT *
INTO lieblings_schlafplatz
FROM schlafplatz
WHERE stadt = stadt_name;
IF lieblings_schlafplatz.anzahl_plaetze < 2 THEN
RAISE exception_zu_allein;
END IF;
DBMS_OUTPUT.PUT_LINE( lieblings_schlafplatz.bezeichnung||' hat '||
lieblings_schlafplatz.anzahl_plaetze ||' Schlafplätze');
EXCEPTION
WHEN exception_zu_allein THEN
DBMS_OUTPUT.PUT_LINE(
lieblings_schlafplatz.bezeichnung ||' hat nur ' ||
lieblings_schlafplatz.anzahl_plaetze||' Schlafplatz/Schlafplätze,
da fühl ich mich zu allein');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Fehler: '||sqlerrm);
END;
Dr. Christian Senger
Einführung PL/SQL
17
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Beispiel
Dr. Christian Senger
Einführung PL/SQL
18
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
CURSOR
• SQL Anweisung liefert i.A. eine Menge von Tupeln zurück
• Cursor zur sequentiellen Abarbeitung der Ergebnismenge
• Deklaration:
DECLARE
...
CURSOR c1 IS
SELECT * FROM schlafplatz;
• Zugriff auf den Inhalt mittels OPEN, FETCH, CLOSE
• Status des Cursors: %ISOPEN, %FOUND, %NOTFOUND,
%ROWCOUNT
Dr. Christian Senger
Einführung PL/SQL
19
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
CURSOR II
DECLARE
CURSOR cur_schlafplatz IS
SELECT *
FROM schlafplatz s
WHERE s.anzahl_plaetze > 0
AND s.beliebtheit_rang > 0
ORDER BY s.beliebtheit_rang DESC;
ein_schlafplatz schlafplatz%rowtype;
Ausgabe:
BEGIN
Datensatz: 3, Bezeichnung:
Max(3)
OPEN cur_schlafplatz;
FETCH cur_schlafplatz INTO ein_schlafplatz;
WHILE cur_schlafplatz%found LOOP
DBMS_OUTPUT.PUT_LINE('Datensatz: '||
cur_schlafplatz%ROWCOUNT||
', Bezeichnung: '||
ein_schlafplatz.bezeichnung||
'('||ein_schlafplatz.anzahl_plaetze||')');
Datensatz: 1, Bezeichnung:
Marten(2)
Datensatz: 2, Bezeichnung:
Kathrinchen(1)
Datensatz: 4, Bezeichnung:
Seppl Berlin(2)
FETCH cur_schlafplatz INTO ein_schlafplatz;
END LOOP;
CLOSE cur_schlafplatz;
END;
Dr. Christian Senger
Einführung PL/SQL
20
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
CURSOR III (Kurzform ohne OPEN, FETCH, CLOSE)
DECLARE
CURSOR cur_schlafplatz IS
SELECT *
FROM schlafplatz s
WHERE s.anzahl_plaetze > 0
AND s.beliebtheit_rang > 0
ORDER BY s.beliebtheit_rang DESC;
Ausgabe:
Datensatz: 1, Bezeichnung:
Marten(2)
Datensatz: 2, Bezeichnung:
Kathrinchen(1)
BEGIN
FOR ein_schlafplatz IN cur_schlafplatz LOOP
DBMS_OUTPUT.PUT_LINE(
'Datensatz: '||
cur_schlafplatz%ROWCOUNT||
', Bezeichnung: '||
ein_schlafplatz.bezeichnung||
'('||ein_schlafplatz.anzahl_plaetze||')');
END LOOP;
Datensatz: 3, Bezeichnung:
Max(3)
Datensatz: 4, Bezeichnung:
Seppl Berlin(2)
END;
Dr. Christian Senger
Einführung PL/SQL
21
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
CURSOR IV (ohne explizite Cursorvariable)
BEGIN
FOR ein_schlafplatz IN (
SELECT *
FROM schlafplatz s
WHERE s.anzahl_plaetze > 0
AND s.beliebtheit_rang > 0
ORDER BY s.beliebtheit_rang DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
'Bezeichnung: '||
ein_schlafplatz.bezeichnung||
'('||ein_schlafplatz.anzahl_plaetze||')');
END LOOP;
Ausgabe:
Bezeichnung: Marten(2)
Bezeichnung: Kathrinchen(1)
Bezeichnung: Max(3)
Bezeichnung: Seppl Berlin(2)
END;
Dr. Christian Senger
Einführung PL/SQL
22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
CURSOR V (mit Parametern)
•
•
in WHERE-Klausel können Auswahlkriterien angegeben werden
Werte können beim öffnen des Cursors mit übergeben werden
•
z.B.: OPEN cur_schlafplatz('Berlin');
•
Ideal bei geschachtelten Tabellen
DECLARE
CURSOR cur_schlafplatz(gesuchte_stadt char) IS
SELECT *
FROM schlafplatz s
WHERE s.stadt = gesuchte_stadt
AND anzahl_plaetze > 0;
BEGIN
FOR ein_schlafplatz IN cur_schlafplatz('Berlin') LOOP
DBMS_OUTPUT.PUT_LINE('Datensatz: '|| cur_schlafplatz%ROWCOUNT||
', Bezeichnung: '|| ein_schlafplatz.bezeichnung||
'('||ein_schlafplatz.anzahl_plaetze||')');
END LOOP;
END;
Dr. Christian Senger
Einführung PL/SQL
23
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Beispiel
Dr. Christian Senger
Einführung PL/SQL
24
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Funktionen & Prozeduren
•
Definition von Prozeduren und Funktionen innerhalb eines PL/SQL Blocks
•
Integration von Prozeduren und Funktionen als Datenbankobjekte
•
Übergabeparameter können als IN, OUT und IN OUT deklariert werden.
•
Übergabeparameter können einen Standardwert besitzen
•
Parametertypen ohne Größenangabe
•
Prozedur-/Funktionsrumpf entspricht einem PL/SQL-Block ohne Schlüsselwort
DECLARE
-- Beispielprozedurkopf
PROCEDURE schlafplatz_suche(ort IN VARCHAR, anzahl_plaetze IN INTEGER DEFAULT 1)
IS
BEGIN
-- Implementierung;
END;
Dr. Christian Senger
Einführung PL/SQL
25
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Funktionen & Prozeduren II
•
•
Funktionsdeklaration entspricht Prozedurdeklaration mit Unterschied, dass
Ergebnistyp der Funktion angegeben wird.
Rückgabe der berechneten Ergebnisse mittels Schlüsselwort RETURN.
-- Funktionen
CREATE
FUNCTION minimum(a IN NUMBER, b IN NUMBER) RETURN NUMBER
IS
BEGIN
IF a < b THEN
RETURN a;
ELSE
RETURN b;
END IF;
END;
-- Ausführen z.B.
BEGIN
DBMS_OUTPUT.PUT_LINE('Minimum von 1 und 3: '|| minimum(1,3));
END;
SELECT minimum(1,3), anzahl_plaetze FROM schlafplatz WHERE stadt='Berlin';
EXEC DBMS_OUTPUT.PUT_LINE('Minimum von 1 und 3: '|| minimum(1,3));
Dr. Christian Senger
Einführung PL/SQL
26
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Funktionen & Prozeduren III (Beispiele)
CREATE
FUNCTION minimum(a IN NUMBER, b IN
NUMBER) RETURN NUMBER
IS
BEGIN
IF a < b THEN
RETURN a;
ELSE
RETURN b;
END IF;
END;
CREATE
PROCEDURE maximum(a IN NUMBER,
b IN NUMBER, res OUT NUMBER)
IS
BEGIN
IF a > b THEN
res := a;
ELSE
res := b;
END IF;
END;
-----------------------------------DECLARE m NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(
'Minimum von 3 und -5: '||
minimum(3,-5));
maximum(2,-4, m);
DBMS_OUTPUT.PUT_LINE('Maximum
von 2 und -4: '||m);
END;
Dr. Christian Senger
Einführung PL/SQL
27
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Prozeduren& Funktionen als Datenbankobjekte
•
•
Abspeichern von
Funktionen/Prozeduren als benannte
Datenbankobjekte
Funktionen/Prozeduren können von
SQLPLUS/ TOAD/..., PL/SQL Code
oder auch über OCI, ODBC, JDBC
aus aufgerufen werden
CREATE OR REPLACE FUNCTION
fak(z IN INTEGER) RETURN
NUMBER
IS
f NUMBER := 1;
BEGIN
FOR i IN 2..z LOOP
f := f * i;
END LOOP;
RETURN f;
END;
Dr. Christian Senger
-- Aufruf (1)
BEGIN
DBMS_OUTPUT.PUT_LINE(fak(5));
END;
-- Aufruf (2, alternativ)
EXEC
DBMS_OUTPUT.PUT_LINE('fak(5)='||
fak(5))
Einführung PL/SQL
28
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Prozeduren& Funktionen als Datenbankobjekte II
•
Fehler bei Definition einer Prozedur/Funktion können mit dem SQLPLUS Kommando
„SHOW ERRORS“ angezeigt werden
•
•
•
Aufruf von PL/SQL Funktionen auch innerhalb von SQL Statements
Funktionen können mit Ausführungsrechten versehen werden (Kapselung)
Funktionen/Prozeduren können als EXTERN deklariert sein (Implementierung in
Java, C++, ...)
Tabelle user_source enthält alle benutzerdefinierten Datenbankobjekte
•
DESC user_source
SELECT
FROM
WHERE
AND
ORDER
text
user_source
name='FAK'
type='FUNCTION'
BY line;
-- Tabellendefinition
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
-- Ausgabe
FUNCTION
fak(z IN INTEGER) RETURN NUMBER…
Dr. Christian Senger
Einführung PL/SQL
29
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II – SS 2015
Beispiele
Dr. Christian Senger
Einführung PL/SQL
30