SQL bei Oracle)

Datenbanken Anwendungsentwicklung in Datenbanken: Unter Anwendungsentwicklung in Datenbanken versteht man die Entwicklung von Anwendungen, die über d...
13 downloads 3 Views 417KB Size
Datenbanken Anwendungsentwicklung in Datenbanken:

Unter Anwendungsentwicklung in Datenbanken versteht man die Entwicklung von Anwendungen, die über die Adhoc-Abfrage mit SQL hinausgeht. Es gibt verschiedene Möglichkeiten, über die SQL-Möglichkeiten hinaus zu gehen: •

Prozedurale Spracherweiterung von SQL (z.B. PL/SQL bei Oracle)



Vorhalten von Aufrufschnittstellen zwischen Programmiersprache und Datenbank (z.B. ODBC, JDBC)



Konzepte der Einbettung von SQL in Programmiersprachen (Embedded SQL, z.B. C++)



Ereignisgesteuerte Programmausführungen (Trigger)

Bei allen Ideen der Kombination von Programmiersprachen einerseits und den Konzepten der relationalen Datenbanken andererseits zeigt sich, dass es einen sogenannten Impedance Mismatch gibt, eine Unverträglichkeit zwischen den beiden Welten, die mit den o.a. Ideen immer nur angenähert gelöst wird.

1

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL

2



Große relationale Datenbankmanagement-System bieten eine eigene, proprietäre Erweiterung des Sprachumfangs über SQL hinaus an, die prozedurale Möglichkeiten einführt und auf das Konzept der Relation anwenden lässt.



Im SQL-99 ist hierzu ein Standard definiert.



Diese Sprachen bieten auch die Möglichkeit, Prozeduren oder Funktionen zu definieren, die parametrisiert mehrfach verwendet werden können.



Auch die Definition von Triggern (aktive, ereignisgesteuerte Programmteile) ist möglich.



Wir lernen im Folgenden die Erweiterung von Oracle (PL/SQL) kennen. PL steht für Procedural Language.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL

3



Im Wesentlichen wird die Sprache um die prozeduralen Konzepte Sequenz, Schleife, Alternative erweitert.



Hierzu werden die Tupel der Relationen sequenzialisiert abgearbeitet und nicht mengenweise, wie bei SQL-DML-Abfragen.



Um dies zu gewährleisten, gibt es den sogenannten Cursor, nicht zu verwechseln mit dem blinkenden Cursor auf dem Bildschirm.



Sowohl Anwendungsprogramm als auch die SQL-Befehle werden im selben DBMS gespeichert, so dass ein Performance-Gewinn zu anderen Ansätzen gegeben ist.



Mit Stored Procedures können ganze Abfolgen als eine Transaktion durchgeführt werden, so dass sichergestellt ist, dass bestimmte Abfolgen (Transaktionen) komplett durchgeführt werden.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle PL/SQL

Bei Oracle PL/SQL ist die einfachste Form der anonyme Block, in dem man eine Abfolge spezifizieren und anschließend ablaufen lassen kann. Die grundlegende Syntax eines anonymen Blockes ist: DECLARE BEGIN EXCEPTION END; /

4

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Beispiel: Gegeben sei die folgende Tabelle „Markt“ Verkaufsmarkt Das anonyme Block:

Marktstandort

Rheinischer Tonmarkt

Mainz

Internat. Tonmarkt

-

DECLARE CURSOR m IS SELECT Verkaufsmarkt FROM Markt; VM Markt.Verkaufsmarkt%type; -- VM ist vom gleichen Typ wie Markt.Verkaufsmarkt BEGIN OPEN m; -- Cursor öffnen FETCH m INTO VM; -- Cursor in Variable VM einlesen dbms_output.put_line('Markt'); -- Ausgabe dbms_output.put_line('-----'); WHILE m%found –- WHILE solange noch Tupel existieren LOOP dbms_output.put_line(VM); FETCH m INTO VM; END LOOP; CLOSE m; -- Cursor schliessen END; /

liefert: Markt ----Rheinischer Tonmarkt Internat. Tonmarkt

5

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Erläuterungen:

6



CURSOR deklariert einen Cursor in Form eines SELECT-Statements.



Der Cursor kann explizit FOR UPDATE deklariert werden, dann können Datenbank-Attribute geändert werden.



%TYPE bei der Deklaration ermöglicht die typgleiche Definition von Variablen.



