Zum Inhalt springen

Eigene Funktionen

Aus Wikibooks

Seitentitel: Einführung in SQL: Eigene Funktionen
(Einführung in SQL: Eigene Funktionen)
(Einführung in SQL: 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.

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

[Bearbeiten]

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

Funktion erstellen

[Bearbeiten]

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

[Bearbeiten]

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

[Bearbeiten]

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

[Bearbeiten]

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

[Bearbeiten]

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

Aufgabe
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

[Bearbeiten]

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

[Bearbeiten]

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

Aufgabe
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

[Bearbeiten]

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

[Bearbeiten]

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?

  1. Eine Funktion dient zur Bestimmung genau eines Wertes.
  2. Eine Funktion kann höchstens einen Eingabe-Parameter enthalten.
  3. Eine Funktion kann mehrere Ausgabe-Parameter enthalten.
  4. Eine Funktion kann einen oder mehrere SQL-Befehle verwenden.
  5. Der Rückgabewert wird mit einer RETURN-Anweisung angegeben.
  6. Der Datentyp des Rückgabewerts ergibt sich automatisch aus der RETURN-Anweisung.
  7. 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

[Bearbeiten]

Verschiedene Einzelheiten werden in den folgenden Kapiteln behandelt: