1. Gesicherte Prozeduren Überblick: Gesicherte Prozeduren DB−Host Client Anw.−Programm Intelligentes Datenmanagement II DB−Server Datenbank DB−Transaktion Peter Becker FH Bonn-Rhein-Sieg Fachbereich Informatik [email protected] Anw.−Programm Vorlesung Wintersemester 2003/04 DB−Server Datenbank DB−Transaktion Transaktions− aufruf Client Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 1. Gesicherte Prozeduren Überblick: Gesicherte Prozeduren 1. Gesicherte Prozeduren DB−Host 2 Überblick: Gesicherte Prozeduren Aspekte von gesicherten Prozeduren: 1. Gesicherte Prozeduren Vorteile von gesicherten Prozeduren: • Implementierung (Server) • Effizienz • Deklaration (Server) • Ausführung der Prozeduren unter Kontrolle des DBMS • Nutzung (Client) • Strukturierungsmittel für größere Anwendungen Bemerkungen: • einfache Wiederverwendbarkeit • Rechtevergabe für Prozeduren • Gesicherte Prozeduren können typischerweise in jeder von einem DBMS unterstützten Wirtssprache geschrieben werden. • Aktive Aspekte: gesicherte Prozeduren im Aktionsteil von Triggern • DB2 unterstützt gesicherte Prozeduren in SQL, sowie in C und Java. Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 1 Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 3 1. Gesicherte Prozeduren Überblick: Gesicherte Prozeduren • Eine Applikation muß nicht in der gleichen Sprache entwickelt sein wie die gesicherten Prozeduren, die von ihr genutzt werden. 1. Gesicherte Prozeduren Client-Seite bei gesicherten Prozeduren Gesicherte Prozeduren und Transaktionen: • Die Prozedur wird übersetzt und auf dem Server installiert. • Prinzipiell können gesicherte Prozeduren COMMIT und ROLLBACK ausführen. • Im Unterschied zu benutzerdefinierten Funktionen darf eine gesicherte Prozedur (eingebettete) SQL-Anweisungen enthalten. ☞ Besser: Transaktionssteuerung durch die Applikation ☞ Kein COMMIT und ROLLBACK in gesicherten Prozeduren. • Nutzung der gesicherten Prozedur über die SQL CALL-Anweisung. Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 1. Gesicherte Prozeduren 4 Client-Seite bei gesicherten Prozeduren Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 1. Gesicherte Prozeduren 6 Client-Seite bei gesicherten Prozeduren Die gesicherte Prozedur führt folgendes aus: Client-Seite bei gesicherten Prozeduren 1. Sie nimmt die Parameter der Client-Applikation entgegen, 1. Deklarieren, Alloziieren und Initialisieren von Datenstrukturen und Host-Variablen 2. führt die in ihr enthaltenen Operationen in der gleichen Transaktion wie die Client-Applikation aus, 2. CONNECT mit einer Datenbank 3. führt optional COMMIT oder ROLLBACK Operationen durch und 3. Aufruf der gesicherten Prozeduren durch SQL CALL 4. gibt einen Resultats- und Statuswert über die SQLCA an die ClientApplikation zurück. 4. DISCONNECT Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 5 Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 7 1. Gesicherte Prozeduren Server-Seite bei gesicherten Prozeduren 1. Gesicherte Prozeduren Server-Seite bei gesicherten Prozeduren sqllib/function Server-Seite bei gesicherten Prozeduren der DB2-Instanz gesucht. Eine Deklaration für eine gesicherte Prozedur enthält: • Für in C implementierte gesicherte Prozeduren ist anzugeben: • Name der Prozedur – Bibliothek oder Pfadname – Einstiegspunkt durch Angabe eines Funktionsnamens • Modus, Name und SQL-Datentyp für jeden Parameter Für Java • Externer Name, Pfadangabe [<jar-file-name>:]<class-name>,<method-name> • Implementierungssprache • Art der Parameter-Übergabe (DB2SQL oder JAVA) • abgeschirmte Ausführung oder nicht (FENCED) Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 1. Gesicherte Prozeduren 8 Server-Seite bei gesicherten Prozeduren Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 1. Gesicherte Prozeduren 10 Server-Seite bei gesicherten Prozeduren Modus der Parameter in gesicherten Prozeduren: Parameterübergabe (C im Modus DB2SQL): IN Eingabeparameter • Analog zu UDFs muß für jeden SQL-Parameter der Deklaration ein C-Parameter definiert werden. OUT Ausgabeparameter • Zusätzlich muß die C-Funktion die folgenden Parameter aufweisen: INOUT Ein- und Ausgabeparameter External: • Die EXTERNAL-Klausel gibt an, wo sich die dynamische Bibliothek befindet, die die gesicherte Prozedur enthält. sqlint16 char char char char nullinds[n]; sqlst[6]; qualname[28]; specname[19]; diagmsg[71]; /* /* /* /* /* Indikatorvariablen */ SQLSTATE */ qualifizierte Name der ges. Prozedur */ spezifischer Name der ges. Prozedur */ SQL diagnostic string */ • Falls ein relativer Pfadname verwendet wird, wird ausgehend vom Verzeichnis Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 9 Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 11 1. Gesicherte Prozeduren Server-Seite bei gesicherten Prozeduren Beispiel 1.1. Implementierung (in Java), Deklaration und Applikation (in C): 1. Gesicherte Prozeduren Server-Seite bei gesicherten Prozeduren Zu Konsistenzzwecken kann in der Deklaration einer gesicherten Prozedur die Benutzung von SQL eingeschränkt werden (“SQL data access level”): 1. Hostvariablen und SQL-Anfrage in SQLJ: NO SQL Keine Ausführung von SQL-Anweisungen in der gesicherten Prozedur. String empid; String name; CONTAINS SQL SQL-Anweisungen dürfen keine Daten lesen oder verändern. #sql { SELECT lastname INTO :name FROM employee WHERE empno=:empid } READS SQL DATA SQL-Anweisungen dürfen keine Daten verändern. 2. Deklaration der gesicherten Prozedur MODIFIES SQL DATA keine Einschränkung CREATE PROCEDURE get_lastname( IN empid CHAR(6), OUT name VARCHAR(15)) Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 1. Gesicherte Prozeduren 12 Server-Seite bei gesicherten Prozeduren EXTERNAL NAME ’myJar:StoredProcedure.getname’ LANGUAGE JAVA PARAMETER STYLE JAVA FENCED READS SQL DATA Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 1. Gesicherte Prozeduren 14 Server-Seite bei gesicherten Prozeduren Eine Schachtelung von gesicherten Prozeduren ist mit Einschränkungen erlaubt: • LANGUAGE C oder LANGUAGE SQL • Auf tieferer Ebene darf der “SQL data access level” nicht schwächer sein. 3. Nutzung in C: • maximale Schachtelungstiefe: 16 EXEC SQL BEGIN DECLARE SECTION; char name[16]; char empid[7]; EXEC SQL END DECLARE SECTION; • kein NOT FENCED Generelle Einschränkungen bei gesicherten Prozeduren: EXEC SQL get_lastname( :empid, :name ); • keine Benutzung der Standard Ein- bzw. Ausgabe Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 13 Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 15 1. Gesicherte Prozeduren Server-Seite bei gesicherten Prozeduren • keine SQL-Anweisungen, die im Zusammenhang mit Datenbankverbindungen stehen • keine Kommandos, die den aktuellen Prozeß terminieren Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 1. Gesicherte Prozeduren 16 Gesicherte SQL-Prozeduren Gesicherte SQL-Prozeduren • Statt eines Verweises auf eine extern implementierte Funktion wird in der Deklaration eine SQL-Anweisung angegeben. • Neben reinen SQL-Anweisungen stehen auch prozedurale Sprachelemente zur Verfügung, die Programmierung bis zu einem gewissen Maße ermöglichen. • Vorteil: direkte SQL-Integration in die Sprachelemente Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 17 1. Gesicherte Prozeduren Gesicherte SQL-Prozeduren Beispiel 1.2. Gesicherte SQL-Prozedur für eine Gehaltserhöhung: CREATE PROCEDURE update_salary ( IN eno CHAR(6), IN rate INTEGER ) LANGUAGE SQL BEGIN UPDATE employee SET salary = salary * (1.0 + rate / 100.0) WHERE empno = eno; END Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04 18