Structures de contrôle dans les procédures

3.1 IF ... THEN ... ELSEIF ... ELSE ... END IF

Besoin : classer un client selon son nombre de commandes (nouveau, regulier, fidele).

sql

DELIMITER //

CREATE PROCEDURE classement_client(
    IN p_id_client INT,
    OUT p_categorie VARCHAR(50)
)
BEGIN
    DECLARE v_nb_commandes INT;

    SELECT COUNT(*)
    INTO v_nb_commandes
    FROM commande
    WHERE id_client = p_id_client;

    IF v_nb_commandes = 0 THEN
        SET p_categorie = 'Prospect';
    ELSEIF v_nb_commandes < 5 THEN
        SET p_categorie = 'Nouveau';
    ELSEIF v_nb_commandes < 20 THEN
        SET p_categorie = 'Regulier';
    ELSE
        SET p_categorie = 'Fidele';
    END IF;
END //

DELIMITER ;

Test :

sql

CALL classement_client(1, @cat);
SELECT @cat;

Syntaxe a retenir : IF condition THEN ... ELSEIF condition THEN ... ELSE ... END IF;

Ne pas oublier le THEN apres chaque condition. Ne pas oublier le END IF a la fin (avec un point-virgule).

3.2 CASE ... WHEN ... THEN ... END CASE

Deux formes.

