Einführung in PL/SQL
Werbung
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Einführung in PL/SQL
• Prozedurale Erweiterung der Sprache SQL um Elemente wie
• Variablen,
• Schleifen,
• Bedingungen,
• Ausnahmebehandlung
• 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
Andreas Schmidt
Einführung in PL/SQL 1/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Struktur eines PL/SQL Programms
DECLARE
-- Deklarationen
BEGIN
-- eigentlicher
-- Programmteil
EXCEPTION
-- Fehlerbehandlung
END;
/
• Deklarations und Ausnahmebehandlung
sind optional
• beliebige Schachtelung der Blöcke ineinander möglich
• Deklarationsteil erlaubt Definition von
Variablen, Konstanten, Cursorn, Funktionen
und Prozeduren
• Variablen in dem Block gültig in dem sie
deklariert wurden
• Kommentare:
-- ich bin ein Kommentar
/* ich bin ein mehrzeiliger
Kommentar */
Andreas Schmidt
Einführung in PL/SQL 2/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Datentypen in PL/SQL
• BINARY_INTEGER
Wertebereich -2147483647 - 2147483646
• NUMBER [(Länge, Nachkommastelle)]
38 Stellen Genauigkeit
• CHAR [(Länge)]
maximale Länge von 32767 Bytes
• VARCHAR2 [(Länge)]
• BOOLEAN
• DATE
• ROWID
• CURSOR
Andreas Schmidt
• Beispiel
DECLARE
owner char(10) := ’ich’;
tablename char(30);
bytes number(10) := 128000;
today date not Null;
ok boolean;
pi constant number := 3.14159;
zaehler number(5) := 1;
CURSOR spieler_cursor is
select *
from spieler
order by name;
Einführung in PL/SQL 3/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Datentypen in PL/SQL
• Namen von Tabellen und Spalten sind als • Beispiele:
vordefinierte Variable vorhanden
DECLARE
stadt_name
• Attribut %TYPE
nevada
liefert Datentyp einer anderen Variablen
hauptstadt
oder einer Tabellenspalte
• Attribut %ROWTYPE
BEGIN
...
liefert Datentyp einer Tabellenzeile
groesse :=
(Zugriff auf einzelne Felder über „.“Notation)
Andreas Schmidt
Spalte name in
Tabelle Stadt
Stadt.name%type;
Wueste%rowtype;
stadt_name%type;
nevada.flaeche;
Tabelle Wueste
Einführung in PL/SQL 4/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
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 an und ausgeschaltet werden
(Default OFF)
• Anweisung (SQLPLUS Sitzung)1:
SET SERVEROUTPUT ON | OFF [SIZE <numbytes>]
• Beispiel:
SQL> set serveroutput on size 10000000
BEGIN
DBMS_OUTPUT.PUT_LINE(’Hallo designierter PL/SQL Crack !!’);
END;
/
1. SQLDeveloper: Menue >> Ansicht >> DBMS-Ausgabe (auf Pluszeichen drücken und passende Datenbankverbindung angeben)
Andreas Schmidt
Einführung in PL/SQL 5/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Kontrollstrukturen
Zuweisungen
• Zuweisungsoperator :=
a := b * 120 + c;
• INTO Operator mit SELECT (nur, wenn genau ein Datensatz zurückgeliefert wird)
declare
anzahl_millionen_staedte number;
begin
select count(*)
into anzahl_millionen_staedte
from mondial.city
where population > 1000000;
dbms_output.put_line('Es gibt '||anzahl_millionen_staedte||
' Millionenstädte in der Mondial Datenbank');
end;
/
• FETCH ... INTO (Mit Cursor)
FETCH cur_station INTO s_id, x, y;
Andreas Schmidt
Einführung in PL/SQL 6/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Kontrollstrukturen (bedingte Anweisungen)
• bedingte Verzweigung
• mehrere Alternativen
IF <bedingung> THEN
<anweisungen>
END IF;
IF <bedingung 1> THEN
<anweisungen>
ELSIF <bedingung 2> THEN
<anweisungen>
ELSIF <bedingung 3> THEN
<anweisungen>
ELSE
<anweisungen>
END IF;
• Alternative
IF <bedingung> THEN
<anweisungen>
ELSE
<anweisungen>
END IF;
Andreas Schmidt
Einführung in PL/SQL 7/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Kontrollstrukturen (Schleifen)
• LOOP ... END LOOP
a := 0;
LOOP
a := a + 1;
if a = 50 then
exit;
end if;
END LOOP;
• FOR <zaehlbereich> LOOP ... END LOOP
FOR i IN 1..50 LOOP
insert into buch (kapitel, text)
values (i, text(i));
END LOOP;
Andreas Schmidt
• Rückwärts
FOR i in REVERSE 1..20 loop
dbms_output.put_line(i);
END LOOP;
• WHILE <bed> LOOP ... END LOOP
a := 0;
WHILE a <= 50 LOOP
b := b + a;
a := a + 1;
END LOOP;
Einführung in PL/SQL 8/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Ausnahmebehandlung
• Behandlung von Fehlern und Ausnahmesi- Beispiel (vordefinierte Ausnahme)
DECLARE
tuationen durch Exceptionhandler
eine_stadt mondial.city%rowtype;
stadt_name varchar2(20) := 'Karlsruhe';
• Tritt Aufnahme auf, so wird normaler
-- alternativ: 'Metropolis' 'Springfield'
Ablauf abgebrochen und eine FehlerbeBEGIN
handlung durchgeführt.
SELECT *
INTO eine_stadt
• Fehlerbehandlung erfolgt in EXCEPTIONFROM mondial.city
WHERE name = stadt_name;
Block.
dbms_output.put_line(eine_stadt.name||' hat
• Ausnahmen können vordefiniert oder
'||eine_stadt.population||' Einwohner');
EXCEPTION
benutzerdefiniert sein.
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;
/
Andreas Schmidt
Einführung in PL/SQL 9/22
Fakultät IWI
DB & IS II - SS 2017
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
PROGRAM_ERROR
ORA-06501
-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
Erläuterungen: http://fara.cs.uni-potsdam.de/~uhlmann/19/ch09.html
Andreas Schmidt
Einführung in PL/SQL 10/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Ausnahmebehandlung - eigene Ausnahmen
DECLARE
exep_moloch exception;
eine_stadt mondial.city%rowtype;
stadt_name varchar2(20) := 'San Francisco'; -- alternativ: 'Mexico City'
BEGIN
SELECT *
INTO eine_stadt
FROM mondial.city
WHERE name = stadt_name;
if eine_stadt.population > 5000000 then
raise exep_moloch;
end if;
dbms_output.put_line(eine_stadt.name||' hat '||eine_stadt.population||' Einwohner');
EXCEPTION
when exep_moloch then
dbms_output.put_line('Stadt '||eine_stadt.name||' ist zu groß für mich');
when others then
dbms_output.put_line('Fehler: '||sqlerrm);
END;
/
Andreas Schmidt
Einführung in PL/SQL 11/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
CURSOR
• SQL Anweisung liefert i.a. eine Menge
von Tupeln zurück
• Cursor zur sequentiellen Abarbeitung
der Ergebnismenge
• Deklaration:
• Status des Cursors
• %ISOPEN
• %FOUND
• %NOTFOUND
• %ROWCOUNT
DECLARE
...
cursor c1 is
select * from land;
• Zugriff auf den Inhalt mittels
• OPEN
• FETCH
• CLOSE
Andreas Schmidt
Einführung in PL/SQL 12/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
CURSOR
• Programmcode:
• Ausgabe:
declare
Datensatz:
cursor cur_french_city is
Datensatz:
select *
Datensatz:
from mondial.city c
Datensatz:
where c.country = 'F'
Datensatz:
and c.population is not null
Datensatz:
order by c.population desc;
Datensatz:
a_city mondial.city%rowtype;
Datensatz:
begin
Datensatz:
OPEN cur_french_city;
Datensatz:
FETCH cur_french_city INTO a_city;
Datensatz:
WHILE cur_french_city%found LOOP
Datensatz:
dbms_output.put_line('Datensatz: '||
Datensatz:
cur_french_city%ROWCOUNT||' Stadt: '|| ...
a_city.name||' ('||
a_city.population||')');
FETCH cur_french_city INTO a_city;
END LOOP;
CLOSE cur_french_city;
end;
Andreas Schmidt
1 Stadt: Paris (2152423)
2 Stadt: Marseille (800550)
3 Stadt: Lyon (415487)
4 Stadt: Toulouse (358688)
5 Stadt: Nice (342439)
6 Stadt: Strasbourg (252338)
7 Stadt: Nantes (244995)
8 Stadt: Bordeaux (210336)
9 Stadt: Montpellier (207996)
10 Stadt: Saint Etienne (199396)
11 Stadt: Rennes (197536)
12 Stadt: Le Havre (195854)
13 Stadt: Reims (180620)
Einführung in PL/SQL 13/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
CURSOR
• Kurzform (ohne OPEN, FETCH, CLOSE)
declare
cursor cur_french_city is
select *
from mondial.city c
where c.country = 'F'
and c.population is not null
order by c.population desc;
begin
FOR a_city in cur_french_city LOOP
dbms_output.put_line('Datensatz: '||cur_french_city%ROWCOUNT||
' Stadt: '||a_city.name||' ('||a_city.population||')');
END LOOP;
end;
/
Andreas Schmidt
Einführung in PL/SQL 14/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
CURSOR
• ohne explizite Cursorvariable:
begin
FOR a_city in (select *
from mondial.city c
where c.country = 'F'
and c.population is not null
order by c.population desc) LOOP
dbms_output.put_line('Datensatz: '||
' Stadt: '||a_city.name||' ('||a_city.population||')');
END LOOP;
end;
/
Andreas Schmidt
Einführung in PL/SQL 15/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
CURSOR mit Parametern
• in where Klausel können Auswahlkriterien angegeben werden
• Werte können beim öffnen des Cursors mit übergeben werden
z.B.:
OPEN cur_city('D');
• Ideal bei geschachtelten Tabellen
• Beispiel
declare
cursor cur_city(country_id char) is select *
from mondial.city c
where c.country = country_id
and c.population is not null
order by c.population desc;
begin
FOR a_city in cur_city('D') LOOP
dbms_output.put_line('Datensatz: '||cur_city%ROWCOUNT||
' Stadt: '||a_city.name||' ('||a_city.population||')');
END LOOP;
END;
/
Andreas Schmidt
Einführung in PL/SQL 16/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
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
Beispiel Prozedurkopf.
procedure spielzug(spieler_id in integer, ziel_station in integer,
ticket in varchar default 'Taxi')
is
begin
-- Implementierung;
end;
• Prozedur-/Funktionsrumpf entspricht einem PL/SQL-Block ohne Schlüsselwort DECLARE
Andreas Schmidt
Einführung in PL/SQL 17/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Funktionen, Prozeduren
• Funktionsdeklaration entspricht Prozedurdeklaration mit Unterschied, dass Ergebnistyp der
Funktion angegeben wird.
• Rückgabe der berechneten Ergebnisse mittels Schlüsselwort RETURN.
• Beispiel:
function minimum(a in number, b in number) return number
is
begin
if a < b then
return a;
else
return b;
end if;
end;
• Aufruf:
begin
dbms_output.put_line('Min. von 12 und -7:' || minimum(12,-7));
end;
/
Andreas Schmidt
Einführung in PL/SQL 18/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Beispiel
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;
function minimum(a in number,
b in number)
return number
is
begin
if a < b then
return a;
else
return b;
end if;
end;
begin
dbms_output.put_line('Minimum von 12 und -7: '||
minimum(12,-7));
maximum(2,-4, m);
dbms_output.put_line('Maximum von 2 und -4: '|| m);
end;
/
Andreas Schmidt
Einführung in PL/SQL 19/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
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.
•
• Aufruf (Definition rechte Seite);
SQL> begin
dbms_output.put_line('fak(5)=' || fak(5));
end;
/
• Beispiel einer Funktionsdeklaration:
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;
/
alternative kurzform (1-Zeiler):
SQL> exec dbms_output.put_line('fak(5)='|| fak(5));
Andreas Schmidt
Einführung in PL/SQL 20/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Prozeduren/Funktionen als Datenbankobjekte
• 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 alll benutzerdefinierten Datenbankobjekte
• SQL> desc user_source
Name Typ
------------------------NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
Andreas Schmidt
select
from
where
and
order
text
user_source
name='FAK'
type='FUNCTION'
by line;
Einführung in PL/SQL 21/22
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Literatur/weitere Informationsquellen
• Einführung in PL/SQL: http://www.a-wilde.de/hp/studium/db/plsql1.htm
• Oracle PL/SQL Programmierung, 2. Auflage; Steven Feuerstein & Bill Pribyl; Deutsche Übersetzung von Dorothea Reder; O’Reilly; 2. Auflage April 2003; ISBN 389721-184-X; Seiten 1084; 64.00 €
• Oracle PL/SQL - kurz & gut, 2. Auflage; Steven Feuerstein, Bill Pribyl & Chip Dawes;
Deutsche Übersetzung von Wolfgang Gabriel & Lars Schulten; 2. Auflage September
2003; ISBN 3-89721-260-9; Seiten 134; 8.90 €
• Overview of PL/SQL (Oracle Seiten):
http://otn.oracle.com/tech/pl_sql/index.html
Andreas Schmidt
Einführung in PL/SQL 22/22
