pdf - DBIS Informatik - Universität Innsbruck

Datenbanksysteme
Dominic Pacher
Datenbanken und Informationssysteme (DBIS)
Institut für Informatik
Universität Innsbruck
dbis-informatik.uibk.ac.at
1
Übersicht
Was passiert in den kommenden 90 Minuten?
•
Programmieren in MySQL Stored Program
Language
•
Stored Procedures
•
Stored Functions
•
Trigger
2
FlughafenDB
3
4
Stored Modules
•
Warum verwenden wenn es SQL gibt?
•
SQL ist deklarativ nicht prozedual
 Keine Schleifen oder Variablen
•
Keine Möglichkeit wiederkehrende Abläufe zu
automatisieren
•
Komplexe Berechnungen (aus bestehenden
prozedualen Algorithmen) sind schwer zu
realisieren
5
Stored Modules
•
•
•
•
•
•
•
Warum nicht außerhalb der Datenbank (in Java,
C++...) umsetzen?
Hoher Datentransferaufwand
Sicherheitsproblematik
Zentraler Speicherort für Prozeduren
Kann nicht durch Datenbank optimiert und
kompiliert werden
Platformabhängig
Lösung: Stored Modules
•
Nach Standard SQL Persistent Stored Modules
6
Stored Modules
•
Untergliederung in drei Unterkategorien
•
Stored Procedures
•
Prozedur die beliebig viele Tupel als
Ergebnis zurückgeben
•
Stored Functions
•
Prozedur die nur einen konstanten Wert als
Ergebnis hat (vgl. Count())
•
Trigger
•
Prozedur die eventbasiert aufgerufen werden
(On INSERT, On DELETE...)
7
Stored Procedures
8
Stored Procedures
•
Grammatik CREATE PROCEDURES
CREATE
[DEFINER = { benutzer | CURRENT_USER }]
PROCEDURE bezeichner_prozedur ([parameter[,...]])
[optionen ...] rumpf_prozedur
parameter:
[ IN | OUT | INOUT ] bezeichner_parameter datentyp
optionen:
COMMENT 'kommentar'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
Rump_prozedur:
befehle...
9
Stored Procedures
Beispiel
•
Wieviele Fluglinien sind in der Datenbank?
CREATE PROCEDURE anzahl_fluglinien(OUT p_rueckgabe INT)
BEGIN
SELECT COUNT(DISTINCT fluglinie_id)
INTO p_rueckgabe
FROM flug;
END
•
IN, OUT und INOUT Parameter möglich
10
Stored Procedures
Ausführung von Procedures
mysql> SET @anzahl = NULL;
mysql> CALL anzahl_fluglinien(@anzahl);
mysql> SELECT @anzahl;
Variablen fangen stehts mit @ an!
11
Stored Procedures
Einige Schlüsselwörter
•
•
•
•
COMMENT ‘Kommentar‘
SQL SECURITY
•
DEFINER: Rechter des Prozedurerstellers werden
angewendet
•
INVOKER: Rechte des aufrufenden Nutzers werden
angewendet
[NOT] DETERMISTIC: Eine Funktion ist deterministisch
wenn für gleiche Parameter stehts die gleiche Ausgabe
erfolgt

