Beispiel einer komplexen Prozedur zur Bestellbearbeitung: Datenvalidierung, Status-Update, Logging

Heute schauen wir uns an, wie man eine echte Prozedur zur Bearbeitung von Bestellungen aufbaut. Sie besteht aus mehreren Schritten: Datenvalidierung, Status-Update der Bestellung und Logging. Stell dir ein Restaurant vor, wo der Chefkoch, der Kellner und der Kassierer zusammenarbeiten müssen. In unserer Prozedur setzen wir eine ähnliche Logik für die Zusammenarbeit zwischen den Schritten um.

Beschreibung der Aufgabenstellung

Die Prozedur zur Bestellbearbeitung soll folgende Schritte ausführen:

1. Prüfen, ob das gewünschte Produkt auf Lager ist.
2. Wenn genug da ist, die Menge vom Lager abziehen.
3. Status der Bestellung auf "Bearbeitet" setzen.
4. Info über die erfolgreiche Operation ins Log schreiben.
5. Bei jedem Fehler alles auf Anfang zurücksetzen (Rollback).

Implementierung der Prozedur

Schritt 1. Schema und Tabellen anlegen

Bevor wir die Prozedur schreiben, legen wir die Tabellen an, mit denen sie arbeitet.

Tabelle orders — Bestellungen

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    status TEXT DEFAULT 'Pending'
);

Diese Tabelle speichert die Bestellungen. Jede Bestellung hat einen Kunden, eine Produkt-ID, eine Menge und einen Status (standardmäßig "Wartet auf Bearbeitung").

Tabelle inventory — Lager

CREATE TABLE inventory (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL UNIQUE,
    stock INT NOT NULL CHECK (stock >= 0)
);

Tabelle mit der Liste der Produkte im Lager. Jedes Produkt hat einen aktuellen Bestand (stock).

Tabelle order_logs — Logbuch

CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    log_message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Das Logbuch wird genutzt, um Infos über den Status der Bestellbearbeitung zu speichern.

Schritt 2. Struktur der Prozedur

So sieht die Struktur der mehrstufigen Prozedur aus:

1. Prüfen, ob das gewünschte Produkt im Lager ist und ob genug davon da ist.
2. Wenn genug da ist, die Menge in der Tabelle inventory verringern.
3. Status der Bestellung auf "Bearbeitet" setzen.
4. Erfolgreiches Ergebnis in die Tabelle order_logs schreiben.
5. Mögliche Fehler mit Rollback behandeln.

Schritt 3. Prozedur schreiben

Lass uns die Prozedur process_order für die oben beschriebenen Schritte schreiben.

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
DECLARE
    v_product_id INT;
    v_quantity INT;
    v_stock INT;
BEGIN
    -- Schritt 1: Bestellinfos holen
    SELECT product_id, quantity
    INTO v_product_id, v_quantity
    FROM orders
    WHERE order_id = $1;

    -- Prüfen, ob die Bestellung existiert
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Bestellung mit ID % existiert nicht.', $1;
    END IF;

    -- Schritt 2: Prüfen, ob das Produkt im Lager ist
    SELECT stock INTO v_stock
    FROM inventory
    WHERE product_id = v_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Produkt mit ID % existiert nicht im Lager.', v_product_id;
    END IF;

    IF v_stock < v_quantity THEN
        RAISE EXCEPTION 'Nicht genug Bestand für Produkt ID %. Angefragt: %, Verfügbar: %.',
            v_product_id, v_quantity, v_stock;
    END IF;

    -- Schritt 3: Produktmenge im Lager verringern
    UPDATE inventory
    SET stock = stock - v_quantity
    WHERE product_id = v_product_id;

    -- Schritt 4: Bestellstatus auf 'Processed' setzen
    UPDATE orders
    SET status = 'Processed'
    WHERE order_id = $1;

    -- Schritt 5: Erfolg ins Log schreiben
    INSERT INTO order_logs (order_id, log_message)
    VALUES ($1, 'Bestellung erfolgreich bearbeitet.');

