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 andere "Procedural Languages" wie 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.

Bevor man eine Funktion in einer "Procedural Language" schreiben kann muss man dieser erst in jeder Datenbank, in der diese verwendet werden soll "installiert" werden.

Dazu verwendet man z.B. für PL/pgsql folgende Anweisung:

CREATE LANGUAGE plpgsql;

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

Quellen

• Ziegler (2007)
• Rütten, Glemser (2006)
• PostgreSQL Manual, Chapter 37, Procedural Languages
• PostgreSQL Manual, Chapter 37, Installing Procedural Languages

Siehe auch

• Prepared Statement
  
• SQL Injection