Einführung in SQL: Programmierung

Aus Wikibooks

Wechseln zu: Navigation, Suche


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

Inhaltsverzeichnis

[Bearbeiten] 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 wiederum 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.

[Bearbeiten] 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, die unter Funktionen sowie Funktionen (2) behandelt werden.

[Bearbeiten] Prozeduren

Eine Prozedur – gespeicherte Prozedur, engl. StoredProcedure (SP) – ist vorgesehen für Arbeitsabläufe, die "immer wiederkehrende" Arbeiten ausführen sollen. Es gibt sie mit und ohne Argumente und Rückgabewerte.

  • Einzelheiten dazu werden in Prozeduren behandelt.

[Bearbeiten] Trigger

Ein Trigger ist ein Arbeitsablauf, der automatisch beim Speichern in einer Tabelle ausgeführt wird.

  • Einzelheiten dazu werden in Trigger behandelt.

[Bearbeiten] 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.

Unter Prozeduren gibt es ein Beispiel "Testdaten in einer Tabelle erzeugen"; dies kann auch so verwirklicht werden:

Firebird-Version
Crystal Clear app terminal.png SQL-Quelltext:

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


[Bearbeiten] Programmieren innerhalb von Routinen

Attention green.svg

 Hinweis an Autoren betr. SQL-Dialekte
Die Hinweise zu MS-SQL, MySql, Oracle wurden nur nach der Dokumentation verfasst. Sie müssen genauso kontrolliert werden wie die Beispiele in den folgenden Kapiteln. Nach Erledigung durch einen Fachmann kann der einzelne Hinweis hier entfernt werden; nach Erledigung für alle DBMS kann der Hinweis insgesamt gelöscht werden.

Attention green.svg

 Hinweis an Autoren betr. "Labels"
Bei den Verzweigungen und Schleifen gibt es die Möglichkeit, durch Labels den Zusammenhang deutlich zu machen. Ich Juetho sehe die Notwendigkeit dazu nicht und habe darauf verzichtet. Wenn es jemand anders sieht, bitte einfach ändern und einfügen.

[Bearbeiten] Allgemeine Hinweise

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 – siehe Trigger. 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 dies zur Trennung der Ausgabe-Parameter auch bei Prozeduren.
  • 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.

Hinweis: 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.

Hinweis: In diesem Abschnitt beschränke ich mich auf die wichtigsten Erläuterungen. Die konkreten SQL-Anweisungen sind in den einzelnen Kapiteln für Eigene Funktionen, Prozeduren und Trigger zu finden. Außerdem gibt es zu fast allen genannten Themen weitere Möglichkeiten.

Attention green.svg

 Hinweis
Wir empfehlen, dieses Kapitel und jeweils eines der nächsten Kapitel in zwei Fenstern Ihres Browsers gleichzeitig zu öffnen und diese nebeneinander zu setzen. Dann können Sie ein umfangreiches Beispiel und die folgenden Einzelheiten je nach SQL-Dialekt gleichzeitig sehen.


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:

  • 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.

[Bearbeiten] 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, 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.

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.


Variablen deklarieren

Jede Variable, die innerhalb der Anweisungen benutzt wird, muss im Kopf der Routine 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 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> ;      /* 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> ;         /* 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 die SUSPEND bei jeder dieser Zeilen stehen. Ein Beispiel steht unter Prozeduren: 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 sowie OR und weiteren Klammern verschachtelt werden.
  • Der ELSE-Zweig ist optional. Es ist auch möglich, dass nur der IF-THEN-Abschnitt ausgeführt werden muss.
  • 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 gemacht 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 sowie OR und 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 in 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. Jegliche andere Befehle werden nicht akzeptiert, also vor allem DDL-Befehle, aber auch GRANT/REVOKE (DCL) und 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 unter Trigger.

Wenn – wie im Skript zur Beispieldatenbank – mehrere Routinen nacheinander erzeugt werden, muss das DBMS zwischen den verschiedenen Arten von Abschlusszeichen unterscheiden. Dazu dient der SET TERM-Befehl (TERM steht für Terminator):

Crystal Clear app terminal.png SQL-Quelltext:

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 wird mit dem neuen Begrenzer beendet. Abschließend wird der Begrenzer wieder auf das Semikolon zurückgesetzt.

 

[Bearbeiten] SQL-Programmierung mit MS-SQL

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, 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:

<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 Anweisungen benutzt wird, muss im Rumpf der Routine 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 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-Befehl mit Variablen weiterverarbeitet...

Aber wie?

Bitte beachten Sie: In anderen DBMS wird hierfür die INTO-Klausel verwendet; diese hat bei MS-SQL eine völlig andere Bedeutung.

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

Aber wie?

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


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-Befehl Teil der <bedingung> ist, muss er 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ückungen der Zusammenhang deutlich gemacht werden.

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-Befehl Teil der <bedingung> ist, muss er 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ückungen der Zusammenhang deutlich gemacht werden.

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 dieser "Einführung" nicht behandelt.


Zulässige Befehle

Welche Einschränkungen hinsichtlich DDL oder so gibt es?


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.

 

[Bearbeiten] SQL-Programmierung mit MySql

Einzelheiten

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.

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 Anweisungen benutzt wird, muss im Rumpf der Routine 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-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:

CALL <routine-name> ( [ <eingabe-parameter> ] )     /* und wie weiter? */

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 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.

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 werden.

LOOP 
BEGIN <anweisungen> END
END LOOP ;

Gibt es ein praxisnahes, nicht konstruiertes Beispiel?

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.

Gibt es kein Verfahren, um die Ergebnismenge eines SELECT zeilenweise zu verarbeiten?


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 unter Trigger.

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

Crystal Clear app terminal.png SQL-Quelltext:

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.

 

[Bearbeiten] SQL-Programmierung mit Oracle

Einzelheiten

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

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 RETURN (anders als bei anderen DBMS nicht durch RETURNS) immer getrennt angeben.

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 Anweisungen benutzt wird, muss im Kopf der Routine festgelegt werden, nämlich zwischen AS/IS und BEGIN: Name, Datentyp; das Schlüsselwort DECLARE entfällt. Jede Deklaration gilt als eine einzelne Anweisung und ist mit Semikolon abzuschließen.

<name> <typ>;   /* ist ein Vorgabewert überhaupt möglich? */

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:

Aber wie?

Jede hier genannte Variable muss (in Reihenfolge und Typ) einem der <ausgabe-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 ELSE IF-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 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

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-Beispiel
Crystal Clear app terminal.png SQL-Quelltext:

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 dieser 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 dieser "Einführung" nicht behandelt.


Zulässige Befehle

Welche Einschränkungen hinsichtlich DDL oder so gibt es?


Anweisungen begrenzen

Welche Notwendigkeit und/oder Maßnahmen wie SET TERM (bei Firebird) oder DELIMITER (bei MySql) oder so gibt es?

 


[Bearbeiten] 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.


[Bearbeiten] Übungen


Von „http://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_Programmierung“
Persönliche Werkzeuge