Forme 1 : CASE simple (comparaison d'egalite)

sql

DELIMITER //

CREATE PROCEDURE libelle_jour(IN p_numero INT, OUT p_jour VARCHAR(20))
BEGIN
    CASE p_numero
        WHEN 1 THEN SET p_jour = 'Lundi';
        WHEN 2 THEN SET p_jour = 'Mardi';
        WHEN 3 THEN SET p_jour = 'Mercredi';
        WHEN 4 THEN SET p_jour = 'Jeudi';
        WHEN 5 THEN SET p_jour = 'Vendredi';
        WHEN 6 THEN SET p_jour = 'Samedi';
        WHEN 7 THEN SET p_jour = 'Dimanche';
        ELSE SET p_jour = 'Invalide';
    END CASE;
END //

DELIMITER ;

Forme 2 : CASE recherche (conditions quelconques)

sql

CASE
    WHEN v_note >= 16 THEN SET v_mention = 'Tres bien';
    WHEN v_note >= 14 THEN SET v_mention = 'Bien';
    WHEN v_note >= 12 THEN SET v_mention = 'Assez bien';
    WHEN v_note >= 10 THEN SET v_mention = 'Passable';
    ELSE SET v_mention = 'Insuffisant';
END CASE;

3.3 WHILE ... DO ... END WHILE

La condition est testee avant chaque iteration. Si la condition est fausse des le depart, le corps n'est jamais execute.

sql

DELIMITER //

CREATE PROCEDURE inserer_produits_test(IN p_nombre INT)
BEGIN
    DECLARE v_compteur INT DEFAULT 1;

    WHILE v_compteur <= p_nombre DO
        INSERT INTO produit (designation, prix_ht, stock)
        VALUES (CONCAT('Produit test ', v_compteur), v_compteur * 10.00, 100);

        SET v_compteur = v_compteur + 1;
    END WHILE;
END //

DELIMITER ;

Test :

sql

CALL inserer_produits_test(5);
SELECT * FROM produit WHERE designation LIKE 'Produit test%';

3.4 REPEAT ... UNTIL ... END REPEAT

La condition est testee apres chaque iteration. Le corps est execute au moins une fois.

sql

DELIMITER //

CREATE PROCEDURE decompte(IN p_depart INT)
BEGIN
    DECLARE v_n INT DEFAULT p_depart;

    REPEAT
        SELECT v_n AS valeur;
        SET v_n = v_n - 1;
    UNTIL v_n < 0
    END REPEAT;
END //

DELIMITER ;

Attention : pas de point-virgule apres la condition UNTIL. C'est UNTIL condition END REPEAT; (le ; est apres END REPEAT).

3.5 LOOP / LEAVE / ITERATE

LOOP cree une boucle infinie. On en sort avec LEAVE. On passe a l'iteration suivante avec ITERATE. La boucle doit porter un label (etiquette).

sql

DELIMITER //

CREATE PROCEDURE somme_entiers(IN p_n INT, OUT p_somme INT)
BEGIN
    DECLARE v_i INT DEFAULT 1;
    SET p_somme = 0;

    boucle_somme: LOOP
        IF v_i > p_n THEN
            LEAVE boucle_somme;
        END IF;

        SET p_somme = p_somme + v_i;
        SET v_i = v_i + 1;
    END LOOP boucle_somme;
END //

DELIMITER ;

Test :

sql

CALL somme_entiers(10, @s);
SELECT @s;
-- Resultat : 55

Exemple avec ITERATE (equivalent de continue dans les langages de programmation) :

sql

DELIMITER //

CREATE PROCEDURE somme_pairs(IN p_n INT, OUT p_somme INT)
BEGIN
    DECLARE v_i INT DEFAULT 0;
    SET p_somme = 0;

    boucle: LOOP
        SET v_i = v_i + 1;

        IF v_i > p_n THEN
            LEAVE boucle;
        END IF;

        IF v_i % 2 != 0 THEN
            ITERATE boucle;   -- sauter les impairs
        END IF;

        SET p_somme = p_somme + v_i;
    END LOOP boucle;
END //

DELIMITER ;

3.6 Curseurs

Un curseur permet de parcourir un jeu de resultats ligne par ligne. C'est necessaire quand on doit effectuer un traitement individuel sur chaque ligne (pas possible avec un simple SELECT).

Principe

DECLARE le curseur (associe a une requete SELECT).
DECLARE un handler pour detecter la fin du resultat.
OPEN le curseur (execute la requete).
FETCH chaque ligne dans des variables.
CLOSE le curseur.

Ordre obligatoire des DECLARE

Les DECLARE doivent respecter un ordre strict dans MySQL :

Variables locales (DECLARE v_xxx TYPE)
Curseurs (DECLARE nom_curseur CURSOR FOR ...)
Handlers (DECLARE CONTINUE HANDLER FOR ...)

Ne pas respecter cet ordre provoque une erreur de syntaxe.

Exemple complet

Besoin : pour chaque produit dont le stock est inferieur a 10, augmenter le stock de 50 unites et logger l'operation.

sql

DELIMITER //

CREATE PROCEDURE reapprovisionner_stock()
BEGIN
    DECLARE v_id INT;
    DECLARE v_designation VARCHAR(200);
    DECLARE v_stock INT;
    DECLARE v_fini BOOLEAN DEFAULT FALSE;

    -- 1. Declaration du curseur
    DECLARE cur_produits CURSOR FOR
        SELECT id_produit, designation, stock
        FROM produit
        WHERE stock < 10;

    -- 2. Handler : quand il n'y a plus de lignes, mettre v_fini a TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_fini = TRUE;

    -- 3. Ouvrir le curseur
    OPEN cur_produits;

    -- 4. Boucle de lecture
    boucle_lecture: LOOP
        FETCH cur_produits INTO v_id, v_designation, v_stock;

        IF v_fini THEN
            LEAVE boucle_lecture;
        END IF;

        -- Traitement : augmenter le stock de 50
        UPDATE produit
        SET stock = stock + 50
        WHERE id_produit = v_id;

        -- Logger dans historique
        INSERT INTO historique_produit
            (id_produit, ancien_stock, nouveau_stock, utilisateur)
        VALUES
            (v_id, v_stock, v_stock + 50, CURRENT_USER());
    END LOOP boucle_lecture;

    -- 5. Fermer le curseur
    CLOSE cur_produits;
END //

DELIMITER ;

Explication pas a pas :

DECLARE cur_produits CURSOR FOR SELECT ... : on associe le curseur a la requete.
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fini = TRUE; : quand FETCH ne trouve plus de ligne, MySQL declenche la condition NOT FOUND. Le handler met v_fini a TRUE. CONTINUE signifie que la procedure continue (ne s'arrete pas).
OPEN cur_produits : execute la requete et prepare le resultat.
FETCH cur_produits INTO v_id, v_designation, v_stock : lit la ligne suivante et stocke les valeurs dans les variables. L'ordre et le nombre de variables doivent correspondre exactement aux colonnes du SELECT.
Quand FETCH echoue (plus de lignes), le handler met v_fini a TRUE, et le IF suivant sort de la boucle.
CLOSE cur_produits : libere les ressources.

Test :

sql

-- Preparer des donnees de test
INSERT INTO produit (designation, prix_ht, stock) VALUES
    ('Clavier', 25.00, 3),
    ('Souris', 15.00, 7),
    ('Ecran', 200.00, 50);

CALL reapprovisionner_stock();

-- Verifier
SELECT * FROM produit WHERE designation IN ('Clavier', 'Souris', 'Ecran');
-- Clavier : stock = 53, Souris : stock = 57, Ecran : stock inchange (50 >= 10)

SELECT * FROM historique_produit;
-- Deux lignes : Clavier et Souris

3.1 IF ... THEN ... ELSEIF ... ELSE ... END IF

3.2 CASE ... WHEN ... THEN ... END CASE

3.3 WHILE ... DO ... END WHILE

3.4 REPEAT ... UNTIL ... END REPEAT

3.5 LOOP / LEAVE / ITERATE

3.6 Curseurs

Principe

Ordre obligatoire des DECLARE

Exemple complet

Chapitre terminé !

Chapitre terminé !