pdf (1/1)

Datenbankanwendung
Wintersemester 2014/15
Prof. Dr.-Ing. Sebastian Michel
TU Kaiserslautern
smichel@cs.uni-kl.de
Embedded SQL
Übersicht der (kommenden) Vorlesungen
ˆ Embedded SQL (in Java und C++)
ˆ Stored Procedures und User-Defined Functions
ˆ Database Triggers
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
2 / 54
Embedded SQL
Wiederholung: JDBC: Connect und einfache Anfrage
// 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 P o s t g r e s q l )
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 ” ,
” use r n a m e ” , ” p a s s w o r d ” ) ;
// 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 s t m t = conn . c r e a t e S t a t e m e n t ( ) ;
// 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 = s t m t . 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
Datenbankanwendung, WS 14/15
3 / 54
Embedded SQL
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 m e t a d at a = r s e t . getMetaData ( ) ;
// 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 = m e ta d at a . getColumnCount ( ) ;
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+” h e i s s t ” +
m e t a d a t a . 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
Datenbankanwendung, WS 14/15
4 / 54
Embedded SQL
Call-level-Interface (CLI)
ˆ 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
Datenbankanwendung, WS 14/15
5 / 54
Embedded SQL
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:
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
Datenbankanwendung, WS 14/15
6 / 54
Embedded SQL
SQLJ
SQLJ: Embedded SQL für Java
ˆ Einbettung von SQL in Java
ˆ Anweisungen der Form
#s q l { <s q l −s t a t e m e n t > } ;
Zum Beispiel:
#s q l {INSERT INTO emp ( ename , s a l )
VALUES ( ' Joe ' , 4 3 0 0 0 ) } ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
7 / 54
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).
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
8 / 54
Embedded SQL
SQLJ
SQLJ: Schritte der Übersetzung
Quelle:
https://docs.oracle.com/cd/B28359_01/java.111/b31227/overview.htm
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
9 / 54
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
Datenbankanwendung, WS 14/15
10 / 54
Embedded SQL
SQLJ
Beispiel
https://docs.oracle.com/cd/B28359_01/java.111/b31227/overview.htm
1
import j a v a . s q l . * ;
2
3
4
5
6
7
8
/* *
T h i s i s what you h a v e 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
9
10
11
12
13
14
15
16
17
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 )
throws S Q L E x c e p t i o n
{
Address addr ;
#s q l { SELECT o f f i c e a d d r INTO : a d d r FROM e m p l o y e e s
WHERE empnumber = : empno } ;
return addr ;
}
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
11 / 54
Embedded SQL
SQLJ
Beispiel
18
pub lic Address updateAddress ( Address addr )
throws S Q L E x c e p t i o n
{
#s q l a d d r = { VALUES(UPDATE ADDRESS ( : a d d r ) ) } ;
return addr ;
}
19
20
21
22
23
24
25
}
ˆ Vergleichbarer Code in JDBC ist um einiges länger
ˆ Siehe obige URL (Oracle)
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
12 / 54
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
Datenbankanwendung, WS 14/15
13 / 54
Embedded SQL
Embedded SQL in C/C++
Beispiel
1
#i n c l u d e < s t d i o . h>
2
3
4
5
6
7
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 ;
8
9
10
11
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 ;
12
13
14
// 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 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 ' ;
15
16
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
Datenbankanwendung, WS 14/15
14 / 54
Embedded SQL
Embedded SQL in C/C++
Beispiel (2)
// 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
17
liefert
18
matrnrBound = 2 7 0 0 0 ;
19
20
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 ;
21
22
23
24
25
26
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;
27
28
29
30
31
32
33
34
35
36
37
}
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
15 / 54
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
Datenbankanwendung, WS 14/15
16 / 54
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
Datenbankanwendung, WS 14/15
17 / 54
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 e c p g ( 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
Datenbankanwendung, WS 14/15
18 / 54
19
20
21
22
Embedded SQL
Embedded SQL in C/C++
Übersetzter ECPG Code (2)
#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 c u r s o r s
{ 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 ) ,
ECPGt NO INDICATOR , NULL , 0L , 0L , 0L , ECPGt EORT ) ; }
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
19 / 54
36
37
Embedded SQL
Embedded SQL in C/C++
Übersetzter ECPG Code (3)
#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
Datenbankanwendung, WS 14/15
20 / 54
50
51
52
Embedded SQL
Embedded SQL in C/C++
Übersetzter ECPG Code (4)
#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 ( c h a r ) ,
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) break ; }
#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
Datenbankanwendung, WS 14/15
21 / 54
67
68
Embedded SQL
Embedded SQL in C/C++
Übersetzter ECPG Code (5)
{ 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
{ ECPGdisconnect (
#l i n e 42 ” t e s t . c ”
LINE
, ”ALL” ) ; }
75
return 0;
76
77
}
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
22 / 54
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
Datenbankanwendung, WS 14/15
23 / 54
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
Datenbankanwendung, WS 14/15
24 / 54
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 ,
' f o o ' , : 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
Datenbankanwendung, WS 14/15
25 / 54
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
Datenbankanwendung, WS 14/15
26 / 54
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.
....
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 ;
ˆ 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
Datenbankanwendung, WS 14/15
27 / 54
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.
// 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;
// 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 Z e i l e n
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 ) ;
}
// c u r s o r w i r d g e s c h l o s s e n
EXEC SQL CLOSE m y c u r s o r ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
28 / 54
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 s t m t 1 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 s t m t 1 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
Datenbankanwendung, WS 14/15
29 / 54
Embedded SQL
Embedded SQL in C/C++
Prepared-Statements und Cursor
Hier wird ein Prepared-Statement erzeugt und dann ein Cursor darüber
definiert:
EXEC SQL PREPARE s t m t 1 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 s t m t 1 ;
// 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
aussteigen
EXEC SQL WHENEVER NOT FOUND DO BREAK;
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 ;
...
}
EXEC SQL CLOSE f o o b a r ;
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
30 / 54
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
Datenbankanwendung, WS 14/15
31 / 54
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
Datenbankanwendung, WS 14/15
32 / 54
Embedded SQL
Embedded SQL in C/C++
Andere 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
Datenbankanwendung, WS 14/15
33 / 54
Stored Procedures/UDFs
Stored Procedures / User-Defined Functions
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
34 / 54
Stored Procedures/UDFs
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 Performanz
ˆ Code kann wiederverwendet werden (zwischen Anwendungen)
Nachteile
ˆ Etwas aufwendiger zu erstellen.
ˆ Debugging schwieriger.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
35 / 54
Stored Procedures/UDFs
SQL vs. SQL/PSM vs. PL/SQL bzw. PL/pgSQL
SQL
ˆ Standard Query Language für Datenbanksysteme.
ˆ SQL Anweisungen können via Anwendungsprogrammierung (JDBC
oder Embedded SQL) an DB geschickt werden.
PSM
ˆ Persistent, Stored Modules (PSM)
ˆ 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
ˆ PL/SQL bzw. PL/pgSQL erlauben diese Anwendungslogik als
Prozedur innerhalb des DBMS zu definieren.
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
36 / 54
Stored Procedures/UDFs
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
ˆ 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
Datenbankanwendung, WS 14/15
37 / 54
Stored Procedures/UDFs
Beispiel
CREATE FUNCTION g e t S t u d e n t ( 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 s t u d e n t e n
WHERE s t u d e n t e n . 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 getstudent(26120);
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
38 / 54
Stored Procedures/UDFs
Unterschied Stored Procedures und UDFs
Generel
ˆ 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
Datenbankanwendung, WS 14/15
39 / 54
Stored Procedures/UDFs
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
Datenbankanwendung, WS 14/15
40 / 54
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
Datenbankanwendung, WS 14/15
41 / 54
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
Datenbankanwendung, WS 14/15
42 / 54
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
Datenbankanwendung, WS 14/15
43 / 54
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
Datenbankanwendung, WS 14/15
44 / 54
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
Datenbankanwendung, WS 14/15
45 / 54
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
Datenbankanwendung, WS 14/15
46 / 54
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
Datenbankanwendung, WS 14/15
47 / 54
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
Datenbankanwendung, WS 14/15
48 / 54
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
Datenbankanwendung, WS 14/15
49 / 54
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
Datenbankanwendung, WS 14/15
50 / 54
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
Datenbankanwendung, WS 14/15
51 / 54
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 Eregebnis zu berücksitigen:
http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html
Prof. Dr.-Ing. S. Michel
TU Kaiserslautern
Datenbankanwendung, WS 14/15
52 / 54
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
Datenbankanwendung, WS 14/15
53 / 54
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
Datenbankanwendung, WS 14/15
54 / 54