Programmierung

Innerhalb einer Datenbank können Arbeitsabläufe selbst gesteuert werden. Dafür gibt es Funktionen, Prozeduren und Trigger.

(Einführung in SQL: Programmierung)

(Einführung in SQL: Programmierung)

Allgemeine Hinweise

Bei diesen Konstruktionen gibt es relativ wenig Gemeinsamkeiten zwischen den DBMS. Für Funktionen und Prozeduren lässt bereits der SQL-Standard den DB-Anbietern „alle“ Freiheiten, wie sie diese Möglichkeiten verwirklichen wollen. Deshalb können auch wir uns nur auf einige Grundlagen beschränken und müssen erneut auf die Dokumentation des jeweiligen DBMS verweisen.

Funktionen und Prozeduren werden oft gemeinsam als Routinen bezeichnet.

Diese Elemente benutzen integrierte Funktionen, DML-Befehle und teilweise Datenbank-Operationen, verbunden in einer speziellen Programmiersprache, die prozedurales SQL o. ä. bezeichnet wird. In diesem Kapitel gibt es allgemeine Erklärungen dazu, wie solche Abläufe erstellt und programmiert werden können; in den folgenden Kapiteln werden diese Mittel konkret benutzt.

Funktionen

Eine (benutzerdefinierte Skalar-) Funktion liefert genau einen Wert eines bestimmten Datentyps. Es handelt sich dabei um eine Ergänzung zu den internen Skalarfunktionen des DBMS. Es gibt sie mit und ohne Argumente; sie werden gezielt vom Anwender bzw. einem Anwendungsprogramm aufgerufen.

• Einzelheiten dazu werden in Eigene Funktionen behandelt.

Prozeduren

Eine Prozedur – gespeicherte Prozedur, engl. StoredProcedure (SP) – ist vorgesehen für „immer wiederkehrende“ Arbeitsabläufe. Es gibt sie mit und ohne Argumente und Rückgabewerte; sie werden gezielt vom Anwender bzw. einem Anwendungsprogramm aufgerufen.

• Einzelheiten dazu werden in Prozeduren behandelt.

Trigger

Ein Trigger ist ein Arbeitsablauf, der automatisch beim Speichern in einer Tabelle ausgeführt wird. Es gibt weder Argumente noch Rückgabewerte und keinerlei direkte Zusammenarbeit zwischen der Datenbank und dem Anwender bzw. einem Anwendungsprogramm.

• Einzelheiten dazu werden in Trigger behandelt.

Routinen ohne feste Speicherung

Das, was als Prozedur gespeichert werden kann, kann in einem DBMS in der Regel auch direkt ausgeführt werden (ohne Speicherung in der Datenbank). Dazu werden die Definition von Parametern und Variablen sowie die Anweisungen mit einer EXECUTE-Anweisung aufgerufen.

Im Kapitel zu Prozeduren gibt es ein Beispiel „Testdaten in einer Tabelle erzeugen“, das auch so verwirklicht werden kann:

Firebird-Version

EXECUTE BLOCK ( Anzahl INT = ?anzahl )
      RETURNS ( Maxid  INT )
AS
  DECLARE VARIABLE Temp INT = 0;     /* usw. identisch wie bei der Prozedur */
BEGIN
  Maxid = 0;
  WHILE (Temp < Anzahl) DO
  BEGIN    /* identischer Arbeitsablauf wie bei der Prozedur */
    Temp = Temp + 1;
  END
  select MAX(ID) from Fahrzeug into :Maxid;
  SUSPEND;
END

Der Aufbau entspricht dem einer Prozedur (siehe unten). Der Unterschied besteht in der direkten Ausführung durch EXECUTE BLOCK.

Programmieren innerhalb von Routinen

Bitte haben Sie Nachsicht: Wegen der vielen Varianten bei den DBMS wurde ein Teil der folgenden Hinweise und der Beispiele in den nächsten Kapiteln nur nach der Dokumentation verfasst und nicht in der Praxis umgesetzt. Da es sich um ein Wikibook handelt, dürfen Sie das Buch gerne um weitere Hinweise und andere Beispiele ergänzen.

Allgemeines

Routinen – also Funktionen und Prozeduren – werden grundsätzlich mit einer Syntax ähnlich der folgenden definiert:

CREATE OR ALTER { FUNCTION | PROCEDURE } <routine-name>
        ( [ <parameterliste> ] )
        RETURNS <parameterliste>
AS  
BEGIN
    <variablenliste>
    <anweisungen>
END

Die Definition von Triggern verläuft so ähnlich: Parameter entfallen, aber die Art der Auslösung kommt hinzu. Die Hinweise zu Variablen und Anweisungen in den folgenden Abschnitten gelten für Trigger in gleicher Weise wie für Routinen.

Der Teil zwischen BEGIN und END (jeweils einschließlich) wird als Rumpf – engl. body – bezeichnet, alles davor heißt Kopf – engl. header – der Routine.

Bitte beachten Sie, dass jedes DBMS seine eigenen Besonderheiten hat. Die wichtigsten Unterschiede sind:

• Bei MySQL müssen CREATE und ALTER getrennt werden, bei Oracle heißt es CREATE OR REPLACE.
• RETURNS gehört zu Funktionen (bei Oracle: RETURN). Nur Firebird benutzt es auch bei Prozeduren zur Trennung der Ausgabe-Parameter.
• Ob die Parameter in Klammern stehen müssen oder nicht, ist unterschiedlich geregelt.
• AS kann teilweise auch entfallen, bei Oracle wird auch IS verwendet.
• Die <variablenliste> ist Bestandteil der <anweisungen>; bei Firebird und Oracle steht sie zwischen AS und BEGIN.

