Funktionen
Neben den Prozeduren dürfen Sie unter MariaDB Funktionen anlegen. Diese erstellen Sie fast genauso wie Prozeduren: Sie wählen einfach in DBeaver FUNCTION anstelle von PROCEDURE. Funktionen geben immer einen Wert zurück. Damit ist es möglich, Funktionen an Stellen in SQL-Statements einzubauen, an denen diese einen Wert erwarten. Um zu testen, welche Werte eine Funktion zurückgibt, genügt ein einfaches SELECT (SELECT Funktion(Parameter)). In diesem Zusammenhang empfiehlt es sich, einen Blick auf die schon in MariaDB vorhandenen Funktionen zu werfen [6].
Trigger ermöglichen es, Prozeduren aufgrund von Ereignissen in der Datenbank auszuführen. Bei Ereignissen handelt es sich etwa um das Einfügen, Löschen oder Aktualisieren von Datensätzen. Der Trigger ist für denjenigen, der das Ereignis auslöst, vollkommen transparent.
Ein gutes Beispiel für einen Trigger liefert das Befüllen einer Audit-Tabelle. Jedes Mal, wenn ein Wert sich ändert, etwa in einer Tabelle von Gehältern, löst der Trigger aus und erzeugt über eine Prozedur einen zusätzlichen Eintrag in der entsprechenden Audit-Tabelle. So lässt sich nachvollziehen, wer wann ein Gehalt angepasst hat (und ob dabei alles mit rechten Dingen zuging).
Zu guter Letzt lohnt es sich, einen Blick auf den SQL-Modus zu werfen. Diesen stellen Sie mit der Anweisung SQL_MODE=ORACLE so ein, dass die Datenbank die SQL-Syntax von Oracle unterstützt. Damit stehen dann Funktionen bereit, die es so nur in PL/SQL gibt. Eine Übersicht der vorhandenen PL/SQL-Kommandos in der MariaDB finden Sie in der Dokumentation [7].
Ein praktisches Feature im Oracle-Modus ist das Exception Handling von PL/SQL, das es ermöglicht, auf Laufzeitfehler innerhalb des Programms zu reagieren. Aktuell gibt es die Ausnahmen TOO_MANY_ROWS, NO_DATA_FOUND und DUP_VAL_ON_INDEX.
Trennzeichen
In SQL dient das Semikolon als Trennzeichen zwischen den einzelnen Kommandos. Beim ersten Auftreten des Zeichens stoppt die Datenbank das Einlesen und führt die Anweisung aus. Folgen weitere Zeichen, beginnt sie wieder mit dem Einlesen. Wollen Sie SQL-Code in SQL-Code schachteln, etwa um Prozeduren in der Datenbank anzulegen, umschiffen Sie diese Klippe, indem Sie kurzzeitig ein anderes Zeichen zum Beenden von Kommandos definieren. Das Kommando zum Ändern heißt DELIMITER. Bevor Sie also eine Prozedur anlegen, setzen Sie mit DELIMITER $$ das Endzeichen auf $$. Haben Sie die Prozedur angelegt, nutzen Sie DELIMITER ; und die Datenbank verhält sich wie vorher. Als Endzeichen kann theoretisch jeder beliebige Wert dienen, der nicht Teil der SQL-Syntax und nicht zufällig ein Parameter oder Wert im eingebauten SQL ist. Mit den zwei Dollar-Zeichen bleiben Sie auf der sicheren Seite. Falls das einmal nicht genügen sollte, verwenden Sie einfach mehr Dollar-Zeichen. DBeaver ergänzt automatisch den Quellcode, wenn ein anderes Trennzeichen nötig ist.
Zusammenspiel
Was machen Sie nun mit Triggern und Prozeduren? Im Beispiel fehlt beim Speichern der Messdaten etwa Folgendes: Es gibt weder einen Zeitpunkt zu den Messwerten noch einen Schlüssel, um einen Wert eindeutig zu machen. Sie benötigen also einen Trigger, der die fehlenden Daten vor dem Einfügen in die Tabelle ergänzt. Dazu verwenden Sie das Schlüsselwort NEW, das in diesem Kontext die neu anzulegenden Daten repräsentiert.
Um einen Trigger zu erstellen oder zu bearbeiten, bietet DBeaver ein gut zu bedienendes Frontend an. Um den Trigger anzulegen, navigieren Sie im Baum zu Databases | sensor | Tables | statistics | Triggers und wählen Anlegen Trigger. Der Trigger erstellt eine einfache Statistik (Listing 10).
Listing 10
Trigger zum Erstellen der Statistiken
CREATE DEFINER=`sensor`@`%` TRIGGER statistics
AFTER INSERT
ON temperature FOR EACH ROW
BEGIN
DECLARE v_min float;
DECLARE v_max float;
DECLARE v_avg float;
SELECT MAX(value) FROM temperature t INTO v_max;
SELECT MIN(value) FROM temperature t INTO v_min;
SELECT AVG(value) FROM temperature t INTO v_avg;
UPDATE statistics
SET min_value = v_min,
max_value = v_max,
avg_value = v_avg;
END
Das Auslagern der Statistik in eine gesonderte Tabelle bringt bei großen Datenbeständen und vielen Zugriffen enorme Vorteile in Bezug auf die Geschwindigkeit, da die Datenbank nicht immer den kompletten Datenbestand heranzuziehen braucht, um einen Mittelwert auszugeben.
Vorteile
Der Einsatz von Stored Procedures bietet viele Vorteile. Neben den zahlreichen Möglichkeiten, die kleine Programme und Trigger in der Datenbank bieten, können Sie je nach Aufgabenstellung viel schnellere SQL-Abfragen aufbauen. Damit umgehen Sie etwa zeitaufwendige Sub-Selects. Es stellt auch kein Problem dar, die komplette Business-Logik innerhalb der Datenbank abzubilden. Das bietet viele Vorteile bei Anwendungen, die intensiv mit den Daten arbeiten müssen.
Neben den bereits genannten Vorteilen spielt der Sicherheitsaspekt eine wichtige Rolle: Anwendungen, die über Stored Procedures mit der Datenbank kommunizieren, sind immun gegen die gefürchteten SQL-Injection-Attacken. Ein Angreifer kann über die Prozedur keine SQL-Fragmente ausführen.
Fazit
Es bietet fast nur Vorteile, Programmcode innerhalb der Datenbank auszuführen. Der einzige Nachteil liegt darin, dass Sie eine zusätzliche Komponente mit in der Anwendung haben, die Sie warten müssen. Allerdings muss sich zumindest ein Entwickler im Team in dieses Thema hineinarbeiten.