OPEN öffnet einen Cursor und setzt ihn auf das erste durch das SELECTStatement definierte Tupel. CLOSE schließt den Cursor.



FETCH liest ein Tupel in die (lokalen) Variablen, die folgen.



DBMS_OUTPUT.PUT_LINE produziert Ausgaben auf den Bildschirm.



%FOUND liefert TRUE, falls der Cursor noch auf einen Datensatz zeigt.



WHILE … LOOP … END LOOP:

WHILE-Schleife



FOR … LOOP … END LOOP:

FOR-Schleife



IF … THEN … [ELSE …] END IF:

Verzweigung



-- bedeutet Kommentar für den Rest der Zeile



/* … */ bedeutet, dass … Kommentar ist.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Beispiel: CURSOR FOR UPDATE Die fehlenden Standorte erhalten ´Darmstadt´ als Standort. DECLARE CURSOR m IS SELECT Marktstandort FROM Markt FOR UPDATE OF Marktstandort; MS Markt.Marktstandort%type; BEGIN Nur mit diesem Zusatz sind Änderungen OPEN m; FETCH m INTO MS; innerhalb des Programms möglich. WHILE m%FOUND LOOP IF MS IS NULL THEN UPDATE Markt SET Marktstandort = 'Darmstadt' WHERE CURRENT OF m; END IF; FETCH m INTO MS; END LOOP; CLOSE m; END; Tupel, auf das der Cursor zeigt /

7

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Beispiel: Komplexer Cursor Im Programm sollen die Produkte, die ein ´Service´ sind, aus dem Angebot genommen werden, wenn sie auf einem Markt angeboten werden, dessen Standort nicht bekannt ist. Markt: Produkt:

8

ProdNr

Produktart

Funktion

Verkaufsmarkt

Marktstandort

11022

Tee-Service

Gebrauch

Rheinischer Tonmarkt

Mainz

10622

Kaffee-Service

Gebrauch

Internat. Tonmarkt

-

20131

Schale

Deko

40030

Krug

Deko

Stephan Karczewski - Datenbanken

Angebot: ProdNr

Verkaufsmarkt

11022

Rheinischer Tonmarkt

10622

Rheinischer Tonmarkt

20131

Rheinischer Tonmarkt

11022

Internat. Tonmarkt

40030

Internat. Tonmarkt

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Beispiel: Komplexer Cursor DECLARE

CURSOR c IS SELECT Produktart, Marktstandort FROM Markt,Produkt,Angebot WHERE MARKT.VERKAUFSMARKT = ANGEBOT.VERKAUFSMARKT AND PRODUKT.PRODUKTNR = ANGEBOT.PRODUKTNR FOR UPDATE OF Angebot.ProduktNr;

PArt Produkt.Produktart%type; MSt Markt.Marktstandort%type;

Bedingungen: ´Service´und auf einem Markt ohne Standort angeboten

BEGIN OPEN c; FETCH c INTO PArt, MSt; WHILE c%FOUND LOOP IF PArt like '%Service%' AND MSt IS NULL THEN DELETE FROM ANGEBOT WHERE CURRENT OF c; END IF; FETCH c INTO PArt, MSt; END LOOP; CLOSE c; END; /

9

Stephan Karczewski - Datenbanken

Löschen des Tupels

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle PL/SQL

Neben dem anonymen Block ist es bei Oracle PL/SQL möglich, Prozeduren und Funktionen zu spezifizieren, die dann i.d.R. mit Parametern dynamisch ablaufen. Funktionen unterscheiden sich von Prozeduren insbesondere durch den Rückgabewert. Die grundlegende Syntax

der Prozedur: CREATE OR REPLACE PROCEDURE (Param1 [IN| OUT| IN OUT] Typ1, ...) IS BEGIN ohne return-Wert EXCEPTION END; /

10

Stephan Karczewski - Datenbanken

der Funktion: CREATE OR REPLACE FUNCTION (Param1 Typ1, ...) RETURN IS BEGIN mit return-Wert EXCEPTION END; / 7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle PL/SQL

Prozeduren und Funktionen:

11



CREATE OR REPLACE dient dazu, evtl. bereits vorhandene Funktionen oder Prozeduren zu überschreiben. Die so definierten Prozeduren sind im DB-System gespeichert und jederzeit aufrufbar.



Funktionen liefern einen Rückgabe-Wert. Mindestens ein RETURN muss innerhalb der Funktion enthalten sein.