Wenn es insgesamt (einschließlich Variablen) nur eine einzige Anweisung gibt, kann auf BEGIN und END verzichtet werden; der Übersichtlichkeit halber ist ihre Verwendung aber fast immer zu empfehlen.

Gleiches gilt innerhalb einzelner Abschnitte (wie Verzweigungen oder Schleifen): Eine einzelne Anweisung kann ohne BEGIN...END angegeben werden; wenn es die Übersichtlichkeit oder Verschachtelung erfordern, ist die Verwendung dieser Schlüsselwörter vorzuziehen.

Eine Funktion benötigt als (letzte) Anweisung RETURN, mit der ein bestimmter Wert zurückgegeben wird.

Bei Verzweigungen und Schleifen kann durch LABELs der Zusammenhang deutlich gemacht werden. Meistens gibt es keine Notwendigkeit dazu, sodass wir in der Regel darauf verzichten.

Hinweis: Dieser Abschnitt beschränkt sich auf die wichtigsten Erläuterungen. Die konkreten SQL-Anweisungen sind in den folgenden Kapiteln zu finden. Außerdem gibt es zu fast allen genannten Themen weitere Möglichkeiten.

Spalten, Variable und Parameter

In diesem Buch werden nur einfache lokale Variable benutzt; deren Gültigkeitsbereich beschränkt sich auf die aktuelle Routine. Je nach DBMS stehen auch globale Variable zur Verfügung. Außerdem kann man über das Schlüsselwort CURSOR eine ganze Zeile von Tabellen oder Ergebnismengen mit einer Variablen benutzen.

In allen Fällen, in denen die Namen von Variablen oder Parametern auf die Namen von Tabellenspalten treffen, muss dem DBMS klar sein, um welche Art von Namen es sich handelt:

• MS-SQL regelt das mit '@' am Anfang des Namens von Variablen oder Parametern.
• MySQL und Oracle unterscheiden nicht. Sie müssen selbst für unterschiedliche Bezeichner sorgen.
• Firebird verlangt in diesen Situationen einen Doppelpunkt vor dem Namen von Variablen und Parametern.

Wegen der vielfältigen Unterschiede werden die wichtigsten Möglichkeiten getrennt behandelt.

SQL-Programmierung mit Firebird

Einzelheiten

Parameter deklarieren

Jeder Parameter, der innerhalb der Anweisungen benutzt wird und dessen Wert an die Routine übergeben oder durch die Bearbeitung zurückgegeben wird, muss im Kopf der Routine festgelegt werden: Name, Datentyp, Vorgabe- oder Anfangswert. Mehrere Parameter werden mit Komma verbunden; nach dem letzten Parameter fehlt es.

Bei Funktionen kann es nur Eingabe-Parameter geben; der Ausgabe-Parameter wird durch RETURNS immer getrennt angegeben.

Eingabe-Parameter stehen nach dem Namen der Routine vor der RETURNS-Klausel, Ausgabe-Parameter sind Teil der RETURNS-Klausel. Als Datentypen sind ab Version 2.1 auch DOMAINS zulässig. Ein einzelner Parameter wird so deklariert:

<name> <typ> [ {= | DEFAULT} <wert> ]

Bei Eingabe-Parametern sind auch Vorgabewerte möglich, die durch '=' oder DEFAULT gekennzeichnet werden. Wichtig ist: Wenn ein Parameter einen Vorgabewert erhält und deshalb beim Aufruf in der Liste nicht benutzt wird, müssen alle nachfolgenden Parameter ebenfalls mit Vorgabewert arbeiten.

Variable deklarieren

Jede Variable, die innerhalb der Routine benutzt wird, muss in ihrem Kopf festgelegt werden, nämlich zwischen AS und BEGIN: Name, Datentyp, Vorgabe- oder Anfangswert. Jede Deklaration gilt als eine einzelne Anweisung und ist mit Semikolon abzuschließen.

DECLARE [VARIABLE] <name>      <typ> [ {=DEFAULT} <wert> ];

Als Vorgabewert ist auch ein SQL-Ausdruck möglich.

Zuweisungen von Werten zu Variablen und Parametern

Der einfachste Weg ist die direkte Zuweisung eines Wertes oder eines Ausdrucks (einer internen oder einer eigenen Funktion) zu einer Variablen oder einem Parameter:

<name>  = <ausdruck> ;      /* Standard: nur das Gleichheitszeichen  */

Sehr oft werden die Werte aus einem SELECT-Befehl mit Variablen weiterverarbeitet. Dazu gibt es die INTO-Klausel:

SELECT <spaltenliste> 
  FROM <usw. alles andere> 
  INTO <variablenliste> ;

Die Liste der Variablen muss von Anzahl und Typ her der Liste der Spalten entsprechen. Bitte beachten Sie, dass bei Firebird die INTO-Klausel erst am Ende des Befehls stehen darf.

In ähnlicher Weise kann auch das Ergebnis einer Prozedur übernommen und in der aktuellen Routine verarbeitet werden:

EXECUTE PROCEDURE <routine-name> [ <eingabe-parameter> ]
        RETURNING_VALUES <variablenliste> ;         /* Variablen mit Doppelpunkt */

Jede hier genannte Variable muss (in Reihenfolge und Typ) einem der <ausgabe-parameter> der Prozedur entsprechen.

Achtung: In allen Fällen, in denen die Namen von Variablen oder Parametern auf die Namen von Tabellenspalten treffen, muss dem DBMS klar sein, um welche Art von Namen es sich handelt. Firebird verlangt in diesen Situationen einen Doppelpunkt vor dem Namen von Variablen und Parametern.

