Datenbanksysteme - Wintersemester 2015/16

Datenbanksysteme
Wintersemester 2015/16
Prof. Dr.-Ing. Sebastian Michel
TU Kaiserslautern
[email protected]
Weiterführende SQL Konzepte
Voraussetzen
vorgaenger nachfolger
5001
5041
5001
5043
5001
5049
5041
5216
5043
5052
5041
5052
5052
5259
Rekursion
vorlnr
5001
5041
5043
5049
4052
5052
5216
5259
5022
4630
Vorlesungen
titel
Grundzuege
Ethik
Erkenntnistheorie
Maeeutik
Logik
Wissenschaftstheorie
Bioethik
Der Wiener Kreis
Glaube und Wissen
Die 3 Kritiken
Welche Vorlesungen müssen besucht werden, um die Vorlesung
“Der Wiener Kreis” verstehen zu können?
Wie kann dies in SQL berechnet werden?
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
2 / 73
Weiterführende SQL Konzepte
Rekursion
Rekursion
Welche Vorlesungen müssen besucht werden, um die Vorlesung
“Der Wiener Kreis” verstehen zu können?
select Vorgaenger
from voraussetzen, Vorlesungen
where Nachfolger=VorlNr and
Titel = ’Der Wiener Kreis’;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
3 / 73
Weiterführende SQL Konzepte
Rekursion
Rekursion
Welche Vorlesungen müssen besucht werden, um die Vorlesung
“Der Wiener Kreis” verstehen zu können?
select Vorgaenger
from voraussetzen, Vorlesungen
where Nachfolger=VorlNr and
Titel = ’Der Wiener Kreis’;
Hier werden allerdings nur die direkten Vorgänger berechnet. Ergebnis:
Vorlesung mit VorlNr=5052.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
3 / 73
Weiterführende SQL Konzepte
Rekursion
Der Wiener Kreis 5259
Wissenschaftstheorie 5052
Erkenntnistheorie 5043
Bioethik 5216
Ethik 5041
Mäeutik 5049
Grundzüge 5001
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
4 / 73
Weiterführende SQL Konzepte
Rekursion
select v1.Vorgaenger
from voraussetzen v1, voraussetzen v2, Vorlesungen v
where v1.Nachfolger= v2.Vorgaenger and
v2.Nachfolger= v.VorlNr and
v.Titel=’Der Wiener Kreis’;
Der Wiener Kreis 5259
Wissenschaftstheorie 5052
Erkenntnistheorie 5043
Bioethik 5216
Ethik 5041
Mäeutik 5049
Grundzüge 5001
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
5 / 73
Weiterführende SQL Konzepte
Rekursion
Vorgänger der Tiefe n
select v1.Vorgaenger
from voraussetzen v1
...
voraussetzen vn minus 1
voraussetzen vn,
Vorlesungen v
where v1.Nachfolger= v2.Vorgaenger and
...
vn minus 1.Nachfolger= vn.Vorgaenger and
vn.Nachfolger = v.VorlNr and
v.Titel=’Der Wiener Kreis’;
Wie kann man alle Vorgänger finden?
Tiefe 1 union Tiefe 2 union Tiefe 3 union ...
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
6 / 73
Weiterführende SQL Konzepte
Rekursion
Transitive Hülle
Was hier sehr hilfreich wäre: Berechnung der transitiven Hülle.
transA,B (R) = {(a,b)|∃k ∈ N(∃τ1 , τ2 , ...τk ∈ R(
τ1 .A = τ2 .B∧
τ2 .A = τ3 .B∧
...
τk−1 .A = τk .B∧
τ1 .A = a∧
τk .B = b))}
Enthält alle Paare, für die ein “Pfad” beliebiger Länge k in R
existiert.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
7 / 73
Weiterführende SQL Konzepte
Rekursion
Values Statement
ˆ Erzeugt konstante Tabelle
ˆ values (1, ’eins’), (2, ’zwei’), (3, ’drei’);
ist äquivalent zu:
select 1 as column1, ’eins’ AS column2
union all
select 2, ’zwei’
union all
select 3, ’drei’;
Verwendung z.B. in Select Statement (auch in Joins) als
normale Tabelle
select * from
(values (1,’eins’), (2,’zwei’) ) as table1 (nummer, wert);
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
8 / 73
Weiterführende SQL Konzepte
Rekursion
Vergleiche hierzu:
with mytable(mycolumn) as(
values (1)
)
select mycolumn+1
from mytable;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
9 / 73
Weiterführende SQL Konzepte
Rekursion
Rekursionen in SQL
Definieren eine rekursive “View”, basierend auf Union (all) von Zwei
Anfragen: Einer nicht-rekursiven Anfrage und einer rekursiven.
with recursive mytable(mycolumn) as (
values(1)
nicht rekursiver Teil
union all
union all
select mycolumn+1
rekursiver Teil:
from mytable
nur dieser darf mytable referenzieren
where mycolumn < 100
)
eigentlicher Aufruf
select sum(mycolumn)
from mytable;
Ergebnis: 5050
Achtung: Prof.
Darauf
achten, dass
die Rekursion
terminiert!
Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
10 / 73
Weiterführende SQL Konzepte
Rekursion
Auswertung von rekursiven Anfragen
Schritt 1
ˆ Evaluiere den nicht-rekursiven Teil. Für UNION (nicht aber für
UNION ALL), entferne Duplikate. Alle verbliebenen Tupel werden in
Ergebnis hinzugefügt und auch in eine temporäre Tabelle kopiert.
Schritt 2: Solange temporäre Tabelle nicht leer ist wiederhole:
(a) Evaluiere den rekursiven Teil, wobei die Eigenreferenz durch die
temporäre Tabelle ersetzt wird. Für UNION (aber nicht UNION
ALL), entferne Duplikate und auch Duplikate zu vorherigen
Ergebnissen. Füge verbliebene Tupel in Ergebnis hinzu und ebenso in
eine temporäre Zwischenergebnis-Tabelle.
(b) Ersetzt Inhalt der temporären Tabelle mit dem Inhalt der
Zwischenergebnis-Tabelle, leere die temporäre
Zwischenergebnis-Tabelle.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
11 / 73
Weiterführende SQL Konzepte
Rekursion
Rekursion: Anfrage für Voraussetzungen
with recursive TransitivVorl (Vorg, Nachf)
as (
select Vorgaenger, Nachfolger
from voraussetzen
union all
select distinct t.Vorg, v.Nachfolger
from TransitivVorl t, voraussetzen v
where t.Nachf=v.Vorgaenger
)
select *
from TransitivVorl
order by (vorg,nachf) asc;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
12 / 73
Weiterführende SQL Konzepte
Rekursion
Rekursion: Anfrage für Voraussetzungen
with recursive TransitivVorl (Vorg, Nachf)
as (
select Vorgaenger, Nachfolger
from voraussetzen
union all
select distinct t.Vorg, v.Nachfolger
from TransitivVorl t, voraussetzen v
where t.Nachf=v.Vorgaenger
)
select v2.titel
from TransitivVorl tv, vorlesungen v1, vorlesungen v2
where tv.nachf=v1.vorlnr and v1.titel=’Der Wiener Kreis’
and vorg=v2.vorlnr;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
13 / 73
Weiterführende SQL Konzepte
Rekursion
with recursive TransitivVorl (Vorg, Nachf, iteration)
as (
select Vorgaenger, Nachfolger, 1 as iteration
from voraussetzen
union all
select distinct t.Vorg, v.Nachfolger, 1+ t.iteration
from TransitivVorl t, voraussetzen v
where t.Nachf=v.Vorgaenger
)
select * from TransitivVorl order by iteration;
Der Wiener Kreis 5259
Wissenschaftstheorie 5052
Erkenntnistheorie 5043
Bioethik 5216
Ethik 5041
Mäeutik 5049
Grundzüge 5001
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
14 / 73
Weiterführende SQL Konzepte
Rekursion
Nur zur Info: User-defined Function (UDF), die für eine bestimmte
Vorlesung alle Vorgänger berechnet. UDFs betrachten wir später noch.
CREATE OR REPLACE FUNCTION alleVorgaenger(id integer)
RETURNS TABLE (VorlNr integer) AS $$
BEGIN
-- temporaere Tabellen
CREATE TEMP TABLE IF NOT EXISTS vorgaengerTabelle(VorlNr integer);
CREATE TEMP TABLE IF NOT EXISTS neu_vorgaengerTabelle(VorlNr integer);
CREATE TEMP TABLE IF NOT EXISTS temp_Tabelle(VorlNr integer);
DELETE FROM vorgaengerTabelle;
DELETE FROM temp_Tabelle;
DELETE FROM neu_vorgaengerTabelle;
--los geht es mit Suche der direkten Vorgaenger
INSERT INTO neu_vorgaengerTabelle (select v.vorgaenger from voraussetzen v where v.nachfolger = id);
LOOP
INSERT INTO vorgaengerTabelle (select r.vorlnr from neu_vorgaengerTabelle r);
INSERT INTO temp_Tabelle
--suche neue Vorgaenger
(SELECT v.vorgaenger
FROM neu_vorgaengerTabelle r, voraussetzen v
WHERE v.nachfolger = r.vorlnr
) --aber nur Neue
EXCEPT (SELECT r.vorlnr FROM vorgaengerTabelle r);
DELETE from neu_vorgaengerTabelle;
INSERT into neu_vorgaengerTabelle (select * from temp_Tabelle);
DELETE FROM temp_Tabelle;
IF NOT EXISTS (SELECT * FROM neu_vorgaengerTabelle) THEN
EXIT; --exit aus Schleife, falls keine weiteren Vorgaenger gefunden
END IF;
END LOOP;
RETURN QUERY SELECT r.vorlnr FROM vorgaengerTabelle r;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM alleVorgaenger(5259)
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
15 / 73
Weiterführende SQL Konzepte
Rekursion
Rekursion in SQL
ˆ Iteration wird so lange ausgeführt bis keine neuen Tupel hinzugefügt
werden.
ˆ Also ein Fixpunkt erreicht ist.
Voraussetzungen
ˆ Die rekursive Anfrage muss monoton sein, d.h. ausgeführt auf einer
Instanz V1 der rekursiven View muss Ergebnis eine Obermenge von
den Ergebnissen auf Instanz V2 sein, falls V1 eine Obermenge von V2
ist.
ˆ D.h. wenn mehr Tupel zur View hinzugefügt werden muss die
rekursive Anfrage mindestens die gleichen Tupel wie zuvor liefern.
ˆ z.B. sollte die rekursive Anfrage also kein NOT EXISTS enthalten.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
16 / 73
Embedded SQL
Übersicht der (kommenden) Vorlesung(en)
ˆ Embedded SQL (in Java und C++)
ˆ Stored Procedures und User-Defined Functions
ˆ Database Triggers
Danach:
ˆ Transaktionsverwaltung (Recovery, Mehrbenutzersynchronisation)
ˆ MapReduce und NoSQL
ˆ ...
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
17 / 73
Embedded SQL
Wiederholung JDBC
Wiederholung: JDBC: Connect und einfache Anfrage
1
2
3
4
5
6
// r e g i s t r i e r e g e e i g n e t e n T r e i b e r ( h i e r f u e r
Postgresql )
C l a s s . forName ( ” o r g . p o s t g r e s q l . D r i v e r ” ) ;
// e r z e u g e V e r b i n d u n g z u r Datenbank
C o n n e c t i o n conn = D r i v e r M a n a g e r . g e t C o n n e c t i o n (
” jdbc : postgresql :// l o c a l h o s t / u n i v e r s i t y ” ,
” username ” , ” p a s s w o r d ” ) ;
7
8
9
// e r z e u g e e i n e i n f a c h e s S t a t e m e n t O b j e k t
S t a t e m e n t stmt = conn . c r e a t e S t a t e m e n t ( ) ;
10
11
12
13
// m i t e x e c u t e Query koennen nun d a r a u f A n f r a g e n
a u s g e f u e h r t werden
// E r g e b n i s s e i n Form e i n e s R e s u l t S e t O b j e k t s
R e s u l t S e t r s e t = stmt . e x e c u t e Q u e r y ( ”SELECT p .
p e r s n r from p r o f e s s o r e n p” ) ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
18 / 73
14
15
Embedded SQL
Wiederholung JDBC
Wiederholung: JDBC: Connect und einfache Anfrage
// d i e s e s b e s i t z t Metadaten
R e s u l t S e t M e t a D a t a metadata = r s e t . getMetaData ( ) ;
16
17
18
// w e l c h e A t t r i b u t e ( S p a l t e n ) b e s i t z e n d i e
E r g e b n i s −T u p e l ?
i n t c o l u m n c o u n t = metadata . getColumnCount ( ) ;
19
20
21
22
23
24
25
26
f o r ( i n t i n d e x =1; i n d e x <=c o l u m n c o u n t ; i n d e x ++)
{
System . o u t . p r i n t l n ( ” S p a l t e ”+i n d e x+”
heisst ” +
metadata . getColumnName ( i n d e x ) ) ;
}
// i t e r i e r e nun u e b e r E r g e b n i s s e
while ( r s e t . next () ) {
System . o u t . p r i n t l n ( r s e t . g e t S t r i n g ( 1 ) ) ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
19 / 73
Embedded SQL
Wiederholung JDBC
Call-level-Interface (CLI) vs. Embedded SQL
ˆ Unter Verwendung einer Bibliothek werden aus dem
Anwendungsprogramm (Wirtssprache) Funktionen aufgerufen, die mit
dem DBMS kommunizieren.
ˆ JDBC ist ein Beispiel für ein CLI
ˆ Im embedded SQL werden hingegen SQL Anweisungen direkt
in der Wirtssprache benutzt.
ˆ (Dennoch werden diese letztendlich durch Aufrufe von
DBMS-spezifischen Bibliotheken realisiert)
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
20 / 73
Embedded SQL
Wiederholung JDBC
Embedded SQL (ESQL)
Idee
ˆ Benutze SQL-Anweisungen direkt im Programmcode
ˆ Syntax in Java:
#s q l { <s q l −s t a t e m e n t > } ;
ˆ Syntax in C oder C++
EXEC SQL <s q l −s t a t e m e n t >;
Zum Beispiel:
1
2
3
4
5
EXEC SQL
SELECT vorname , nachname
INTO : vorname , : nachname
FROM m i t a r b e i t e r t a b e l l e
WHERE p n r = : p n r ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
21 / 73
Embedded SQL
SQLJ
SQLJ: Embedded SQL für Java
ˆ Einbettung von SQL in Java
ˆ Anweisungen der Form
1
#s q l { <s q l −s t a t e m e n t > } ;
Zum Beispiel:
1
2
#s q l {INSERT INTO emp ( ename , s a l )
VALUES ( ' Joe ' , 43000) } ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
22 / 73
Embedded SQL
SQLJ
SQLJ: Embedded SQL für Java
Idee
ˆ SQL Anweisungen werden direkt im Java Code benutzt (embedded)
ˆ Ein Precompiler übersetzt diesen gemischten Code (in *.sqlj
Dateien) in normalen Java Code (in *.java Dateien).
ˆ Java Code benutzt dann JDBC oder Implementierung ähnlich zum
JDBC Konzept.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
23 / 73
Embedded SQL
SQLJ
SQLJ: Schritte der Übersetzung
Check gegen DBMS ist optional.
Quelle:
https://docs.oracle.com/cd/B28359_01/java.111/b31227/overview.htm
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
24 / 73
Embedded SQL
SQLJ
SQLJ: Vor- und Nachteile im Vergleich zu JDBC
Vorteile
ˆ Einfacher (kompakter) Code
ˆ Verwendung der gleichen Variablen in SQL und in Java
Nachteile
ˆ Erfordert extra Übersetzung in “normales” Java.
Umsetzung/Unterstützung
ˆ Wird von Oracle angeboten (im eigenen DBMS)
ˆ Sonst kaum (nicht) anzutreffen
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
25 / 73
Embedded SQL
SQLJ
Beispiel
https://docs.oracle.com/cd/B28359_01/java.111/b31227/overview.htm
1
import java . s q l . * ;
2
3
4
5
6
7
8
9
10
11
12
13
14
/* *
T h i s i s what you have t o do i n SQLJ
* */
p u b l i c c l a s s SimpleDemoSQLJ
{
//TO DO: make a main t h a t c a l l s t h i s
p u b l i c A d d r e s s g e t E m p l o y e e A d d r e s s ( i n t empno )
t h r o w s SQLException
{
Address addr ;
#s q l { SELECT o f f i c e a d d r INTO : a d d r FROM
employees
WHERE empnumber = : empno } ;
r e t u r n addr ;
}
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
26 / 73
Embedded SQL
SQLJ
Beispiel
15
p u b l i c Address updateAddress ( Address addr )
t h r o w s SQLException
{
#s q l a d d r = { VALUES(UPDATE ADDRESS ( : a d d r ) )
};
r e t u r n addr ;
}
16
17
18
19
20
21
22
}
ˆ Vergleichbarer Code in JDBC ist um einiges länger
ˆ Siehe obige URL (Oracle)
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
27 / 73
Embedded SQL
Embedded SQL in C/C++
Embedded SQL in C/C++
ˆ Weit verbreitet, wird von vielen DBMS unterstützt
ˆ Postgresql: ECPG compiler
ˆ Oracle: Pro*C/C++ compiler
Embedded SQL ist standardisiert. Dies gilt allerdings nicht für
Spracherweiterungen wie Oracles PL/SQL oder Postgresqls PL/pgSQL.
Microsoft hat mit LINQ (Language Integrated Query) einen zu embedded
SQL ähnlichen Ansatz für das .NET Framework entwickelt.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
28 / 73
Embedded SQL
Embedded SQL in C/C++
Beispiel
1
2
3
4
5
6
#i n c l u d e < s t d i o . h>
EXEC SQL BEGIN DECLARE SECTION ;
i n t matrnr ;
c h a r name [ 1 0 2 4 ] ;
i n t matrnrBound ;
EXEC SQL END DECLARE SECTION ;
7
8
9
i n t main ( )
{ EXEC SQL CONNECT TO u n i x : p o s t g r e s q l : / /
localhost/ university ;
10
11
12
13
// s e l e c t f o r s i n g l e r e s u l t i t e m s , o t h e r w i s e
use c u r s o r s
EXEC SQL SELECT m a t r n r INTO : m a t r n r from
s t u d e n t e n where name = ' F i c h t e ' ;
p r i n t f ( ” m a t r n r=%s \n ” , m a t r n r ) ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
29 / 73
Embedded SQL
// nun A n f r a g e m i t m e h r e r e n E r g e b n i s s e n
matrnrBound = 2 7 0 0 0 ;
EXEC SQL DECLARE m y c u r s o r CURSOR FOR
SELECT matrnr , name
FROM s t u d e n t e n
WHERE m a t r n r < : matrnrBound
ORDER BY s e m e s t e r ;
EXEC SQL OPEN m y c u r s o r ;
EXEC SQL WHENEVER NOT FOUND DO BREAK ;
while (1) {
EXEC SQL FETCH m y c u r s o r INTO : matrnr , : name ;
p r i n t f (”% i \ t%s \n ” , matrnr , name ) ;
}
EXEC SQL CLOSE m y c u r s o r ;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT ALL ;
return 0;
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Embedded SQL in C/C++
}
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
30 / 73
Embedded SQL
Embedded SQL in C/C++
Embedded SQL in C/C++
ECPG
ˆ http://www.postgresql.org/docs/9.3/interactive/
ecpg-commands.html
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
31 / 73
Embedded SQL
Embedded SQL in C/C++
Übersetzen von embedded SQL Code
Das Tool ecpg ist der Precompiler für Postgresqls embedded C.
ˆ Eingabe ist eine Quellcode-Code in C mit eingebetteten SQL
Befehlen.
ˆ Ausgabe ist C-Code (Datei), der mittels der ECPG Bibliothek in der
Lage ist mit der Postgresql Datenbank zu kommunizieren.
Übersetzung von embedded SQL in reines C:
ecpg test.c -o test_parsed.c
Kombilieren des C-Codes:
gcc test_parsed.c -o test -I /usr/include/postgresql/ -lecpg
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
32 / 73
Embedded SQL
Embedded SQL in C/C++
Übersetzter ECPG Code
Nur zur Veranschaulichung!
1
2
3
4
5
6
/ * P r o c e s s e d by ecpg ( 4 . 8 . 0 ) * /
/ * These i n c l u d e f i l e s a r e added by t h e p r e p r o c e s s o r * /
#i n c l u d e < e c p g l i b . h>
#i n c l u d e <e c p g e r r n o . h>
#i n c l u d e <s q l c a . h>
/ * End o f a u t o m a t i c i n c l u d e s e c t i o n * /
7
8
#l i n e 1 ” t e s t . c ”
9
10
#i n c l u d e < s t d i o . h>
11
12
/* e x e c s q l b e g i n d e c l a r e s e c t i o n */
13
14
15
#l i n e 6 ” t e s t . c ”
i n t matrnr ;
16
17
18
#l i n e 7 ” t e s t . c ”
c h a r name
[ 1024
;
Prof. Dr.-Ing.
S. Michel]
TU Kaiserslautern
Datenbanksysteme, WS 15/16
33 / 73
19
20
21
22
Embedded SQL
Embedded SQL in C/C++
#l i n e 8 ” t e s t . c ”
i n t matrnrBound ;
/ * e x e c s q l end d e c l a r e s e c t i o n * /
#l i n e 9 ” t e s t . c ”
23
24
25
26
27
i n t main ( )
{
{ ECPGconnect ( L I N E , 0 , ” u n i x : p o s t g r e s q l : / /
l o c a l h o s t / u n i v e r s i t y ” , NULL , NULL , NULL , 0 ) ; }
#l i n e 14 ” t e s t . c ”
28
29
30
// a s con1 USER s m i c h e l ;
//EXEC SQL CONNECT TO u n i x : p o s t g r e s q l : / / l o c a l h o s t /
u n i v e r s i t y AS m y c o n n e c t i o n USER s m i c h e l ;
31
32
33
34
35
// s e l e c t f o r s i n g l e r e s u l t i t e m s , o t h e r w i s e u s e
cursors
{ ECPGdo ( L I N E , 0 , 1 , NULL , 0 , ECPGst normal , ”
s e l e c t m a t r n r from s t u d e n t e n where name = ' F i c h t e ' ” ,
ECPGt EOIT ,
ECPGt int ,&( m a t r n r ) , ( l o n g ) 1 , ( l o n g ) 1 , s i z e o f ( i n t ) ,
Prof.NO
Dr.-Ing.
S. Michel
TU
15/16
34 / 73 ) ; }
ECPGt
INDICATOR
, Kaiserslautern
NULL , 0LDatenbanksysteme,
, 0L , 0L ,WS ECPGt
EORT
36
37
Embedded SQL
Embedded SQL in C/C++
#l i n e 19 ” t e s t . c ”
p r i n t f ( ” m a t r n r=%i \n” , m a t r n r ) ;
38
39
// nun e i n e A n f r a g e , d i e m e h r e r e E r g e b n i s s e z u r u e c k
liefert
40
41
matrnrBound = 2 7 0 0 0 ;
42
43
44
/ * d e c l a r e m y c u r s o r c u r s o r f o r s e l e c t m a t r n r , name
from s t u d e n t e n where m a t r n r < $ 1 o r d e r by s e m e s t e r
*/
#l i n e 31 ” t e s t . c ”
45
46
47
48
49
{ ECPGdo ( L I N E , 0 , 1 , NULL , 0 , ECPGst normal , ”
d e c l a r e m y c u r s o r c u r s o r f o r s e l e c t m a t r n r , name from
s t u d e n t e n where m a t r n r < $ 1 o r d e r by s e m e s t e r ” ,
ECPGt int ,&( matrnrBound ) , ( l o n g ) 1 , ( l o n g ) 1 , s i z e o f ( i n t ) ,
ECPGt NO INDICATOR , NULL , 0L , 0L , 0L , ECPGt EOIT ,
ECPGt EORT ) ; }
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
35 / 73
50
51
52
Embedded SQL
Embedded SQL in C/C++
#l i n e 33 ” t e s t . c ”
/* e x e c s q l w h e n e v e r n o t f o u n d
#l i n e 34 ” t e s t . c ”
b r e a k ; */
53
54
55
56
57
58
59
60
while (1) {
{ ECPGdo ( L I N E , 0 , 1 , NULL , 0 , ECPGst normal , ”
f e t c h m y c u r s o r ” , ECPGt EOIT ,
ECPGt int ,&( m a t r n r ) , ( l o n g ) 1 , ( l o n g ) 1 , s i z e o f ( i n t ) ,
ECPGt NO INDICATOR , NULL , 0L , 0L , 0L ,
ECPGt char , ( name ) , ( l o n g ) 1 0 2 4 , ( l o n g ) 1 , ( 1 0 2 4 ) * s i z e o f (
char ) ,
ECPGt NO INDICATOR , NULL , 0L , 0L , 0L , ECPGt EORT ) ;
#l i n e 36 ” t e s t . c ”
61
62
63
i f ( s q l c a . s q l c o d e == ECPG NOT FOUND) b r e a k ; }
#l i n e 36 ” t e s t . c ”
64
p r i n t f ( ”%i \ t%s \n” , matrnr , name ) ;
65
66
}
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
36 / 73
67
68
Embedded SQL
Embedded SQL in C/C++
{ ECPGdo ( L I N E , 0 , 1 , NULL , 0 , ECPGst normal , ”
c l o s e m y c u r s o r ” , ECPGt EOIT , ECPGt EORT ) ; }
#l i n e 40 ” t e s t . c ”
69
70
71
{ ECPGtrans ( L I N E
#l i n e 41 ” t e s t . c ”
, NULL , ” commit ” ) ; }
72
73
74
{ ECPG di sc onn ec t (
#l i n e 42 ” t e s t . c ”
LINE
, ”ALL” ) ; }
75
return 0;
76
77
}
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
37 / 73
Embedded SQL
Embedded SQL in C/C++
Aufbau einer Verbindung zur DB
#i n c l u d e < s t d i o . h>
i n t main ( )
{
EXEC SQL CONNECT TO
u n i x : p o s t g r e s q l : // l o c a l h o s t / u n i v e r s i t y ;
// . . .
EXEC SQL COMMIT;
EXEC SQL DISCONNECT ALL ;
return 0;
}
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
38 / 73
Embedded SQL
Embedded SQL in C/C++
Aufbau einer Verbindung zur DB (2)
ˆ Es können auch mehrere Verbindungen aufgebaut werden und via
Namen benutzt werden:
EXEC SQL CONNECT TO
u n i x : p o s t g r e s q l : // l o c a l h o s t / u n i v e r s i t y ;
AS conn1 ;
ˆ Weitere Parameter wie Login, Passwort, Port sind natürlich ebenfalls
möglich
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
39 / 73
Embedded SQL
Embedded SQL in C/C++
Host-Variablen
Die sogennanten Host-Variablen sind Variablen, die gemeinsam vom
Programmcode und SQL Anweisungen benutzt werden können.
Der Name der C/C++ Variablen wird in SQL mit einem Doppelpunkt als
Präfix benutzt. Z.B.
EXEC SQL INSERT INTO s o m e t a b l e VALUES ( : v1 ,
' ,
: v2 ) ;
'
Deklaration
Host-Variablen müssen speziell deklariert werden:
EXEC SQL BEGIN DECLARE SECTION ;
i n t x =4;
char foo [ 1 6 ] , bar [ 1 6 ] ;
EXEC SQL END DECLARE SECTION ;
http://www.postgresql.org/docs/9.3/interactive/
ecpg-variables.html
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
40 / 73
foo
Embedded SQL
Embedded SQL in C/C++
Transaktionen
ˆ Abschließen einer Transaktion
EXEC SQL COMMIT
ˆ Rollback der aktuellen Transaktion
EXEC SQL ROLLBACK
ˆ Ein- bzw. Abschalten des automatischen Commits
EXEC SQL SET AUTOCOMMIT TO ON
EXEC SQL SET AUTOCOMMIT TO OFF
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
41 / 73
Embedded SQL
Embedded SQL in C/C++
Arbeiten mit Cursorn
Gibt eine Anweisung mehrere Zeilen zurück müssen Cursor benutzt
werden. In JDBC-Terminologie ist ein ResultSet ein Cursor.
1
2
....
matrnrBound = 2 7 0 0 0 ;
3
4
5
6
7
8
9
EXEC SQL DECLARE m y c u r s o r CURSOR FOR
SELECT matrnr , name
FROM s t u d e n t e n
WHERE m a t r n r < : matrnrBound
ORDER BY s e m e s t e r ;
ˆ Die Host-Variable matrnBound wird hier direkt in der SQL-Anweisung
benutzt.
ˆ Man könnte auch eine parametrisierte SQL Anweisung benutzten.
Wie wurde diese Klasse in JDBC genannt?
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
42 / 73
Embedded SQL
Embedded SQL in C/C++
Arbeiten mit Cursorn (2)
ˆ Der Cursor muss nun nur noch geöffnet werden
ˆ Dann kann via FETCH auf die einzelnen Tupel bzw. Spalten
zugegriffen werden.
1
2
3
4
// c u r s o r w i r d g e o e f f n e t
EXEC SQL OPEN m y c u r s o r ;
// was s o l l g e s c h e h e n wenn k e i n e E r g e b n i s s e
g e l i e f e r t werden ?
EXEC SQL WHENEVER NOT FOUND DO BREAK ;
5
6
7
8
9
10
11
// s o l a n g e k e i n BREAK a u f g e r u f e n w i r d l a u f e u e b e r
Zeilen
while (1) {
EXEC SQL FETCH m y c u r s o r INTO : matrnr , : name ;
p r i n t f (”% i \ t%s \n ” , matrnr , name ) ;
}
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
43 / 73
Embedded SQL
Embedded SQL in C/C++
Prepared-Statements
Ähnlich zu JDBC können wir auch in embedded SQL Prepared-Statements
benutzen.
EXEC SQL PREPARE stmt1 FROM ”SELECT o i d , datname
FROM p g d a t a b a s e WHERE o i d = ? ” ;
Bei der (bzw. vor der) Ausführung müssen dann die freien Parameter
gesetzt werden. Zudem wird erst jetzt angegeben in welchen
Host-Variablen die Attribute des Ergebnis-Tupels gespeichert werden soll.
EXEC SQL EXECUTE stmt1 INTO : d b o i d , : dbname
USING 1 ;
Wenn das Prepared-Statement nicht mehr gebraucht wird:
EXEC SQL DEALLOCATE PREPARE name ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
44 / 73
Embedded SQL
Embedded SQL in C/C++
Prepared-Statements und Cursor
Hier wird ein Prepared-Statement erzeugt und dann ein Cursor darüber
definiert:
1
2
EXEC SQL PREPARE stmt1 FROM ”SELECT o i d , datname
FROM p g d a t a b a s e WHERE o i d > ? ” ;
EXEC SQL DECLARE f o o b a r CURSOR FOR stmt1 ;
3
4
5
// wenn Ende e r r e i c h t i s t , m i t t e l s BREAK a u s
While−S c h l e i f e a u s s t e i g e n
EXEC SQL WHENEVER NOT FOUND DO BREAK ;
6
7
8
9
10
11
EXEC SQL OPEN f o o b a r USING 1 0 0 ;
...
while (1)
{
EXEC SQL FETCH NEXT FROM f o o b a r INTO : d b o i d
, : dbname ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
45 / 73
Embedded SQL
Embedded SQL in C/C++
Allgemein: Prepared-Statements, Static vs. Dynamic SQL
Übersetzung von Anfragen im DBMS
ˆ Tritt eine Anfrage zum ersten Mal auf, muss sie “übersetzt” werden
(compiled).
ˆ D.h. Syntaxprüfung, Prüfung von Rechten, Anfrageoptimierung,
Code-Generierung, etc.
Wiederverwendung von kompilierten Anfragen
ˆ Sind Anfragen parametrisiert, wie im Falle von Prepared-Statements,
so kann das DBMS den bereits erzeugten Plan wiederverwenden.
ˆ Im Vergleich zu nicht parametrisierten Statements, fallen hier die
Kosten für die Übersetzung nur ein Mal an.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
46 / 73
Embedded SQL
Embedded SQL in C/C++
Allgemein: Prepared-Statements, Static vs. Dynamic SQL
Wiederverwendung von kompilierten Anfragen
ˆ DBMS prüft bei Eintreffen einer Anfrage ob Plan bereits existiert.
ˆ Dazu werden Pläne in Cache gehalten (→Ersetzungsstrategien)
Neu-Kompilierung bestehender Pläne
Falls sich Eigenschaften der DB ändern die essentiell für Plangenerierung
sind. Zum Beispiel:
ˆ Indexe werden hinzugefügt oder gelöscht.
ˆ Sehr viele Änderungen an Daten der relevanten Tabellen
ˆ Explizite Anweisungen neu zu kompilieren, bzw.
ˆ neue Statistiken verfügbar
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
47 / 73
Embedded SQL
Embedded SQL in C/C++
CLIs für Postgresql
Für C++: libpqxx
ˆ http://pqxx.org/
Für Ruby: pg
ˆ https://rubygems.org/gems/pg
require 'pg'
conn = PG::Connection.open(:host => 'localhost',
:dbname => 'university', :user => 'username',
:password => 'my password')
res = conn.exec("select name from studenten")
res.each do |row|
puts row['name']
end
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
48 / 73
Stored Procedures/UDFs
Motivation und Übersicht
Stored Procedures / User-Defined Functions
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
49 / 73
Stored Procedures/UDFs
Motivation und Übersicht
Stored Procedures/UDFs
Bislang: Kommunikation mit DBMS via Anwendungsprogrammen:
Einzelne Statements, Verarbeitung in Wirtssprache.
Manchmal ist es aber sinnvoll, Teile der Anwendung direkt im DBMS
auszuführen und nicht via einzelnen SQL Statements.
Vorteile
ˆ Daten müssen nicht erst auf dem DBMS zur Anwendung gebracht
werden (und umgekehrt)
ˆ Höhere Performance
ˆ Code kann wiederverwendet werden (zwischen Anwendungen)
Nachteile
ˆ Etwas aufwendiger zu erstellen.
ˆ Debugging schwieriger.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
50 / 73
Stored Procedures/UDFs
Motivation und Übersicht
SQL vs. SQL/PSM vs. PL/SQL bzw. PL/pgSQL
SQL
ˆ Standard Query Language für Datenbanksysteme. Deklarativ.
ˆ SQL Anweisungen können via Anwendungsprogrammierung (JDBC
oder Embedded SQL) an DB geschickt werden.
PSM
ˆ Persistent, Stored Modules (PSM), bzw. Sprache diese zu realisieren.
ˆ Im SQL:2003 Standard definiert. Erlaubt es prozeduralen Code direkt
innerhalb der DB zu schreiben.
PL/SQL bzw. PL/pgSQL
ˆ Procedural Language/(PostgreSQL) Structured Query Language
ˆ Prozedurale Sprache, benutzt in Oracle bzw. Postgresql
ˆ Inspiriert von bzw. implementiert PSM.
ˆ PL/SQL bzw. PL/pgSQL erlauben diese Anwendungslogik als
Prozedur
innerhalb des TU
DBMS
zu definieren.
Prof. Dr.-Ing. S. Michel
Kaiserslautern
Datenbanksysteme, WS 15/16
51 / 73
Stored Procedures/UDFs
Motivation und Übersicht
Vorteile von Stored Procedures / User Defined Functions
ˆ Ausführungspläne können vor-übersetzt werden, sind
wiederverwendbar
ˆ Anzahl der Zugriffe des Anwendungsprogramms auf das DBMS
werden reduziert, ebenso wie Menge an Daten, die zwischen
Anwendung und DBMS hin und her geschickt wird.
ˆ Prozeduren sind als gemeinsamer Code für verschiedene
Anwendungsprogramme nutzbar
ˆ Es wird ein höherer Isolationsgrad der Anwendung von dem DBMS
erreicht.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
52 / 73
Stored Procedures/UDFs
Motivation und Übersicht
Beispiel
CREATE
FUNCTION w i e V i e l e V L ( m a t r n r i n t )
RETURNS i n t AS $$
DECLARE
qty i n t ;
BEGIN
SELECT COUNT( * ) INTO q t y
FROM h o e r e n h
WHERE h . m a t r n r = m a t r n r ;
RETURN q t y ;
END ;
$$ LANGUAGE p l p g s q l ;
select *
from wieVieleVL(28106);
Liefert Ergebnis: 4
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
53 / 73
Stored Procedures/UDFs
Motivation und Übersicht
Unterschied Stored Procedures und UDFs
Generell
ˆ UDF = user-defined function
ˆ Stored procedure muss explizit mit CALL aufgerufen werden (SQL
EXEC CALL name))
ˆ UDF kann direkt in SQL ohne CALL benutzt werden
In Postgresql
ˆ In Postgres (9.3) gibt es allerdings keinen Unterschied zwischen
stored procedures und UDFs
ˆ UDF wird aufgerufen in SELECT statements, z.b.
select from myFunction(44234234);
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
54 / 73
Stored Procedures/UDFs
Motivation und Übersicht
UDFs in Postgresql
Verschiedene Arten von UDFs
ˆ Query language Funktionen (SQL)
ˆ Procedural language Funktionen (PL/pgSQL, Perl, ...)
ˆ Interne Funktionen
ˆ C Funktionen
ˆ PL/Java erlaubt auch die Nutzung von Java
(http://pgfoundry.org/projects/pljava/)
http://www.postgresql.org/docs/9.3/static/xfunc.html
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
55 / 73
Stored Procedures/UDFs
LANGUAGE SQL
Query Language (SQL) Funktionen
Mit Hilfe des Schlüsselworts LANGUAGE wird angegeben welche Sprache
zur Definition dieser Funktion benutzt wurde, hier SQL.
ˆ Diese Funktion hat den Rückgabewert void
ˆ Sie ist definiert als einfaches SQL delete Statement und besitzt auch
keine Eingabeparameter.
CREATE FUNCTION c l e a n e m p ( ) RETURNS
v o i d AS $$
DELETE FROM emp
WHERE s a l a r y < 0 ;
$$ LANGUAGE SQL ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
56 / 73
Stored Procedures/UDFs
LANGUAGE SQL
Query Language Funktionen (2)
ˆ Diese Funktion hat als Parameter ein Tupel der Relation emp, die
neben Name des Mitarbeiters, dessen Gehalt (Salary), Alter und
Raum (Als Point-Objekt) enthält.
ˆ Der Rückgabewert ist Typ numeric
INSERT INTO emp VALUES ( ' B i l l ' , 4 2 0 0 , 4 5 , ' ( 2 , 1 ) ' ) ;
CREATE FUNCTION d o u b l e s a l a r y ( emp )
RETURNS n u m e r i c AS $$
SELECT $ 1 . s a l a r y * 2 AS s a l a r y ;
$$ LANGUAGE SQL ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
57 / 73
Stored Procedures/UDFs
LANGUAGE SQL
Query Language Funktionen (3)
CREATE FUNCTION addtoroom ( p r o f e s s o r e n )
RETURNS i n t AS $$
s e l e c t $ 1 . raum+1 ;
$$ LANGUAGE SQL
Anwendung/Aufruf:
select name, addtoroom(professoren.*) from professoren;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
58 / 73
Stored Procedures/UDFs
LANGUAGE SQL
Query Language Funktionen (4)
Hier wird eine Funktion definiert, die ein Dummy-Tupel für einen neuen
Professor erzeugt (gemäß der Relation professoren):
CREATE FUNCTION n e u e r P r o f ( )
RETURNS p r o f e s s o r e n
AS $$
SELECT 1 a s p e r s n r , t e x t ' Unbekannt ' AS name ,
t e x t ' C3 ' a s rang , 123 a s raum ;
$$ LANGUAGE SQL ;
Anwendung zum Beispiel:
insert into professoren (select * from neuerProf());
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
59 / 73
Stored Procedures/UDFs
LANGUAGE SQL
Query Language Funktionen (5)
Diese Funktion hat mehrere Eingaben und mehrere Ausgaben:
CREATE FUNCTION s u m n p r o d u c t
( x i n t , y i n t , OUT sum i n t , OUT p r o d u c t i n t )
AS $$ SELECT $ 1 + $ 2 , $ 1 * $ 2
$$ LANGUAGE SQL ;
Beispielaufruf:
SELECT * FROM sum_n_product(11,42);
sum | product
-----+--------53 |
462
(1 row)
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
60 / 73
Stored Procedures/UDFs
LANGUAGE SQL
Query Language Funktionen (6)
Ruckgabewerte: Einzelne Zeilen vs. Tabellen
CREATE FUNCTION a l l e P r o f s ( )
RETURNS p r o f e s s o r e n
a s $$
s e l e c t * from p r o f e s s o r e n ;
$$ LANGUAGE SQL ;
Beispielaufruf:
select * from alleProfs();
persnr |
name
| rang | raum
--------+----------+------+-----2125 | Sokrates | C4
| 226
(1 row)
Was macht select alleProfs(); ?
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
61 / 73
Stored Procedures/UDFs
LANGUAGE SQL
Tabellen als Rückgabewerte: Table Functions
CREATE FUNCTION g e t P r o f s ( i n t )
RETURNS TABLE( p e r s n r i n t ) AS $$
SELECT p e r s n r from p r o f e s s o r e n p
WHERE p . p e r s n r < $ 1 ;
$$ LANGUAGE SQL ;
select * from getProfs(2130);
persnr
-------2125
2126
2127
(3 rows)
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
62 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
PL/pgSQL
Anstelle von SQL in den vorherigen Beispielen wird nun PL/pgSQL
betrachtet.
[ <<l a b e l >> ]
[ DECLARE
declarations ]
BEGIN
statements
END [ l a b e l ] ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
63 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
PL/pgSQL
Anstelle von SQL in den vorherigen Beispielen wird nun PL/pgSQL
betrachtet.
CREATE FUNCTION s a l e s t a x ( s u b t o t a l r e a l )
RETURNS r e a l AS $$
BEGIN
RETURN s u b t o t a l * 0 . 0 6 ;
END ;
$$ LANGUAGE p l p g s q l ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
64 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
PL/pgSQL
CREATE FUNCTION
c o n c a t s e l e c t e d f i e l d s ( i n t sometablename )
RETURNS t e x t AS $$
BEGIN
RETURN i n t . f 1 | | i n t . f 3 | | i n t . f 5 | | i n t . f 7 ;
END ;
$$ LANGUAGE p l p g s q l ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
65 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
PL/pgSQL
Funktion mit zwei Eingabeparametern und zwei Ausgabeparametern:
CREATE FUNCTION
sum n product ( x int , y int ,
OUT sum i n t , OUT p r o d i n t )
AS $$
BEGIN
sum := x + y ;
p r o d := x * y ;
END ;
$$ LANGUAGE p l p g s q l ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
66 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
PL/pgSQL: SELECT INTO
SQL Anfragen, die nur eine Zeile zurück liefern können direkt in Variablen
eingelesen werden, z.B.
SELECT * INTO myrec FROM emp WHERE empname = myname ;
Falls mehrere Ergebnisse geliefert werden wird die erste Zeile benutzt.
Durch die Angabe von STRICT, also
SELECT * INTO STRICT myrec FROM emp
WHERE empname = myname ;
wird darauf geachtet, dass es nur genau ein Ergebnis gibt (ansonsten wird
eine Exception geworfen).
Siehe EXECUTE für dynamische Anfragen und PERFORM für Anfragen
ohne Ergebnis zu berücksichtigen:
http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
67 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
PL/pgSQL: Kontrollstrukturen
PL/pgSQL bietet die übliche Auswahl and Kontrollstrukturen wie
IF-Statements und Schleifen (LOOP WHILE, FOR), EXIT (=break),
CONTINUE
LOOP
I F c o u n t > 0 THEN
EXIT ;
END I F ;
END LOOP ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
68 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
PL/pgSQL: Kontrollstrukturen und SQL Anfragen
Über Anfrageergebnisse iterieren
CREATE OR REPLACE FUNCTION t e s t i t ( ) RETURNS i n t a s
$$
DECLARE
m y p r o f s RECORD ;
myint i n t = 0 ;
BEGIN
FOR m y p r o f s i n SELECT * FROM p r o f e s s o r e n
WHERE p e r s n r <2130
LOOP
myint = myprofs . p e r s n r + myint ;
END LOOP ;
r e t u r n myint ;
END ;
$$ LANGUAGE p l p g s q l ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
69 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
PL/pgSQL: IF Statement
I F number = 0 THEN
r e s u l t := ' z e r o ' ;
ELSIF number > 0 THEN
r e s u l t := ' p o s i t i v e ' ;
ELSIF number < 0 THEN
r e s u l t := ' n e g a t i v e ' ;
ELSE
−− dann muss d i e Z a h l wohl NULL s e i n . . .
r e s u l t := ' NULL ' ;
END I F ;
Siehe auch CASE statements.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
70 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
PL/pgSQL: Weitere Befehle - RAISE NOTICE
Zum Ausgeben von Meldungen/Warnungen oder einfach zur zum
Debuggen Ihreres PL/pgSQL Codes:
RAISE NOTICE ' P a r a m e t e r x = % und y = % ' , x , y
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
71 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
PL/pgSQL: Exception Handling
Syntax
....
EXCEPTION
WHEN c o n d i t i o n [ OR c o n d i t i o n . . . ] THEN
...
Beispiel
BEGIN
x := x + 1 ;
y := x / 0 ;
EXCEPTION
WHEN d i v i s i o n b y z e r o THEN
RAISE NOTICE ' c a u g h t d i v i s i o n b y z e r o ' ;
RETURN x ;
−−− bzw . a e q u i v a l e n t : WHEN SQLSTATE ' 2 2 0 1 2 ' THEN
END ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
72 / 73
Stored Procedures/UDFs
LANGAGE PL/pgSQL
Zusammenfassung UDFs bzw. PL/pgSQL
ˆ Die Implementierung von Teilen der Anwendungslogik direkt im
Datenbanksystem kann einige Vorteile haben. Z.B.
Wiederverwendbarkeit von Code und dass Daten nicht bewegt werden
müssen.
ˆ Realisiert im DBMS durch Stored Procedures bzw. User Defined
Functions (UDFs)
ˆ Basierend auf prozeduraler Sprache (PSM=Persistent Stored
Modules)
ˆ Haben uns PL/pgSQL als Beispiel genauer angeschaut
Ausblick
ˆ Integritätskontrolle im DBMS durch Trigger: Dort werden in
PL/pgSQL Prozeduren geschrieben, die beim Eintreffen eines
Ereignisses ausgeführt werden.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbanksysteme, WS 15/16
73 / 73