EXCEPTION
    WHEN OTHERS THEN
        -- Fehler ins Log schreiben, falls was schiefgeht
        INSERT INTO order_logs (order_id, log_message)
        VALUES ($1, 'Fehler bei der Bearbeitung der Bestellung: ' || SQLERRM);

        -- Alles zurückrollen
        RAISE;
END;
$$ LANGUAGE plpgsql;

Schauen wir uns die Prozedur im Detail an.

1. Prüfschritt:

   Wir prüfen, ob die angegebene Bestellung in der Tabelle orders existiert. Wenn nicht, wird eine Exception mit einer genauen Nachricht geworfen. Genauso prüfen wir, ob das Produkt im Lager ist und wie viel davon da ist.

2. Lager-Schritt:

   Wenn genug da ist, verringern wir die Menge im Lager. Das passiert mit UPDATE.

3. Status-Update-Schritt:

   Wir setzen den Status auf "Processed" (Bearbeitet), um zu zeigen, dass die Bestellung erfolgreich abgeschlossen wurde.

4. Logging-Schritt:

   Nach erfolgreicher Bearbeitung der Bestellung fügen wir eine Nachricht in die Tabelle order_logs ein, um die Aktion zu dokumentieren.

5. Exception-Handling:

   Wenn etwas schiefgeht, fangen wir den Fehler im EXCEPTION-Block ab, schreiben eine genaue Fehlermeldung ins Log und rollen alle Änderungen zurück.

Beispiele zur Nutzung

Wir legen Testdaten an, um unsere Prozedur zu testen.

-- Produkt ins Lager legen
INSERT INTO inventory (product_name, stock)
VALUES ('Laptop', 10), ('Monitor', 5);

-- Bestellungen anlegen
INSERT INTO orders (customer_name, product_id, quantity)
VALUES
    ('Alice', 1, 2),
    ('Bob', 2, 1),
    ('Charlie', 1, 20); -- Diese Bestellung soll einen Fehler auslösen

Jetzt testen wir die Prozedur:

-- Bestellung von Alice bearbeiten
SELECT process_order(1);

-- Bestellung von Bob bearbeiten
SELECT process_order(2);

-- Versuch, Bestellung von Charlie zu bearbeiten (Fehler)
SELECT process_order(3);

Ergebnisse:

• Die Bestellungen von Alice und Bob werden erfolgreich bearbeitet, ins Log geschrieben und der Lagerbestand wird reduziert.
• Die Bestellung von Charlie löst einen Fehler aus, weil nicht genug auf Lager ist, und der Fehler wird im Log festgehalten.

Schauen wir uns die Tabellen nach den Abfragen an:

SELECT * FROM inventory; -- Änderungen im Bestand
SELECT * FROM orders; -- Änderungen im Bestellstatus
SELECT * FROM order_logs; -- Logeinträge

Typische Fehler und Tipps

1. Fehler: NOT FOUND nach SELECT INTO vergessen.

   Behandle immer den Fall, dass eine Abfrage kein Ergebnis liefert, sonst gibt's unerwartete Exceptions.

2. Fehler: EXCEPTION-Block fehlt.

   Wenn du keinen Fehler-Handler hast, kann eine Exception die Transaktion blockieren oder die Logik kaputt machen.

3. Tipp: Schütze dich vor SQL-Injections.

   Nutze strikt typisierte Parameter und vermeide dynamisches SQL, wenn es nicht nötig ist.

Prozedur erweitern

Im echten Leben kann man noch mehr Checks einbauen, zum Beispiel:

• Rabatte oder Aktionen für Kunden berücksichtigen.
• Kreditlimit des Kunden vor der Bearbeitung prüfen.
• Nicht nur erfolgreiche Aktionen, sondern auch Rollbacks loggen.