Erst die SUSPEND-Anweisung sorgt dafür, dass ein Ausgabe-Parameter vom „rufenden“ Programm entgegengenommen werden kann. Bei einer Rückgabe einfacher Werte steht diese Anweisung am Ende einer Prozedur; bei einer Funktion übernimmt RETURN diese Aufgabe. Wenn aber (wie durch einen SELECT-Befehl) mehrere Zeilen zu übergeben sind, muss SUSPEND bei jeder dieser Zeilen stehen. Ein Beispiel steht im Kapitel zu Prozeduren unter Ersatz für eine View mit Parametern.

Verzweigungen

Die IF-Abfrage steuert den Ablauf nach Bedingungen:

IF ( <bedingung> ) THEN 
BEGIN 
  <anweisungen>
END
[ ELSE 
BEGIN 
  <anweisungen>
END
]

Diese Abfrage sieht so aus:

• Die <bedingung> muss in Klammern stehen; sie kann auch mit AND und OR sowie weiteren Klammern verschachtelt werden.
• Der ELSE-Zweig ist optional; die IF-Abfrage kann auch auf den IF-THEN-Abschnitt beschränkt werden.
• Der ELSE-Zweig kann durch weitere IF-Abfragen verschachtelt werden.
• Vor allem bei verschachtelten Abfragen sollten BEGIN...END immer benutzt und durch Einrückungen der Zusammenhang deutlich werden.

Schleifen

Es gibt zwei Arten von Schleifen: eine Schleife mit einer Bedingung und eine Schleife mit einer Ergebnismenge für eine SELECT-Abfrage.

Die WHILE-Schleife prüft eine Bedingung und wird so lange durchlaufen, wie diese Bedingung wahr ist:

WHILE ( <bedingung> ) DO
BEGIN 
  <anweisungen>
END

Diese Schleife sieht so aus:

• Die <bedingung> muss in Klammern stehen; sie kann auch mit AND und OR sowie weiteren Klammern verschachtelt werden.
• Die <bedingung> wird jeweils am Anfang eines Durchgangs geprüft. Wenn ihr Wert von Anfang an FALSE ist, wird die Schleife überhaupt nicht durchlaufen.

Die FOR SELECT-Schleife erstellt durch einen SELECT-Befehl eine Ergebnismenge und führt für jede Zeile des Ergebnisses etwas aus:

FOR SELECT <abfrage-einzelheiten>
      INTO <variablenliste>
DO BEGIN
  <anweisungen>
END

Diese Schleife sieht so aus:

• Beim SELECT-Befehl handelt es sich um eine beliebige Abfrage.
• Für jede Zeile des Ergebnisses wird der DO-Block einmal durchlaufen.
• Die Ergebnisspalten und ihre Werte sind nur innerhalb des SELECT-Befehls bekannt, nicht innerhalb der DO-Anweisungen. Die Werte müssen deshalb zunächst an Variablen übergeben (für jede Spalte eine Variable oder Ausgabe-Parameter, mit Doppelpunkt gekennzeichnet), bevor sie in den <anweisungen> benutzt werden können.
• Wenn diese Werte für jede Zeile einzeln zurückgegeben werden sollen, wird SUSPEND als eine Anweisung benötigt.
• Wenn SUSPEND die einzige Anweisung ist und kein Einzelwert später (außerhalb der Schleife) noch benötigt wird, kann auf die INTO-Klausel verzichtet werden.

Zulässige Befehle

Innerhalb von Routinen sind ausschließlich DML-Befehle zulässig. Keinerlei anderer Befehl wird akzeptiert, also vor allem kein DDL-Befehl, aber auch nicht GRANT/REVOKE (DCL) oder COMMIT/ROLLBACK (TCL).

Sämtliche DML-Befehle, die innerhalb einer Routine ausgeführt werden, gehören zurselben Transaktion wie die Befehle, durch die sie aufgerufen bzw. ausgelöst werden.

Anweisungen begrenzen

Jede einzelne Anweisung innerhalb einer Routine und jeder SQL-Befehl müssen mit einem Semikolon abgeschlossen werden. Das ist kein Problem, wenn nur ein einzelner CREATE PROCEDURE o. ä. ausgeführt werden soll; dann wird das abschließende Semikolon weggelassen, und es gibt keine Unklarheiten (siehe ein Firebird-Beispiel im Trigger-Kapitel).

Bei mehreren Routinen nacheinander – wie im Skript zur Beispieldatenbank – muss das DBMS zwischen den verschiedenen Abschlusszeichen unterscheiden. Dazu dient SET TERM (TERM steht für Terminator, also Begrenzer):

Firebird-Version

SET TERM ^ ;
 
CREATE OR ALTER TRIGGER Abteilung_BI0 FOR Abteilung
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF ((new.ID IS NULL) OR (new.ID = 0))
  THEN new.ID = NEXT VALUE FOR Abteilung_ID;
END
^
 
SET TERM ; ^

Zuerst wird der Begrenzer für SQL-Befehle auf '^' geändert; das Abschlusszeichen für einzelne Anweisungen bleibt das Semikolon. Dann folgen alle Befehle zur Trigger-Definition; jeder einzelne Trigger wird mit dem neuen Begrenzer beendet. Abschließend wird der Begrenzer wieder auf das Semikolon zurückgesetzt.

SQL-Programmierung mit MS-SQL

Einzelheiten

Als erste Anweisung einer Routine sollte immer SET NOCOUNT ON; verwendet werden; dies beschleunigt die Ausführung.

Parameter deklarieren

