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
- 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 DB geschickt
werden.
- Eine Funktion kann von verschiedenen Clients genutzt werden (Wiederverwendbarkeit). Die SQL-
Anweisung verbirgt sich in der Funktion uns muss nicht jedesmal neu formuliert werden. Eine
Änderung muss auch nur an einer Stelle durchgeführt werden (Wartbarkeit).
- Vermeidung unnötiger Roundtrips zwischen Clientprogramm und DB-Server, da z.B. eine Rekursion
vollständig in der SP formuliert werden kann. Dadurch ergibt sich unter Umständen ein gravierender
Performancegewinn.
Implementierungssprachen
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
Es soll ein Dateisystem in einer Datenbank abgelegt werden, dh. eine Baumstruktur. Ein Knoten kann
also eine Datei oder ein Verzeichnis sein, welches wiederrum Kind-Elemente enthalten kann. 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 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 dieser Funktion 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 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;
SELECT
CASE
WHEN type = 'MediaDirectory' THEN
calc_mediadirectory_size(id)
WHEN type = 'MediaFile' THEN
size
END AS size, name
FROM
media_items
