Stored Procedure

Eine Stored Procedure (deutsch: gespeicherte Prozedur) ist ein vorkompiliertes SQL-Programm, das direkt auf dem Datenbankserver gespeichert und ausgeführt wird. Stored Procedures fassen mehrere SQL-Befehle zu einer wiederverwendbaren Einheit zusammen und können Parameter entgegennehmen, komplexe Geschäftslogik ausführen und Ergebnisse zurückliefern. Sie gehören zu den wichtigsten Werkzeugen für effiziente und sichere Datenbankprogrammierung.

Funktionsweise von Stored Procedures

Im Gegensatz zu einzelnen SQL-Abfragen, die bei jeder Ausführung neu geparst und optimiert werden müssen, werden Stored Procedures beim Erstellen einmalig kompiliert. Der Datenbankserver speichert den optimierten Ausführungsplan und kann diesen bei jedem Aufruf direkt verwenden. Das spart Rechenzeit und beschleunigt die Ausführung erheblich.

Der typische Ablauf sieht so aus:

1. Erstellung: Die Prozedur wird mit CREATE PROCEDURE definiert und auf dem Server gespeichert
2. Kompilierung: Der SQL-Code wird geparst, validiert und in einen Ausführungsplan umgewandelt
3. Speicherung: Der Ausführungsplan wird im Cache des Datenbankservers abgelegt
4. Aufruf: Anwendungen rufen die Prozedur per CALL oder EXEC auf
5. Ausführung: Der gecachte Plan wird direkt ausgeführt, ohne erneutes Parsen

Syntax und Beispiele

Die Syntax für Stored Procedures unterscheidet sich je nach Datenbanksystem. Hier findest du Beispiele für die gängigsten Systeme.

MySQL und MariaDB

DELIMITER $$

CREATE PROCEDURE GetMitarbeiterByAbteilung(IN abteilung_id INT)
BEGIN
    SELECT vorname, nachname, position
    FROM mitarbeiter
    WHERE abteilung = abteilung_id
    ORDER BY nachname;
END$$

DELIMITER ;

-- Aufruf der Prozedur
CALL GetMitarbeiterByAbteilung(5);

MySQL erfordert den DELIMITER-Befehl, da Semikolons innerhalb der Prozedur sonst als Statement-Ende interpretiert würden.

Microsoft SQL Server (T-SQL)

CREATE PROCEDURE GetMitarbeiterByAbteilung
    @AbteilungID INT
AS
BEGIN
    SELECT vorname, nachname, position
    FROM mitarbeiter
    WHERE abteilung = @AbteilungID
    ORDER BY nachname;
END;
GO

-- Aufruf der Prozedur
EXEC GetMitarbeiterByAbteilung @AbteilungID = 5;

SQL Server verwendet das @-Zeichen für Parameter und EXEC statt CALL zum Aufruf.

PostgreSQL (PL/pgSQL)