Jeder Parameter, dessen Wert an die Routine übergeben oder durch die Bearbeitung zurückgegeben wird, muss im Kopf festgelegt werden: Name, Datentyp, Verwendung für Eingabe und/oder Ausgabe, Vorgabe- oder Anfangswert. Mehrere Parameter werden mit Komma verbunden; nach dem letzten Parameter fehlt es. Bei Funktionen kann es nur Eingabe-Parameter geben; der Ausgabe-Parameter wird durch RETURNS immer getrennt angeben.

Ein einzelner Parameter wird so deklariert:

<name> <typ> [ = <wert> ] [ OUT | OUTPUT ]

Parameternamen müssen immer mit '@' beginnen. Ausgabe-Parameter werden mit OUT bzw. OUTPUT markiert; alle anderen sind Eingabe-Parameter. Durch das Gleichheitszeichen kann ein Vorgabewert zugewiesen werden.

Variablen deklarieren

Jede Variable, die innerhalb der Routine benutzt wird, muss in ihrem Rumpf festgelegt werden: Name, Datentyp, Vorgabe- oder Anfangswert. Jede Deklaration gilt als eine einzelne Anweisung und ist mit Semikolon abzuschließen.

DECLARE <name> [AS] <typ> [ = <wert> ];

Bei MS-SQL muss der Name immer mit '@' beginnen. Als Vorgabewert ist auch ein SQL-Ausdruck möglich.

Zuweisungen von Werten zu Variablen und Parametern

Der einfachste Weg ist die direkte Zuweisung eines Wertes oder eines Ausdrucks (einer internen oder einer eigenen Funktion) zu einer Variablen oder einem Parameter mit dem SET-Befehl:

SET <name> = <ausdruck> ;

Sehr oft werden die Werte aus einem SELECT-Befehl mit Variablen weiterverarbeitet. Ein einzelner Wert wie das Ergebnis von SELECT COUNT(*) wird ebenfalls durch SET der Variablen zugewiesen. Mehrere Werte können innerhalb des SELECT direkt zugewiesen werden:

SELECT @Variable1 = <spalte1>, @Variable2 = <spalte2>
  FROM <tabellenliste> /* usw. weitere Bedingungen */

Für eine Ergebnismenge wird ein CURSOR benötigt, der mit FETCH einen Datensatz holt und den Wert einer Spalte mit INTO an eine Variable übergibt.

Eine Routine wird meistens mit EXECUTE ausgeführt. Rückgabewerte, die über OUTPUT-Parameter bestimmt werden, werden vorher deklariert und mit dem <ausgabe-parameter> der Prozedur verbunden.

declare @inputvariable varchar(25);
set @inputvariable = 'Meier';
declare @outputvariable MONEY;
execute myprocedure @inputparameter = @inputvariable, @outputparameter = @outputvariable OUTPUT;
select @outputvariable;

Jede hier genannte Variable muss (in Reihenfolge und Typ) allen Parametern der Prozedur entsprechen.

Für EXECUTE (auch die Abkürzung EXEC ist möglich) gibt es viele Varianten für Aufruf und Zuweisung der Parameter.

Verzweigungen

Die IF-Abfrage steuert den Ablauf nach Bedingungen:

IF <bedingung>
BEGIN 
  <anweisungen>
END
[ ELSE IF <bedingung> 
  BEGIN 
    <anweisungen>
  END ]
[ ELSE
  BEGIN 
    <anweisungen>
  END ]
END

Diese Abfrage sieht so aus:

• Bei <bedingung> handelt es sich um eine einfache Prüfung, die nicht mit AND oder OR erweitert werden kann.
• Wenn ein SELECT Teil der <bedingung> ist, muss es in Klammern stehen.
• Der ELSE-Zweig ist optional. Es ist auch möglich, dass nur der IF-THEN-Abschnitt ausgeführt werden muss.
• Durch ELSE IF-Zweige sind Verschachtelungen, also auch weitere Prüfungen möglich.
• Vor allem bei verschachtelten Abfragen sollten BEGIN...END immer benutzt und durch Einrückung den Zusammenhang deutlich machen.

Schleifen

Die WHILE-Schleife prüft eine Bedingung und wird so lange durchlaufen, wie diese Bedingung wahr ist:

WHILE <bedingung> 
BEGIN <anweisungen> END

Dabei sind folgende Punkte wichtig:

• Bei <bedingung> handelt es sich um eine einfache Prüfung, die nicht mit AND oder OR erweitert werden kann.
• Wenn ein SELECT Teil der <bedingung> ist, muss es in Klammern stehen.
• Die <bedingung> wird jeweils am Anfang eines Durchgangs geprüft. Wenn ihr Wert von Anfang an FALSE ist, wird die Schleife überhaupt nicht durchlaufen.
• Schleifen können verschachtelt werden. Vor allem dann sollten BEGIN...END immer benutzt und durch Einrückung den Zusammenhang deutlich machen.

Eine Schleife oder ein IF-Zweig kann mit BREAK vorzeitig abgebrochen; mit CONTINUE kann direkt der nächste Durchlauf begonnen werden. Bei Verschachtelung wird mit BREAK zur nächsthöheren Ebene gesprungen.

Um alle Datensätze einer Ergebnismenge, also eines SELECT-Befehls zu durchlaufen, wird ein CURSOR benötigt. Dies wird in diesem Buch nicht behandelt.

Zulässige Befehle

Einige Anweisungen können nicht in einer Routine verwendet werden. Dazu gehören vor allem der Wechsel der aktuellen Datenbank sowie CREATE/ALTER für Views, Routinen und Trigger. Im Gegensatz zu anderen DBMS ist aber z. B. CREATE TABLE und unter Umständen auch COMMIT bzw. ROLLBACK möglich.

Anweisungen begrenzen

