Esempio di procedura complessa per la gestione degli ordini: validazione dati, aggiornamento stato, logging

Oggi vediamo come costruire una procedura reale per la gestione degli ordini. Comprende diversi step: validazione dei dati, aggiornamento dello stato dell’ordine e anche logging. Immagina un ristorante dove chef, cameriere e cassiere devono lavorare in sincronia. Nella nostra procedura implementiamo una logica simile di collaborazione tra gli step.

Descrizione del compito della procedura

La procedura per la gestione degli ordini deve eseguire i seguenti step:

1. Verificare se il prodotto richiesto è disponibile in magazzino.
2. Se il prodotto è sufficiente, scalare la quantità dal magazzino.
3. Aggiornare lo stato dell’ordine a "Elaborato".
4. Scrivere le info sull’operazione riuscita nel log.
5. Se succede un errore, fare rollback di tutte le modifiche.

Implementazione della procedura

Step 1. Creiamo schema e tabelle per lavorare

Prima di scrivere la procedura, creiamo le tabelle con cui lavorerà.

Tabella orders — ordini

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

Questa tabella contiene gli ordini. Ogni ordine ha un cliente, un id prodotto, una quantità e uno stato (di default "In attesa di elaborazione").

Tabella inventory — magazzino

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

Tabella con la lista dei prodotti in magazzino. Ogni prodotto ha una quantità attuale (stock).

Tabella order_logs — log operazioni

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

Il log verrà usato per scrivere info sullo stato di esecuzione degli ordini.

Step 2. Struttura della procedura

Ecco la struttura della procedura multi-step:

1. Verificare se il prodotto richiesto è in magazzino e se ce n’è abbastanza.
2. Se il prodotto è sufficiente, diminuire la quantità nella tabella inventory.
3. Cambiare lo stato dell’ordine a "Elaborato".
4. Scrivere il risultato positivo nella tabella order_logs.
5. Gestire eventuali errori con rollback delle modifiche.

Step 3. Scrittura della procedura

Dai, scriviamo la procedura process_order per fare tutti gli step sopra.

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
DECLARE
    v_product_id INT;
    v_quantity INT;
    v_stock INT;
BEGIN
    -- Step 1: Prendiamo info sull’ordine
    SELECT product_id, quantity
    INTO v_product_id, v_quantity
    FROM orders
    WHERE order_id = $1;

    -- Controlliamo se l’ordine esiste
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Ordine con ID % non esiste.', $1;
    END IF;

    -- Step 2: Controlliamo la presenza del prodotto in magazzino
    SELECT stock INTO v_stock
    FROM inventory
    WHERE product_id = v_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Prodotto con ID % non esiste in magazzino.', v_product_id;
    END IF;

    IF v_stock < v_quantity THEN
        RAISE EXCEPTION 'Stock insufficiente per prodotto ID %. Richiesto: %, Disponibile: %.',
            v_product_id, v_quantity, v_stock;
    END IF;

    -- Step 3: Diminuiamo la quantità del prodotto in magazzino
    UPDATE inventory
    SET stock = stock - v_quantity
    WHERE product_id = v_product_id;

    -- Step 4: Aggiorniamo lo stato dell’ordine a 'Processed'
    UPDATE orders
    SET status = 'Processed'
    WHERE order_id = $1;

    -- Step 5: Scriviamo l’esito positivo nel log
    INSERT INTO order_logs (order_id, log_message)
    VALUES ($1, 'Ordine elaborato con successo.');

EXCEPTION
    WHEN OTHERS THEN
        -- Logghiamo l’errore in caso di fallimento
        INSERT INTO order_logs (order_id, log_message)
        VALUES ($1, 'Errore nell’elaborazione ordine: ' || SQLERRM);

        -- Facciamo rollback di tutte le modifiche
        RAISE;
END;
$$ LANGUAGE plpgsql;

Vediamo un attimo questa procedura.

1. Fase di controllo:

   controlliamo se l’ordine indicato esiste nella tabella orders. Se non c’è, viene lanciata un’eccezione con messaggio dettagliato. Allo stesso modo, controlliamo la presenza e la quantità del prodotto in magazzino.

2. Fase magazzino:

   se il prodotto è sufficiente, ne diminuiamo la quantità in magazzino. Si fa con UPDATE.

3. Fase cambio stato ordine:

   cambiamo lo stato in "Processed" (Elaborato) per indicare che l’ordine è andato a buon fine.

4. Fase logging:

   dopo l’elaborazione positiva dell’ordine aggiungiamo un messaggio nella tabella order_logs per salvare info sull’operazione.

5. Gestione eccezioni:

   se qualcosa va storto, intercettiamo l’errore nel blocco EXCEPTION, scriviamo nel log un messaggio dettagliato e facciamo rollback di tutto.

Esempi d’uso

Creiamo dei dati di test per provare la nostra procedura.

-- Aggiungiamo prodotti in magazzino
INSERT INTO inventory (product_name, stock)
VALUES ('Laptop', 10), ('Monitor', 5);

-- Aggiungiamo ordini
INSERT INTO orders (customer_name, product_id, quantity)
VALUES
    ('Alice', 1, 2),
    ('Bob', 2, 1),
    ('Charlie', 1, 20); -- Questo ordine deve generare errore

Ora testiamo la procedura:

-- Elaboriamo ordine Alice
SELECT process_order(1);

-- Elaboriamo ordine Bob
SELECT process_order(2);

-- Proviamo a elaborare ordine Charlie (errore)
SELECT process_order(3);

Risultati:

• Gli ordini di Alice e Bob saranno elaborati con successo, verranno scritti nel log e le quantità in magazzino diminuiranno.
• L’ordine di Charlie genererà un errore per mancanza di prodotto in magazzino, e la scritta dell’errore apparirà nel log.

Controlliamo le tabelle dopo le query:

SELECT * FROM inventory; -- Cambiamenti nelle scorte
SELECT * FROM orders; -- Cambiamenti negli stati degli ordini
SELECT * FROM order_logs; -- Voci nel log

Errori tipici e consigli

1. Errore: hai dimenticato di controllare NOT FOUND dopo SELECT INTO.

   Gestisci sempre i casi in cui la query non restituisce nulla, altrimenti rischi eccezioni inaspettate.

2. Errore: non hai aggiunto il blocco EXCEPTION.

   Se nella procedura non c’è un gestore degli errori, in caso di eccezione la transazione può bloccarsi o rompere la logica.

3. Consiglio: proteggiti dalle SQL injection.

   Usa parametri fortemente tipizzati ed evita SQL dinamico se non serve davvero.

Estensione della procedura

Nella vita reale puoi aggiungere altri controlli, tipo:

• Considerare sconti o promozioni per i clienti.
• Verificare il limite di credito del cliente prima di elaborare l’ordine.
• Loggare non solo le operazioni riuscite, ma anche i rollback.