pdf (1/1)
Werbung
Datenbankanwendung Wintersemester 2014/15 Prof. Dr.-Ing. Sebastian Michel TU Kaiserslautern [email protected] 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