Bei MS-SQL gibt es keine Notwendigkeit, zwischen Anweisungen innerhalb einer Routine und getrennten SQL-Befehlen zu unterscheiden: Jede Anweisung wird mit Semikolon abgeschlossen; ein „selbständiger“ SQL-Befehl wird durch GO abgeschlossen und ausgeführt.

SQL-Programmierung mit MySQL

Einzelheiten

MySQL hat gespeicherte Prozeduren und Funktionen erst mit Version 5 eingeführt, sodass noch nicht alle Wünsche erfüllt werden. Aber das wird natürlich von Version zu Version besser.

Bitte achten Sie unbedingt darauf, dass sich die Namen von Parametern und Variablen von Spaltennamen unterscheiden, die in derselben Anweisung vorkommen. Wenn Sie gegen diese Bedingung verstoßen, gibt es unvorhersehbare Ergebnisse, weil die Namen der Variablen Vorrang haben gegenüber gleichnamigen Spalten. Ein gängiges Verfahren ist es (wie bei MS-SQL), dass diese Namen mit '@' beginnen.

Parameter deklarieren

Jeder Parameter, der innerhalb der Anweisungen benutzt wird und dessen Wert an die Routine übergeben oder durch die Bearbeitung zurückgegeben wird, muss im Kopf der Routine festgelegt werden: Name, Datentyp, Verwendung für Eingabe und/oder Ausgabe, Vorgabe- oder Anfangswert. Mehrere Parameter werden mit Komma verbunden; nach dem letzten Parameter fehlt dies.

Bei Funktionen kann es nur Eingabe-Parameter geben; der Ausgabe-Parameter wird durch RETURNS immer getrennt angeben.

Ein einzelner Parameter wird so deklariert:

[ IN | OUT | INOUT ] <name> <typ>

Die Festlegung als Eingabe-Parameter kann entfallen; IN ist der Standardwert.

Variablen deklarieren

Jede Variable, die innerhalb der Routine benutzt wird, muss in ihrem Rumpf festgelegt werden: Name, Datentyp, Vorgabe- oder Anfangswert. Jede Deklaration gilt als eine einzelne Anweisung und ist mit Semikolon abzuschließen.

DECLARE <name> <typ> [ DEFAULT <wert> ];

Als Vorgabewert ist auch ein SQL-Ausdruck möglich.

Zuweisungen von Werten zu Variablen und Parameter

Der einfachste Weg ist die direkte Zuweisung eines Wertes oder eines Ausdrucks (einer internen oder einer eigenen Funktion) zu einer Variablen oder einem Parameter mit dem SET-Befehl:

SET <name> = <ausdruck> ;

Sehr oft werden die Werte aus einem SELECT mit Variablen weiterverarbeitet. Dazu gibt es die INTO-Klausel, die direkt nach der <spaltenliste> kommt. Die Liste der Variablen muss von Anzahl und Typ her der Liste der Spalten entsprechen.

SELECT <spaltenliste> INTO <variablenliste> 
  FROM <usw. alles andere> ;

Eine Prozedur wird mit CALL ausgeführt. Rückgabewerte, die über OUT-Parameter bestimmt werden, werden vorher deklariert.

declare @inputvariable varchar(25);
SET @inputvariable = 'Meier';
declare @outputvariable MONEY = 0;
CALL myprocedure (@inputvariable, @outputvariable);
SELECT @outputvariable;

Jede hier genannte Variable muss (in Reihenfolge und Typ) einem der <ausgabe-parameter> der Prozedur entsprechen.

Verzweigungen

MySQL kennt zwei Arten, um auf unterschiedliche Bedingungen zu reagieren:

Die IF-Abfrage steuert den Ablauf nach Bedingungen:

IF <bedingung> THEN 
BEGIN 
  <anweisungen>
END
[ ELSEIF <bedingung> THEN
  BEGIN 
    <anweisungen>
  END ]
[ ELSE
  BEGIN 
    <anweisungen>
  END ]
END IF

Diese Abfrage sieht so aus:

• Bei <bedingung> handelt es sich um eine einfache Prüfung, die nicht mit AND oder OR erweitert werden kann.
• Der ELSE-Zweig ist optional. Es ist auch möglich, dass nur der IF-THEN-Abschnitt ausgeführt werden muss.
• Durch ELSEIF-Zweige sind Verschachtelungen, also auch weitere Prüfungen möglich.
• Vor allem bei verschachtelten Abfragen sollten BEGIN...END immer benutzt und durch Einrückungen der Zusammenhang deutlich gemacht werden.

Die CASE-Prüfung entspricht der Fallunterscheidung aus dem Kapitel Nützliche Erweiterungen:

-- Variante 1
CASE <ausdruck>
    WHEN <ausdruck 1>  THEN BEGIN <anweisungen 1> END
  [ WHEN <ausdruck 1>  THEN BEGIN <anweisungen 2> END ]  /* usw. */
  [ ELSE                    BEGIN <anweisungen n> END ]
END CASE
-- Variante 2
CASE
    WHEN <bedingung 1> THEN BEGIN <anweisungen 1> END
  [ WHEN <bedingung 2> THEN BEGIN <anweisungen 2> END ]  /* usw. */
  [ ELSE                    BEGIN <anweisungen n> END ]
END CASE

Bei dieser Prüfung gelten die gleichen Prüfungen wie bei der Fallunterscheidung mit folgenden Abweichungen:

• Im ELSE-Zweig darf es keine NULL-Anweisung geben.
• Die Verzweigung muss mit END CASE abgeschlossen werden.

Schleifen

MySQL kennt mehrere Arten von Schleifen, aber keine FOR-Schleife. Wenn ITERATE oder LEAVE verwendet werden, müssen LABELs gesetzt werden.

