Stored Functions in SQL

Stored Functions sind ein mächtiges Werkzeug in SQL, mit dem du wiederverwendbare Codeblöcke für Berechnungen und Datenaufbereitung anlegen kannst. Anders als eingebaute Funktionen schreibst du Stored Functions selbst, um spezifische Probleme zu lösen.

Eine Stored Function ist ein benannter Block aus SQL-Code, der Parameter entgegennimmt, Berechnungen ausführt und stets einen Wert eines bestimmten Typs zurückgibt.

Allgemeine Struktur einer Stored Function

MySQL 8.1
CREATE FUNCTION funktions_name(parameter1 TYP, parameter2 TYP, ...)
RETURNS rueckgabe_typ
BEGIN
    -- Logik der Funktion
    RETURN ergebnis_der_berechnung;
END;

MySQL 8.1
CREATE OR REPLACE FUNCTION funktions_name(parameter1 TYP, parameter2 TYP, ...)
RETURNS rueckgabe_typ
LANGUAGE plpgsql
AS $$
BEGIN
    -- Logik der Funktion
    RETURN ergebnis_der_berechnung;
END;
$$;

Einfaches Beispiel

Bauen wir eine Funktion, die anhand des Geburtsdatums prüft, ob jemand volljährig ist:

MySQL 8.1
CREATE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;
END;

MySQL 8.1
CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18;
END;
$$;

Diese Funktion kannst du jetzt in beliebigen Queries verwenden:

MySQL 8.1
-- Funktion anlegen
CREATE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;
END;

-- Funktion verwenden
SELECT
    is_adult('2010-05-15') AS child_status,
    is_adult('2000-03-20') AS adult_status;

MySQL 8.1
-- Funktion anlegen
CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18;
END;
$$;

-- Funktion verwenden
SELECT
    is_adult('2010-05-15') AS child_status,
    is_adult('2000-03-20') AS adult_status;

Funktionen in Queries auf Tabellen einsetzen

Stored Functions sind besonders praktisch bei der Arbeit mit echten Daten. Zum Beispiel können wir unsere Funktion verwenden, um Studierende nach Alter zu filtern:

MySQL 8.1
-- Funktion anlegen
CREATE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;
END;

-- Funktion in einer Query auf einer Tabelle nutzen
SELECT
    first_name,
    last_name,
    birthday,
    is_adult(birthday) AS is_adult
FROM Student
WHERE is_adult(birthday) = TRUE
LIMIT 5;

MySQL 8.1
-- Funktion anlegen
CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) >= 18;
END;
$$;

-- Funktion in einer Query auf einer Tabelle nutzen
SELECT
    first_name,
    last_name,
    birthday,
    is_adult(birthday) AS is_adult
FROM Student
WHERE is_adult(birthday) = TRUE
LIMIT 5;

Funktionen mit SQL-Queries

Stored Functions können in ihrem Inneren selbst SQL-Queries ausführen, um die benötigten Daten zu holen:

MySQL 8.1
CREATE FUNCTION get_student_lessons_count(student_id INT, target_date DATE)
RETURNS INT
BEGIN
    DECLARE lessons_count INT;

    SELECT COUNT(*) INTO lessons_count
    FROM Schedule s
    INNER JOIN Student_in_class sic ON s.class = sic.class
    WHERE sic.student = student_id
      AND s.date = target_date;

    RETURN lessons_count;
END;

MySQL 8.1
CREATE OR REPLACE FUNCTION get_student_lessons_count(student_id INT, target_date DATE)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    lessons_count INT;
BEGIN
    SELECT COUNT(*) INTO lessons_count
    FROM Schedule s
    INNER JOIN Student_in_class sic ON s.class = sic.class
    WHERE sic.student = student_id
      AND s.date = target_date;

    RETURN lessons_count;
END;
$$;

Diese Funktion zählt die Anzahl der Unterrichtsstunden eines bestimmten Studenten an einem bestimmten Tag:

MySQL 8.1
SELECT get_student_lessons_count(1, '2019-09-01') AS lessons_today;

MySQL 8.1
SELECT get_student_lessons_count(1, '2019-09-01') AS lessons_today;

Variablen Schritt für Schritt erklärt

Bislang sind wir Variablen in SQL noch nicht begegnet — bei Stored Functions und Procedures sind sie aber ein wichtiges Konzept. Schauen wir uns das vorige Beispiel deshalb Schritt für Schritt an:

MySQL 8.1
DECLARE lessons_count INT;

Diese Zeile deklariert die Variable lessons_count vom Typ INT. Die Variable nimmt das Ergebnis unserer Query auf.

MySQL 8.1
SELECT COUNT(*) INTO lessons_count
FROM Schedule s
INNER JOIN Student_in_class sic ON s.class = sic.class
WHERE sic.student = student_id
  AND s.date = target_date;

Hier wird das Ergebnis der Query in einer Variablen gespeichert:

• SELECT COUNT(*) — zählt die Datensätze
• INTO lessons_count — speichert das Ergebnis in der Variablen lessons_count
• Der Rest ist eine ganz normale SQL-Query mit JOIN und Bedingungen

MySQL 8.1
RETURN lessons_count;

Wir geben den Wert der Variablen als Ergebnis der Funktion zurück.

Wichtig: Die Konstruktion INTO speichert das Ergebnis einer SELECT-Query in einer Variablen. Das ist die Grundlage für die Datenarbeit innerhalb von Stored Functions.

Stored Functions verwalten

• Vorhandene Funktionen anzeigen

  MySQL 8.1
  SHOW FUNCTION STATUS WHERE Db = 'your_database_name';
  

  MySQL 8.1
  SELECT routine_name, routine_type
  FROM information_schema.routines
  WHERE routine_type = 'FUNCTION' AND routine_schema = 'public';
  

• Funktion löschen

  MySQL 8.1
  DROP FUNCTION IF EXISTS is_adult;
  

  MySQL 8.1
  DROP FUNCTION IF EXISTS is_adult(DATE);
  

• Funktion ändern

  Zum Ändern einer Funktion in MySQL musst du die alte Version erst löschen und dann eine neue anlegen:

  MySQL 8.1
  DROP FUNCTION IF EXISTS is_adult;
  -- Neue Version der Funktion anlegen
  CREATE FUNCTION is_adult(birth_date DATE) ...
  

  In PostgreSQL kannst du CREATE OR REPLACE FUNCTION verwenden:

  MySQL 8.1
  CREATE OR REPLACE FUNCTION is_adult(birth_date DATE)
  RETURNS BOOLEAN
  -- neue Implementierung
  

Stored Functions sind ein mächtiges Werkzeug, um wiederverwendbare Business-Logik direkt in der Datenbank zu bündeln. Sie helfen, Berechnungen zu zentralisieren und die Datenkonsistenz in der gesamten Anwendung sicherzustellen! 🚀