Structurer ses instructions

• Procédures stockées
• Sommaire
• Gestionnaires d'erreurs, curseurs et utilisation avancée

Lorsque l'on écrit une série d'instructions, par exemple dans le corps d'une procédure stockée, il est nécessaire d'être capable de structurer ses instructions. Cela va permettre d’instiller de la logique dans le traitement : exécuter telles ou telles instructions en fonction des données que l'on possède, répéter une instruction un certain nombre de fois, etc.

Voici quelques outils indispensables à la structuration des instructions :

• les variables locales : qui vont permettre de stocker et modifier des valeurs pendant le déroulement d'une procédure ;
• les conditions : qui vont permettre d'exécuter certaines instructions seulement si une certaine condition est remplie ;
• les boucles : qui vont permettre de répéter une instruction plusieurs fois.

Ces structures sont bien sûr utilisables dans les procédures stockées, que nous avons vues au chapitre précédent, mais pas uniquement. Elles sont utilisables dans tout objet définissant une série d'instructions à exécuter. C'est le cas des fonctions stockées (non couvertes par ce cours et qui forment avec les procédures stockées ce qu'on appelle les "routines"), des événements (non couverts), et également des triggers, auxquels un chapitre est consacré à la fin de cette partie.

• Blocs d'instructions et variables locales
• Structures conditionnelles
• Boucles

Blocs d'instructions et variables locales

Blocs d'instructions

Nous avons vu qu'un bloc d'instructions était défini par les mots-clés BEGIN et END, entre lesquels on met les instructions qui composent le bloc (de zéro à autant d'instructions que l'on veut, séparées bien sûr d'un ;).

Il est possible d'imbriquer plusieurs blocs d'instructions. De même, à l'intérieur d'un bloc d'instructions, plusieurs blocs d'instructions peuvent se suivre. Ceux-ci permettent donc de structurer les instructions en plusieurs parties distinctes et sur plusieurs niveaux d'imbrication différents.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

BEGIN
    SELECT 'Bloc d''instructions principal';

    BEGIN
        SELECT 'Bloc d''instructions 2, imbriqué dans le bloc principal';

        BEGIN
            SELECT 'Bloc d''instructions 3, imbriqué dans le bloc d''instructions 2';
        END;
    END;

    BEGIN
        SELECT 'Bloc d''instructions 4, imbriqué dans le bloc principal';
    END;

END;

Variables locales

Nous connaissons déjà les variables utilisateur, qui sont des variables désignées par @. J'ai également mentionné l'existence des variables système, qui sont des variables prédéfinies par MySQL. Voyons maintenant les variables locales, qui peuvent être définies dans un bloc d'instructions.

Déclaration d'une variable locale

La déclaration d'une variable locale se fait avec l'instruction DECLARE :

1

DECLARE nom_variable type_variable [DEFAULT valeur_defaut];

Cette instruction doit se trouver au tout début du bloc d'instructions dans lequel la variable locale sera utilisée (donc directement après le BEGIN).

On a donc une structure générale des blocs d'instructions qui se dégage :

1
2
3
4
5

BEGIN
    -- Déclarations (de variables locales par exemple)

    -- Instructions (dont éventuels blocs d'instructions imbriqués)
END;

Si aucune valeur par défaut n'est précisée, la variable vaudra NULL tant que sa valeur n'est pas changée. Pour changer la valeur d'une variable locale, on peut utiliser SET ou SELECT ... INTO.

Exemple : voici une procédure stockée qui donne la date d'aujourd'hui et de demain :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

DELIMITER |
CREATE PROCEDURE aujourdhui_demain ()
BEGIN
    DECLARE v_date DATE DEFAULT CURRENT_DATE();               -- On déclare une variable locale et on lui met une valeur par défaut

    SELECT DATE_FORMAT(v_date, '%W %e %M %Y') AS Aujourdhui;

    SET v_date = v_date + INTERVAL 1 DAY;                     -- On change la valeur de la variable locale
    SELECT DATE_FORMAT(v_date, '%W %e %M %Y') AS Demain;
END|
DELIMITER ;

Testons-la :

1
2

SET lc_time_names = 'fr_FR';
CALL aujourdhui_demain();

Portée des variables locales dans un bloc d'instruction

Les variables locales n'existent que dans le bloc d'instructions dans lequel elles ont été déclarées. Dès que le mot-clé END est atteint, toutes les variables locales du bloc sont détruites.

Exemple 1 :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

DELIMITER |
CREATE PROCEDURE test_portee1()
BEGIN
    DECLARE v_test1 INT DEFAULT 1;

    BEGIN
        DECLARE v_test2 INT DEFAULT 2;

        SELECT 'Imbriqué' AS Bloc;
        SELECT v_test1, v_test2;
    END;
    SELECT 'Principal' AS Bloc;
    SELECT v_test1, v_test2;

END|
DELIMITER ;

CALL test_portee1();

1

ERROR 1054 (42S22): Unknown column 'v_test2' in 'field list'

La variable locale v_test2 existe bien dans le bloc imbriqué, puisque c'est là qu'elle est définie, mais pas dans le bloc principal. v_test1 par contre existe dans le bloc principal (où elle est définie), mais aussi dans le bloc imbriqué.

Exemple 2 :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

DELIMITER |
CREATE PROCEDURE test_portee2()
BEGIN
    DECLARE v_test1 INT DEFAULT 1;

    BEGIN
        DECLARE v_test2 INT DEFAULT 2;

        SELECT 'Imbriqué 1' AS Bloc;
        SELECT v_test1, v_test2;
    END;

    BEGIN
        SELECT 'imbriqué 2' AS Bloc;
        SELECT v_test1, v_test2;    
    END;


END|
DELIMITER ;

CALL test_portee2();

1

ERROR 1054 (42S22): Unknown column 'v_test2' in 'field list'

À nouveau, v_test1, déclarée dans le bloc principal, existe dans les deux blocs imbriqués. Par contre, v_test2 n'existe que dans le bloc imbriqué dans lequel elle est déclarée.

Exemple 3 :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

DELIMITER |
CREATE PROCEDURE test_portee3()
BEGIN
    DECLARE v_test INT DEFAULT 1;

    SELECT v_test AS 'Bloc principal';

    BEGIN
        DECLARE v_test INT DEFAULT 0;

        SELECT v_test AS 'Bloc imbriqué';
        SET v_test = 2;
        SELECT v_test AS 'Bloc imbriqué après modification';
    END;

    SELECT v_test AS 'Bloc principal';
END |
DELIMITER ;

CALL test_portee3();

La variable locale v_test est déclarée dans le bloc principal et dans le bloc imbriqué, avec deux valeurs différentes. Mais lorsqu'on revient dans le bloc principal après exécution du bloc d'instructions imbriqué, v_test a toujours la valeur qu'elle avait avant l'exécution de ce bloc et sa deuxième déclaration. Il s'agit donc bien de deux variables locales distinctes.

Structures conditionnelles

Les structures conditionnelles permettent de déclencher une action ou une série d'instructions lorsqu'une condition préalable est remplie.

MySQL propose deux structures conditionnelles : IF et CASE.

La structure IF

Voici la syntaxe de la structure IF :

1
2
3
4
5

IF condition THEN instructions
[ELSEIF autre_condition THEN instructions
[ELSEIF ...]]
[ELSE instructions]
END IF;

Le cas le plus simple : si la condition est vraie, alors on exécute ces instructions

Voici la structure minimale d'un IF :

1
2
3

IF condition THEN
    instructions
END IF;

Soit on exécute les instructions (si la condition est vraie), soit on ne les exécute pas.

Exemple : la procédure suivante affiche 'J''ai déjà été adopté !', si c'est le cas, à partir de l'id d'un animal :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

DELIMITER |
CREATE PROCEDURE est_adopte(IN p_animal_id INT)
BEGIN
    DECLARE v_nb INT DEFAULT 0;           -- On crée une variable locale

    SELECT COUNT(*) INTO v_nb             -- On met le nombre de lignes correspondant à l'animal 
    FROM Adoption                         --  dans Adoption dans notre variable locale
    WHERE animal_id = p_animal_id;

    IF v_nb > 0 THEN                      -- On teste si v_nb est supérieur à 0 (donc si l'animal a été adopté)
        SELECT 'J''ai déjà été adopté !';
    END IF;                               -- Et on n'oublie surtout pas le END IF et le ; final
END |
DELIMITER ;

CALL est_adopte(3);
CALL est_adopte(28);

Seul le premier appel à la procédure va afficher 'J''ai déjà été adopté !', puisque l'animal 3 est présent dans la table Adoption, contrairement à l'animal 28.

Deuxième cas : si … alors, sinon …

Grâce au mot-clé ELSE, on peut définir une série d'instructions à exécuter si la condition est fausse.

Exemple : la procédure suivante affiche 'Je suis né avant 2010' ou 'Je suis né après 2010', selon la date de naissance de l'animal transmis en paramètre.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

DELIMITER |
CREATE PROCEDURE avant_apres_2010(IN p_animal_id INT)
BEGIN
    DECLARE v_annee INT;

    SELECT YEAR(date_naissance) INTO v_annee
    FROM Animal
    WHERE id = p_animal_id;

    IF v_annee < 2010 THEN
        SELECT 'Je suis né avant 2010' AS naissance;
    ELSE                                               -- Pas de THEN
        SELECT 'Je suis né après 2010' AS naissance;
    END IF;                                            -- Toujours obligatoire

END |
DELIMITER ;

CALL avant_apres_2010(34);   -- Né le 20/04/2008
CALL avant_apres_2010(69);   -- Né le 13/02/2012

Troisième et dernier cas : plusieurs conditions alternatives

Enfin, le mot-clé ELSEIF... THEN permet de vérifier d'autres conditions (en dehors de la condition du IF), chacune ayant une série d'instructions définies à exécuter en cas de véracité. Si plusieurs conditions sont vraies en même temps, seule la première rencontrée verra ses instructions exécutées. On peut bien sûr toujours (mais ce n'est pas obligatoire) ajouter un ELSE pour le cas où aucune condition ne serait vérifiée.

Exemple : cette procédure affiche un message différent selon le sexe de l'animal passé en paramètre.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

DELIMITER |
CREATE PROCEDURE message_sexe(IN p_animal_id INT)
BEGIN
    DECLARE v_sexe VARCHAR(10);

    SELECT sexe INTO v_sexe
    FROM Animal
    WHERE id = p_animal_id;

    IF (v_sexe = 'F') THEN                   -- Première possibilité
        SELECT 'Je suis une femelle !' AS sexe;
    ELSEIF (v_sexe = 'M') THEN                  -- Deuxième possibilité
        SELECT 'Je suis un mâle !' AS sexe;
    ELSE                                           -- Défaut
        SELECT 'Je suis en plein questionnement existentiel...' AS sexe;
    END IF;
END|
DELIMITER ;

CALL message_sexe(8);   -- Mâle
CALL message_sexe(6);   -- Femelle
CALL message_sexe(9);   -- Ni l'un ni l'autre

Il peut bien sûr y avoir autant de ELSEIF... THEN que l'on veut (mais un seul ELSE).

La structure CASE

Deux syntaxes sont possibles pour utiliser CASE.

Première syntaxe : conditions d'égalité

1
2
3
4
5

CASE valeur_a_comparer
    WHEN possibilite1 THEN instructions
    [WHEN possibilite2 THEN instructions] ...
    [ELSE instructions]
END CASE;

Exemple : on reprend la procédure message_sexe(), et on l'adapte pour utiliser CASE.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

DELIMITER |
CREATE PROCEDURE message_sexe2(IN p_animal_id INT)
BEGIN
    DECLARE v_sexe VARCHAR(10);

    SELECT sexe INTO v_sexe
    FROM Animal
    WHERE id = p_animal_id;

    CASE v_sexe
        WHEN 'F' THEN                          -- Première possibilité
            SELECT 'Je suis une femelle !' AS sexe;
        WHEN 'M' THEN                             -- Deuxième possibilité
            SELECT 'Je suis un mâle !' AS sexe;
        ELSE                                         -- Défaut
            SELECT 'Je suis en plein questionnement existentiel...' AS sexe;
    END CASE;
END|
DELIMITER ;

CALL message_sexe2(8);   -- Mâle
CALL message_sexe2(6);   -- Femelle
CALL message_sexe2(9);   -- Ni l'un ni l'autre

On définit donc v_sexe comme point de comparaison. Chaque WHEN donne alors un élément auquel v_sexe doit être comparé. Les instructions exécutées seront celles du WHEN dont l'élément est égal à v_sexe. Le ELSE sera exécuté si aucun WHEN ne correspond.

Ici, on compare une variable locale (v_sexe) à des chaînes de caractères ('F' et 'M'), mais on peut utiliser différents types d'éléments. Voici les principaux :

• des variables locales ;
• des variables utilisateur ;
• des valeurs constantes de tous types (0, 'chaîne', 5.67, '2012-03-23',…) ;
• des expressions (2 + 4, NOW(), CONCAT(nom, ' ', prenom),…) ;
• …

Seconde syntaxe : toutes conditions

Cette seconde syntaxe ne compare pas un élément à différentes valeurs, mais utilise simplement des conditions classiques et permet donc de faire des comparaisons de type "plus grand que", "différent de", etc. (bien entendu, elle peut également être utilisée pour des égalités).

1
2
3
4
5

CASE
    WHEN condition THEN instructions
    [WHEN condition THEN instructions] ...
    [ELSE instructions]
END CASE

Exemple : on reprend la procédure avant_apres_2010(), qu'on réécrit avec CASE, et en donnant une possibilité en plus. De plus, on passe le message en paramètre OUT pour changer un peu.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

DELIMITER |
CREATE PROCEDURE avant_apres_2010_case (IN p_animal_id INT, OUT p_message VARCHAR(100))
BEGIN
    DECLARE v_annee INT;

    SELECT YEAR(date_naissance) INTO v_annee
    FROM Animal
    WHERE id = p_animal_id;

    CASE
        WHEN v_annee < 2010 THEN
            SET p_message = 'Je suis né avant 2010.';
        WHEN v_annee = 2010 THEN
            SET p_message = 'Je suis né en 2010.';
        ELSE
            SET p_message = 'Je suis né après 2010.';   
    END CASE;
END |
DELIMITER ;

CALL avant_apres_2010_case(59, @message);   
SELECT @message;
CALL avant_apres_2010_case(62, @message);   
SELECT @message;
CALL avant_apres_2010_case(69, @message);
SELECT @message;

Comportement particulier : aucune correspondance trouvée

En l'absence de clause ELSE, si aucune des conditions posées par les différentes clauses WHEN n'est remplie (quelle que soit la syntaxe utilisée), une erreur est déclenchée.

Par exemple, cette procédure affiche une salutation différente selon la terminaison du nom de l'animal passé en paramètre :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

DELIMITER |
CREATE PROCEDURE salut_nom(IN p_animal_id INT)
BEGIN
    DECLARE v_terminaison CHAR(1);

    SELECT SUBSTRING(nom, -1, 1) INTO v_terminaison  -- Une position négative signifie qu'on recule au lieu d'avancer. 
    FROM Animal                                      --  -1 est donc la dernière lettre du nom.
    WHERE id = p_animal_id;

    CASE v_terminaison
        WHEN 'a' THEN
            SELECT 'Bonjour !' AS Salutations;
        WHEN 'o' THEN
            SELECT 'Salut !' AS Salutations;
        WHEN 'i' THEN
            SELECT 'Coucou !' AS Salutations;
    END CASE;

END|
DELIMITER ;

CALL salut_nom(69);  -- Baba
CALL salut_nom(5);   -- Choupi
CALL salut_nom(29);  -- Fiero
CALL salut_nom(54);  -- Bubulle

1

ERROR 1339 (20000): Case not found for CASE statement

L'appel de la procédure avec Bubulle présente un cas qui n'est pas couvert par les trois WHEN. Une erreur est donc déclenchée

Donc, si l'on n'est pas sûr d'avoir couvert tous les cas possibles, il faut toujours ajouter une clause ELSE pour éviter les erreurs. Si l'on veut qu'aucune instruction ne soit exécutée par le ELSE, il suffit simplement de mettre un bloc d'instructions vide (BEGIN END;).

Exemple : reprenons la procédure salut_nom(), et ajoutons-lui une clause ELSE vide :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

DROP PROCEDURE salut_nom;
DELIMITER |
CREATE PROCEDURE salut_nom(IN p_animal_id INT)
BEGIN
    DECLARE v_terminaison CHAR(1);

    SELECT SUBSTRING(nom, -1, 1) INTO v_terminaison
    FROM Animal
    WHERE id = p_animal_id;

    CASE v_terminaison
        WHEN 'a' THEN
            SELECT 'Bonjour !' AS Salutations;
        WHEN 'o' THEN
            SELECT 'Salut !' AS Salutations;
        WHEN 'i' THEN
            SELECT 'Coucou !' AS Salutations;
        ELSE
            BEGIN                              -- Bloc d'instructions vide
            END;
    END CASE;

END|
DELIMITER ;

CALL salut_nom(69);  -- Baba
CALL salut_nom(5);   -- Choupi
CALL salut_nom(29);  -- Fiero
CALL salut_nom(54);  -- Bubulle

Cette fois, pas d'erreur. Le dernier appel (avec Bubulle) n'affiche simplement rien.

Utiliser une structure conditionnelle directement dans une requête

Jusqu'ici, on a vu l'usage des structures conditionnelles dans des procédures stockées. Il est cependant possible d'utiliser une structure CASE dans une simple requête.

Par exemple, écrivons une requête SELECT suivant le même principe que la procédure message_sexe() :

1
2
3
4
5
6
7

SELECT id, nom, CASE
          WHEN sexe = 'M' THEN 'Je suis un mâle !'
          WHEN sexe = 'F' THEN 'Je suis une femelle !'
          ELSE 'Je suis en plein questionnement existentiel...'
       END AS message
FROM Animal
WHERE id IN (9, 8, 6);

Quelques remarques :

• On peut utiliser les deux syntaxes de CASE.
• Il faut clôturer le CASE par END, et non par END CASE (et bien sûr ne pas mettre de ; si la requête n'est pas finie).
• Ce n'est pas limité aux clauses SELECT, on peut tout à fait utiliser un CASE dans une clause WHERE par exemple.
• Ce n'est par conséquent pas non plus limité aux requêtes SELECT, on peut l'utiliser dans n'importe quelle requête.

Il n'est par contre pas possible d'utiliser une structure IF dans une requête. Cependant, il existe une fonction IF(), beaucoup plus limitée, dont la syntaxe est la suivante :

1

IF(condition, valeur_si_vrai, valeur_si_faux)

Exemple :

1
2
3

SELECT nom, IF(sexe = 'M', 'Je suis un mâle', 'Je ne suis pas un mâle') AS sexe
FROM Animal
WHERE espece_id = 5;

Boucles

Une boucle est une structure qui permet de répéter plusieurs fois une série d'instructions. Il existe trois types de boucles en MySQL : WHILE, LOOP et REPEAT.

La boucle WHILE

La boucle WHILE permet de répéter une série d'instructions tant que la condition donnée reste vraie.

1
2
3

WHILE condition DO    -- Attention de ne pas oublier le DO, erreur classique
    instructions
END WHILE;

Exemple : la procédure suivante affiche les nombres entiers de 1 à p_nombre (passé en paramètre).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DELIMITER |
CREATE PROCEDURE compter_jusque_while(IN p_nombre INT)
BEGIN
    DECLARE v_i INT DEFAULT 1;

    WHILE v_i <= p_nombre DO
        SELECT v_i AS nombre; 

        SET v_i = v_i + 1;    -- À ne surtout pas oublier, sinon la condition restera vraie
    END WHILE;
END |
DELIMITER ;

CALL compter_jusque_while(3);

La boucle REPEAT

La boucle REPEAT travaille en quelque sorte de manière opposée à WHILE, puisqu'elle exécute des instructions de la boucle jusqu'à ce que la condition donnée devienne vraie.

Exemple : voici la même procédure écrite avec une boucle REPEAT.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DELIMITER |
CREATE PROCEDURE compter_jusque_repeat(IN p_nombre INT)
BEGIN
    DECLARE v_i INT DEFAULT 1;

    REPEAT
        SELECT v_i AS nombre; 

        SET v_i = v_i + 1;    -- À ne surtout pas oublier, sinon la condition restera vraie
    UNTIL v_i > p_nombre END REPEAT;
END |
DELIMITER ;

CALL compter_jusque_repeat(3);

Test

1
2
3
4
5

-- Condition fausse dès le départ, on ne rentre pas dans la boucle
CALL compter_jusque_while(0);   

-- Condition fausse dès le départ, on rentre quand même une fois dans la boucle
CALL compter_jusque_repeat(0);

Donner un label à une boucle

Il est possible de donner un label (un nom) à une boucle, ou à un bloc d'instructions défini par BEGIN... END. Il suffit pour cela de faire précéder l'ouverture de la boucle/du bloc par ce label, suivi de :.

La fermeture de la boucle/du bloc peut alors faire référence à ce label (mais ce n'est pas obligatoire).

Exemples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Boucle WHILE
-- ------------
super_while: WHILE condition DO    -- La boucle a pour label "super_while"
    instructions
END WHILE super_while;             -- On ferme en donnant le label de la boucle (facultatif)

-- Boucle REPEAT
-- -------------                  
repeat_genial: REPEAT              -- La boucle s'appelle "repeat_genial"
    instructions
UNTIL condition END REPEAT;        -- Cette fois, on choisit de ne pas faire référence au label lors de la fermeture

-- Bloc d'instructions
-- -------------------
bloc_extra: BEGIN                  -- Le bloc a pour label "bloc_extra"
    instructions
END bloc_extra;

D'une part, cela peut permettre de clarifier le code lorsqu'il y a beaucoup de boucles et de blocs d'instructions imbriqués. D'autre part, il est nécessaire de donner un label aux boucles et aux blocs d'instructions pour lesquels on veut pouvoir utiliser les instructions ITERATE et LEAVE.

Les instructions LEAVE et ITERATE

LEAVE : quitter la boucle ou le bloc d'instructions

L'instruction LEAVE peut s'utiliser dans une boucle ou un bloc d'instructions et déclenche la sortie immédiate de la structure dont le label est donné.

1

LEAVE label_structure;

Exemple : cette procédure incrémente de 1, et affiche, un nombre entier passé en paramètre. Et cela, 4 fois maximum. Mais si l'on trouve un multiple de 10, la boucle s'arrête.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

DELIMITER |
CREATE PROCEDURE test_leave1(IN p_nombre INT)
BEGIN
    DECLARE v_i INT DEFAULT 4;

    SELECT 'Avant la boucle WHILE';

    while1: WHILE v_i > 0 DO

        SET p_nombre = p_nombre + 1;        -- On incrémente le nombre de 1

        IF p_nombre%10 = 0 THEN              -- Si p_nombre est divisible par 10,
            SELECT 'Stop !' AS 'Multiple de 10';
            LEAVE while1;                   -- On quitte la boucle WHILE.
        END IF;

        SELECT p_nombre;                    -- On affiche p_nombre
        SET v_i = v_i - 1;                  -- Attention de ne pas l'oublier

    END WHILE while1;

    SELECT 'Après la boucle WHILE';
END|
DELIMITER ;

CALL test_leave1(3); -- La boucle s'exécutera 4 fois

1

CALL test_leave1(8); -- La boucle s'arrêtera dès qu'on atteint 10

Il est par conséquent possible d'utiliser LEAVE pour provoquer la fin de la procédure stockée.

Exemple : voici la même procédure. Cette fois-ci un multiple de 10 provoque l'arrêt de toute la procédure, pas seulement de la boucle WHILE.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

DELIMITER |
CREATE PROCEDURE test_leave2(IN p_nombre INT)
corps_procedure: BEGIN                           -- On donne un label au bloc d'instructions principal
    DECLARE v_i INT DEFAULT 4;

    SELECT 'Avant la boucle WHILE';
    while1: WHILE v_i > 0 DO
        SET p_nombre = p_nombre + 1;             -- On incrémente le nombre de 1
        IF p_nombre%10 = 0 THEN                  -- Si p_nombre est divisible par 10,
            SELECT 'Stop !' AS 'Multiple de 10';
            LEAVE corps_procedure;               -- je quitte la procédure.
        END IF;

        SELECT p_nombre;                         -- On affiche p_nombre
        SET v_i = v_i - 1;                       -- Attention de ne pas l'oublier
    END WHILE while1;

    SELECT 'Après la boucle WHILE';
END|
DELIMITER ;

CALL test_leave2(8);

'Après la boucle WHILE' ne s'affiche plus lorsque l'instruction LEAVE est déclenchée, puisque l'on quitte la procédure stockée avant d'arriver à l'instruction SELECT qui suit la boucle WHILE.

En revanche, LEAVE ne permet pas de quitter directement une structure conditionnelle (IF ou CASE). Il n'est d'ailleurs pas non plus possible de donner un label à ces structures. Cette restriction est cependant aisément contournable en utilisant les blocs d'instructions.

Exemple : la procédure suivante affiche les nombres de 4 à 1, en précisant s'ils sont pairs. Sauf pour le nombre 2, pour lequel une instruction LEAVE empêche l'affichage habituel.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

DELIMITER |
CREATE PROCEDURE test_leave3()
BEGIN
    DECLARE v_i INT DEFAULT 4;

    WHILE v_i > 0 DO

        IF v_i%2 = 0 THEN
            if_pair: BEGIN
                IF v_i = 2 THEN                   -- Si v_i vaut 2
                    LEAVE if_pair;                -- On quitte le bloc "if_pair", ce qui revient à quitter la structure IF v_i%2 = 0
                END IF;
                SELECT CONCAT(v_i, ' est pair') AS message;
            END if_pair; 
        ELSE    
            if_impair: BEGIN
                SELECT CONCAT(v_i, ' est impair') AS message;
            END if_impair;   
        END IF;

        SET v_i = v_i - 1;
    END WHILE;
END|
DELIMITER ;

CALL test_leave3();

'2 est pair' n'est pas affiché, puisqu'on a quitté le IF avant cet affichage.

ITERATE : déclencher une nouvelle itération de la boucle

Cette instruction ne peut être utilisée que dans une boucle. Lorsqu'elle est exécutée, une nouvelle itération de la boucle commence. Toutes les instructions suivant ITERATE dans la boucle sont ignorées.

Exemple : la procédure suivante affiche les nombres de 1 à 3, avec un message avant le IF et après le IF. Sauf pour le nombre 2, qui relance une itération de la boucle dans le IF.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

DELIMITER |
CREATE PROCEDURE test_iterate()
BEGIN
    DECLARE v_i INT DEFAULT 0;

    boucle_while: WHILE v_i < 3 DO
        SET v_i = v_i + 1;
        SELECT v_i, 'Avant IF' AS message;

        IF v_i = 2 THEN
            ITERATE boucle_while;
        END IF;

        SELECT v_i, 'Après IF' AS message;  -- Ne sera pas exécuté pour v_i = 2
    END WHILE;
END |
DELIMITER ;

CALL test_iterate();

La boucle LOOP

On a gardé la boucle LOOP pour la fin, parce qu'elle est un peu particulière. En effet, voici sa syntaxe :

1
2
3

[label:] LOOP
    instructions
END LOOP [label]

Vous voyez bien : il n'est question de condition nulle part. En fait, une boucle LOOP doit intégrer dans ses instructions un élément qui va la faire s'arrêter : typiquement une instruction LEAVE. Sinon, c'est une boucle infinie.

Exemple : à nouveau une procédure qui affiche les nombres entiers de 1 à p_nombre.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

DELIMITER |
CREATE PROCEDURE compter_jusque_loop(IN p_nombre INT)
BEGIN
    DECLARE v_i INT DEFAULT 1;

    boucle_loop: LOOP
        SELECT v_i AS nombre; 

        SET v_i = v_i + 1;

        IF v_i > p_nombre THEN
            LEAVE boucle_loop;
        END IF;    
    END LOOP;
END |
DELIMITER ;

CALL compter_jusque_loop(3);

En résumé

• Un bloc d'instructions est délimité par BEGIN et END. Il est possible d'imbriquer plusieurs blocs d'instructions.
• Une variable locale est définie dans un bloc d'instructions grâce à la commande DECLARE. Une fois la fin du bloc d'instructions atteinte, toutes les variables locales qui y ont été déclarées sont supprimées.
• Une structure conditionnelle permet d'exécuter une série d'instructions si une condition est respectée. Les deux structures conditionnelles de MySQL sont IF et CASE.
• Une boucle est une structure qui permet de répéter une série d'instructions un certain nombre de fois. Il existe trois types de boucle pour MySQL : WHILE, REPEAT et LOOP.
• L'instruction LEAVE permet de quitter un bloc d'instructions ou une boucle.
• L'instruction ITERATE permet de relancer une itération d'une boucle.

• Procédures stockées
• Sommaire
• Gestionnaires d'erreurs, curseurs et utilisation avancée