Die LOOP-Schleife arbeitet ohne Bedingung am Anfang oder Ende und wird solange durchlaufen, bis – vor allem aufgrund einer „inneren“ Bedingung – die LEAVE-Anweisung getroffen wird. Mit ITERATE wird direkt der nächste Durchgang gestartet.

LOOP 
BEGIN <anweisungen> END
END LOOP ;

Die REPEAT-Schleife wird mindestens einmal durchlaufen, und zwar solange, bis die Ende-Bedingung FALSE ergibt oder bis – aufgrund einer „inneren“ Bedingung – die LEAVE-Anweisung getroffen wird.

REPEAT
BEGIN <anweisungen> END
UNTIL <bedingung>
END REPEAT

Die WHILE-Schleife prüft eine Bedingung und wird so lange durchlaufen, wie diese Bedingung wahr ist:

WHILE <bedingung> DO
BEGIN <anweisungen> END
END WHILE

Die <bedingung> wird jeweils am Anfang eines Durchgangs geprüft. Wenn ihr Wert von Anfang an FALSE ist, wird die Schleife überhaupt nicht durchlaufen.

Um alle Datensätze einer Ergebnismenge, also eines SELECT-Befehls zu durchlaufen, wird ein CURSOR benötigt. Dies wird in diesem Buch nicht behandelt.

Zulässige Befehle

Innerhalb von eigenen Funktionen, Prozeduren und Triggern sind nicht alle SQL-Befehle zulässig. Bitte lesen Sie in Ihrer Dokumentation Einzelheiten nach.

Anweisungen begrenzen

Jede einzelne Anweisung innerhalb einer Routine und jeder SQL-Befehl müssen mit einem Semikolon abgeschlossen werden. Das ist kein Problem, wenn nur ein einzelner CREATE PROCEDURE o. ä. ausgeführt werden soll; dann wird das abschließende Semikolon weggelassen, und es gibt keine Unklarheiten (siehe ein MySQL-Beispiel zu Trigger).

Wenn mehrere Routinen nacheinander erzeugt werden, muss das DBMS zwischen den verschiedenen Arten von Abschlusszeichen unterscheiden. Dazu dient der delimiter-Befehl:

MySQL-Version

delimiter //
 
CREATE OR ALTER TRIGGER Mitarbeiter_BD 
  ACTIVE BEFORE DELETE ON Mitarbeiter
FOR EACH ROW
BEGIN
  UPDATE Dienstwagen
     SET Mitarbeiter_ID = NULL
   WHERE Mitarbeiter_ID = old.ID;
END
//
 
delimiter ;

Zuerst wird der Begrenzer für SQL-Befehle auf '//' geändert; das Abschlusszeichen für einzelne Anweisungen bleibt das Semikolon. Dann folgen alle Befehle zur Trigger-Definition; jeder einzelne wird mit dem neuen Begrenzer beendet. Abschließend wird der Begrenzer wieder auf das Semikolon zurückgesetzt.

SQL-Programmierung mit Oracle

Einzelheiten

Bei Oracle gilt das prozedurale SQL nicht als Teil des DBMS, sondern als spezielle Programmiersprache PL/SQL. In der Praxis wird beides gemischt verwendet.

Bitte achten Sie unbedingt darauf, dass sich die Namen von Parametern und Variablen von Spaltennamen unterscheiden, die in derselben Routine vorkommen.

Parameter deklarieren

Jeder Parameter, dessen Wert an die Routine übergeben oder durch die Bearbeitung zurückgegeben wird, muss im Kopf der Routine festgelegt werden: Name, Datentyp, Verwendung für Eingabe und/oder Ausgabe, Vorgabe- oder Anfangswert. Mehrere Parameter werden mit Komma verbunden; nach dem letzten Parameter fehlt dies.

Bei Funktionen kann es nur Eingabe-Parameter geben; der Ausgabe-Parameter wird durch RETURN (nicht wie sonst oft durch RETURNS) getrennt angegeben.

Ein einzelner Parameter wird so deklariert:

<name> [ IN | OUT | IN OUT ] <typ>

Die Festlegung als Eingabe-Parameter kann entfallen; IN ist der Standardwert. Beim Datentyp darf die Größe nicht angegeben werden: VARCHAR2 ist zulässig, aber VARCHAR2(20) nicht.

Variablen deklarieren

Jede Variable, die innerhalb der Routine benutzt wird, muss in ihrem Kopf festgelegt werden, nämlich zwischen AS/IS und BEGIN: Name, Datentyp und ggf. Vorgabewert; das Schlüsselwort DECLARE entfällt. Jede Deklaration gilt als eine einzelne Anweisung und ist mit Semikolon abzuschließen.

<name> <typ> [ := <ausdruck> ] ; 

Als Vorgabewert ist auch ein SQL-Ausdruck möglich.

Zuweisungen von Werten zu Variablen und Parameter

Der einfachste Weg ist die direkte Zuweisung eines Wertes oder eines Ausdrucks (einer internen oder einer eigenen Funktion) zu einer Variablen oder einem Parameter:

<name> := <ausdruck> ;

Bitte beachten Sie, dass (wie bei Pascal) Doppelpunkt und Gleichheitszeichen zu verwenden sind.

Sehr oft werden die Werte aus einem SELECT-Befehl mit Variablen weiterverarbeitet. Dazu gibt es die INTO-Klausel, die direkt nach der <spaltenliste> kommt:

SELECT <spaltenliste> INTO <variablenliste> 
  FROM <usw. alles andere> ;

Die Liste der Variablen muss von Anzahl und Typ her der Liste der Spalten entsprechen.

