Stored Procedures und Funktionen

Bisher hast du SQL nur als deklarative Abfragesprache kennengelernt: du sagst, was du willst, die DB findet den Weg. Aber moderne Datenbanken können auch prozedural programmiert werden: mit IF, Schleifen, Variablen, Fehlerbehandlung. Die Bausteine dafür sind Stored Procedures und Funktionen.

Beide sind in der Datenbank gespeicherte, benannte Code-Blöcke, die du wie eine View oder eine Subroutine aufrufen kannst. Sie sind die Antwort auf Aufgaben, die mit einer einzelnen SELECT-Abfrage nicht lösbar sind – etwa „Prüfe Bedingung A, dann führe je nach Ergebnis B oder C aus". Diese Lektion zeigt dir die Grundsyntax (mit MySQL als Beispiel-Dialekt), Parameter, Kontrollstrukturen und wann sich Stored Procedures überhaupt lohnen.

1) Wozu brauche ich das?

Klassische Anwendungsfälle für Stored Procedures und Funktionen:

• Komplexe Geschäftslogik, die nahe an den Daten laufen soll (z. B. Bestellung verarbeiten, Bestand reduzieren, Rechnung erzeugen – alles in einer Transaktion)
• Wiederverwendung: dieselbe Logik soll von mehreren Anwendungen gleich ausgeführt werden, ohne sie in jeder Anwendung neu zu schreiben
• Performance: weniger Roundtrips zwischen Anwendung und DB – ein Aufruf statt zehn
• Sicherheit: Anwendung darf nur die Procedure aufrufen, hat aber keine direkten Schreibrechte auf die Tabellen
• Berechnungsfunktionen: einheitliche Berechnungsregeln (Brutto/Netto, Rabatte, Steuerformeln) in einer Funktion gekapselt

2) Stored Procedure vs. Funktion

Beide sind ähnlich, unterscheiden sich aber in wichtigen Punkten:

3) Die kleinste Stored Procedure

Hier ein einfaches Beispiel in MySQL/MariaDB-Syntax: eine Procedure, die die Anzahl der Bestellungen ausgibt:

DELIMITER //

CREATE PROCEDURE bestellungen_anzahl()
BEGIN
  SELECT COUNT(*) FROM bestellungen;
END //

DELIMITER ;

Aufruf:

CALL bestellungen_anzahl();

Was steckt da drin?

• DELIMITER //: ändert temporär das Ende-Zeichen für SQL-Anweisungen. Innerhalb der Procedure stehen Semikolons (für interne Statements). Ohne diese Umschaltung würde der Parser den ersten ; als Ende der gesamten Anweisung interpretieren. Am Ende wieder zurück auf ;
• CREATE PROCEDURE name (): Deklaration, Parameter in Klammern (hier leer)
• BEGIN … END: der Rumpf der Procedure

4) Parameter: IN, OUT, INOUT

Procedures können Parameter haben. Es gibt drei Arten:

Beispiel mit IN- und OUT-Parameter:

DELIMITER //

CREATE PROCEDURE kunde_umsatz(
  IN  p_kunde_id INT,
  OUT p_summe    DECIMAL(10,2)
)
BEGIN
  SELECT COALESCE(SUM(summe), 0)
  INTO p_summe
  FROM bestellungen
  WHERE kunde_id = p_kunde_id;
END //

DELIMITER ;

Aufruf mit Variable, die das Ergebnis aufnimmt:

CALL kunde_umsatz(1, @umsatz);
SELECT @umsatz;
-- Ergibt z. B. 700.00

SELECT … INTO p_summe schreibt das Ergebnis der Abfrage in den Ausgabeparameter. @umsatz ist eine sogenannte Session-Variable (in MySQL mit @ markiert) – sie hält den Wert nur für die aktuelle DB-Verbindung.

5) Variablen innerhalb der Procedure

Lokale Variablen werden mit DECLARE definiert (am Anfang des BEGIN…END-Blocks):

DELIMITER //

CREATE PROCEDURE rabatt_geben(IN p_kunde_id INT)
BEGIN
  DECLARE v_summe DECIMAL(10,2);
  DECLARE v_rabatt DECIMAL(3,2);

  SELECT COALESCE(SUM(summe), 0) INTO v_summe
  FROM bestellungen WHERE kunde_id = p_kunde_id;

  IF v_summe >= 1000 THEN
    SET v_rabatt = 0.10;
  ELSEIF v_summe >= 500 THEN
    SET v_rabatt = 0.05;
  ELSE
    SET v_rabatt = 0;
  END IF;

  SELECT p_kunde_id AS kunde, v_summe AS umsatz, v_rabatt AS rabatt;