Parameter in Prozeduren können als IN oder OUT oder als IN OUT spezifiziert werden. Nur mit OUT spezifizierte Parameter haben eine Auswirkung auf die Datenbank.



Funktions- und Prozeduraufrufe können in Programmen durchgeführt werden.



Funktionsaufrufe können auch direkt innerhalb SELECT-Befehlen verwendet werden.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Beispiel: Prozedur Für angebotene Produkte gibt der Benutzer den Ort und die Funktion an. Die Prozedur ermittelt daraus die Anzahl der passenden Angebote. Alternative 1: Die Bedingungen werden im Programm überprüft. create or replace PROCEDURE AngebotAnzahl2 (Ort IN varchar, Fkt IN varchar, Anzahl OUT integer) AS CURSOR c1 IS SELECT ProduktNr FROM Produkt WHERE Fkt = Funktion; CURSOR c2 IS SELECT Verkaufsmarkt FROM Markt WHERE Ort = Marktstandort; CURSOR c3 IS SELECT ProduktNr, Verkaufsmarkt FROM Angebot; PPNr Produkt.ProduktNr%type; MVM Markt.Verkaufsmarkt%type; APNr Angebot.ProduktNr%type; AVM Angebot.Verkaufsmarkt%type; i INTEGER; BEGIN i := 0; OPEN c1; FETCH c1 INTO PPNr; WHILE c1%FOUND LOOP OPEN c3; FETCH c3 INTO APNr, AVM; WHILE C3%FOUND

12

Stephan Karczewski - Datenbanken

LOOP IF PPNr = APNr THEN OPEN c2; FETCH c2 INTO MVM; WHILE c2%FOUND LOOP IF MVM = AVM THEN i := i + 1; exit; END IF; FETCH c2 INTO MVM; END LOOP; CLOSE c2; END IF; FETCH c3 INTO APNr, AVM; END LOOP; CLOSE c3; FETCH c1 INTO PPNr; END LOOP; CLOSE c1; Anzahl := i; END; /

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Beispiel: Prozedur Für angebotene Produkte gibt der Benutzer den Ort und die Funktion an. Die Prozedur ermittelt daraus die Anzahl der passenden Angebote. Alternative 2: Die Bedingungen werden im Cursor überprüft. CREATE OR REPLACE PROCEDURE AngebotAnzahl (Ort IN varchar, Fkt IN varchar, Anzahl OUT integer) AS CURSOR c IS SELECT COUNT(*) FROM Markt, Produkt, Angebot WHERE MARKT.VERKAUFSMARKT = ANGEBOT.VERKAUFSMARKT AND PRODUKT.PRODUKTNR = ANGEBOT.PRODUKTNR AND Ort = Marktstandort AND Fkt = Funktion; BEGIN OPEN c; FETCH c INTO Anzahl; CLOSE c; END;

13

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Durch die Kombinationsmöglichkeit von CURSOR mit der prozeduralen Programmierung ist stets zu überprüfen, welche Teile bereits mit dem CURSOR abgearbeitet werden können und welche im Programmcode zu implementieren sind. Folgender anonyme Block ruft die Prozedur (beide Alternativen sind möglich) auf: declare k integer; begin dbms_output.put_line ('Anzahl der Angebote von Dekoprodukten'); AngebotAnzahl ('Mainz', 'Deko', k); dbms_output.put_line (k); dbms_output.put_line ('Anzahl der Angebote von Gebrauchsprodukten'); AngebotAnzahl ('Mainz', 'Gebrauch', k); dbms_output.put_line (k); end; /

14

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Beispiel: Funktion Für angebotene Produkte gibt der Benutzer den Ort und die Funktion an. Die Prozedur ermittelt daraus die Anzahl der passenden Angebote. Das als Prozedur bekannte Beispiel wird nun als Funktion spezifiziert. Der Ausgabeparameter ist nun das Ergebnis der Funktion selbst. CREATE OR REPLACE FUNCTION AngebotAnzahl (Ort varchar, Fkt varchar) RETURN INTEGER AS CURSOR c IS SELECT COUNT(*) FROM Markt,Produkt,Angebot Rückgabewert WHERE MARKT.VERKAUFSMARKT = ANGEBOT.VERKAUFSMARKT AND PRODUKT.PRODUKTNR = ANGEBOT.PRODUKTNR AND Ort = Marktstandort AND Fkt = Funktion; Anzahl integer; BEGIN OPEN c; FETCH c INTO Anzahl; CLOSE c; RETURN Anzahl; END; / Rückgabewert

