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