END //

DELIMITER ;

CALL rabatt_geben(1);

Ergebnis bei Anna (700 € Umsatz):

6) Kontrollstrukturen: IF, CASE, Schleifen

In Procedure-Code stehen dir typische Kontrollstrukturen zur Verfügung:

-- IF / ELSEIF / ELSE
IF v_alter < 18 THEN
  SET v_kategorie = 'minderjährig';
ELSEIF v_alter < 65 THEN
  SET v_kategorie = 'erwerbsfähig';
ELSE
  SET v_kategorie = 'rentenalter';
END IF;

-- CASE-Statement
CASE v_status
  WHEN 'neu'      THEN SET v_text = 'frische Bestellung';
  WHEN 'versandt' THEN SET v_text = 'unterwegs';
  ELSE             SET v_text = 'unbekannt';
END CASE;

-- WHILE-Schleife
DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
  INSERT INTO log (msg) VALUES (CONCAT('Iteration ', i));
  SET i = i + 1;
END WHILE;

-- LOOP mit explizitem Abbruch
my_loop: LOOP
  SET i = i + 1;
  IF i > 5 THEN
    LEAVE my_loop;
  END IF;
END LOOP;

Schleifen in SQL solltest du sparsam einsetzen. Wenn du dich dabei ertappst, viele Zeilen in einer Schleife einzeln zu bearbeiten, prüfe ob ein mengenbasierter Ansatz möglich ist (also ein einzelnes UPDATE/INSERT, das alle betroffenen Zeilen auf einmal anfasst). Mengenbasiert ist fast immer schneller.

7) Funktionen erstellen

Eine Funktion gibt einen Wert zurück und ist in SQL-Abfragen direkt nutzbar. Beispiel: eine Funktion, die ein Brutto in ein Netto umrechnet (Mehrwertsteuer 19 %):

DELIMITER //

CREATE FUNCTION brutto_zu_netto(p_brutto DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN ROUND(p_brutto / 1.19, 2);
END //

DELIMITER ;

Nutzung in einer Abfrage – wie eine eingebaute Funktion:

SELECT id, summe AS brutto, brutto_zu_netto(summe) AS netto
FROM bestellungen;

Wichtige Schlüsselwörter:

• RETURNS: Datentyp des Rückgabewerts (Pflicht)
• DETERMINISTIC: erklärt, dass die Funktion bei gleichem Input immer dasselbe Ergebnis liefert. Wichtig für Optimierung und Replikation
• RETURN expr: gibt den Wert zurück (Pflicht)

8) Funktionen mit mehreren Anweisungen

Funktionen können wie Procedures Variablen, IF, Schleifen enthalten – Hauptregel: am Ende muss ein RETURN stehen:

DELIMITER //

CREATE FUNCTION rabatt_satz(p_umsatz DECIMAL(10,2))
RETURNS DECIMAL(3,2)
DETERMINISTIC
BEGIN
  IF p_umsatz >= 1000 THEN
    RETURN 0.10;
  ELSEIF p_umsatz >= 500 THEN
    RETURN 0.05;
  ELSE
    RETURN 0;
  END IF;
END //

DELIMITER ;

SELECT name, SUM(b.summe) AS umsatz,
       rabatt_satz(SUM(b.summe)) AS rabatt
FROM kunden k JOIN bestellungen b ON k.id = b.kunde_id
GROUP BY k.id, k.name;

9) Fehlerbehandlung

Mit DECLARE HANDLER kann Code festgelegt werden, der bei bestimmten Fehlern oder Warnungen ausgeführt wird:

CREATE PROCEDURE sicher_einfuegen(IN p_kunde_id INT, IN p_summe DECIMAL(10,2))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Bei jedem SQL-Fehler hier landen und Transaktion zurückrollen
    ROLLBACK;
    SELECT 'Fehler – Vorgang abgebrochen' AS ergebnis;
  END;

  START TRANSACTION;
  INSERT INTO bestellungen (kunde_id, summe) VALUES (p_kunde_id, p_summe);
  COMMIT;
  SELECT 'OK' AS ergebnis;
END

Wenn das INSERT fehlschlägt (z. B. wegen verletztem Fremdschlüssel), springt der Handler an, macht ROLLBACK und gibt eine Fehlermeldung zurück. Das ist das prozedurale Pendant zu try/catch in Programmiersprachen.

10) Stored Procedure mit Transaktion

Procedures sind ideal für Mehrschritt-Abläufe, die atomar ausgeführt werden müssen. Klassiker: eine Bestellung erstellen und gleichzeitig den Lagerbestand reduzieren – beides oder nichts:

