ws 2012/13 datenbanksysteme fr 15:15 – 16:45 r 2.007 vorlesung #8 sql (teil 5)

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

Vorlesung #8

SQL (Teil 5)

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

„Fahrplan“ Besprechung der Übungsaufgaben Einschub: „Self Joins“ (relevant fürs Praktikum) Dynamische Intergritätsbedingungen, das „Trigger“-

Konzept von Oracle Prozedurale Erweiterungen, Beispiel Oracle PL/SQL Einbettung von SQL in Wirtssprachen, C, C++ ODBC – Open Database Connectivity JDBC – Java Database Connectivity SQLJ – Einbettung von SQL in Java QBE – Query by Example Datenbankanwendungen und Paradigmen Fazit und Ausblick Vorlesung #9

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Dynamische Integritätsbedingungen Bedingungen an Zustandsänderungen bzw.

Datenänderungen werden mittels Trigger überprüft. Man kann mit Triggern auch nachträgliche bzw. zusätzliche Berechnungen durchführen. CREATE TRIGGER – eigenständiger

Datenbankobjekt der einer Tabelle zugeordnet wird, d.h. auf eine Tabelle „aufpasst“

Auslöser wird definiert (before update, for each row)

Bedingungen gefolgt von Anweisungen

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Trigger in Oracle Syntax

create or replace trigger keineDegradierungbefore update on Professorenfor each rowwhen (old.Rang is not null)begin

if :old.Rang = 'C3' and :new.Rang = 'C2' then:new.Rang := 'C3';

end if;if :old.Rang = 'C4' then

:new.Rang := 'C4'; end if; if :new.Rang is null then

:new.Rang := :old.Rang; end if;end;

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Trigger in Oracle Syntax (2)

CREATE [OR REPLACE] TRIGGER [schema .] trigger { BEFORE | AFTER | INSTEAD OF } { DELETE | INSERT | UPDATE [OF column [, column]...] } [FOR EACH ROW] [WHEN ( condition ) ] { pl/sql_block | call_procedure_statement }

Vereinfacht – nur DML Events, man kann auch DDL abfangenVereinfacht – nur DML Events, man kann auch DDL abfangen

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Trigger in DB2 Syntax

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Prozedurale Erweiterung von SQL – Oracle PL/SQL Trigger waren bzw. sind bereits prozedural! Prozedurale Erweiterung ist notwendig

Aus theoretischer Sicht – um Touring-Vollständigkeit zu erreichen

Aus praktischer Sicht - um Probleme des Alltags, viel einfacher prozedural statt deklarativ zu lösen

Hauptbegriff: Datenbank Cursor Dynamisches SQL in PL/SQL – würde hier

den Rahmen sprengen

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Oracle PL/SQL Prozedurale Erweiterung von SQL Beispiel: Funktion Summe - rekursiv

CREATE FUNCTION Summe1 (n INTEGER)RETURN INTEGERISBEGIN

IF n = 0 THEN return 0;

ELSIF n = 1 THEN return 1; ELSIF n > 1 THEN return n + Summe1(n - 1);

END IF;END;

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Oracle PL/SQL (2) nicht rekursiv

FUNCTION Summe2(n INTEGER)RETURN INTEGERISresult INTEGER DEFAULT 0;v_n INTEGER DEFAULT n;BEGIN WHILE v_n >= 0 LOOP result := result + v_n; v_n := v_n - 1; END LOOP; return result;END;

nicht rekursiv, schlau

FUNCTION Summe3(n INTEGER)RETURN INTEGERISBEGIN RETURN (n*(n+1))/2;END;

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Oracle PL/SQL

Beispiel: man verfügt über eine Tabelle (oder eine View über zwei Tabellen), die zu jedem Ort die zugehörige PLZ und das Bundesland beinhaltet

Man möchte für jedes Bundesland PLZ Intervale bestimmen In SQL sehr umständlich, mit vielen „unsauberen“

Hilfskonstrukten

