1. Gesicherte Prozeduren

Intelligentes Datenmanagement II
Peter Becker
FH Bonn-Rhein-Sieg
Fachbereich Informatik
[email protected]
Vorlesung Wintersemester 2003/04
1. Gesicherte Prozeduren
Überblick: Gesicherte Prozeduren
1. Gesicherte Prozeduren
Vorteile von gesicherten Prozeduren:
• Effizienz
• Ausführung der Prozeduren unter Kontrolle des DBMS
• Strukturierungsmittel für größere Anwendungen
• einfache Wiederverwendbarkeit
• Rechtevergabe für Prozeduren
• Aktive Aspekte: gesicherte Prozeduren im Aktionsteil von Triggern
Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04
1
1. Gesicherte Prozeduren
Überblick: Gesicherte Prozeduren
DB−Host
Client
Anw.−Programm
DB−Server
Datenbank
DB−Transaktion
Anw.−Programm
DB−Server
Datenbank
DB−Transaktion
Transaktions−
aufruf
Client
Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04
1. Gesicherte Prozeduren
DB−Host
2
Überblick: Gesicherte Prozeduren
Aspekte von gesicherten Prozeduren:
• Implementierung (Server)
• Deklaration (Server)
• Nutzung (Client)
Bemerkungen:
• Gesicherte Prozeduren können typischerweise in jeder von einem
DBMS unterstützten Wirtssprache geschrieben werden.
• DB2 unterstützt gesicherte Prozeduren in SQL, sowie in C und Java.
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.
• Die Prozedur wird übersetzt und auf dem Server installiert.
• Im Unterschied zu benutzerdefinierten Funktionen darf eine gesicherte Prozedur (eingebettete) SQL-Anweisungen enthalten.
• 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
Client-Seite bei gesicherten Prozeduren
1. Deklarieren, Alloziieren und Initialisieren von Datenstrukturen und
Host-Variablen
2. CONNECT mit einer Datenbank
3. Aufruf der gesicherten Prozeduren durch SQL CALL
4. DISCONNECT
Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04
5
1. Gesicherte Prozeduren
Client-Seite bei gesicherten Prozeduren
Gesicherte Prozeduren und Transaktionen:
• Prinzipiell können gesicherte Prozeduren COMMIT und ROLLBACK
ausführen.
☞ Besser: Transaktionssteuerung durch die Applikation
☞ Kein COMMIT und ROLLBACK in 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:
1. Sie nimmt die Parameter der Client-Applikation entgegen,
2. führt die in ihr enthaltenen Operationen in der gleichen Transaktion
wie die Client-Applikation aus,
3. führt optional COMMIT oder ROLLBACK Operationen durch und
4. gibt einen Resultats- und Statuswert über die SQLCA an die ClientApplikation zurück.
Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04
7
1. Gesicherte Prozeduren
Server-Seite bei gesicherten Prozeduren
Server-Seite bei gesicherten Prozeduren
Eine Deklaration für eine gesicherte Prozedur enthält:
• Name der Prozedur
• Modus, Name und SQL-Datentyp für jeden Parameter
• Externer Name, Pfadangabe
• 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
Modus der Parameter in gesicherten Prozeduren:
IN Eingabeparameter
OUT Ausgabeparameter
INOUT Ein- und Ausgabeparameter
External:
• Die EXTERNAL-Klausel gibt an, wo sich die dynamische Bibliothek befindet, die die gesicherte Prozedur enthält.
• Falls ein relativer Pfadname verwendet wird, wird ausgehend vom
Verzeichnis
Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04
9
1. Gesicherte Prozeduren
Server-Seite bei gesicherten Prozeduren
sqllib/function
der DB2-Instanz gesucht.
• Für in C implementierte gesicherte Prozeduren ist anzugeben:
– Bibliothek oder Pfadname
– Einstiegspunkt durch Angabe eines Funktionsnamens
Für Java
[<jar-file-name>:]<class-name>,<method-name>
Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04
1. Gesicherte Prozeduren
10
Server-Seite bei gesicherten Prozeduren
Parameterübergabe (C im Modus DB2SQL):
• Analog zu UDFs muß für jeden SQL-Parameter der Deklaration ein
C-Parameter definiert werden.
• Zusätzlich muß die C-Funktion die folgenden Parameter aufweisen:
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 */
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. Hostvariablen und SQL-Anfrage in SQLJ:
String empid;
String name;
#sql { SELECT lastname INTO :name
FROM employee WHERE empno=:empid }
2. Deklaration der gesicherten Prozedur
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
3. Nutzung in C:
EXEC SQL BEGIN DECLARE SECTION;
char name[16];
char empid[7];
EXEC SQL END DECLARE SECTION;
EXEC SQL get_lastname( :empid, :name );
Intelligentes Datenmanagement II — FH Bonn-Rhein-Sieg, WS 03/04
13
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”):
NO SQL Keine Ausführung von SQL-Anweisungen in der gesicherten
Prozedur.
CONTAINS SQL SQL-Anweisungen dürfen keine Daten lesen oder
verändern.
READS SQL DATA SQL-Anweisungen dürfen keine Daten verändern.
MODIFIES SQL DATA keine Einschränkung
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.
• maximale Schachtelungstiefe: 16
• kein NOT FENCED
Generelle Einschränkungen bei gesicherten Prozeduren:
• keine Benutzung der Standard Ein- bzw. Ausgabe
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