15

Stephan Karczewski - Datenbanken

Folgender anonyme Block ruft die Funktion auf: declare begin dbms_output.put_line ('Anzahl der Angebote von Dekoprodukten: ‘ ||AngebotAnzahlFKT ('Mainz', 'Deko')); dbms_output.put_line ('Anzahl der Angebote von Gebrauchsprodukten: ‘ ||AngebotAnzahlFKT ('Mainz', 'Gebrauch')); end; /

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Als Alternative zu dem anonymen Block ist es bei Funktionen möglich eine Funktion direkt in einem SELECT-Statement aufzurufen. Dies dient i.d.R. aber nur zum schnellen Testen der Funktion. Hierzu ist eine spezielle Tabelle DUAL vordefiniert, die als DUMMY dem Benutzer zur Verfügung steht. Das folgende SQL-Statement: select AngebotAnzahlFKT ('Mainz', 'Gebrauch') AS Mainzer_Gebrauchtangebote FROM DUAL; liefert

Mainzer_Gebrauchtangebote 2

16

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Benutzereingaben sind möglich. Die Variablen müssen entsprechend deklariert werden, wie das folgende Beispiel zeigt: declare o varchar(20) := &OrtsEingabe; f varchar(20) := &FunktionsEingabe; begin dbms_output.put_line ('Anzahl der Angebote von '||f||'produkten in '||o||': '||AngebotAnzahlFKT (o, f)); end; /

Im Ergebnis liefert der Programmaufruf bei Ortseingabe von ´Mainz´ und Funktionseingabe von ´Deko´:

Anzahl der Angebote von Dekoprodukten in Mainz: 1

17

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Prozedurale Spracherweiterungen von SQL: hier Oracle Bewertung von Funktionen und Prozeduren:

18



Funktionen und Prozeduren dienen der besseren Strukturierung in großen Programmen.



Gleiche Programmabläufe werden auf diese Weise nur einmal deklariert.



Die Prozeduren, Funktionen und Programme werden im DBMS selbst gespeichert.



Sowohl Programme, Prozeduren und Funktionen als auch die eingebetteten SQLBefehle werden vom gleichen DBMS ausgeführt (hohe Performance).



Stored Procedures können auch zur Integritätssicherung genutzt werden.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Java DataBase Connectivity (JDBC)

19



JDBC wurde 1996 durch die Firma SUN spezifiziert.



JDBC ist ein Standard im Bereich der Schnittstelle von Java zu relationalen Datenbanken.



Es stellt ein Application Programming Interface (API) bereit, um JAVA mit SQL zu verbinden.



Durch JDBC wird dynamisches SQL unterstützt, d.h. die genutzten SQL-Befehle können zur Laufzeit (und nicht bereits zur Übersetzungszeit) generiert werden mit dem Vorteil der möglichen Dynamik und dem Nachteil der Nicht-Überprüfbarkeit vor dem Programm-Ablauf.



Wesentlich zur Verwendung der entsprechenden Interface-Befehle ist die Klassenbibliothek java.sql.



Vier Klassen in dieser Bibliothek werden grundsätzlich in jedem JAVA-Programm verwendet, um die Datnbank-Anwendungen zu implementieren: 

java.sql.DriverManager



java.sql.Connection



java.sql.statement



java.sql.ResultSet

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Java DataBase Connectivity (JDBC) Beispiel (Abfrage mit Schleife):

Import der Klassenbibliothek

package jdbc_beispiele; import java.sql.*; public class JDBC_Beispiele { Laden der Oracle-Treiber public static void main(String[] args) throws SQLException { Herstellen der Verbindung DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); zur Datenbank String url = "jdbc:oracle:thin:@localhost:1521:XE"; (url, User, Pwd) Connection con = DriverManager.getConnection(url, "hr", "hr"); Statement stmt = con.createStatement(); ResultSet rset = stmt.executeQuery("SELECT ProduktNr, Produktart, Funktion FROM PRODUKT"); while (rset.next()) { int i = rset.getInt("ProduktNr"); String s1 = rset.getString("Produktart"); String s2 = rset.getString("Funktion"); System.out.println (i + " " + s1 + " " + s2); } con.close(); } } 20

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Java DataBase Connectivity (JDBC) Beispiel (Abfrage mit Schleife, Fortsetzung):

Weiterschalten des Cursor und Abfrage auf Existenz eines Tupel (vgl. FETCH / FOUND bei PL/SQL)

Statement stmt = con.createStatement(); ResultSet rset = stmt.executeQuery("SELECT ProduktNr, Produktart, Funktion FROM PRODUKT"); while (rset.next()) { Auslesen int i = rset.getInt("ProduktNr"); der String s1 = rset.getString("Produktart"); Spalten String s2 = rset.getString("Funktion"); System.out.println (i + " " + s1 + " " + s2); } con.close(); Bildschirmausgabe } }

Anfrage

21

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Java DataBase Connectivity (JDBC) Beispiel (UPDATE): package jdbc_beispiele; import java.sql.*; public class JDBC_Änderung_mit_Cursor { public static void main(String[] args) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); String url = "jdbc:oracle:thin:@localhost:1521:XE"; Connection con = DriverManager.getConnection(url, "hr", "hr"); String updateQuery = "UPDATE Markt SET Marktstandort = 'Darmstadt' WHERE Marktstandort IS NULL"; PreparedStatement ustmt = con.prepareStatement (updateQuery); int num = ustmt.executeUpdate (updateQuery); con.close(); } }

22

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Java DataBase Connectivity (JDBC) Beispiel (INSERT):

package jdbc_beispiele; import java.sql.*; public class JDBC_Einfügen_mit_Cursor { public static void main(String[] args) throws SQLException { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); String url = "jdbc:oracle:thin:@localhost:1521:XE"; Connection con = DriverManager.getConnection(url, "hr", "hr"); String insertQuery = "INSERT INTO Markt values ('Neumarkt', 'Wiesbaden')"; PreparedStatement istmt = con.prepareStatement (insertQuery); int num = istmt.executeUpdate (insertQuery); con.close(); } }

23

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Java DataBase Connectivity (JDBC) •

JDBC ermöglicht im Unterschied zu dem proprietären PL/SQL, in einer StandardProgrammiersprache auf professionelle Datenbanksysteme zuzugreifen.



JAVA-Programmierer können mit objektorientierten Konzepten auf die relationale Datenbank zugreifen.



Neben dem hier gezeigten Vorgehen gibt es mit JDBC die Möglichkeit



24



Stored Procedures aufzurufen (vgl. PL/SQL)



Verteilte Transaktionen zu spezifizieren



weitere Datentypen zu nutzen.

Grundsätzlich sind die PL/SQL-Programme und die Programme mit JDBC miteinander vergleichbar (Cursor-Definition, Schleifen, If-Then-Else, …). Bei JDBC kommt die Notwendigkeit hinzu, die Verbindung zu dem Datenbankserver aufzubauen, da das Programmsystem nicht innerhalb des Datenbank-Systems liegt wie bei PL/SQL.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken SQLJ

25



SQLJ bietet die Möglichkeit, SQL-Anweisungen direkt in JAVA-Code einzubetten.



Die SQL-Anweisungen innerhalb des JAVA-Codes sind statisch, können also nicht mehr zur Laufzeit verändert werden.



Dies liefert allerdings den Vorteil, dass die SQL-Anweisungen zur Übersetzungszeit syntaktisch und semantisch überprüft werden können.



SQLJ-Anweisungen werden mittels Pre-Compiler in JAVA-Code übersetzt. Anschließend kann das so komplettierte JAVA-Programm kompiliert werden.



Es gibt weitere Standard-Definitionen innerhalb von SQLJ, in denen JAVA-Klassen für User Defined Types spezifiziert werden bzw. es wird beschrieben wie JAVA-Klassen in SQL-Statements angesprochen werden können.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken SQLJ Ablauf der Abarbeitung

26



Start mit einem JAVA-Quellcode, der statisch eingebettet SQL-Befehle enthält.



Der SQLJ-Übersetzer erstellt daraus JAVA-Quell-Code mit JDBC-_Aufrufen.



Dieser kann – wie zuvor gesehen – mit einem JAVA-Compiler übersetzt werden.



Ergebnis: JVA-Bytecode mit JDBC-Aufrufen.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken SQLJ Codierung



SQL-Befehle im JAVA-Code werden mit #sql eingeleitet.



Ein Cursor wird durch ein sogenanntes Iterator-Objekt spezifiziert.



Der Iterator muss speziell deklariert werden und zu dem Format (Anzahl und Typen der Spalten) des Cursors passen. Definition des Iterators Beispiel:



