Eigene Funktionen

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.

(Einführung in SQL: Eigene Funktionen)

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.

Bitte haben Sie Nachsicht: Wegen der vielen Varianten bei den DBMS wurde ein Teil der Hinweise und Beispiele auch in diesem Kapitel 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.

Funktion definieren

Benutzerdefinierte Funktionen geben (ebenso wie die eigenen Funktionen des DBMS) genau einen Wert zurück, sind also Skalarfunktionen.

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 SQL-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 für jede einzelne Anweisung benutzt wird, beachten Sie bitte auch die Hinweise zur SQL-Programmierung 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.

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.

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

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.

Einfache Bestimmung eines Wertes

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

Aufgabe

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

MySQL-Version

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

Bei diesem Beispiel kann auf BEGIN...END verzichtet werden, weil der „Rumpf“ der Funktion nur eine einzige Anweisung enthält, nämlich RETURN unter Benutzung des CASE-Ausdrucks. Zur Verkettung der Zeichenketten wird CONCAT verwendet.

Eine solche Funktion wird wie jede eingebaute Funktion benutzt.

SELECT Hello('Juetho')   [from fiktiv];

Ausgabe

'Guten Abend, Juetho!'

Text in Anführungszeichen einschließen

Im folgenden Beispiel werden mehr Schritte bis zum RETURN-{}Wert benötigt. Der Nutzen liegt im Export von Daten aus einem System zu einem anderen.

Aufgabe

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

MySQL-Version

CREATE FUNCTION Quoting 
          ( instring  VARCHAR(80) )
  RETURNS VARCHAR(100) 
BEGIN
  IF (instr(instring, '"')!=0)
    THEN BEGIN
    SET instring = REPLACE( instring, '"', '""' );
    END;
  END IF;
  RETURN CONCAT( '"', instring, '"');
END

Hinweis: Auf das BEGIN und END innerhalb des IF-Konstrukts kann verzichtet werden, da innerhalb des IFs nur ein Statement ausgeführt wird.

Diese Funktion kann direkt aufgerufen werden:

select Quoting('Schulze'), Quoting('Restaurant "Abendrot"') [from fiktiv];

Ausgabe

"Schulze"   "Restaurant ""Abendrot"""

Anzahl der Mitarbeiter einer Abteilung

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

Aufgabe

Suche zu einer Abteilung gemäß Kuerzel die Anzahl der Mitarbeiter.

Oracle-Version

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:

select AnzahlMitarbeiter('Vert')    [from fiktiv] ;

Ausgabe

AnzahlMitarbeiter :  4

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.

Übungen

Wenn bei den folgenden Übungen der Begriff „Funktion“ ohne nähere Erklärung verwendet wird, ist immer eine „Eigene Funktion“, d. h. eine benutzerdefinierte Funktion gemeint.

Unter „Skizzieren“ (Übung 3, 5) ist gemeint: Eingabe- und Ausgabeparameter sowie Variablen mit sinnvollen Namen und Datentypen benennen, Arbeitsablauf möglichst genau mit Pseudo-Code oder normaler Sprache beschreiben.

Tipp: Die Parameter ergeben sich in der Regel aus der Aufgabenstellung. Aus der Überlegung zum Arbeitsablauf folgen die Variablen.

Übung 1

Definition einer Funktion

Zur Lösung

Welche der folgenden Aussagen sind richtig, welche sind falsch?

Eine Funktion dient zur Bestimmung genau eines Wertes.
Eine Funktion kann höchstens einen Eingabe-Parameter enthalten.
Eine Funktion kann mehrere Ausgabe-Parameter enthalten.
Eine Funktion kann einen oder mehrere SQL-Befehle verwenden.
Der Rückgabewert wird mit einer RETURN-Anweisung angegeben.
Der Datentyp des Rückgabewerts ergibt sich automatisch aus der RETURN-Anweisung.
Die Definition einer Funktion kann nicht geändert werden; sie kann nur gelöscht und mit anderem Inhalt neu aufgenommen werden.

