Einführung in PL/SQLsmiffy.de/DB-IS-II/folien/v11-PL-SQL-einfuehrung.pdf · Andreas Schmidt Einführung in PL/SQL 1/22 Fakultät für Informatik & Wirtschaftsinformatik DB & IS II

Andreas Schmidt Einführung in PL/SQL 1/22

Fakultät für Informatik & Wirtschaftsinformatik DB & IS II - SS 2020

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



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 */



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;



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



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)



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)



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;



Kontrollstrukturen (Schleifen)

• LOOP ... END LOOPa := 0;LOOP

a := a + 1;if a = 50 then

exit;end if;

END LOOP;

• FOR <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;



Ausnahmebehandlung

• Behandlung von Fehlern und Aus-nahmesituationen durch Excep-tionhandler

• Tritt Aufnahme 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; stadt_name varchar2(20) := 'Karlsruhe'; -- alternativ: 'Metropolis' 'Springfield'

BEGIN SELECT * INTO eine_stadt FROM mondial.city WHERE name = stadt_name;

dbms_output.put_line(eine_stadt.name||' hat '||eine_stadt.population||' Einwohner');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;/


Fakultät IWI DB & IS II - WS 2019

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



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;/



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



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)...



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;/



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 !!



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;/



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



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;/



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;/



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;

/



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;



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

Documents

Einführung in PL/SQLsmiffy.de/DB-IS-II/folien/v11-PL-SQL-einfuehrung.pdf · Andreas Schmidt Einführung in PL/SQL 1/22 Fakultät für Informatik & Wirtschaftsinformatik DB & IS II