In ähnlicher Weise kann auch das Ergebnis einer Prozedur übernommen und in der aktuellen Routine verarbeitet werden:

1. Deklaration der Übergabevariablen:
   • direkt:
     <variablenname> <typ>;
   • oder unter Bezug auf den Typ einer definierten Variablen:
     <variablenname> <package_name.typ_name>;
2. Aufruf der Prozedur:
   <prozedur_name> ( <Eingabe_Parameter>, <Ausgabe_Parameter> );
3. Weiterarbeiten mit den Ausgabeparametern aus der Prozedur

Jede hier genannte Variable muss (in Reihenfolge und Typ) einem der <parameter> der Prozedur entsprechen.

Verzweigungen

Oracle kennt zwei Arten, um auf unterschiedliche Bedingungen zu reagieren:

Die IF-Abfrage steuert den Ablauf nach Bedingungen:

IF <bedingung> THEN 
BEGIN <anweisungen> END
[ ELSE 
  BEGIN <anweisungen> END 
]
END IF

Diese Abfrage sieht so aus:

• Bei <bedingung> handelt es sich um eine einfache Prüfung, die nicht mit AND oder OR erweitert werden kann.
• Der ELSE-Zweig ist optional. Es ist auch möglich, dass nur der IF-THEN-Abschnitt ausgeführt werden muss.
• Durch ELSIF-Zweige sind auch Verschachtelungen möglich.
• Vor allem bei verschachtelten Abfragen sollten BEGIN...END immer benutzt und durch Einrückungen der Zusammenhang deutlich gemacht werden.

Die CASE-Prüfung entspricht der Fallunterscheidung aus dem Kapitel Nützlichen Erweiterungen:

-- Variante 1
CASE <ausdruck>
    WHEN <ausdruck 1>  THEN BEGIN <anweisungen 1> END
  [ WHEN <ausdruck 1>  THEN BEGIN <anweisungen 2> END ]  /* usw. */
  [ ELSE                    BEGIN <anweisungen n> END ]
END CASE
-- Variante 2
CASE
    WHEN <bedingung 1> THEN BEGIN <anweisungen 1> END
  [ WHEN <bedingung 2> THEN BEGIN <anweisungen 2> END ]  /* usw. */
  [ ELSE                    BEGIN <anweisungen n> END ]
END CASE

Bei dieser Prüfung gelten die gleichen Prüfungen wie bei der Fallunterscheidung mit folgenden Abweichungen:

• Im ELSE-Zweig darf es keine NULL-Anweisung geben.
• Die Verzweigung muss mit END CASE abgeschlossen werden.

Schleifen

Oracle kennt mehrere Arten von Schleifen.

Die LOOP-Schleife arbeitet ohne Bedingung am Anfang oder Ende und wird solange durchlaufen, bis – vor allem aufgrund einer „inneren“ Bedingung – die EXIT-Anweisung getroffen wird.

LOOP 
BEGIN <anweisungen> END
END LOOP ;

Oracle-Version

DECLARE x NUMBER := 1;
BEGIN
  LOOP
     X := X + 1;
     EXIT WHEN x > 10;
  END LOOP;
END;

Die WHILE-Schleife prüft eine Bedingung und wird so lange durchlaufen, wie diese Bedingung wahr ist:

WHILE <bedingung> LOOP
BEGIN <anweisungen> END
END LOOP;

Die <bedingung> wird jeweils am Anfang eines Durchgangs geprüft. Wenn ihr Wert von Anfang an FALSE ist, wird die Schleife überhaupt nicht durchlaufen.

Die FOR-Schleife durchläuft eine vorgegebene Liste von Werten von Anfang bis Ende:

FOR <variable> IN <werteliste> LOOP
BEGIN <anweisungen> END
END LOOP;

Bei der FOR-Schleife wird der Wert der „Laufvariablen“ am Beginn jedes weiteren Durchlaufs automatisch erhöht; bei LOOP und WHILE müssen Sie sich selbst um die Änderung einer solchen Variablen kümmern.

Um alle Datensätze einer Ergebnismenge, also eines SELECT-Befehls zu durchlaufen, wird ein CURSOR benötigt. Dies wird in diesem Buch nicht behandelt.

Zulässige Befehle

Neben Anweisungen und den DML-Befehlen sind auch TCL-Befehle (Steuerung von Transaktionen) sowie die Sperre von Tabellen durch LOCK TABLE möglich.

Anweisungen begrenzen

Jede einzelne Anweisung innerhalb einer Routine und jeder SQL-Befehl müssen mit einem Semikolon abgeschlossen werden, auch das abschließende END. Das ist kein Problem, wenn nur ein einzelner CREATE PROCEDURE o. ä. ausgeführt werden soll. Andernfalls folgt in einer eigenen Zeile ein einzelner Schrägstrich:

BEGIN
  <anweisungen>
END;
/

Zusammenfassung

In diesem Kapitel wurden die wichtigsten Bestandteile besprochen, mit denen SQL-Befehle in eigenen Funktionen, in gespeicherten Prozeduren oder in Triggern verarbeitet werden:

• Deklaration von Parametern und Variablen
• Verwendung von Parametern und Variablen
• Verzweigungen mit IF u. a. sowie Schleifen
• Zulässigkeit von DML- und DDL-Befehlen
• Trennung zwischen einer Routine insgesamt und einer einzelnen Anweisung

Bei allen Einzelheiten müssen die Besonderheiten eines jeden DBMS beachtet werden.

Übungen

Übung 1

Erklärungen

Zur Lösung