#sql ITERATOR PrdItr (int ProdNr, String ProdArt); #sql PrdItr = {select ProdNr, Produktart from Toepferprodukt}; while (PrdItr.next ()) { System.out.println (PrdItr.ProdNr() + " " + PrdItr.Produktart()); } PrdItr.close();

27

Stephan Karczewski - Datenbanken

Zuweisung zu dem entsprechenden SQL-Befehl Abarbeitung des Cursors (vgl. FETCH / FOUND)

7. Anwendungsentwicklung in Datenbanken

Datenbanken JDBC und SQLJ im Vergleich

28



Die Programme können bei SQLJ bereits zur Übersetzungszeit syntaktisch überprüft werden.



Eine Vorübersetzung bei SQLJ bedeutet auch einen Performance-Gewinn gegenüber JDBC.



SQLJ ist statisch, d.h. die SQL-Befehle müssen bereits zur Übersetzungszeit feststehen.



Bei JDBC sind die SQL-Befehle dynamisch. Sie können also zur Laufzeit angepasst werden.



Die Syntax ist bei SQLJ insofern einfacher, da sie sehr stark an JAVA angepasst ist.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Trigger •

Trigger sind ereignisgesteuerte Prozeduren, d.h. sie werden nicht durch konkrete Aufrufe wie Prozeduren aktiviert, sondern durch Ereignisse, aufgrund derer sie reagieren.



Ein Trigger bezieht sich auf genau eine Tabelle.



Trigger werden definiert mindestens durch



29



das Ereignis, durch das sie aktiviert werden (insert, update, delete),



den Zeitpunkt (vorher oder nachher) zu dem Ereignis



eine Menge von Anweisungen (vgl. Prozedur), die ausgeführt werden.

Optional 

können Variablen verwendet werden, die die Verwendung der aktivierten Attribute ermöglichen. Durch OLD bzw. NEW können diese vor und nach dem Ereignis erfasst werden. Die Verwendung erfolgt mit einem vorgestellten ´:´



kann man mit WHEN die Ausführung des Trigger-Rumpfes an Bedingungen knüpfen.



kann festgelegt werden, ob sie ein Mal (pro Befehlsausführung) oder mehrmals (für jeden geänderten Datensatz) ausgeführt werden. Ersteres ist die Voreinstellung.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Trigger Syntax (Oracle):

CREATE [OR REPLACE] TRIGGER {BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON [REFERENCING [NEW AS ] OLD AS ]] [FOR EACH ROW [WHEN ()]]

30



Hinweis: Bei Verwendung von Views (s. Beispiel auf kommenden Folien) kann statt ´BEFORE´ bzw. ´AFTER´ auch ´INSTEAD OF´ stehen., was bedeutet, dass das eingegebene Statement nicht ausgeführt wird, sondern nur die Statements des Triggers.



´REFERENCING´ kann verwendet werden, um den Variablen NEW bzw. OLD AliasNamen zu geben.

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Trigger Beispiel:

Ein Produkt soll in der entsprechenden Tabelle gelöscht werden. Da es Angebote zu den Produkten gibt, müssen diese zuvor gelöscht werden, da sonst die Konsistenz in der Datenbank (hier: referentielle Integrität) verletzt wird. Dies erledigt der folgende Trigger CREATE OR REPLACE TRIGGER DeleteTrigger

BEFORE DELETE ON Produkt FOR EACH ROW BEGIN DELETE FROM Angebot WHERE ProduktNr = :OLD.ProduktNr; END;

31

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Trigger Beispiel:

Ein Angebot soll in der entsprechenden Tabelle eingefügt werden. Angebote benötigen jedoch jeweils einen Eintrag in den Tabellen Produkt und Markt, auf die sie referenzieren. Der folgende Trigger erledigt diese Aufgabe, wobei bei Produkt bzw. Markt jeweils nur der Primärschlüssel eingetragen werden kann. Die restlichen Attribute erhalten NULL-Werte. CREATE OR REPLACE TRIGGER InsertTrigger BEFORE INSERT ON Angebot FOR EACH ROW BEGIN INSERT INTO Produkt (ProduktNr) VALUES (:NEW.ProduktNr); INSERT INTO Markt (Verkaufsmarkt) VALUES (:NEW.Verkaufsmarkt); END;

32

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Trigger Beispiel:

