Datenabstraktion / Kapselung in (O)RDBMS • Prozeduren und Funktionen (Routinen) SQL‐92: 92: Stored Stored Procedures • SQL • SQL:2003: Unterscheidung zw. Funktionen und Prozeduren • Prozedur: Nicht an einen bestimmten Datentyp gebundene Routine mit Parameter, die gemäß IN OUT und INOUT spezifiziert sind (überladen möglich # Parameter unterschiedlich, Aufruf CALL) yp g • Funktion: Nicht an einen bestimmten Datentyp gebundene Routine mit IN‐ Parameter und ein RETURN‐ Datentyp (überlanden möglich mit unterschiedlichen Typen Aufruf durch Name auch in Anfragen) unterschiedlichen Typen, Aufruf durch Name, auch in Anfragen) • SQL:2003 besitzt eine vollständige Programmiersprache • Aufruf externer Routinen unterstützt 19, Prof. Jasper: Datenbanksysteme 1 Prozeduren und Funktionen in SQL:2003 • Beispiel: CREATE TABLE Konto( kontoNr kunde eroeffnung zinsSatz kontoStand INTEGER Primary Key, REF(KundenTyp) SCOPE(Kunde), DATE, DECIMAL(4,2), DECIMAL(15,2)); • Zugehörige Prozedur CREATE PROCEDURE GesKtoStand( IN kundenId INTEGER, OUT gesamtKtoStand DECIMAL(15,2)) BEGIN gesamtKtoStand := 0; SELECT SUM(k SELECT SUM(kontoStand) S d) INTO gesamtKtoStand FROM Konto WHERE k nde ‐> kundenNr WHERE kunde > k ndenNr = kundenId k ndenId GROUP BY kunde ‐> kundenNr; IF gesamtKtoStand < 0 THEN gesamtKtoStand := 0 END IF; END; 19. Prof. Jasper: Datenbanksysteme 2 Prozeduren und Funktionen in SQL:2003 • Zugehörige Funktion CREATE FUNCTION GesKtoStand(kundenId INTEGER) RETURNS DECIMAL(15,2)) BEGIN DECLARE gesamtKtoStand DECIMAL(15,2); gesamtKtoStand := 0; SELECT SUM(kontoStand) SELECT SUM(kontoStand) INTO gesamtKtoStand FROM Konto WHERE kunde ‐>> kundenNr WHERE kunde kundenNr = kundenId = kundenId GROUP BY kunde ‐> kundenNr; IF gesamtKtoStand < 0 THEN gesamtKtoStand := 0 END IF; RETURN gesamtKtoStand; RETURN gesamtKtoStand; END; • Externe Prozedur CREATE PROCEDURE GesKtoStand( IN kundenId INTEGER, OUT gesamtKtoStand DECIMAL(15,2)) LANGUAGE Java EXTERNAL NAME geheim\weisnichwo\hieristsie\KtoStandProc.jar; 19. Prof. Jasper: Datenbanksysteme 3 Prozeduren und Funktionen in SQL:2003 • Aufruf einer Prozedur (mit entsprechenden Parametern): CALL GesKtoStand(KundeParam, GesamtKtoStandParam); • Aufruf einer Funktion in SQL: SELECT kunde ‐> kundenNr, kunde ‐> kundenName, kunde ‐> kundenVorname FROM Konto AS Kto WHERE GesKtoStand(Kto.kunde.kundenNr) > 10000; 19. Prof. Jasper: Datenbanksysteme 4 Methoden in SQL:2003 • Methoden: Verhalten abstrakter Datentypen – – – – – – – – – Sind an genau einen Datentypen (Zeilentyp) gebunden Einzige Möglichkeit zur Manipulation des Datentypen g g p yp Können überschrieben werden Der erste Parameter ist vom Datentyp des Zeilentyps (SELF) Dynamisches Binden notwendig Dynamisches Binden notwendig Instanzen‐Methoden, Statische Methoden („Klassenebene“), Konstruktor‐Methode Deklaration im Methodenblock Separate Methodenimplementierung Separate Methodenimplementierung Beispiel: nächste Folie • Implizite Methoden bei benannten Zeilentypen (CREATE TUPLE): Für jedes Attribut – Observer Observer‐Methoden: Methoden: liefert Wert liefert Wert – Mutator‐Medode: setzt Wert – Nicht veränder‐ / überschreibbar 19. Prof. Jasper: Datenbanksysteme 5 Methoden in SQL:2003 • Beispiel CREATE TPYE ProduktTyp AS ( produkt_Nr FünfstelligeZahl, produkt_Bez VARCHAR(50), product_Typ VARCHAR(25), stueck_Kosten PositiveReal, netto_Preis PositiveReal, REF IS SYSTEM GENERATED, STATIC METHOD gesamtZahlProdukte() RETURNS INTEGER); CREATE METHOD gesamtZahlProdukte() FOR ProduktTyp BEGIN DECLARE gesZahlProd INTEGER; gesZahlProd := 0; SELECT COUNT(produktNr) INTO INTO gesZahlProd Z hlP d FROM Produkt; RETURN gesZahlProd; END; 19. Prof. Jasper: Datenbanksysteme 6 Methoden in SQL:2003 • Beispiel CREATE TYPE KundenTyp UNDER PersonTyp AS( rabatt DECIMAL(4 2) DEFAULT 3 DECIMAL(4,2) DEFAULT 3, ansprechpartner REF(KontaktPersTyp) ARRAY[3], INSTANCE METHOD gesKtoStand() RETURNS DECIMAL(15,2) ); CREATE METHOD gesKtoStand() FOR KundenTyp BEGIN DECLARE gesamtKtoStand DECIMAL(15,2); gesamtKtoStand := 0; SELECT SUM(kontoStand) INTO gesamtKtoStand FROM Konto WHERE kunde ‐> kundenOID = SELF IF gesamtKtoStand < 0 THEN gesamtKtoStand := 0 END IF; RETURN gesamtKtoStand; END; 19. Prof. Jasper: Datenbanksysteme 7 Methoden in SQL:2003 • Aufruf einer statischen Methode: hier ist doppelte Doppelpunktnotation zu verwenden (Annahme Produkt ist eine Tabelle vom Typ ProduktTyp): Tabelle vom Typ ProduktTyp): SELECT Produkt::gesamtZahProdukte FROM Produkt; FROM Produkt; • Aufruf einer Instanzen‐Methoden A f f i I t M th d SELECT kundenNr, kundenName, kundenVorname FROM Kunde WHERE gesKtoStand > 10000; 19. Prof. Jasper: Datenbanksysteme 8 Trigger • IN SQL‐92 Keine Trigger, jedoch viele Implementierungen • ECA‐Konzept ECA K t • Ereignisse: INSERT; UPDATE; DELETE auf Tabellen und Sichten Ereignisse: INSERT; UPDATE; DELETE auf Tabellen und Sichten – Ausführungszeitpunkt: BEFORE und AFTER – Triggergranularität: FOR EACH ROW / FOR EACH STATEMENT – Zugriff auf neuen (REFERENCING NEW AS) oder alten Wert (REFERENCING OLD AS) über Namen • Condition (Optional: WHERE‐Klausel) • Action: eine oder mehrere SQL‐Anweisungen d h – Prozeduren, Funktionen, Methoden erlaubt – BEGIN ATOMIC <Anweisungen> END‐Block g 19. Prof. Jasper: Datenbanksysteme 9 Erlaubte Datenbankbezüge innerhalb von Triggern G Granularität l ität Zeitpunkt p der Aktiviterung BEFORE ROW AFTER BEFORE Auslösende Operation Zulässiger g Spaltenbezug INSERT NEW UPDATE OLD, NEW keiner DELETE INSERT OLD NEW NEW_TABLE UPDATE OLD, NEW OLD_, NEW_TABLE DELETE INSERT OLD OLD_TABLE UPDATE keiner keiner DELETE INSERT STATEMENT AFTER UPDATE Zulässiger g Tabelleneintrag NEW_TABLE keiner DELETE 19. Prof. Jasper: Datenbanksysteme OLD_, NEW_TABLE OLD_TABLE _ 10 Trigger in SQL:2003 • Beispiel CREATE TRIGGER VerlustVermeidung CREATE TRIGGER VerlustVermeidung AFTER UPDATE OF nettoPreis ON Produkt REFERENCING NEW AS NeuerWert OLD AS AlterWert FOR EACH ROW FOR EACH ROW WHEN (NeuerWert.nettoPreis < (AlterWert.nettoPreis *0.9) OR (nettoPreis < (stueckKosten * 1.5) ROLLBACK;; 19. Prof. Jasper: Datenbanksysteme 11 Weitere Neuerungen in SQL:2003 • Sichten – SQL‐92 hatte sehr rigide Regeln • • • • • 1. FROM‐Klausel nur auf eine Tabelle bezogen (Basistabelle) 1 FROM Kl l f i T b ll b (B i t b ll ) 2. Weder GROUP BY noch HAVING 3. Kein DISTINCT 4. Kein SWF‐Block in der WHERE‐Klausel 5. Spalte im SELECT müssen eindeutig Spalten der Basistabelle zugeordnet werden können – In SQL:2003: • ausgefeiltes Regelwerk, dass sich auf funktionale Abhängigkeiten stützt • Typisierte Sichten (entsprechend den typisierten Tabellen) 19. Prof. Jasper: Datenbanksysteme 12 Weitere Neuerungen in SQL:2003 • Benennung von SFW‐Blöcken – Vorteil: Ergebnis einer Anfrage kann benannt und damit in anderen Anfragen genutzt werden. • Rekursion – Stücklistenauflösung (u.a.) ist möglich – Unterstützt wahlweise Breiten‐ Unterstützt wahlweise Breiten oder Tiefensuche oder Tiefensuche • Datenschutz und –sicherheit – Rollenkonzept: ist nicht im SQL‐92‐Standard enthalten; wird jedoch schon von fast allen Produkten unterstützt • Online Analytical Processing (OLAP) – Innerhalb Innerhalb von GROUP BY sind von GROUP BY sind • ROLLUP • CUBE und • GROUPING SETS möglich: Beispiele GROUPING SETS möglich: Beispiele • Sicherungspunkte – Sicherungspunkte können für die Realisierung geschachtelter Transaktionen genutzt werden 19. Prof. Jasper: Datenbanksysteme 13 Zusammenfassung SQL:2003 • Komplexe Objekte in SQL:2003; ABER: – Keine SET OF ROW / SET OF <Zeilentyp>, bzw. geschachtelte Relationen – ARRAY erlaubt endliche, über Index adressierbare „Relation“ , „ – Problem: assoziativer Zugriff auf geschachtelte Relationen: Starker Eingriff in SQL; Siehe NF2‐Relationen • Parallel typisierte und untypisierte Tabellen: – Willkür wg. Aufwärtskompatibilität – Transformation der untypisierten T f ti d t i i t „alten“ Tabellen in typisierte Tabellen schwierig: lt “ T b ll i t i i t T b ll h i i Zusätzliche OID und andere Fremdschlüsselbeziehungen • Datenunabhängigkeit bh k – Das „alte“ relationale Modell gut – SQL:2003 mit den neuen Konzepten nicht mehr: OIDs (direkte Referenzen) können p ( ) auch von den Anwendungsprogrammen genutzt werden. 19. Prof. Jasper: Datenbanksysteme 14 Woran wird noch (weiter) gearbeitet • SQL/MM (multi media) – Multimediaunterstützung M lti di t tüt – Anwendungsklassenspezifische Datentypen, z. B.: • Geo‐Daten • Bilder • Audiodaten • SQL/MED (Management of External Data) – Data Links verknüpfen Tabelleneinträge mit externen Dateien – Transaktionsbindung: Dateien werden in Tx eingebunden • JAVA Sprachanbindung JAVA Sprachanbindung – SQLJ Part0: Einbettung von SQL in JAVA – SQLJ Part1 und Part2: Einbettung von JAVA in SQL 19. Prof. Jasper: Datenbanksysteme 15 Zusammenfassung: Entwicklung von DBMS Theorie Prototypen Produkte Standards t 2000 1990 1980 1970 Hierarchisches Modell Netzwerkmodell Relationales Modell 19. Prof. Jasper: Datenbanksysteme Objekt Objektorientiertes Modell Obje t Objektrelationales Modell 16