KK10_13 Stored Procedures und Triggers

Datenbanken
13 Views, Stored Procedures und
Triggers
Karl Meier
[email protected]
07.12.2010
MySQL 5.0
MySQL 5 wurde von vielen
Datenbankentwicklern sehnsüchtig erwartet.
Neue Funktionen wie Stored Procedures,
Trigger, Views und Subselects (In
ausgereifter Form) als auch das Caching
System sollen die Professionalität der
MySQL Datenbank weiter ausbauen. Doch
die neuen Features zu nutzen, nur weil sie
vorhanden sind, macht keinen Sinn.
07.12.2010
13 Views, Stored Procedures und
Triggers
2
1
Inhalt
Views
Stored Procedures
Triggers
Exceptions
Prepared Statements
13 Views, Stored Procedures und
Triggers
07.12.2010
3
3 – Ebenen – Modell
(ANSI-SPARC 1978)
Externe Ebene
Sicht 1
Sicht 2
Sicht 3
Benutzerdefinierte Sichten
Transformationsregeln
Konzeptionelle Ebene
Konzeptionelles Schema
Logische Gesamtsicht
Transformationsregeln
Internes Schema
Interne Ebene
Physikalische Beschreibung
07.12.2010
13 Views, Stored Procedures und
Triggers
4
2
Definition View
Eine View ist eine virtuelle Tabelle, die mit
einer Select Abfrage erzeugt worden ist.
Das Rechte-System von MySQL
behandelt Views wie eigenständige
Tabellen. Somit wird eine Freigabe auf
View Ebene möglich.
07.12.2010
13 Views, Stored Procedures und
Triggers
5
Syntax
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
07.12.2010
13 Views, Stored Procedures und
Triggers
6
3
Beispiel
CREATE VIEW zuercher AS
SELECT * FROM t_ma WHERE ort=„zuerich“;
07.12.2010
13 Views, Stored Procedures und
Triggers
7
Definition Stored Procedure
Stored Procedures (SP) sind eigenständig
abgespeicherte Formulierungen von komplexen
Zugriffs- und Datenmodifikationsoperationen.
Diese werden in Tabellen gespeichert.
Der Benutzer benötigt keine Privilegien für die
darunterliegende Tabelle.
07.12.2010
13 Views, Stored Procedures und
Triggers
8
4
SP in MySQL
basierend auf Vorgaben in SQL2003
noch sehr viele Restriktionen
Wird sich in den kommenden Versionen
noch stark weiterentwickeln
07.12.2010
13 Views, Stored Procedures und
Triggers
9
Syntax
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL | [NOT] DETERMINISTIC
|{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string‘
routine_body:
Valid SQL procedure statement
07.12.2010
13 Views, Stored Procedures und
Triggers
10
5
Beispiel MySQL
DELIMITER //
CREATE PROCEDURE AlleMitarbeiter()
BEGIN
SELECT * FROM t_ma;
END //
DELIMITER ;
CALL AlleMitarbeiter();
07.12.2010
13 Views, Stored Procedures und
Triggers
11
Beispiel MySQL
CREATE PROCEDURE SalaerErhoehung (IN AngNr INT, IN SalIncr INT)
BEGIN
UPDATE Angestellter
SET Salaer = Salaer + SalIncr
WHERE PersNr = AngNr;
END;
GRANT EXECUTE ON SalaerErhoehung TO user;
CALL SalaerErhoehung (&AngNr, &SalIncr);
07.12.2010
13 Views, Stored Procedures und
Triggers
12
6
Beispiel ORACLE
PROCEDURE SalaerErhoehung (AngNr IN NUMBER, SalIncr IN NUMBER)
AS
BEGIN
UPDATE Angestellter A
SET A.Salaer = A.Salaer + SalIncr
WHERE A.PersNr = AngNr;
IF SQL%NOTFOUND THEN
Raise_application_error(…);
END IF;
END Salaererhoehung;
GRANT EXECUTE ON SalaerErhoehung TO user;
EXECUTE SalaerErhoehung (&AngNr, &SalIncr);
07.12.2010
13 Views, Stored Procedures und
Triggers
13
Variablen in Stored Procedures
DECLARE variable_name datatype(size) DEFAULT default_value;
DECLARE x, y INT DEFAULT 0;
DECLARE total_count INT DEFAULT 0
SET total_count = 10;
DECLARE total_products INT DEFAULT 0
SELECT COUNT(*) INTO total_products
FROM products;
Eine Variable beginnend mit ‘@’ ist eine Session Variable.
07.12.2010
13 Views, Stored Procedures und
Triggers
14
7
Vorteile
Erhöhte Datensicherheit



Automatisierte Programmabläufe
Systemrelevante Daten sind nicht sichtbar
Einfachere Wartung der Programmcodes
Verbesserter Datenschutz

kein Zugriff auf sensible Daten
Höhere Leistungsfähigkeit


direkter Zugriff auf gespeicherte Daten
von Compiler in interne DB-Sprache übersetzt
07.12.2010
13 Views, Stored Procedures und
Triggers
15
Nachteile
Eigene Implementierung pro RDBMS

MySQL kennt Stored Procedures ab der Version 5.0
Unterschiedliche Deklaration / Verwendung
07.12.2010
13 Views, Stored Procedures und
Triggers
16
8
Aufgabe
Schreiben Sie eine Prozedur „Durchschnitt“, an
die zwei Zahlen übergeben werden und die als
Ergebnis (x + y)/2 liefert:
DELIMITER //
CREATE PROCEDURE durchschnitt (IN x INT, IN y INT)
BEGIN
SELECT (x + y)/2;
END//
07.12.2010
13 Views, Stored Procedures und
Triggers
17
Definition Trigger
Trigger sind Event-Handler, die bei dem
Erreichen von Werten in einer Tabelle Stored
Procedures oder auch externe Prozesse
anstossen können.
07.12.2010
13 Views, Stored Procedures und
Triggers
18
9
Syntax
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
trigger_time: BEFORE | AFTER
trigger_event: INSERT | DELETE | UPDATE
Verfügbar ab MySQL 5.0
07.12.2010
13 Views, Stored Procedures und
Triggers
19
Beispiel MySQL
DELIMITER //
CREATE TRIGGER LogSalaerAenderung
AFTER UPDATE ON Angestellter
FOR EACH ROW
IF (new.Salaer != old.Salaer) THEN
INSERT INTO AngAudit VALUES
(user, new.PersNr, now(), new.Salaer, „Neues Salaer“);
END IF//
DELIMITER ;
07.12.2010
13 Views, Stored Procedures und
Triggers
20
10
Beispiel (ORACLE)
CREATE OR REPLACE TRIGGER LogSalaerAenderung
AFTER UPDATE OF Salaer ON Angestellter
FOR EACH ROW
WHEN (new.Salaer != old.Salaer)
BEGIN
INSERT INTO AngAudit VALUES
(user, :new.PersNr, SYSDATE, :new.Salaer, ´Neues Salaer´);
END;
07.12.2010
13 Views, Stored Procedures und
Triggers
21
Verwendung
Sicherstellung komplexer Konsistenzbedingungen
Berechnen der Werte von abgeleiteten Attributen
Zugriffsschutz und Auditing
Sammeln von Statistik- und Logdaten
Trigger sind




keine DB-Objekte
immer einer Tabelle zugeordnet
ohne Parameter
ereignisgesteuert
07.12.2010
13 Views, Stored Procedures und
Triggers
22
11
Eigenschaften
Auslösendes Ereignis (Trigger Event)



INSERT
DELETE
UPDATE
Trigger Time


BEFORE
AFTER
Vorbedingungen
Nachbedingungen
Trigger Restrictions (Bedingte Ausführung)
Trigger Typen


ROW-Trigger
Aufruf pro Datensatz
STATEMENT-Trigger Aufruf pro SQL-Anweisung
13 Views, Stored Procedures und
Triggers
07.12.2010
23
Eigenschaften
Trigger Action


Block mit SQL Anweisungen
Deklaration für Variablen, etc.
Implizite Parameter


OLD
NEW
alter Datensatz
neuer Datensatz
Rechte


Rechte ihres Owners bei Ausführung
Systemprivileg CREATE TRIGGER notwendig
07.12.2010
13 Views, Stored Procedures und
Triggers
24
12
Exceptions (Ausnahmen)
Ein Trigger mit der Angabe BEFORE kann eine
entsprechende INSERT, UPDATE oder
DELETE Anweisung abbrechen, bevor sie
ausgeführt wird.
Es existieren System Exceptions und eigene
Exceptions
Nicht jede Exception ist ein Fehler!
Wichtig ist das korrekte Exception Handling.
07.12.2010
13 Views, Stored Procedures und
Triggers
25
Prepared Statements
Prepared statements bieten die Möglichkeit, ein
SQL Statement mit verschiedenen Parametern
mehrmals auszuführen.
(Verfügbar ab MySQL 4.1)
SELECT * FROM Country WHERE code = ?
( ? wird als Platzhalter benötigt )
Vorteile: - Sicherheit
- Effizienz
07.12.2010
13 Views, Stored Procedures und
Triggers
26
13
Beispiel
mysql> PREPARE stmt_name FROM "SELECT name FROM Country WHERE
Query OK, 0 rows affected (0.09 sec)
Statement prepared
code = ?";
mysql> SET @test_parm := „CH";
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE stmt_name USING @test_parm;
+---------+
| name
|
+---------+
| Schweiz |
+---------+
1 row in set (0.03 sec)
mysql> DEALLOCATE PREPARE stmt_name;
Query OK, 0 rows affected (0.00 sec)
13 Views, Stored Procedures und
Triggers
07.12.2010
27
MySQL White Papers
Stored Procedures
http://dev.mysql.com/tech-resources/articles/mysqlstoredprocedures.pdf
Triggers
http://dev.mysql.com/tech-resources/articles/mysql-triggers.pdf
07.12.2010
13 Views, Stored Procedures und
Triggers
28
14
07.12.2010
13 Views, Stored Procedures und
Triggers
29
15