Cachen/Optimieren von Ergebnissen möglich!
CONTAINS SQL,NO SQL, READS/Modifies SQL
•
Nur bei Replikation/Verteilung wichtig
12
Stored Procedures
Löschen und Ändern
•
•
Drop Procedure
•
mysql> DROP PROCEDURE IF EXISTS
anzahl_fluglinien;
Alter Procedure
•
mysql> ALTER PROCEDURE anzahl_fluglinien
-> COMMENT ‘Neuer Kommentar';
•
Achtung! ALTER PROCEDURE ändert nur
Merkmale wie Deterministic, Comment ...
•
Um den Code einer Prozedur zu ändern
müssen DROP / CREATE verwendet werden
13
Handler
•
Handler entspechen entfernt dem herkömmlichen
Exception Handling aus anderen
Programmiersprachen
•
Werden in MySQL jedoch auch zur Steuerung und
Bestätigung „normaler“ Ablaufe gebraucht
•
Finden oft bei Cursors Verwendung
14
Handler
DECLARE handler_art HANDLER
FOR bedingung [, bedingung] ...
befehle…
handler_art = EXIT | CONTINUE | UNDO
bedingung = mysql_fehlernummer |
SQLSTATE sqlstate | condition_bezeichner |
SQLEXCEPTION | NOT FOUND |
SQLWARNING
15
Handler
•
Exit Handler
•
Fängt das Event auf und springt in den nächsten
Scope. Alle verbleibenden Codezeilen im aktuellen
Scope werden übersprungen.
•
Continue Handler
•
Fängt das Event auf und fährt mit nächste
Codezeile fort. Diese Funktionalität ist insbesondere
für Cursors wichtig.
16
Handler
Beispiel
•
Fehlercode 1048 (NULL Wert in Spalte Einfügen die
keine NULL-Werte zulässt) abfangen:
DECLARE EXIT HANDLER
FOR 1048
SELECT 'Spalten dürfen nicht NULL sein!';
•
Alle Fehler auffangen:
DECLARE EXIT HANDLER
FOR SQLEXCEPTION
SELECT 'Es ist ein Fehler aufgetreten';
17
Signals
•
Anwendungsspezifische Exceptions erleichtern oft
das Verständnis im Fehlerfall
• Hiefür gibt’s es in MySQL sogenannte Signals:
• SIGNAL condition_wert
[SET signal_information [, signal_information] ...]
condition_wert:
SQLSTATE [sqlstate_nummer] sqlstate_wert |
bezeichner_condition
18
Signals
Beispiel
set @test = „101010“
Call bereinige_flugnummer(@test);
19
Cursor
•
Alternativ zu DQL Statements können Cursor
verwendet werden um Ergebnisse zu berechnen
•
Ein Cursor kann hierbei als Zeiger auf eine Zeile in
einer Tabelle gesehen werden
•
Durch den Cursor kann so durch Zeilen iteriert
werden und durch Fetch auf den Inhalt zugegriffen
werden.
•
Bei Verwendung von Cursor ist Vorsicht geboten!
•
Oft können verfügbare Joins gleiche Ergebnisse
schneller und mit weniger Aufwand berechnen
•
Bei komplexen Anfragen kann jedoch die
Verwendung eines Cursors sinnvoll sein
20
Cursor
21
Cursor
Beispiel
•
Erste Zeile von Tabelle ‚Flug‘ ausgeben
CREATE PROCEDURE liste_fluege ()
BEGIN
DECLARE v_flugnr CHAR(8);
DECLARE v_fluglinie INT;
DECLARE v_fertig INT DEFAULT 0;
DECLARE c_fluege CURSOR
FOR SELECT flugnr, fluglinie_id
FROM flug WHERE flug_id;
-- Handler Definition
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fertig = 1;
OPEN c_fluege;
FETCH c_fluege INTO v_flugnr, v_fluglinie;
SELECT v_fluglinie,v_flugnr;
END
22
Cursor
Beispiel mit Fehler
•
Alle Zeilen von Tabelle ‚Flug‘ ausgeben
CREATE PROCEDURE liste_fluege()
BEGIN DECLARE v_flugnr CHAR(8);
DECLARE v_fluglinie INT;
DECLARE v_fertig INT DEFAULT 0;
DECLARE c_fluege CURSOR FOR
SELECT flugnr, fluglinie_id
FROM flug WHERE flug_id < 100;
OPEN c_fluege;
l_fetch_daten: LOOP
FETCH c_fluege INTO v_flugnr, v_fluglinie;
SELECT v_fluglinie,v_flugnr;
END LOOP l_fetch_daten;
END
23
Cursor
Beispiel ohne Fehler
•
Alle Zeilen von Tabelle ‚Flug‘ ausgeben
CREATE PROCEDURE liste_fluege ()
BEGIN
DECLARE v_flugnr CHAR(8);
DECLARE v_fluglinie INT;
DECLARE v_fertig INT DEFAULT 0;
DECLARE c_fluege CURSOR
FOR SELECT flugnr, fluglinie_id
FROM flug
WHERE flug_id < 10;
Auf der nächsten Slide geht’s weiter....
24
Cursor
Beispiel ohne Fehler
•
Fortsetzung:
-- Handler Definition
DECLARE CONTINUE HANDLER
FOR NOT FOUND
SET v_fertig = 1;
OPEN c_fluege;
l_fetch_daten: LOOP
FETCH c_fluege INTO v_flugnr, v_fluglinie;
SELECT v_fluglinie,v_flugnr;
-- testen, ob Handler bereits Ende festgestellt hat
IF v_fertig THEN
CLOSE c_fluege;
LEAVE l_fetch_daten;
END IF;
END LOOP l_fetch_daten;
END
25
Verschachtelte Cursors
•
•
•
Bisher wurden Cursor nur von jeweils einer Tabelle
verwendet
Wie können Cursor zweier Tabellen verwendet
werden um mit den Inhalten beider zu arbeiten?
Ein Handler kann nicht unterscheiden zu welchem
Cursor der die Fehlermeldung (Not Found) gehört
26
Verschachtelte Cursors
Statistik-Beispiel
•
Folgende Procedure errechnet die geflogenen km
pro Flugzeug sowie aller Fluglinien und gibt diese
aus:
•
CALL erzeuge_flugstatisktik();
27
Rekursive Procedures
•
•
Procedures unterstützen rekursive Aufrufe. Diese
sind allerdings standardmäßig deaktiviert.
Alle erreichbaren Flughäfen ausgehen von einem
Startflughafen und Anzahl der Hops suchen:
mysql> CALL erreichbare_flughaefen(5797, 2, 0);
mysql> SELECT * FROM flughafen_erreichbar;
28
Rekursive Procedures
Beispiel
CREATE PROCEDURE erreichbare_flughaefen(IN p_von INT, IN p_hops
INT, IN p_hop_aktuell INT)
BEGIN
IF (p_hop_aktuell = 0) THEN
DROP TABLE IF EXISTS flughafen_erreichbar;
CREATE TABLE flughafen_erreichbar(
flughafen_id INTEGER PRIMARY KEY,
hops INTEGER
);
INSERT INTO flughafen_erreichbar
SELECT DISTINCT nach, p_hop_aktuell
FROM flug
WHERE von = p_von;
Fortsetzung nächste Slide...
29
Rekursive Procedures
Beispiel
ELSEIF (p_hop_aktuell <= p_hops) THEN
INSERT INTO flughafen_erreichbar
SELECT DISTINCT f.nach, p_hop_aktuell
FROM flughafen_erreichbar fh JOIN flug f
ON (fh.flughafen_id = f.von);
CALL erreichbare_flughaefen(p_von, p_hops,
p_hop_aktuell + 1);
END IF;
END
30
Temporäre Tabellen
•
•
Procedures akzeptieren keine Tabellen als IN /
OUT Parameter
Oft benötigt man eine Art Zwischenspeicher um
Procedures sinnvoll umsetzen zu können
CREATE TEMPORARY TABLE tmp_flug(
flug_id INT PRIMARY KEY,
von SMALLINT NOT NULL,
nach SMALLINT NOT NULL );
31
Temporäre Tabellen
Einschränkungen
•
Sie können innerhalb einer Abfrage nicht zwei Mal
aus der gleichen Tabelle referenziert werden.
• Werden nur von Memory, MyISAM, Merge und
InnoDB werden als Storage Engines unterstützt
• Der SHOW TABLES-Befehl führt Temporäre
Tabellen nicht an.
32
Stored Functions
33
Stored Functions
•
•
Sind Stored Procedures sehr ähnlich
Unterschied:
•
Funktionen können nur einen konstanten Wert
zurückgeben
•
Können auch im Select Teil verwendet werden:
mysql> SELECT berechne_distanz(5486, 4017)
AS distanz;
•
5486 und 4017 sind Flughafennummer von
Innsbruck und Frankfurt (später mehr)
34
Stored Functions
•
Grammatik CREATE FUNCTION:
CREATE
[DEFINER = { benutzer | CURRENT_USER }]
FUNCTION bezeichner_funktion ([parameter_funktion[,...]])
RETURNS type 35
optionen rump_funktion
optionen:
COMMENT 'kommentar'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA |
MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
parameter_funktion:
bezeichner datentyp
35
Stored Functions
Beispiel
•
Wieviele Fluglinien sind in der Datenbank?
CREATE FUNCTION preis_uebergepaeck (p_gewicht
DECIMAL(5,2)) RETURNS DECIMAL(6,2)
BEGIN
IF p_gewicht < 30 THEN RETURN 0.0;
ELSEIF p_gewicht < 40 THEN RETURN 100.0;
ELSEIF p_gewicht < 50 THEN RETURN 150.0;
ELSE RETURN 250.0;
END IF;
END
•
Besser nur ein Return:
36
Stored Functions
Beispiel
•
Mit einem return:
CREATE FUNCTION preis_uebergepaeck (p_gewicht
DECIMAL(5,2)) RETURNS DECIMAL(6,2)
BEGIN
DECLARE v_preis INT;
IF p_gewicht < 30 THEN SET v_preis = 0.00;
ELSEIF p_gewicht < 40 THEN SET v_preis = 100.00;
ELSEIF p_gewicht < 50 THEN SET v_preis = 150.00;
ELSE SET v_preis = 250.00;
END IF;
RETURN v_preis;
END
37
Stored Functions
Einschränkungen
•
•
•
•
•
•
Keine Transaktionsanweisungen innerhalb von
Gespeicherten Funktionen
•
COMMIT, ROLLBACK, START TRANSACTION, LOCK
bzw. UNLOCK TABLES, SET AUTOCOMMIT, etc.
Keine Prepared Statements
•
PREPARE, EXECUTE und DEALLOCATE PREPARE
Ein Großteil der DDL (Data Definition Language) ist ungültig
•
CREATE, ALTER, DROP, etc. benötigen implizit ein
Commit
Nur SELECT INTO-Statements. Nur ein atomarer
Rückgabewert ist zulässig!
Keine rekursiver Aufruf erlaubt
Kein SHOW, EXPLAIN und CHECK
38
Stored Functions
Löschen / Ändern
•
•
Für Lösch- und Änderungsoperationen können die
Befehle DROP und ALTER analog zu Procedures
verwendet werden
Auch hier gilt: Soll der Rumpf einer Funktion
geändert werden müssen DROP/CREATE
verwendet werden!
39
Stored Functions
Längeres Beispiel 1
•
Abstand zweier Flughäfen berechnen
CREATE FUNCTION berechne_distanz(p_von SMALLINT,
p_nach SMALLINT) RETURNS DECIMAL(7,2)
BEGIN
DECLARE v_von_laenge DECIMAL(11,8);
DECLARE v_von_breite DECIMAL(11,8);
DECLARE v_nach_laenge DECIMAL(11,8);
DECLARE v_nach_breite DECIMAL(11,8);
DECLARE v_distanz DECIMAL(7,2);
Fortsetzung auf nächster Slide...
40
Stored Functions
Längeres Beispiel 2
-- Laenge, Breite der Flughaefen holen
SELECT laenge,breite INTO v_von_laenge,v_von_breite
FROM flughafen_geo WHERE flughafen_id = p_von ;
SELECT laenge,breite INTO v_nach_laenge,v_nach_breite
FROM flughafen_geo WHERE flughafen_id = p_nach;
-- Berechnung der Distanz
SELECT ROUND((SQRT(POW(ABS(v_von_laenge - v_nach_laenge),2)
+ POW(ABS(v_von_breite - v_nach_breite), 2)) * 111),2) INTO
v_distanz;
RETURN v_distanz;
END
mysql> SELECT berechne_distanz(5486, 4017)
AS distanz;
41
Trigger
42
Trigger
• Datenbanken procedures (auch bekannt unter ECA
rules (Event-Condition-Action)) die per Event
ausgelöst werden
• Seit SQL:1999 standardisiert
• Trotzdem weichen viele Datenbanken (e.g. Oracle,
MySQL, DB2 UDB, ...) in Bereich der funktionellen
und syntaktischen Umsetzung voneinander ab.
43
Trigger
Beispiel
Grammatik:
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name {BEFORE | AFTER}
{INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW commands…;
DROP TRIGGER trigger_name;
Definer: Der Benutzer welcher für die Ausführung der Statements verwendet
wird.
• Wann wird ein Trigger ausgeführt?
•BEFORE oder AFTER
•
Auf was für ein event reagiert der Trigger?
•INSERT/UPDATE/DELETE
44
Neue / Alte Werte
•
Abhängig von der Art des Triggers, kann auf die neuen und
alten Werte (durch NEW bzw. OLD) zugegriffen werden
•
Neue Werte können zusätzlich geändert werden
CREATE TRIGGER check_booking
BEFORE INSERT ON booking
FOR EACH ROW
BEGIN
IF NEW.price < 0
THEN SET NEW.price = 0;
END IF;
END
45
Neue / Alte Werte
Trigger
BEFORE INSERT
OLD
NEW
Keine OLD
Werte die eingefügt
werden sollen
AFTER INSERT
Keine OLD
Werte die eingefügt
wurden
BEFORE
UPDATE
Werte vor dem Update
Neue Werte des
Updates
AFTER UPDATE
Werte vor dem Update
Neue Werte nach
dem Update
BEFORE DELETE
Werte die gelöscht werden sollen
Kein NEW
AFTER DELETE
Werte die gelöschten wurden
Kein NEW
46
MySQL vs. SQL
• SQL beschreibt Statement-orientierte und Tupelorientierte Trigger
• FOR EACH STATEMENT: Wird durch Update /
Insert / Delete ausgelöst
• FOR EACH ROW: Für jede betroffene Zeile wird
der Trigger ausgelöst
• Durch durch das WHEN Konstrukt kann zusätzlich
auf eine Bedingung geprüft werden (z.B. neuer
wert>5). Ist diese nicht erfüllt wird der Trigger nicht
ausgeführt.
• MySQL 5.5 unterstützt WHEN allerdings nicht!
47
Beispiel
Loggt jede Änderung in der Flug Tabelle mit und speichert den Benutzer in
einer zusätzlichen Tabelle ab:
CREATE TRIGGER log_aenderungen
AFTER UPDATE ON Flug
FOR EACH ROW
INSERT INTO flug_log (
date, user, flug_id, flugnr_old, flugnr_new, von_old,
nach_new, nach_old,
nach_new, abflug_old,
abflug_new, ankunft_old,
ankunft_new,
fluglinie_id_old, fluglinie_id_new, flugzeug_id_old,
flugzeug_id_new)
VALUES (
current_timestamp(), user(), OLD.flug_id, OLD.flugnr,
NEW.flugnr, OLD.von,
NEW.von, OLD.nach, NEW.nach, OLD.abflug,
NEW.abflug, OLD.ankunft,
NEW.ankunft, OLD.fluglinie_id, NEW.fluglinie_id, OLD.flugzeug_id,
NEW.flugzeug_id
)
48
Einschränkungen (MySQL)
•
Nur ein Trigger pro Ereignis (MySQL)
• ERROR 1235 (42000): This version of MySQL doesn’t yet
support ’multiple triggers with the same action time and
event for one table’
•
Trigger können Datenbanken erheblich verlangsamen, da sie
bei jedem Statement aufgerufen werden (müssen gesperrt oder
vgl.werden).
• Deshalb sollte mit dem Einsatz sparsam und bedacht
umgangen werden.
•
Triggeraktionen laufen vom „einfachen“ Benutzer verborgen ab
• Kann zur Verwirrung führen
•
Trigger werden bei CASCADING DELETE nicht für alle
untergeordneten deletes aufgerufen.
49
Zusammenfassung
•
•
•
•
•
•
•
•
Prozeduale Abläufe in Datenbanken
Stored Procedures
Handler und Signale
Cursor
Rekursion
Stored Functions
Trigger
Automatisierung von Vorgängen in
Datenbanken
50