Przykład złożonej procedury do obsługi zamówień: walidacja danych, aktualizacja statusu, logowanie

Dzisiaj ogarniemy jak zbudować realną procedurę do obsługi zamówień. Składa się ona z kilku etapów: walidacji danych, aktualizacji statusu zamówienia oraz logowania. Wyobraź sobie restaurację, gdzie szef kuchni, kelner i kasjer muszą działać razem. W naszej procedurze wdrożymy podobną logikę współpracy między etapami.

Opis zadania procedury

Procedura do obsługi zamówień powinna wykonać następujące kroki:

1. Sprawdzić, czy potrzebny towar jest dostępny na magazynie.
2. Jeśli towaru jest wystarczająco, zdjąć jego ilość z magazynu.
3. Zaktualizować status zamówienia na "Przetworzone".
4. Zapisać info o udanej operacji do dziennika (logu).
5. W przypadku jakiegokolwiek błędu cofnąć zmiany do początku.

Implementacja procedury

Krok 1. Tworzymy schemat i tabele do pracy

Zanim napiszemy procedurę, stworzymy tabele, na których będzie działać.

Tablica orders — zamówienia

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'
);

Ta tabela przechowuje zamówienia. Każde zamówienie ma klienta, identyfikator produktu, ilość i status (domyślnie "Oczekuje na przetworzenie").

Tablica inventory — magazyn

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

Tablica z listą towarów na magazynie. Każdy produkt ma aktualny stan (stock).

Tablica order_logs — dziennik operacji

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

Dziennik będzie używany do zapisywania informacji o statusie realizacji zamówień.

Krok 2. Struktura procedury

Oto struktura wieloetapowej procedury:

1. Sprawdzić, czy żądany towar jest na magazynie i czy jest go wystarczająco.
2. Jeśli towaru jest dość, zmniejszyć jego ilość w tabeli inventory.
3. Zmienić status zamówienia na "Przetworzone".
4. Zapisać udany wynik do tabeli order_logs.
5. Obsłużyć możliwe błędy z cofnięciem zmian.

Krok 3. Pisanie procedury

Napiszmy procedurę process_order do wykonania powyższych kroków.

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
DECLARE
    v_product_id INT;
    v_quantity INT;
    v_stock INT;
BEGIN
    -- Krok 1: Pobieramy info o zamówieniu
    SELECT product_id, quantity
    INTO v_product_id, v_quantity
    FROM orders
    WHERE order_id = $1;

    -- Sprawdzamy, czy zamówienie istnieje
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Zamówienie o ID % nie istnieje.', $1;
    END IF;

    -- Krok 2: Sprawdzamy dostępność towaru na magazynie
    SELECT stock INTO v_stock
    FROM inventory
    WHERE product_id = v_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Produkt o ID % nie istnieje w magazynie.', v_product_id;
    END IF;

    IF v_stock < v_quantity THEN
        RAISE EXCEPTION 'Za mało towaru dla produktu ID %. Zamówiono: %, Dostępne: %.',
            v_product_id, v_quantity, v_stock;
    END IF;

    -- Krok 3: Zmniejszamy ilość towaru na magazynie
    UPDATE inventory
    SET stock = stock - v_quantity
    WHERE product_id = v_product_id;

    -- Krok 4: Aktualizujemy status zamówienia na 'Processed'
    UPDATE orders
    SET status = 'Processed'
    WHERE order_id = $1;

    -- Krok 5: Zapisujemy udane wykonanie do dziennika
    INSERT INTO order_logs (order_id, log_message)
    VALUES ($1, 'Zamówienie przetworzone pomyślnie.');

EXCEPTION
    WHEN OTHERS THEN
        -- Logujemy błąd przy każdej porażce
        INSERT INTO order_logs (order_id, log_message)
        VALUES ($1, 'Błąd podczas przetwarzania zamówienia: ' || SQLERRM);

        -- Cofamy wszystkie zmiany
        RAISE;
END;
$$ LANGUAGE plpgsql;

Ogarniemy teraz tę procedurę.

1. Etap sprawdzania:

   sprawdzamy, czy podane zamówienie istnieje w tabeli orders. Jeśli zamówienia nie ma, rzucamy wyjątek z dokładną wiadomością. Podobnie sprawdzamy obecność i ilość towaru na magazynie.

2. Etap pracy z magazynem:

   jeśli towaru jest wystarczająco, zmniejszamy jego ilość na magazynie. To robimy przez UPDATE.

3. Etap zmiany statusu zamówienia:

   zmieniamy status na "Processed" (Przetworzone), żeby pokazać, że zamówienie zostało ogarnięte.

4. Etap logowania:

   po udanym przetworzeniu zamówienia dodajemy wiadomość do tabeli order_logs, żeby zachować info o operacji.

5. Obsługa wyjątków:

   jeśli coś pójdzie nie tak, łapiemy błąd w bloku EXCEPTION, zapisujemy do logu szczegółową wiadomość o błędzie i cofamy wszystkie zmiany.

Przykłady użycia

Stwórzmy dane testowe, żeby sprawdzić naszą procedurę.

-- Dodajemy towar na magazyn
INSERT INTO inventory (product_name, stock)
VALUES ('Laptop', 10), ('Monitor', 5);

-- Dodajemy zamówienia
INSERT INTO orders (customer_name, product_id, quantity)
VALUES
    ('Alice', 1, 2),
    ('Bob', 2, 1),
    ('Charlie', 1, 20); -- To zamówienie powinno wywołać błąd

Teraz testujemy procedurę:

-- Przetwarzamy zamówienie Alice
SELECT process_order(1);

-- Przetwarzamy zamówienie Bob
SELECT process_order(2);

-- Próbujemy przetworzyć zamówienie Charlie (błąd)
SELECT process_order(3);

Wyniki:

• Zamówienia Alice i Bob zostaną przetworzone poprawnie, zapiszą się do logu, a stany magazynowe się zmniejszą.
• Zamówienie Charlie wywoła błąd przez brak wystarczającej ilości towaru na magazynie i wpis o błędzie pojawi się w logu.

Sprawdzamy tabele po wykonaniu zapytań:

SELECT * FROM inventory; -- Zmiany w stanach magazynowych
SELECT * FROM orders; -- Zmiany w statusach zamówień
SELECT * FROM order_logs; -- Wpisy w dzienniku

Typowe błędy i porady

1. Błąd: zapomniano sprawdzić NOT FOUND po SELECT INTO.

   Zawsze obsługuj przypadki, gdy zapytanie zwraca pusty wynik, bo inaczej pojawią się nieoczekiwane wyjątki.

2. Błąd: brak bloku EXCEPTION.

   Jeśli w procedurze nie ma obsługi błędów, przy wyjątku transakcja może się zawiesić lub popsuć logikę działania.

3. Porada: chroń się przed SQL injection.

   Używaj ściśle typowanych parametrów i unikaj dynamicznego SQL, jeśli nie jest to konieczne.

Rozszerzenie procedury

W realu można dodać więcej sprawdzeń, na przykład:

• Uwzględniać zniżki lub promocje dla klientów.
• Sprawdzać limit kredytowy klienta przed obsługą zamówienia.
• Logować nie tylko udane operacje, ale też cofnięcia.