Andreas Schmidt Einführung in PL/SQL 1/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
Einführung in PL/SQL
Rec. on?
Andreas Schmidt Einführung in PL/SQL 2/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
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üg-bar.
• Einsatzfelder:
• anonyme Blöcke
• Funktionen und Prozeduren, Methoden
• Trigger
Rec. on?
Andreas Schmidt Einführung in PL/SQL 3/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
Struktur eines PL/SQL Programms
DECLARE
-- Deklarationen
BEGIN
-- eigentlicher -- Programmteil
EXCEPTION
-- Fehlerbehandlung
END;/
• Deklarations und Ausnahmebehand-lung sind optional
• beliebige Schachtelung der Blöcke ineinander möglich
• Deklarationsteil erlaubt Definition von Variablen, Konstanten, Cursorn, Funk-tionen 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 4/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
Datentypen in PL/SQL
• BINARY_INTEGERWertebereich -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
• BeispielDECLARE
owner char(10) := ’ich’; tablename char(30); bytes number(10) := 128000; today date not Null;ok boolean;pi constant number := 3.14159;
CURSOR spieler_cursor isselect *from spielerorder by name;
Andreas Schmidt Einführung in PL/SQL 5/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
Datentypen in PL/SQL
• Namen von Tabellen und Spalten sind als vordefinierte Variable vor-handen
• Attribut %TYPEliefert Datentyp einer anderen Variablen oder einer Tabellenspalte
• Attribut %ROWTYPEliefert Datentyp einer Tabellenzeile (Zugriff auf einzelne Felder über „.“-Notation)
• Beispiele:DECLARE
stadt_name Stadt.name%type;nevada Wueste%rowtype;
hauptstadt stadt_name%type;
BEGIN...groesse := nevada.flaeche;
Spalte name in
Tabelle Stadt
Tabelle Wueste
Andreas Schmidt Einführung in PL/SQL 6/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
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 (mit sqlplus):SQL> set serveroutput on size 10000000
BEGINDBMS_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 7/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
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)
Andreas Schmidt Einführung in PL/SQL 8/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
Kontrollstrukturen (bedingte Anweisungen)
• bedingte Verzweigung
IF <bedingung> THEN<anweisungen>
END IF;
• AlternativeIF <bedingung> THEN
<anweisungen>ELSE
<anweisungen>END IF;
• mehrere Alternativen
IF <bedingung 1> THEN<anweisungen>
ELSIF <bedingung 2> THEN<anweisungen>
ELSIF <bedingung 3> THEN<anweisungen>
ELSE<anweisungen>
END IF;
Andreas Schmidt Einführung in PL/SQL 9/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
Kontrollstrukturen (Schleifen)
• LOOP ... END LOOPa := 0;LOOP
a := a + 1;if a = 50 then
exit;end if;
END LOOP;
• FOR <var> in <zaehlbereich> LOOP ... END LOOP
FOR i IN 1..50 LOOPinsert into buch (kapitel, text)values (i, text(i));
END LOOP;
• RückwärtsFOR i in REVERSE 1..20 loopdbms_output.put_line(i);
END LOOP;
• WHILE <bed> LOOP ... END LOOPa := 0;WHILE a <= 50 LOOP
b := b + a;a := a + 1;
END LOOP;
Andreas Schmidt Einführung in PL/SQL 10/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
Ausnahmebehandlung
• Behandlung von Fehlern und Aus-nahmesituationen durch Excep-tionhandler
• Tritt Ausnahme auf, so wird normaler Ablauf abgebrochen und eine Feh-lerbehandlung durchgeführt.
• Fehlerbehandlung erfolgt im EXCEPTION-Block.
• Ausnahmen können vordefiniert oder benutzerdefiniert sein.
Beispiel (vordefinierte Ausnahme)DECLARE eine_stadt mondial.city%rowtype;
s_name mondial.city.name%type := 'Karlsruhe'; -- alternativ: 'Metropolis' 'Springfield'
BEGIN SELECT * INTO s_name FROM mondial.city WHERE name = s_name;
dbms_output.put_line(eine_stadt.name||' hat '||eine_stadt.population||' Einwohner');EXCEPTION when no_data_found then dbms_output.put_line('Stadt '||s_name||
' gibt es nicht'); when too_many_rows then dbms_output.put_line('Stadt '||s_name||
' gibt es es mehrfach');END;/
Andreas Schmidt
Fakultät IWI DB & IS II
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 12/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
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 13/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
CURSOR
• SQL Anweisung liefert i.a. eine Menge von Tupeln zurück
• Cursor zur sequentiellen Abarbei-tung der Ergebnismenge
• Deklaration:DECLARE
...cursor c1 is
select * from land;
• Zugriff auf den Inhalt mittels
• OPEN
• FETCH
• CLOSE
• Status des Cursors
• %ISOPEN
• %FOUND
• %NOTFOUND
• %ROWCOUNT
Andreas Schmidt Einführung in PL/SQL 14/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
CURSOR
• Programmcode:declarecursor cur_french_city is
select * from mondial.city c where c.country = 'F'
and c.population is not null order by c.population desc; a_city mondial.city%rowtype;begin OPEN cur_french_city; FETCH cur_french_city INTO a_city; WHILE cur_french_city%found LOOP dbms_output.put_line('Datensatz '||
cur_french_city%ROWCOUNT||': '||a_city.name||' ('||a_city.population||')');
FETCH cur_french_city INTO a_city; END LOOP; CLOSE cur_french_city;end;
• Ausgabe:Datensatz 1: Paris (2152423)Datensatz 2: Marseille (800550)Datensatz 3: Lyon (415487)Datensatz 4: Toulouse (358688)Datensatz 5: Nice (342439)Datensatz 6: Strasbourg (252338)Datensatz 7: Nantes (244995)Datensatz 8: Bordeaux (210336)Datensatz 9: Montpellier (207996)Datensatz 10: Saint Etienne (199396)Datensatz 11: Rennes (197536)Datensatz 12: Le Havre (195854)Datensatz 13: Reims (180620)...
Andreas Schmidt Einführung in PL/SQL 15/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
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|| ': '||a_city.name||' ('||a_city.population||')'); END LOOP;end;/
Andreas Schmidt Einführung in PL/SQL 16/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
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: '||
a_city.name||' ('||a_city.population||')'); END LOOP;end;/
Hinweis: Hierbei kein Zugriff auf ROWCOUNT mehr möglich !!
Andreas Schmidt Einführung in PL/SQL 17/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
CURSOR mit Parametern
• in where Klausel können Auswahlkriterien angegeben werden
• Werte können beim öffnen des Cursors mit übergeben werdenz.B.: OPEN cur_city('D');
• Ideal bei geschachtelten Tabellen
• Beispieldeclare 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|| ': '||a_city.name||' ('||a_city.population||')'); END LOOP;END;/
Andreas Schmidt Einführung in PL/SQL 18/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
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')as-- hier koennen Variable,Cursor, etc deklariert werdenbegin
-- Implementierung;end;
• Prozedur-/Funktionsrumpf entspricht einem PL/SQL-Block ohne Schlüsselwort DECLARE
Andreas Schmidt Einführung in PL/SQL 19/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
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 numberas 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 20/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
Beispiel
declare
m number;
function minimum(a in number, b in number)
return numberas
begin if a < b then return a; else return b; end if; end;
procedure maximum(a in number, b in number, res out number)
as begin if a > b then res := a; else res := 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 21/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
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 aufgeru-fen werden.
• Aufruf (Definition rechte Seite);SQL> begindbms_output.put_line('fak(5)=' || fak(5));
end;/
-- alternative Kurzform (1-Zeiler):
SQL> exec dbms_output.put_line('fak(5)='||fak(5));
• Beispiel Funktionsdeklaration:create or replace function
fak(z in integer)return number
as-- Delarationsteil
f number := 1; begin for i in 2..z loop f := f * i; end loop; return f; end;
/
Andreas Schmidt Einführung in PL/SQL 22/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
Prozeduren/Funktionen als Datenbankobjekte
• Fehler bei Definition einer Prozedur/Funktion können mit dem SQLPLUS Kom-mando „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)
select text from user_source where name='FAK' and type='FUNCTION' order by line;
Andreas Schmidt Einführung in PL/SQL 23/23
Fakultät für Informatik & Wirtschaftsinformatik Datenbanken u. Informationssysteme II
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 3-89721-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. Auf-lage 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