Stored Procedures in Datenbanken

1. Stored Procedures in Datenbanken

2. Was sind stored procedures? • Gespeicherte Programmteile • Datenbankobjekte • Vom DB-Benutzer definiert • Werden im DB-Server-Prozess ausgeführt • Erweitern die Funktionalität des DBMS

3. Wozu sind Stored Procedures gut? • Erweiterung der Funktionalität von DBMSn • wenn die Möglichkeiten von SQL nicht genügen u. • wenn eine Verarbeitung am Server sinnvoller ist • Trigger • um die DB konsistent zu halten • um mehr semantische Information zu definieren • Methoden von Objekten • bei objekt-relationalen DBn

4. Wer bietet stored procedures? • Große DBMS bieten Stored Procedures, z. B. • IBM DB2 • Microsoft SQL-Server • Oracle • Caché • ... • Stored Procedures werden nicht unterstützt von • File-Server Datenbanken (z. B. Access) • nicht kommerziellen Systemen (z. B. MySQL)

5. Aufruf und Ablauf von Stored Procedures Client-Programm (z. B. JAVA, JDBC) ... CallableStatement cstmt =con.prepareCall ({?=CALL Reserv(?,?,?,?)}); cstmt.setInt(1, KundenID); ... cstmt.execute(); ... DB-Server Reserv(k integer, ...) begin if x > 10 then ... SELECT * FROM Tab2 ... ... INSERT INTO Reservierung ... ... end; Tab2 Tab1

6. Stored Procedures bei Oracle • Programmiersprache PL/SQL • C, C++ und JAVA sind auch möglich • PL/SQL ist typenstrenge Programmiersprache • Cursor verbinden SQL mit prozeduraler Verarbeitung • Besondere Konstrukte für Datenzugriff • for cursor loop • Datensatztyp-Deklaration • Collection-Typen

7. Stored Procedures bei Oracle: Aufbau • Deklarationsteil • Deklaration von Variablen, Cursor, Collections, ... • Ausführungsteil • Programmierung des Algorithmus • Transaktionssteuerung • Ausnahmebehandlung • Reaktionen auf Fehler werden hier zusammengefasst • Damit wird GOTO vermieden

8. Beispiel Reservierung:Deklarationsteil create or replace function Reserv (kid integer, aid integer, von date, bis date) return integer asdeclare autozahl integer; z integer; tage integer; cursor c1 is Select * from Reservierung where ModellID = aid and ((Beginn >= von and Beginn <= bis) or (Beginn <= von and Ende >= von)); var_c c1%rowtype;

9. Beispiel Reservierung: Ausführungsteil begin tage = bis - von; Select count(*) into autozahl from Auto where Modell = aid; For i in 0 .. tage loop z := autozahl; for var_c in c1 loop if von + i >= var_c.Beginn and von + i <= var_c.Ende then z := z - 1; end if; end loop; exit when z <= 0; end loop; if z <= 0 then return 0; else insert into Reservierung values (reservierungseq.nextval, kid, aid, von, bis); return 1; end if; end; /

10. Ausnahmebehandlung • Benutzerdefinierte Ausnahmendeclare ausnahme1 exception; ... begin ... raise ausnahme1 ...exception when ausnahme1 then <aktion>end; • Daten-Ausnahmen (vordefiniert), z. B. when data_not_foundwhen max_open_cursors