Exemple de procédure complexe pour le traitement des commandes : validation des données, mise à jour du statut, logging

Aujourd'hui, on va décortiquer la création d'une vraie procédure pour gérer les commandes. Elle comprend plusieurs étapes : validation des données, mise à jour du statut de la commande, et aussi logging. Imagine un resto où le chef, le serveur et le caissier doivent bosser ensemble. Dans notre procédure, on va faire un truc du même genre entre les étapes.

Description de la tâche de la procédure

La procédure pour gérer les commandes doit faire les étapes suivantes :

1. Vérifier si le produit demandé est dispo en stock.
2. Si le stock est suffisant, déduire la quantité du stock.
3. Mettre à jour le statut de la commande pour qu'il devienne "Traitée".
4. Enregistrer l'info sur l'opération réussie dans le journal (log).
5. En cas d'erreur, rollback tous les changements.

Implémentation de la procédure

Étape 1. On crée le schéma et les tables pour bosser

Avant d'écrire la procédure, on va créer les tables avec lesquelles elle va bosser.

Table orders — commandes

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

Cette table stocke les commandes. Chaque commande a un client, un identifiant de produit, une quantité et un statut (par défaut "En attente de traitement").

Table inventory — stock

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

Table avec la liste des produits en stock. Chaque produit a un stock actuel (stock).

Table order_logs — journal des opérations

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

Le journal va servir à enregistrer les infos sur le statut des commandes.

Étape 2. Structure de la procédure

Voilà la structure de la procédure multi-étapes :

1. Vérifier si le produit demandé est en stock et s'il y en a assez.
2. Si le stock est suffisant, diminuer la quantité dans la table inventory.
3. Changer le statut de la commande en "Traitée".
4. Enregistrer le résultat réussi dans la table order_logs.
5. Gérer les erreurs possibles avec rollback.

Étape 3. Écriture de la procédure

On va écrire la procédure process_order pour faire tout ça.

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
DECLARE
    v_product_id INT;
    v_quantity INT;
    v_stock INT;
BEGIN
    -- Étape 1 : On récupère les infos de la commande
    SELECT product_id, quantity
    INTO v_product_id, v_quantity
    FROM orders
    WHERE order_id = $1;

    -- On vérifie si la commande existe
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Commande avec ID % n''existe pas.', $1;
    END IF;

    -- Étape 2 : On vérifie la présence du produit en stock
    SELECT stock INTO v_stock
    FROM inventory
    WHERE product_id = v_product_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Produit avec ID % n''existe pas dans le stock.', v_product_id;
    END IF;

    IF v_stock < v_quantity THEN
        RAISE EXCEPTION 'Pas assez de stock pour le produit ID %. Demandé : %, Disponible : %.',
            v_product_id, v_quantity, v_stock;
    END IF;

    -- Étape 3 : On diminue la quantité du produit en stock
    UPDATE inventory
    SET stock = stock - v_quantity
    WHERE product_id = v_product_id;

    -- Étape 4 : On met à jour le statut de la commande à "Processed"
    UPDATE orders
    SET status = 'Processed'
    WHERE order_id = $1;

    -- Étape 5 : On enregistre le succès dans le journal
    INSERT INTO order_logs (order_id, log_message)
    VALUES ($1, 'Commande traitée avec succès.');

EXCEPTION
    WHEN OTHERS THEN
        -- On log l'erreur si ça foire
        INSERT INTO order_logs (order_id, log_message)
        VALUES ($1, 'Erreur lors du traitement de la commande : ' || SQLERRM);

        -- On rollback tout
        RAISE;
END;
$$ LANGUAGE plpgsql;

Allez, on décortique cette procédure.

1. Étape de vérification :

   on vérifie si la commande indiquée existe dans la table orders. Si la commande n'est pas trouvée, on lève une exception avec un message détaillé. Pareil pour la présence et la quantité du produit en stock.

2. Étape gestion du stock :

   si le stock est suffisant, on diminue la quantité en stock. Ça se fait avec UPDATE.

3. Étape changement de statut de la commande :

   on change le statut en "Processed" (Traitée) pour indiquer que la commande est bien finie.

4. Étape logging :

   après le traitement réussi de la commande, on ajoute un message dans la table order_logs pour garder une trace de l'opération.

5. Gestion des exceptions :

   si un truc se passe mal, on chope l'erreur dans le bloc EXCEPTION, on écrit un message détaillé dans le log et on rollback tout.

Exemples d'utilisation

On va créer des données de test pour vérifier notre procédure.

-- On ajoute des produits en stock
INSERT INTO inventory (product_name, stock)
VALUES ('Laptop', 10), ('Monitor', 5);

-- On ajoute des commandes
INSERT INTO orders (customer_name, product_id, quantity)
VALUES
    ('Alice', 1, 2),
    ('Bob', 2, 1),
    ('Charlie', 1, 20); -- Cette commande doit provoquer une erreur

Maintenant, on teste la procédure :

-- On traite la commande d'Alice
SELECT process_order(1);

-- On traite la commande de Bob
SELECT process_order(2);

-- On essaie de traiter la commande de Charlie (erreur)
SELECT process_order(3);

Résultats :

• Les commandes d'Alice et Bob seront traitées avec succès, enregistrées dans le log, et le stock sera diminué.
• La commande de Charlie va provoquer une erreur à cause du manque de stock, et une entrée d'erreur apparaîtra dans le log.

On vérifie les tables après les requêtes :

SELECT * FROM inventory; -- Changements dans les stocks
SELECT * FROM orders; -- Changements dans les statuts des commandes
SELECT * FROM order_logs; -- Entrées dans le journal

Erreurs typiques et conseils

1. Erreur : on a oublié de vérifier NOT FOUND après SELECT INTO.

   Pense toujours à gérer les cas où la requête renvoie rien, sinon tu risques des exceptions surprises.

2. Erreur : pas de bloc EXCEPTION ajouté.

   Si tu prévois pas de gestion d'erreur dans la procédure, une exception peut bloquer la transaction ou casser la logique.

3. Conseil : protège-toi contre les injections SQL.

   Utilise des paramètres typés strictement et évite le SQL dynamique si t'en as pas besoin.

Extension de la procédure

Dans la vraie vie, tu peux ajouter plus de vérifs, par exemple :

• Prendre en compte les remises ou promos pour les clients.
• Vérifier le plafond de crédit du client avant de traiter la commande.
• Logger non seulement les succès mais aussi les rollbacks.

2

Mission

SQL SELF, niveau 54, leçon 0

Bloqué

Procédure de traitement de commande

Procédure de traitement de commande

Ouvrir