Einführung in SQL: Eigene Funktionen

Aus Wikibooks

Auch wenn ein DBMS viele Funktionen zur Verfügung stellt, kommt man in der Praxis immer wieder einmal zu weiteren Wünschen und Anforderungen. Dafür kann ein Benutzer eigene Funktionen definieren und dem DBMS bekannt geben oder in einer Datenbank speichern. Einmal definiert, erspart dies künftig, die gleiche Routine immer neu zu erstellen; stattdessen wird die Funktion aufgerufen und liefert den Rückgabewert.

Firebird hat solche Funktionen erst für Version 3 angekündigt. Zurzeit kann eine Funktion nur als UDF (user-defined function) aus einer externen DLL, die mit einer Programmiersprache erstellt wurde, eingebunden werden.

	Hinweis an Autoren betr. SQL-Dialekte Die Beispiele zu MySql, Oracle wurden nur nach der Dokumentation verfasst; ein Beispiel für MS-SQL fehlt. Sie müssen genauso kontrolliert werden wie die Hinweise unter Programmierung. 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.

[Bearbeiten] Funktion definieren

[Bearbeiten] Funktion erstellen

Mit CREATE FUNCTION in der folgenden Syntax (vereinfachte Version des SQL-Standards) wird eine Funktion definiert:

CREATE FUNCTION <routine-name>
       ( [ <parameterliste> ] )
       RETURNS <datentyp>
       [ <characteristics> ]
       <routine body>

Der <routine-name> der Funktion muss innerhalb eines gewissen Bereichs ("Schema" genannt) eindeutig sein und darf auch nicht als Name einer Prozedur verwendet werden. Teilweise wird verlangt, dass der Name der Datenbank ausdrücklich angegeben wird. Es empfiehlt sich, keinen Namen einer eingebauten Funktion zu verwenden.

Die Klammern sind erforderlich und kennzeichnen eine Routine. Eingabeparameter können vorhanden sein, müssen es aber nicht. Mehrere Parameter werden durch Kommata getrennt, der einzelne <parameter> wird wie folgt definiert:

[ IN ] <parameter-name> <datentyp>

Der <parameter-name> muss innerhalb der Funktion eindeutig sein. Der Zusatz "IN" kann entfallen, weil es bei Funktionen nur Eingabe-Parameter (keine Ausgabe-Parameter) gibt.

Der RETURNS-Parameter ist wesentlich und kennzeichnet eine Funktion.

Der <datentyp> sowohl für den RETURNS-Parameter als auch für die Eingabe-Parameter muss einer der Datentypen des DBMS sein.

Für <characteristics> gibt es diverse Festlegungen, wie die Funktion arbeiten soll, z.B. durch LANGUAGE mit der benutzten Programmiersprache.

<routine body> kennzeichnet den eigentlichen Arbeitsablauf, also die Schritte, die innerhalb der Routine ausgeführt werden sollen. Bei diesen Befehlen handelt es sich um "normale" SQL-Befehle, die mit Bestandteilen der SQL-Programmiersprache verbunden werden. Zum Semikolon, das den Abschluss des CREATE-Befehls darstellen sollte, aber innerhalb des Inhalts bereits für jede einzelne Anweisung benutzt wird, beachten Sie bitte auch die Hinweise unter Anweisungen begrenzen.

Bei einer Funktion muss – beispielsweise durch eine RETURN-Anweisung – der gesuchte Rückgabewert ausdrücklich festgelegt werden. In der Regel werden die Befehle durch BEGIN ... END zusammengefasst; bei einem einzelnen Befehl ist dies nicht erforderlich.

[Bearbeiten] Funktion ausführen

Eine benutzerdefinierte Funktion kann wie jede interne Funktion des DBMS benutzt werden. Sie kann an jeder Stelle benutzt werden, an der ein einzelner Wert erwartet wird, wie in den Beispielen zu sehen ist.

Hoch zum Seitenanfang

[Bearbeiten] Funktion ändern oder löschen

Mit ALTER FUNCTION wird die Definition einer Funktion geändert. Dafür gilt die gleiche Syntax:

ALTER  FUNCTION <routine-name>
       ( [ <parameterliste> ] )
       RETURNS <datentyp>
       [ <characteristics> ]
       <routine body>

Mit DROP FUNCTION wird die Definition einer Funktion gelöscht.

DROP FUNCTION <routine-name>;

Hoch zum Seitenanfang

