Stored Procedure
Definition
Mit Hilfe von Stored Procedures ist es dem Benutzer eines Datenbank-Management-Systems (DBMS) möglich, komplexe Folgen von SQL-Anweisungen im System unter einem Namen zu speichern und jederzeit auszuführen.
Stored Procedures sind Funktionen, die innerhalb des DB-Server gespeichert werden und wie alle anderen, bereits eingebauten Funktionen z.B. round() aufgerufen werden können.
Vorteile
- Wiederverwendbarkeit: Eine Funktion kann von verschiedenen Clients genutzt werden. Die SQL-Anweisung verbirgt sich in der Funktion uns muss nicht jedesmal neu formuliert werden.
- Wartbarkeit: Änderungen müssen nur zentral an einer Stelle durchgeführt werden.
- Performance: Vermeidung unnötiger Roundtrips zwischen Clientprogramm und Datenbank-Server, da z.B. eine Rekursion vollständig in der SP formuliert werden kann. Dadurch ergibt sich unter Umständen ein gravierender Performancegewinn, da jegliche Kommunikation mit dem Clientprogramm während der Rekursion entfällt. Erst das Ergebnis wird dann wieder zum Clientprogramm geschickt.
- Sicherheit: Absicherung gegen SQL Injection, da die Stored Procedures zusätzliche Befehle zur Ablaufsteuerung und Auswertung erfolgter Anweisungen enthalten können. Noch besser ist es aber SQL Injection bereits auf Anwendungs-Ebene vorzubeugen, also bevor die Abfragen an die Datenbank geschickt werden.
Implementierungs-Sprachen in PostgreSQL
In Postgresql können neben reinem SQL auch andere Sprache für das Schreiben von SP´s genutzt werden, z.B. in der Version 8.3: PL/pgSQL, eine PostgreSQL-eigene Sprache für das Schreiben von SP´s, aber auch PL/Tcl, PL/Python, PL/Perl oder C.
Vorteil dieser Sprachen ist, dass man auf gewohnte Kontrollstrukturen zurückgreifen kann die es in reinem SQL nicht gibt z.B. Schleifen.
Implementierungs-Beispiel in PostgreSQL
Es soll ein Dateisystem in einer Datenbank modelliert werden. Ein Knoten kann also eine Datei oder ein Verzeichnis sein, welches wiederrum Kind-Elemente enthalten kann - das heißt man legt letztendlich eine Baumstruktur in der Datenbank ab. Ein Problem in einer solchen Anwendung ist es, die Größe eines Ordners zu ermittteln.
Im folgenden entspricht eine Datei einem MediaFile, ein Verzeichnis einem MediaDirectory. MediaFile und MediaDirectory sind beides MediaItems (Ist-Ein-Beziehung). Im Attribut parent_id ist der jeweilige Ordner referenziert in dem sich ein MediaItem befindet. Im Attribut type ist hinterlegt um welchen Typ (MediaFile oder MediaDirectory) von MediaItem es sich handelt.
Vereinfachtes Datenbank-Schema:
CREATE TABLE media_items
(
id INTEGER NOT NULL,
parent_id INTEGER,
type VARCHAR(80) NOT NULL,
name VARCHAR(80) NOT NULL,
size INTEGER,
content BYTEA,
);
Mit der Funktion "calc_mediadirectory_size" kann die Größe eines Ordners rekursiv bestimmt werden. Die Verwendung der coalesce-Funktion ist notwendig da ein MediaDirectory ohne Kind-Elemente NULL liefert, was in dieser Beispielanwendung aber als 0 interpretiert werden soll.
CREATE FUNCTION calc_mediadirectory_size(integer) RETURNS numeric AS $$
SELECT coalesce(sum(s), 0)
FROM (
SELECT
CASE
WHEN type = 'MediaDirectory' THEN
calc_mediadirectory_size(id)
WHEN type = 'MediaFile' THEN
size
END
FROM media_items WHERE parent_id = $1
) sizetab(s)
$$ LANGUAGE SQL;
Aufruf der Funktion:
SELECT
CASE
WHEN type = 'MediaDirectory' THEN
calc_mediadirectory_size(id)
WHEN type = 'MediaFile' THEN
size
END AS size, name
FROM
media_items
