vierseitig

2. Gesicherte Prozeduren
Allgemeine Aspekte gesicherter Prozeduren
2. Gesicherte Prozeduren
Vorteile gesicherter Prozeduren
2. Gesicherte Prozeduren
• Wenn eine Anwendung auf einer Client-Maschine läuft, wird normalerweise
– jede SQL-Anweisung einzeln vom Client an den Server gesandt, und
– jedes Ergebnistupel wird einzeln zurückgeliefert.
• Oft hat man jedoch Operationen innerhalb einer Anwendung, die eine hohe Last
auf der Datenbank erzeugen und eine geschlossene Einheit ohne Benutzerinteraktion darstellen.
• Für solche Operationen ist es sinnvoll, sie auf der Server-Maschine als sogenannte
gesicherte Prozedur (stored procedure) abzulegen und sie durch eine einzelne
Nachricht vom Client aus aufzurufen.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
2. Gesicherte Prozeduren
48
Allgemeine Aspekte gesicherter Prozeduren
DB−Host
Client
Anw.−Programm
Allgemeine Aspekte gesicherter Prozeduren
•
•
•
•
Effizienz
Ausführung der Prozeduren unter Kontrolle des DBMS
Strukturierungsmittel für größere Anwendungen
zentrale Kontrollen der Prozeduren, redundanzfreie Darstellung relevanter Aspekte
der Anwendungsfunktionalität
☞ einfache Wiederverwendbarkeit
• Rechtevergabe für Prozeduren
• Aktive Aspekte: gesicherte Prozeduren im Aktionsteil von Triggern
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
2. Gesicherte Prozeduren
50
Allgemeine Aspekte gesicherter Prozeduren
Teile und Aspekte gesicherter Prozeduren
DB−Server
Datenbank
DB−Transaktion
• Eine vollständige Anwendung mit einer gesicherten Prozedur hat stets zwei Teile:
– Die Prozedur selbst, die auf der Servermaschine läuft, und
– das eigentliche Anwendungsprogramm, das auf der Client-Maschine l äuft.
• Anwendungen mit gesicherten Prozeduren haben die Restriktion, daß:
– sämtliche Eingabedaten zum Zeitpunkt des Aufrufs vom Anwendungsprogramm
an die gesicherte Prozedur übergeben werden müssen, und daß
– Ergebnisdaten erst dann an das Anwendungsprogramm zurückgegeben werden, wenn die Prozedur vollständig abgearbeitet wurde.
Anwendung ohne gesicherte Prozedur:
Anw.−Programm
DB−Server
Datenbank
Anwendung mit gesicherter Prozedur:
DB−Transaktion
Transaktions−
aufruf
Client
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
DB−Host
49
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
51
2. Gesicherte Prozeduren
Allgemeine Aspekte gesicherter Prozeduren
• Während der Prozedurausführung ist keine Interaktion zwischen Anwendungsprogramm und gesicherter Prozedur möglich.
• Man kann gesicherte Prozeduren typischerweise in jeder vom DB-System unterstützten Wirtssprache schreiben.
• Häufig kommen hierfür prozedurale SQL-Erweiterungen zum Einsatz, beispielsweise PL/SQL (Oracle).
• Aspekte von gesicherten Prozeduren:
– Implementierung (Server)
– Deklaration (Server)
– Nutzung (Client)
2. Gesicherte Prozeduren
Client-Seite gesicherter Prozeduren
Client-Seite gesicherter 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
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.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
2. Gesicherte Prozeduren
52
Allgemeine Aspekte gesicherter Prozeduren
Bemerkungen:
2. Gesicherte Prozeduren
54
Client-Seite gesicherter Prozeduren
Die gesicherte Prozedur führt folgendes aus:
• 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.
• 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.
• Eine gesicherte Prozedur darf eingebettete SQL-Anweisungen enthalten.
• Nutzung der gesicherten Prozedur über die SQL CALL-Anweisung.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
53
1. Sie nimmt die Parameter der Client-Applikation entgegen,
2. führt die in ihr enthaltenen Operationen in der gleichen Transaktion wie die ClientApplikation aus,
3. führt optional COMMIT oder ROLLBACK Operationen durch und
4. gibt einen Resultats- und Statuswert über die SQLCA an die Client-Applikation
zurück.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
55
2. Gesicherte Prozeduren
Client-Seite gesicherter Prozeduren
2. Gesicherte Prozeduren
Nutzung von gesicherten Prozeduren (DB2)
Server-Seite bei gesicherten Prozeduren
Server-Seite bei gesicherten Prozeduren
Eine Deklaration für eine gesicherte Prozedur enthält:
• Eine gesicherte Prozedur wird von einem Anwendungsprogramm aufgerufen mit
exec sql call prozedurname ( host-variable, ...
)
• CALL-Anweisungen sind nur als statisches SQL möglich.
• Der Prozedurname kann als Konstante oder über eine Wirtsvariable angegeben
werden.
• Konstanten als Parameter sind nicht erlaubt.
• Die Parameter müssen Indikator-Variablen aufweisen.
• Parameter können sein:
– Eingabeparameter
– Ausgabeparameter
– Sowohl Eingabe- als auch Ausgabeparameter
Dies wird bei der Deklaration der gesicherten Prozedur definiert.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
2. Gesicherte Prozeduren
56
Client-Seite gesicherter Prozeduren
• Die SQLCA zeigt Erfolg oder Fehler bei der Prozedurausführung an.
• Detaillierte Angaben zu Implementierung und Deklaration von gesicherten Prozeduren
☞ siehe nächstes Kapitel
•
•
•
•
•
•
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)
Modus der Parameter in gesicherten Prozeduren:
IN Eingabeparameter
OUT Ausgabeparameter
INOUT Ein- und Ausgabeparameter
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
2. Gesicherte Prozeduren
58
Server-Seite bei gesicherten Prozeduren
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
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>
Parameterübergabe (C im Modus DB2SQL):
• Analog zu UDFs muß für jeden SQL-Parameter der Deklaration ein C-Parameter
definiert werden.
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
57
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
59
2. Gesicherte Prozeduren
Server-Seite bei gesicherten Prozeduren
2. Gesicherte Prozeduren
• 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];
/*
/*
/*
/*
/*
Server-Seite bei gesicherten Prozeduren
SQL Data Access Level
Indikatorvariablen */
SQLSTATE */
qualifizierte Name der ges. Prozedur */
spezifischer Name der ges. Prozedur */
SQL diagnostic string */
Zu Konsistenzzwecken kann in der Deklaration einer gesicherten Prozedur die Benutzung von SQL eingeschränkt werden (“SQL data access level”):
Beispiel 2.1. Implementierung (in Java), Deklaration und Applikation (in C):
NO SQL Keine Ausführung von SQL-Anweisungen in der gesicherten Prozedur.
CONTAINS SQL SQL-Anweisungen dürfen keine Daten lesen oder ver ändern.
1. Hostvariablen und SQL-Anfrage in SQLJ:
MODIFIES SQL DATA keine Einschränkung
READS SQL DATA SQL-Anweisungen dürfen keine Daten verändern.
String empid;
String name;
#sql { SELECT lastname INTO :name
FROM employee WHERE empno=:empid }
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
2. Gesicherte Prozeduren
60
Server-Seite bei gesicherten Prozeduren
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
2. Gesicherte Prozeduren
2. Deklaration der gesicherten Prozedur
Server-Seite bei gesicherten Prozeduren
Schachtelung und Einschränkungen
CREATE PROCEDURE get_lastname( IN empid CHAR(6),
OUT name VARCHAR(15))
EXTERNAL NAME ’myJar:StoredProcedure.getname’
LANGUAGE JAVA
PARAMETER STYLE JAVA
FENCED
READS SQL DATA
Eine Schachtelung von gesicherten Prozeduren ist mit Einschr änkungen erlaubt:
•
•
•
•
3. Nutzung in C:
EXEC SQL BEGIN DECLARE SECTION;
char name[16];
char empid[7];
EXEC SQL END DECLARE SECTION;
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
• keine SQL-Anweisungen, die im Zusammenhang mit Datenbankverbindungen stehen
• keine Kommandos, die den aktuellen Prozeß terminieren
EXEC SQL get_lastname( :empid, :name );
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
62
61
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
63
2. Gesicherte Prozeduren
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
Beispiel 2.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
Datenbanksysteme: Weiterf ¨uhrende Konzepte — FH Bonn-Rhein-Sieg, SS 07
64