Übung 2

Definition einer Funktion prüfen

Zur Lösung

Nennen Sie in der folgenden Definition Punkte, die unabhängig vom SQL-Dialekt falsch sind. (Je nach DBMS können noch andere Punkte falsch sein, danach wird aber nicht gefragt.) Die Funktion soll folgende Aufgabe erledigen:

Es wird eine Telefonnummer in beliebiger Formatierung als Eingabe-Parameter übergeben, z. B. als '(0049 / 030) 99 68-32 53'.
Das Ergebnis soll diese Nummer ganz ohne Trennzeichen zurückgeben.
Sofern die Ländervorwahl enthalten ist, soll das führende '00' durch '+' ersetzt werden.

CREATE Telefon_Standard AS Function
    ( IN  Eingabe VARCHAR(20),
      OUT Ausgabe VARCHAR(20) )
AS
  DECLARE INTEGER x1, i1;
  DECLARE CHAR c1;
BEGIN
  -- Rückgabewert vorbelegen
  Ausgabe = '';
  -- Länge der Schleife bestimmen
  i1 = CHAR_LENGTH(Eingabe);
  -- die Schleife für jedes vorhandene Zeichen verarbeiten
  WHILE (i1 < x1) 
  DO
    -- das nächste Zeichen auslesen
    x1 = x1 + 1
    c1 = SUBSTRING(Eingabe from x1 for 1);
    -- dieses Zeichen prüfen: ist es eine Ziffer 
    if (c1 >= '0' and (c1 <= '9') 
    THEN 
      -- ja: an den bisherigen Rückgabewert anhängen
      Ausgabe = Ausgabe || c1;
    END
  END

  -- Zusatzprüfung: '00' durch '+' ersetzen
  IF (Ausgabe STARTS WITH '00') 
  THEN
    Ausgabe = '+' || SUBSTRING(Ausgabe from 3 for 20)
  END

END

Übung 3

Funktion DateToString erstellen

Zur Lösung

Skizzieren Sie eine Funktion DateToString: Aus einem Date- oder DateTime-Wert als Eingabe-Parameter soll eine Zeichenkette mit genau 8 Zeichen der Form 'JJJJMMTT' gemacht werden. Benutzen Sie dafür nur die EXTRACT- und LPAD-Funktionen sowie die „Addition“ von Zeichenketten; Sie können davon ausgehen, dass eine Zahl bei LPAD korrekt als Zeichenkette verstanden wird.

Übung 4

Funktion DateToString erstellen

Zur Lösung

Erstellen Sie die Funktion aus der vorigen Übung.

Übung 5

Funktion String_Insert erstellen

Zur Lösung

Skizzieren Sie eine Funktion String_Insert: In eine Zeichenkette soll an einer bestimmten Position eine zweite Zeichenkette eingefügt werden; alle Zeichenketten sollen max. 80 Zeichen lang sein. Benutzen Sie dafür nur SUBSTRING und CAST sowie die „Addition“ von Zeichenketten; stellen Sie außerdem sicher, dass das Ergebnis die mögliche Maximallänge nicht überschreitet und schneiden Sie ggf. ab.

Übung 6

Funktion String_Insert erstellen

Zur Lösung

Erstellen Sie die Funktion aus der vorigen Übung.

Lösungen

Lösung zu Übung 1

Definition einer Funktion

Zur Übung

Die Aussagen 1, 4, 5 sind richtig. Die Aussagen 2, 3, 6, 7 sind falsch.

Lösung zu Übung 2

Definition einer Funktion prüfen

Zur Übung

Zeile 1: Die Definition erfolgt durch CREATE FUNCTION <Name>.
Zeile 2: Bei einer Funktion gehören in die Klammern nur die Eingabe-Parameter.
Zeile 3: Der Ausgabe-Parameter muss mit RETURNS festgelegt werden.
Zeile 4: Bei jeder Variablen ist zuerst der Name, dann der Typ anzugeben.
Zeile 12: Der Startwert für die Schleifenvariable x1 = 0 fehlt.
Zeile 14: Die Schleife verarbeitet mehrere Befehle, muss also hinter DO auch ein BEGIN erhalten.
Zeile 16 und 29: Jeder einzelne Befehl muss mit einem Semikolon abgeschlossen werden.
Zeile 19: Die Klammern sind falsch gesetzt; am einfachsten wird die zweite öffnende Klammer entfernt.
Zeile 20/23: Entweder es wird THEN BEGIN...END verwendet, oder das END wird gestrichen.
Zeile 28/30 enthält den gleichen Fehler.
Zeile 31: Es fehlt die RETURN-Anweisung, mit der der erzeugte Ausgabe-String zurückgegeben wird.

Lösung zu Übung 3

Funktion DateToString erstellen

Zur Übung

Eingabe-Parameter: ein DateTime-Wert Eingabe
Ausgabe-Parameter: ein CHAR(8) Ausgabe
drei INTEGER-Variable: jjjj, mm, tt
mit 3x EXTRACT werden die Bestandteile jjjj, mm, tt herausgeholt
diese werden mit LPAD auf CHAR(2) für Monat und Tag gesetzt
schließlich werden diese Werte per „Addition“ verknüpft und
als Ergebnis zurückgegeben

Lösung zu Übung 4

Funktion DateToString erstellen

Zur Übung

CREATE FUNCTION DateToString 
        ( Eingabe DATE )
RETURNS ( Ausgabe CHAR(8) )
AS
  DECLARE VARIABLE jjjj int;
  DECLARE VARIABLE mm   int;
  DECLARE VARIABLE tt   int;
BEGIN
  jjjj = EXTRACT(YEAR  from Eingabe);
  mm   = EXTRACT(MONTH from Eingabe);
  tt   = EXTRACT(DAY   from Eingabe);
  Ausgabe = jjjj || LPAD(mm, 2, '0') || LPAD(tt, 2, '0');
  RETURN Ausgabe;
END

Lösung zu Übung 5

Funktion String_Insert erstellen

Zur Übung

Eingabe-Parameter: Original-String Eingabe, pos als Position (der Name Position ist als interne Funktion verboten), der Zusatz-String part
Ausgabe-Parameter: die neue Zeichenkette Ausgabe
Variable: temp mit Platz für 160 Zeichen
es wird einfach eine neue Zeichenkette temp zusammengesetzt aus drei Teilen:
- der Anfang von Eingabe mit pos Zeichen
- dann der Zusatz-String part
- dann der Rest von Eingabe ab Position pos + 1
falls die Zeichenkette temp zu lang ist, werden mit SUBSTRING nur noch die ersten 80 Zeichen verwendet
das Ergebnis muss wegen der seltsamen Definition von SUBSTRING mit CAST auf VARCHAR(80) gebracht werden und
kann dann als Rückgabewert verwendet werden

Lösung zu Übung 6

Funktion String_Insert erstellen

Zur Übung

CREATE FUNCTION String_Insert
        ( Eingabe VARCHAR(80), pos INTEGER, part VARCHAR(80) )
RETURNS ( Ausgabe VARCHAR(80) )
AS
  -- genügend Zwischenspeicher bereitstellen
  DECLARE VARIABLE temp VARCHAR(160);
BEGIN
  -- Teil 1, dazu Zwischenteil, dann Teil 2
  temp = SUBSTRING( Eingabe from 1 for pos )
      || part
      || SUBSTRING( Eingabe from pos + 1 );
  -- auf jeden Fall auf die Maximallänge von 80 Zeichen bringen
  if (CHAR_LENGTH(temp) > 80) THEN
    Ausgabe = cast( substring(temp from 1 for 80) as varchar(80));
  ELSE
    Ausgabe = cast( temp as varchar(80));
  RETURN Ausgabe;
END

Siehe auch

Verschiedene Einzelheiten werden in den folgenden Kapiteln behandelt:

Inhaltsverzeichnis: Erweiterungen

Zurück zu: Programmierung

Weiter zu: Prozeduren