Der Benutzer erhält einen View, so dass er alle Angebote mit den Märkten und Produkten gemeinsam sieht. Dieser View ist nicht eine Tabelle sondern der natürliche Verbund zwischen den drei Tabellen Produkt, Markt und Angebot: CREATE OR REPLACE VIEW PAM AS SELECT P.ProduktNR, Produktart, Funktion, M.Verkaufsmarkt, Marktstandort FROM Produkt P, Angebot A, Markt M WHERE P.ProduktNr = A.ProduktNr AND A.Verkaufsmarkt = M.Verkaufsmarkt;

33

SELECT * FROM PAM;

liefert

PRODUKTNR 20131 10622 11022 40030 11022

FUNKTION Deko Gebrauch Gebrauch Deko Gebrauch

PRODUKTART Schale Kaffee-Service Tee-Service Krug Tee-Service

Stephan Karczewski - Datenbanken

VERKAUFSMARKT Rheinischer Tonmarkt Rheinischer Tonmarkt Rheinischer Tonmarkt Internat. Tonmarkt Internat. Tonmarkt

MARKTSTANDORT Mainz Mainz Mainz -

7. Anwendungsentwicklung in Datenbanken

Datenbanken Trigger Beispiel (Fortsetzung):

Es ist nicht möglich, in diesen View, der nur virtuell existiert, eine Zeile einzufügen, zu ändern oder zu löschen, da sich der View aus drei Tabellen zusammensetzt. Folgender Trigger löst das Problem des INSERT: CREATE OR REPLACE TRIGGER EinfView INSTEAD OF INSERT ON PAM FOR EACH ROW BEGIN INSERT INTO Produkt (ProduktNr, Produktart, Funktion) VALUES (:NEW.ProduktNr, :NEW.Produktart, :NEW.Funktion); INSERT INTO Markt (Verkaufsmarkt, Marktstandort) VALUES (:NEW.Verkaufsmarkt, :NEW.Marktstandort); INSERT INTO Angebot (ProduktNr, Verkaufsmarkt) VALUES (:NEW.ProduktNr, :NEW.Verkaufsmarkt); END; INSERT INTO PAM VALUES (47112, ‘Tasse', ‘Gebrauch', ‘Lokalmarkt', ‘Wiesbaden'); bewirkt, dass die genannten Attributwerte konsistent in die drei Tabellen mit jeweils einer Zeile eingetragen werden. 34

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Trigger

Beispiel (Fortsetzung): Das Löschen auf dem View lässt sich analog lösen: CREATE OR REPLACE TRIGGER DelView INSTEAD OF DELETE ON PAM FOR EACH ROW BEGIN DELETE FROM Angebot WHERE ProduktNr = :OLD.ProduktNr AND Verkaufsmarkt = :OLD.Verkaufsmarkt; DELETE FROM Produkt WHERE ProduktNr = :OLD.ProduktNr; DELETE FROM Markt WHERE Verkaufsmarkt = :OLD.Verkaufsmarkt; END; DELETE FROM PAM WHERE ProduktNr = 47112 AND Verkaufsmarkt = 'Lokalmarkt'; bewirkt, dass die genannten Attributwerte konsistent aus den drei Tabellen gelöscht werden.

35

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken

Datenbanken Trigger Beispiel (Fortsetzung): Der folgende Trigger enthält die Funktionalität der beiden vorherigen Trigger in einem: CREATE OR REPLACE TRIGGER EinfDelView INSTEAD OF INSERT OR DELETE ON PAM FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO Produkt (ProduktNr, Produktart, Funktion) VALUES (:NEW.ProduktNr, :NEW.Produktart, :NEW.Funktion); INSERT INTO Markt (Verkaufsmarkt, Marktstandort) VALUES (:NEW.Verkaufsmarkt, :NEW.Marktstandort); INSERT INTO Angebot (ProduktNr, Verkaufsmarkt) VALUES (:NEW.ProduktNr, :NEW.Verkaufsmarkt); END IF; IF DELETING THEN DELETE FROM Angebot WHERE ProduktNr = :OLD.ProduktNr AND Verkaufsmarkt = :OLD.Verkaufsmarkt; DELETE FROM Produkt WHERE ProduktNr = :OLD.ProduktNr; DELETE FROM Markt WHERE Verkaufsmarkt = :OLD.Verkaufsmarkt; END IF; END; 36

Stephan Karczewski - Datenbanken

7. Anwendungsentwicklung in Datenbanken