[Bearbeiten] Beispiele

	Hinweis Wir empfehlen, dieses Kapitel sowie das Kapitel Programmierung in zwei Fenstern Ihres Browsers gleichzeitig zu öffnen und diese nebeneinander zu setzen. Dann können Sie ein umfangreiches Beispiel sowie die passenden Einzelheiten je nach SQL-Dialekt gleichzeitig sehen.

[Bearbeiten] Einfache Bestimmung eines Wertes

Das folgende Beispiel erstellt den Rückgabewert direkt aus der Eingabe.

Zur Begrüßung wird ein Name mit einem Standardtext, der von der Tageszeit abhängt, verbunden.

Definition einer Funktion nach MySql
SQL-Quelltext:

CREATE FUNCTION Hello (s CHAR(20)) 
  RETURNS CHAR(50)
  RETURN WHEN
         CASE CURRENT_TIME < '12:00' THEN 'Guten Morgen, ' || s || '!'
         CASE CURRENT_TIME < '18:00' THEN 'Guten Tag, '    || s || '!'
         ELSE                             'Guten Abend, '  || s || '!'
         END;

Bei diesem Beispiel kann auf BEGIN ... END verzichtet werden, weil der "Rumpf" der Funktion mit RETURN nur eine einzige Anweisung – unter Benutzung des WHEN-Ausdrucks – enthält.

Eine solche Funktion kann dann wie jede eingebaute Funktion benutzt werden.

SQL-Quelltext:

SELECT Hello('Juetho');

SQL-Ausgabe:

'Guten Abend, Juetho!'

Hoch zum Seitenanfang

[Bearbeiten] Text in Anführungszeichen einschließen

Im folgenden Beispiel werden mehr Maßnahmen benötigt, bis der RETURN-Wert feststeht. (Diese Maßnahme wird oft für den Export von Daten aus einem System für ein anderes benötigt.)

Ein gegebener Text soll in Anführungszeichen eingeschlossen werden; Gänsefüßchen innerhalb des Textes müssen verdoppelt werden.

Definition einer Funktion nach MySql
SQL-Quelltext:

CREATE FUNCTION Quoting 
          ( instring  VARCHAR(80) )
  RETURNS ( VARCHAR(82) )
AS 
BEGIN
  IF (instring CONTAINING('"')) THEN BEGIN
    instring = REPLACE( instring, '"', '""' );
  END
  RETURN CONCAT( '"', instring, '"');
END

Diese Funktion kann direkt aufgerufen werden:

SQL-Quelltext:

SELECT Quoting('Schulze'), Quoting('Restaurant "Abendrot"') [FROM rdb$database];

SQL-Ausgabe:

"Schulze"   "Restaurant ""Abendrot"""

Hoch zum Seitenanfang

[Bearbeiten] Anzahl der Mitarbeiter einer Abteilung

Bei der folgenden Funktion werden zunächst weitere Informationen benötigt.

Suche zu einer Abteilung (angegeben durch das Kuerzel) die Anzahl der Mitarbeiter.

Definition einer Funktion nach Oracle
SQL-Quelltext:

CREATE OR REPLACE FUNCTION AnzahlMitarbeiter
         ( abt CHAR(4) )
  RETURN ( INTEGER )
AS 
  anzahl INTEGER;
BEGIN
  SELECT COUNT(*) INTO anzahl
    FROM Mitarbeiter mi
         JOIN Abteilung ab ON ab.ID = mi.Abteilung_ID
   WHERE ab.Kuerzel = abt;
  RETURN anzahl;
END

Damit erhalten wir die Anzahl der Mitarbeiter einer bestimmten Abteilung:

SQL-Quelltext:

SELECT AnzahlMitarbeiter('Vert')    [FROM rdb$database] ;

SQL-Ausgabe:

AnzahlMitarbeiter :  4

Hoch zum Seitenanfang

[Bearbeiten] Zusammenfassung

In diesem Kapitel lernten wir die Grundregeln für die Erstellung eigener Funktionen kennen:

Benutzerdefinierte Funktionen sind immer Skalarfunktionen, die genau einen Wert zurückgeben.
Der Datentyp des Rückgabewerts ist in der RETURNS-Klausel anzugeben, der Wert selbst durch eine RETURN-Anweisung.
Komplexe Maßnahmen müssen in BEGIN ... END eingeschlossen werden; eine Funktion kann aber auch nur aus der RETURN-Anweisung bestehen.

Hoch zum Seitenanfang