CREATE OR REPLACE PROCEDURE transfer_gehalt(
    sender_id INT,
    empfaenger_id INT,
    betrag DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Gehalt vom Sender abziehen
    UPDATE mitarbeiter
    SET gehalt = gehalt - betrag
    WHERE id = sender_id;

    -- Gehalt beim Empfänger hinzufügen
    UPDATE mitarbeiter
    SET gehalt = gehalt + betrag
    WHERE id = empfaenger_id;

    COMMIT;
END;$$;

-- Aufruf der Prozedur
CALL transfer_gehalt(1, 2, 500.00);

PostgreSQL nutzt Dollar-Quoting ($$) zur Abgrenzung des Prozedurkörpers und unterstützt verschiedene prozedurale Sprachen wie PL/pgSQL, PL/Python oder PL/Perl.

Parametertypen

Stored Procedures unterstützen verschiedene Parametertypen, die den Datenfluss zwischen Anwendung und Prozedur steuern:

-- Beispiel mit OUT-Parameter (MySQL)
CREATE PROCEDURE CountMitarbeiter(
    IN abt_id INT,
    OUT anzahl INT
)
BEGIN
    SELECT COUNT(*) INTO anzahl
    FROM mitarbeiter
    WHERE abteilung = abt_id;
END;

-- Aufruf
CALL CountMitarbeiter(5, @ergebnis);
SELECT @ergebnis;

Vorteile von Stored Procedures

Performance-Vorteile

• Vorkompilierung: Der Ausführungsplan wird einmalig erstellt und gecacht
• Reduzierter Netzwerkverkehr: Statt vieler Einzelabfragen wird nur ein Prozeduraufruf übertragen
• Servernahe Ausführung: Komplexe Berechnungen laufen direkt auf dem Datenbankserver
• Optimierte Indexnutzung: Der Query Optimizer kann den Ausführungsplan für alle Aufrufe optimieren

Sicherheitsvorteile

Stored Procedures bieten einen effektiven Schutz gegen SQL Injection, eine der häufigsten Angriffsmethoden auf Webanwendungen. Da Parameter strikt als Daten behandelt werden und nicht als ausführbarer Code interpretiert werden, können Angreifer keinen schadhaften SQL-Code einschleusen.

• Parameterisierte Ausführung: Eingaben werden automatisch escaped
• Abstraktion: Anwendungen kennen die Tabellenstruktur nicht direkt
• Principle of Least Privilege: Benutzer erhalten nur EXECUTE-Rechte, nicht SELECT/UPDATE auf Tabellen
• Zentrale Validierung: Eingabeprüfung an einer Stelle statt in jeder Anwendung

Wartbarkeit und Wiederverwendbarkeit

• Zentrale Logik: Geschäftsregeln an einer Stelle, nicht über Anwendungen verteilt
• Einfache Updates: Änderungen an der Prozedur gelten sofort für alle Aufrufer
• Konsistenz: Alle Anwendungen nutzen dieselbe Datenzugriffslogik
• Code-Wiederverwendung: Eine Prozedur für viele Anwendungen

Nachteile und Herausforderungen

Trotz ihrer Vorteile bringen Stored Procedures auch Herausforderungen mit sich:

• Datenbankabhängigkeit: Jedes DBMS hat eigene Syntax (T-SQL, PL/pgSQL, PL/SQL) - Migration ist aufwendig
• Debugging: Fehlersuche ist komplexer als bei Anwendungscode, spezielle Tools erforderlich
• Versionierung: Integration in Git und CI/CD-Pipelines erfordert zusätzliche Werkzeuge
• Spezialisiertes Know-how: Entwickler benötigen Kenntnisse in der jeweiligen Prozedursprache
• Skalierung: Logik auf dem Datenbankserver kann zum Engpass werden
• Testing: Unit-Tests für Stored Procedures sind aufwendiger als für Anwendungscode

Unterschied zu Funktionen und Triggern

Stored Procedures werden oft mit Funktionen und Triggern verwechselt. Hier die wichtigsten Unterschiede:

Praxisbeispiele

Beispiel: Bestellvorgang mit Transaktionssicherheit

CREATE PROCEDURE NeueBestellung(
    IN kunde_id INT,
    IN produkt_id INT,
    IN menge INT,
    OUT bestellung_id INT
)
BEGIN
    DECLARE verfuegbar INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET bestellung_id = -1;
    END;

    START TRANSACTION;

    -- Lagerbestand prüfen
    SELECT lagerbestand INTO verfuegbar
    FROM produkte WHERE id = produkt_id FOR UPDATE;

    IF verfuegbar < menge THEN
        ROLLBACK;
        SET bestellung_id = -2; -- Nicht genug auf Lager
    ELSE
        -- Bestellung anlegen
        INSERT INTO bestellungen (kunde_id, datum)
        VALUES (kunde_id, NOW());
        SET bestellung_id = LAST_INSERT_ID();

        -- Bestellposition hinzufügen
        INSERT INTO bestellpositionen (bestellung_id, produkt_id, menge)
        VALUES (bestellung_id, produkt_id, menge);

        -- Lagerbestand reduzieren
        UPDATE produkte
        SET lagerbestand = lagerbestand - menge
        WHERE id = produkt_id;

        COMMIT;
    END IF;
END;

Diese Prozedur zeigt typische Elemente einer produktiven Stored Procedure: Transaktionssteuerung, Fehlerbehandlung, Geschäftslogik (Lagerprüfung) und mehrere zusammenhängende Datenbankoperationen.

Beispiel: ETL-Prozess für Reporting

CREATE PROCEDURE AggregiereTagesumsatz(IN stichtag DATE)
BEGIN
    -- Alte Daten für den Tag löschen
    DELETE FROM umsatz_taeglich WHERE datum = stichtag;

    -- Neue Aggregation einfügen
    INSERT INTO umsatz_taeglich (datum, abteilung_id, umsatz, anzahl_bestellungen)
    SELECT 
        stichtag,
        p.abteilung_id,
        SUM(bp.menge * bp.preis),
        COUNT(DISTINCT b.id)
    FROM bestellungen b
    JOIN bestellpositionen bp ON b.id = bp.bestellung_id
    JOIN produkte p ON bp.produkt_id = p.id
    WHERE DATE(b.datum) = stichtag
    GROUP BY p.abteilung_id;
END;

Best Practices für Stored Procedures

Für professionelle Stored-Procedure-Entwicklung haben sich folgende Best Practices etabliert:

• Einheitliche Namenskonvention: z.B. [Tabelle]_[Aktion] wie Kunde_Insert, Bestellung_GetByID
• Fehlerbehandlung: Immer TRY...CATCH oder Exception Handler implementieren
• Kommentierung: Zweck, Parameter und Rückgabewerte dokumentieren
• Modularität: Komplexe Logik in mehrere kleinere Prozeduren aufteilen
• Set-basierte Operationen: Mengenoperationen statt Cursor und Schleifen bevorzugen
• Explizite Schemas: Immer Schema angeben (dbo.Tabelle statt nur Tabelle)
• Transaktionen bewusst einsetzen: Nur wenn nötig, und so kurz wie möglich
• Versionskontrolle: Prozeduren in Git versionieren und per Migration deployen

Stored Procedures in der IT-Ausbildung

Stored Procedures sind ein wichtiges Thema für Fachinformatiker für Anwendungsentwicklung und werden in der IHK-Abschlussprüfung abgefragt. Du solltest verstehen, wann Stored Procedures sinnvoll sind, wie die grundlegende Syntax aussieht und welche Sicherheitsvorteile sie bieten.

In der Praxis begegnest du Stored Procedures häufig bei der Arbeit mit Unternehmensanwendungen, ERP-Systemen und überall dort, wo komplexe Datenbanklogik performant ausgeführt werden muss. Auch die Zusammenarbeit mit Datenbankadministratoren erfordert Kenntnisse über Stored Procedures, da diese oft für Wartungsaufgaben, Datenmigrationen und Reporting eingesetzt werden.

Quellen und weiterführende Links

• W3Schools SQL Stored Procedures - Einführung mit Beispielen
• MySQL CREATE PROCEDURE Syntax - Offizielle MySQL-Dokumentation
• Microsoft T-SQL CREATE PROCEDURE - SQL Server Referenz
• PostgreSQL CREATE PROCEDURE - PostgreSQL-Dokumentation
• OWASP SQL Injection Prevention - Sicherheitsaspekte