DELIMITER //

CREATE PROCEDURE bestellung_anlegen(
  IN p_kunde_id INT,
  IN p_artikel_id INT,
  IN p_menge INT
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;

  INSERT INTO bestellungen (kunde_id, artikel_id, menge)
  VALUES (p_kunde_id, p_artikel_id, p_menge);

  UPDATE artikel
  SET bestand = bestand - p_menge
  WHERE id = p_artikel_id AND bestand >= p_menge;

  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Nicht genug Bestand';
  END IF;

  COMMIT;
END //

DELIMITER ;

Das SIGNAL-Statement löst einen Fehler aus – das wiederum aktiviert den Handler, der die Transaktion zurückrollt. So bleibt die DB konsistent: entweder klappt beides (Bestellung + Bestand) oder nichts.

11) Procedures verwalten

Procedures und Funktionen sind dauerhaft in der DB gespeichert. Verwaltung wie bei Views:

-- Liste der vorhandenen Procedures
SHOW PROCEDURE STATUS WHERE Db = 'meine_db';

-- Quellcode anzeigen
SHOW CREATE PROCEDURE bestellung_anlegen;

-- Löschen
DROP PROCEDURE IF EXISTS bestellung_anlegen;
DROP FUNCTION IF EXISTS brutto_zu_netto;

Ändern ist meist nur durch DROP und neu CREATE möglich – nicht alle Datenbanken unterstützen CREATE OR REPLACE PROCEDURE (PostgreSQL und Oracle ja, MySQL nicht direkt).

12) Stored Procedure vs. anderswo

Wann gehört Logik in die DB, wann in die Anwendung? Eine sensible Abwägung:

Moderne Architekturen tendieren dazu, Geschäftslogik in die Anwendung zu legen und die DB schlank zu halten (nur Tabellen, Views, Indizes). Stored Procedures werden eher für DB-nahe Verwaltung (Datenmigration, Reports) verwendet. In Klausuren und Altsystemen begegnen sie dir aber häufig.

13) Dialekt-Hinweise

Die Syntax für Procedures unterscheidet sich zwischen Datenbanken stärker als sonst im SQL. Die wichtigsten Unterschiede:

• MySQL/MariaDB: DELIMITER nötig, CREATE PROCEDURE, Variablen mit DECLARE
• PostgreSQL: CREATE FUNCTION bzw. CREATE PROCEDURE (ab v11), Sprache wie LANGUAGE plpgsql
• SQL Server: T-SQL, mit BEGIN…END, Variablen mit DECLARE @name, Aufruf mit EXEC
• Oracle: PL/SQL, eigene reiche Sprache mit Packages, Cursorn etc.

In Prüfungen wird meist MySQL-Syntax erwartet, falls nicht anders angegeben. Das Konzept ist überall gleich – die genauen Schlüsselwörter ändern sich.

14) Häufige Fehler

• DELIMITER vergessen: Procedure-Definition bricht nach dem ersten internen Semikolon ab. Klassiker bei MySQL.
• DECLARE zu spät: lokale Variablen müssen am Anfang des BEGIN…END-Blocks deklariert werden, vor allen anderen Statements.
• Funktion mit DML: Funktionen sollten Daten nicht ändern. MySQL erlaubt das mit Einschränkungen, aber es ist schlechter Stil und kann Replikationsprobleme machen.
• OUT-Parameter nicht initialisieren: vor dem Aufruf braucht der Aufrufer eine Session-Variable (@x), in die der OUT-Wert geschrieben wird.
• Performance-Falle Cursor: zeilenweise Verarbeitung mit Cursorn ist langsam – wann immer möglich mengenbasiert arbeiten.
• Keine Fehlerbehandlung: ohne EXIT HANDLER bleiben Transaktionen offen, wenn etwas schiefläuft.

Zusammenfassung

Stored Procedures und Funktionen sind in der Datenbank gespeicherte Code-Blöcke mit Parametern, Variablen und Kontrollstrukturen (IF, CASE, WHILE). Procedures werden mit CALL aufgerufen, können Daten verändern und Result-Sets oder OUT-Parameter zurückgeben. Funktionen liefern einen Wert, sind in SQL-Abfragen direkt nutzbar und sollten keine Daten ändern. Parameter gibt es als IN (Eingabe), OUT (Ausgabe) und INOUT. Fehler werden mit DECLARE … HANDLER behandelt, eigene Fehler mit SIGNAL ausgelöst. Procedures sind hilfreich für mehrstufige Abläufe mit Transaktion und Sicherheits-Layern; ihre Logik gehört aber heute meist in die Anwendung, nicht in die DB.