1. Erläutern Sie den Zweck einer (benutzerdefinierten) Funktion.
2. Erläutern Sie den Zweck einer (gespeicherten) Prozedur.
3. Erläutern Sie den Zweck eines Triggers.
4. Worin unterscheiden sich „Eigene Funktionen“ und Prozeduren?
5. Worin unterscheiden sich Prozeduren und Trigger?

Übung 2

Der Kopf einer Routine

Zur Lösung

Erläutern Sie die folgenden Bestandteile aus dem „Kopf“ (header) einer Routine, d. h. einer Funktion oder einer Prozedur. Erwähnen Sie auch, was zu diesen Bestandteilen gehört.

1. CREATE OR ALTER bzw. CREATE OR REPLACE
2. <Parameterliste>
3. Eingabe-Parameter
4. Ausgabe-Parameter
5. RETURNS bzw. RETURN

Übung 3

Der Rumpf einer Routine

Zur Lösung

Erläutern Sie die folgenden Bestandteile aus dem „Rumpf“ (body) einer Routine, d. h. einer Funktion oder einer Prozedur. Erwähnen Sie auch, was zu diesen Bestandteilen gehört; die Antworten können sehr knapp ausfallen, weil sie sich in Abhängigkeit vom DBMS sehr unterscheiden müssten.

1. BEGIN und END am Anfang und Ende des Rumpfes
2. BEGIN und END innerhalb des Rumpfes
3. Variablen
4. Verzweigungen
5. Schleifen
6. Zuweisung von Werten

Lösungen

Lösung zu Übung 1

Erklärungen

Zur Übung

1. Eine benutzerdefinierte Funktion ist eine Ergänzung zu den internen Funktionen des DBMS und liefert immer einen bestimmten Wert zurück.
2. Eine gespeicherte Prozedur (StoredProcedure, SP) ist ein Arbeitsablauf, der fest innerhalb der Datenbank gespeichert ist und Aufgaben ausführt, die innerhalb der Datenbank erledigt werden sollen – im wesentlichen ohne „Kommunikation“ mit dem Benutzer bzw. dem auslösenden Programm.
3. Ein Trigger ist ein Arbeitsablauf, der fest innerhalb der Datenbank gespeichert ist und Aufgaben ausführt, die automatisch beim Speichern innerhalb der Datenbank erledigt werden sollen.
4. Eine Funktion liefert genau einen Wert zurück, der durch RETURNS festgelegt wird. Prozeduren gibt es mit und ohne Rückgabewert.
5. Ein Trigger wird automatisch bei einem Speichern-Befehl ausgeführt, und es gibt keinen Rückgabewert. Eine Prozedur wird bewusst aufgerufen, und sie kann Rückgabewerte liefern.

Lösung zu Übung 2

Der Kopf einer Routine

Zur Übung

1. Dies definiert eine Routine. Dazu gehören einer der Begriffe FUNCTION und PROCEDURE sowie der Name der Routine.
2. Die Parameterliste enthält diejenigen Parameter, die beim Aufruf an die Routine übergeben werden (Eingabe-Parameter), sowie diejenigen, die nach Erledigung an den Aufrufer zurückgegeben werden (Ausgabe-Parameter). Wie Eingabe- und Ausgabe-Parameter gekennzeichnet bzw. unterschieden werden, hängt vom DBMS ab.
3. Alle Eingabe-Parameter sind (durch Komma getrennt) aufzuführen; die Reihenfolge muss beim Aufruf beachtet werden. Zu jedem Parameter gehören Name und Datentyp; unter Umständen ist ein Standardwert möglich.
4. Alle Ausgabe-Parameter sind (durch Komma getrennt) aufzuführen; die Reihenfolge muss bei der Auswertung im aufrufenden Programm o. ä. beachtet werden. Zu jedem Parameter gehören Name und Datentyp; unter Umständen ist ein Standardwert möglich.
5. RETURNS bzw. RETURN gibt bei Funktionen den Ausgabe-Parameter, d. h. das Ergebnis der Funktion an. Nur Firebird benutzt dies, um auch die Ausgabe-Parameter einer Prozedur anzugeben.

Lösung zu Übung 3

Der Rumpf einer Routine

Zur Übung

1. Dies beschreibt Anfang und Ende des gesamten Rumpfes. Wenn der Rumpf nur einen einzelnen Befehl enthält, kann es entfallen.
2. Dies begrenzt einzelne Abschnitte innerhalb des Rumpfes, vor allem bei Schleifen und IF-Verzweigungen. Wenn der Abschnitt nur einen einzelnen Befehl enthält, kann BEGIN/END entfallen; die Verwendung wird aber stets empfohlen.
3. Dies definiert Werte, die (nur) innerhalb der Routine benutzt werden. Sie müssen mit Namen und Datentyp ausdrücklich deklariert werden.
4. Mit IF-ELSE o. ä. können – abhängig von Bedingungen – unterschiedliche Befehle ausgeführt werden.
5. Mit WHILE, FOR o. a. können einzelne Befehle oder Abschnitte von Befehlen wiederholt ausgeführt werden. Wie oft bzw. wie lange die Schleife durchlaufen wird, hängt von der Art der Schleife (unterschiedlich nach DBMS) und Bedingungen ab.
6. Den Variablen und Parametern können Werte zugewiesen werden, und zwar durch das Gleichheitszeichen mit konkreten Angaben oder als Ergebnis von Funktionen, Prozeduren oder SELECT-Befehlen.

Siehe auch

Verschiedene Einzelheiten stehen in den folgenden Kapiteln:

• SQL-Ausdruck
• Funktionen und Funktionen (2)
• Nützliche Erweiterungen

Zur SQL-Programmierung mit Oracle gibt es das (unvollständige) Wikibook PL/SQL.