Wie bringt aber man aber Daten aus einer Tabelle in eine prozedurale Sprache hinein?

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Deklarativ vs. Prozedural (2)

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Deklarativ vs. Prozedural (3)

PLZ Interval für jedes Bundesland Idee

Sortiere nach PLZ Merke das Bundesland für die kleinste PLZ Bei jeder weiteren PLZ überprüfe ob sich das Bundesland

geändert hat, d.h. vergleich das aktuelle Bundesland mit dem Bundesland des vorherigen Laufes

Wenn ja, dokumentiere die Änderung

Wiederhole bis zur größten PLZ

Cursor-FOR oder Cursor-WHILE Schleife vorgestellt in der Vorlesung

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Cursor Beispiel (3)

CURSOR c_orte is select PLZ, Land from orteorder by PLZ ASC; lv_c_orte c_orte%ROWTYPE;BEGIN

FOR lv_c_orte IN c_orteLOOP... – Anweisungen, IT THEN ELSE usw. ...END LOOP;

END;

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Einbettung in Wirtssprachen „Embedded SQL“Mit Hilfe eines Präcompilers!!!

#include <stdio.h>

exec sql begin declare section;

varchar user_passwd[30];

int exMatrNr;

exec sql end declare section;

exec sql include SQLCA;

main()

{

printf("Name/Password:");

scanf("%", user_passwd.arr);

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Einbettung in Wirtssprachen „Embedded SQL“ (2)user_passwd.len=strlen(user_passwd.arr);

exec sql wheneversqlerror goto error;

exec sql connect :user_passwd;

while (1) {

printf("Matrikelnummer (0 zum beenden):");

scanf("%d", &ecMatrNr);

if (!exMatrNr) break;

exec sql delete from Studenten

where MatrNr= :exMatrNr;

}

exec sql commit work release;

exit(0);

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Einbettung in Wirtssprachen „Embedded SQL“ (3)error:

exec sql whenever sqlerror continue;exec sql rollback work release;printf("fehler aufgetreten!\n");exit(-1);}

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Datenbankanbindungen

ODBC (Open Database Connecticity) Anbindung von Windows-Programmen Beispiel –Excel Zugriff auf Oracle

JDBC (Java Database Connectivity) Eigentlich ein CLI Call-Level Interface

SQLJ Echte Einbettung von SQL in Java, wie C, C++

Wichtig bei allen Schnittstellen: immer an die Voreinstellungen denken was passiert bei einem Fehler gibt es automatisch einen COMMIT oder ROLLBACK

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Abgrenzung: Datenbanken vs. Anwendungen Marktbeherrschende Datenbanken beinhalten heute

bereits Eingebaute und erweiterbare SQL Funktionen und

Prozeduren als prozedurale Erweiterung XML und Java Standardschnittstellen zu jeder wichtigen

Programmiersprache (Fortran, Cobol, C, C++) Eingebaute Funktionen für Volltextsuche, Geodaten, OLAP,

Data Mining, Web usw. Objekte, benutzerdefinierte Datentypen

Wo ist die Grenze zwischen einer Datenbank und einer „klassischen“ Anwendung?

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

© Bojan Milijaš, 23.11.2012

Wohin mir der Logik?Datenbank oder Anwendung Man entscheidet sich für die Datenbank

Wenn man aus Performance-, Skalierbarkeit-, Sicherheits- oder Verfügbarkeitsgründen eine (bestimmte) Datenbank wählen muss

Wenn man mit mehreren verschiedenen Anwendung gleichzeitig auf die Daten zugreifen möchte, so dass die Anwendungen „nur visualisieren“ und die Datenbank die Zugriffe und die Datenverarbeitung regelt

Man entscheidet sich für die Anwendung Wenn man Datenbank-unabhängig bleiben möchte, d.h. die

zugrundeliegende(n) Datenbank(en) lediglich als SQL-Datenablage betrachtet

Wenn eine Datenbankzentralisierung aus finanziellen oder technischen Gründen keinen Sinn macht

WS 2012/13Datenbanksysteme

Fr 15:15 – 16:45R 2.007

Vorlesung #8

Ende