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

Quellen

• Ziegler (2007)
• Rütten, Glemser (2006)

Siehe auch

• Prepared Statement
  
• SQL Injection