Gestionnaires d'erreurs, curseurs et utilisation avancée

• Structurer ses instructions
• Sommaire
• Triggers

Dans ce chapitre, nous verrons tout d'abord deux structures utilisables dans les blocs d'instructions et qui vont vous ouvrir d'énormes possibilités :

• les gestionnaires d'erreur, qui permettent de gérer les cas où une erreur se produirait pendant l'exécution d'une série d'instructions ;
• les curseurs, qui permettent de parcourir les lignes de résultat d'une requête SELECT.

Ensuite, nous verrons quelques cas d'utilisation avancée des blocs d'instructions, utilisant non seulement les structures décrites dans ce chapitre et le précédent, mais également d'autres notions et objets (transaction, variables utilisateur, etc.).

• Gestion des erreurs
• Curseurs
• Utilisation avancée des blocs d'instructions

Gestion des erreurs

Il arrive régulièrement qu'un traitement soit susceptible de générer une erreur SQL. Prenons la procédure suivante, qui enregistre une adoption :

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

DELIMITER |
CREATE PROCEDURE ajouter_adoption(IN p_client_id INT, IN p_animal_id INT, IN p_date DATE, IN p_paye TINYINT)
BEGIN
    DECLARE v_prix DECIMAL(7,2);

    SELECT COALESCE(Race.prix, Espece.prix) INTO v_prix
    FROM Animal
    INNER JOIN Espece ON Espece.id = Animal.espece_id
    LEFT JOIN Race ON Race.id = Animal.race_id
    WHERE Animal.id = p_animal_id;

    INSERT INTO Adoption (animal_id, client_id, date_reservation, date_adoption, prix, paye)
    VALUES (p_animal_id, p_client_id, CURRENT_DATE(), p_date, v_prix, p_paye);


    SELECT 'Adoption correctement ajoutée' AS message;
END|
DELIMITER ;

Plusieurs erreurs sont susceptibles de se déclencher selon les paramètres passés à cette procédure.

Exemple 1 : le client n'existe pas.

1
2
3

SET @date_adoption = CURRENT_DATE() + INTERVAL 7 DAY;

CALL ajouter_adoption(18, 6, @date_adoption, 1);

1

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`elevage`.`Adoption`, CONSTRAINT `Adoption_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `Client` (`id`))

Exemple 2 : l'animal a déjà été adopté.

1

CALL ajouter_adoption(12, 21, @date_adoption, 1);

1

ERROR 1062 (23000): Duplicate entry '21' for key 'ind_uni_animal_id'

Exemple 3 : l'animal n'existe pas, v_prix est donc NULL.

1

CALL ajouter_adoption(12, 102, @date_adoption, 1);

1

ERROR 1048 (23000): Column 'prix' cannot be null

Pour empêcher ces erreurs intempestives, deux solutions :

• vérifier chaque paramètre pouvant poser problème (p_animal_id et p_client_id ne sont pas NULL et correspondent à quelque chose dans les tables Animal et Client, p_animal_id ne correspond pas à un animal déjà adopté, etc.) ;
• utiliser un gestionnaire d'erreur : c'est ce que nous allons apprendre à faire ici.

Création d'un gestionnaire d'erreur

Voici la syntaxe à utiliser pour créer un gestionnaire d'erreur :

1
2

DECLARE { EXIT | CONTINUE } HANDLER FOR { numero_erreur | { SQLSTATE identifiant_erreur } | condition } 
    instruction ou bloc d'instructions

• Un gestionnaire d'erreur définit une instruction (une seule !), ou un bloc d'instructions (BEGIN ... END;), qui va être exécuté en cas d'erreur correspondant au gestionnaire.
• Tous les gestionnaires d'erreur doivent être déclarés au même endroit : après la déclaration des variables locales, mais avant les instructions de la procédure.
• Un gestionnaire peut, soit provoquer l'arrêt de la procédure (EXIT), soit faire reprendre la procédure après avoir géré l'erreur (CONTINUE).
• On peut identifier le type d'erreur que le gestionnaire va reconnaître de trois manières différentes : un numéro d'erreur, un identifiant, ou une CONDITION.
• Un gestionnaire étant défini grâce au mot-clé DECLARE, comme les variables locales, il a exactement la même portée que celles-ci.

Exemples : ces deux procédures enregistrent une adoption en gérant les erreurs, l'une arrêtant la procédure, l'autre relançant celle-ci :

 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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

DELIMITER |
CREATE PROCEDURE ajouter_adoption_exit(IN p_client_id INT, IN p_animal_id INT, IN p_date DATE, IN p_paye TINYINT)
BEGIN
    DECLARE v_prix DECIMAL(7,2);
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' 
        BEGIN
            SELECT 'Une erreur est survenue...';
            SELECT 'Arrêt prématuré de la procédure';
        END;

    SELECT 'Début procédure';

    SELECT COALESCE(Race.prix, Espece.prix) INTO v_prix
    FROM Animal
    INNER JOIN Espece ON Espece.id = Animal.espece_id
    LEFT JOIN Race ON Race.id = Animal.race_id
    WHERE Animal.id = p_animal_id;

    INSERT INTO Adoption (animal_id, client_id, date_reservation, date_adoption, prix, paye)
    VALUES (p_animal_id, p_client_id, CURRENT_DATE(), p_date, v_prix, p_paye);

    SELECT 'Fin procédure' AS message;
END|

CREATE PROCEDURE ajouter_adoption_continue(IN p_client_id INT, IN p_animal_id INT, IN p_date DATE, IN p_paye TINYINT)
BEGIN
    DECLARE v_prix DECIMAL(7,2);
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SELECT 'Une erreur est survenue...';

    SELECT 'Début procédure';

    SELECT COALESCE(Race.prix, Espece.prix) INTO v_prix
    FROM Animal
    INNER JOIN Espece ON Espece.id = Animal.espece_id
    LEFT JOIN Race ON Race.id = Animal.race_id
    WHERE Animal.id = p_animal_id;

    INSERT INTO Adoption (animal_id, client_id, date_reservation, date_adoption, prix, paye)
    VALUES (p_animal_id, p_client_id, CURRENT_DATE(), p_date, v_prix, p_paye);

    SELECT 'Fin procédure';
END|
DELIMITER ;

SET @date_adoption = CURRENT_DATE() + INTERVAL 7 DAY;

CALL ajouter_adoption_exit(18, 6, @date_adoption, 1);
CALL ajouter_adoption_continue(18, 6, @date_adoption, 1);

Les instructions définies par le gestionnaire sont bien exécutées, mais 'Fin procédure' n'est affiché que dans le cas de ajouter_adoption_continue(), qui fait reprendre la procédure une fois l'erreur gérée. La procédure ajouter_adoption_exit() utilise un bloc d'instructions et peut donc exécuter plusieurs instructions.

Définition de l'erreur gérée

Identifiant ou numéro MySQL de l'erreur

Voici la déclaration du gestionnaire dans la procédure ajouter_adoption_continue() :

1

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SELECT 'Une erreur est survenue...';

Et voici une des erreurs qui peut être interceptée par ce gestionnaire :

1

ERROR 1062 (23000): Duplicate entry '21' for key 'ind_uni_animal_id'

Le message d'erreur est constitué de trois éléments importants :

• 1062 : le numéro d'erreur MySQL (un nombre entier) ;
• 23000 : l'identifiant de l'état SQL (une chaîne de 5 caractères) ;
• Duplicate entry '21' for key 'ind_uni_animal_id' : un message donnant le détail de l'erreur.

Identifiant de l'état SQL

Dans la procédure ajouter_adoption_continue(), c'est l'identifiant de l'état SQL ('23000') qui a été utilisé. Il s'agit d'une chaîne de 5 caractères, renvoyée par le serveur au client pour informer de la réussite ou de l'échec d'une instruction. Un identifiant commençant par '00' par exemple, signifie que l'instruction a réussi. '23000' est l'identifiant renvoyé lorsqu'une erreur concernant une contrainte (NOT NULL, unicité, clé primaire ou secondaire,…) a été déclenchée. Pour utiliser cet identifiant dans un gestionnaire d'erreur, il faut le faire précéder de SQLSTATE.

Numéro d'erreur MySQL

Pour utiliser le numéro d'erreur SQL, par contre, il suffit de l'indiquer, comme un nombre entier :

1

DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Une erreur est survenue...';

Ce sont des codes qui, contrairement aux identifiants SQLSTATE, sont propres à MySQL. Ils sont aussi en général plus précis. L'identifiant SQL '23000' par exemple, correspond à une dizaine de codes d'erreur MySQL différents.

Quelques exemples de codes souvent rencontrés

Pour une liste plus complète, il suffit d'aller sur la documentation officielle.

Pour finir, notez qu'il est tout à fait possible d'intercepter des avertissements avec un gestionnaire d'erreur, qui sont également représentés par un identifiant SQL et un code d'erreur MySQL. Un avertissement, contrairement à une erreur, ne fait pas échouer l'instruction par défaut, mais en l'interceptant dans une requête stockée avec un gestionnaire, vous pouvez décider du comportement à adopter suite à cet avertissement.

Vous aurez par exemple un avertissement si vous insérez un DATETIME dans une colonne DATE, puisque la donnée sera tronquée pour correspondre au type de la colonne (l'heure sera supprimée pour ne garder que la date) : code Mysql 1265, SQLSTATE '01000'.

Utilisation d'une CONDITION

Avec un numéro d'erreur MySQL et un identifiant d'état SQL, il existe une troisième manière d'identifier les erreurs reconnues par un gestionnaire : une CONDITION. Une CONDITION est en fait simplement un nom donné à un numéro d'erreur MySQL ou à un identifiant d'état SQL. Cela vous permet de travailler avec des erreurs plus claires.

Voici la syntaxe à utiliser pour nommer une erreur. Il s'agit à nouveau d'un DECLARE. Les déclarations de CONDITION doivent se trouver avant les déclarations de gestionnaires.

1

DECLARE nom_erreur CONDITION FOR { SQLSTATE identifiant_SQL | numero_erreur_MySQL };

Exemple : réécrivons la procédure ajouter_adoption_exit() en nommant l'erreur :

 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

DROP PROCEDURE ajouter_adoption_exit;
DELIMITER |
CREATE PROCEDURE ajouter_adoption_exit(IN p_client_id INT, IN p_animal_id INT, IN p_date DATE, IN p_paye TINYINT)
BEGIN
    DECLARE v_prix DECIMAL(7,2);

    DECLARE violation_contrainte CONDITION FOR SQLSTATE '23000';   -- On nomme l'erreur dont l'identifiant est 23000 "violation_contrainte"

    DECLARE EXIT HANDLER FOR violation_contrainte                  -- Le gestionnaire sert donc à intercepter
        BEGIN                                                      --   les erreurs de type "violation_contrainte"
            SELECT 'Une erreur est survenue...';
            SELECT 'Arrêt prématuré de la procédure';
        END;

    SELECT 'Début procédure';

    SELECT COALESCE(Race.prix, Espece.prix) INTO v_prix
    FROM Animal
    INNER JOIN Espece ON Espece.id = Animal.espece_id
    LEFT JOIN Race ON Race.id = Animal.race_id
    WHERE Animal.id = p_animal_id;

    INSERT INTO Adoption (animal_id, client_id, date_reservation, date_adoption, prix, paye)
    VALUES (p_animal_id, p_client_id, CURRENT_DATE(), p_date, v_prix, p_paye);

    SELECT 'Fin procédure';
END|
DELIMITER ;

Conditions prédéfinies

Il existe trois conditions prédéfinies dans MySQL :

• SQLWARNING : tous les identifiants SQL commençant par '01', c'est-à-dire les avertissements et les notes ;
• NOT FOUND : tous les identifiants SQL commençant par '02', et que nous verrons plus en détail avec les curseurs ;
• SQLEXCEPTION : tous les identifiants SQL ne commençant ni par '00', ni par '01', ni par '02', donc les erreurs.

Exemple : réécriture de la procédure ajouter_adoption_exit(), de façon à ce que le gestionnaire intercepte toutes les erreurs SQL.

 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

DROP PROCEDURE ajouter_adoption_exit;
DELIMITER |
CREATE PROCEDURE ajouter_adoption_exit(IN p_client_id INT, IN p_animal_id INT, IN p_date DATE, IN p_paye TINYINT)
BEGIN
    DECLARE v_prix DECIMAL(7,2);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION              
        BEGIN
            SELECT 'Une erreur est survenue...';
            SELECT 'Arrêt prématuré de la procédure';
        END;

    SELECT 'Début procédure';

    SELECT COALESCE(Race.prix, Espece.prix) INTO v_prix
    FROM Animal
    INNER JOIN Espece ON Espece.id = Animal.espece_id
    LEFT JOIN Race ON Race.id = Animal.race_id
    WHERE Animal.id = p_animal_id;

    INSERT INTO Adoption (animal_id, client_id, date_reservation, date_adoption, prix, paye)
    VALUES (p_animal_id, p_client_id, CURRENT_DATE(), p_date, v_prix, p_paye);

    SELECT 'Fin procédure';
END|
DELIMITER ;

Déclarer plusieurs gestionnaires, gérer plusieurs erreurs par gestionnaire

Un gestionnaire peut reconnaître plusieurs types d'erreurs différents. Par ailleurs, il est possible de déclarer plusieurs gestionnaires dans un même bloc d'instructions.

Exemple : toujours avec la procédure ajouter_adoption_exit(). On peut l'écrire en détaillant différentes erreurs possibles, puis en ajoutant un gestionnaire général qui reconnaîtra les SQLEXCEPTION et les SQLWARNING, pour tous les cas qu'on ne traite pas dans les autres gestionnaires. Ce qui donne :

 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
30
31
32
33
34
35
36
37
38
39
40
41
42

DROP PROCEDURE ajouter_adoption_exit;
DELIMITER |
CREATE PROCEDURE ajouter_adoption_exit(IN p_client_id INT, IN p_animal_id INT, IN p_date DATE, IN p_paye TINYINT)
BEGIN
    DECLARE v_prix DECIMAL(7,2);

    DECLARE violation_cle_etrangere CONDITION FOR 1452;            -- Déclaration des CONDITIONS
    DECLARE violation_unicite CONDITION FOR 1062;

    DECLARE EXIT HANDLER FOR violation_cle_etrangere               -- Déclaration du gestionnaire pour     
        BEGIN                                                      -- les erreurs de clés étrangères
            SELECT 'Erreur : violation de clé étrangère.';
        END;
    DECLARE EXIT HANDLER FOR violation_unicite                     -- Déclaration du gestionnaire pour
        BEGIN                                                      -- les erreurs d'index unique
            SELECT 'Erreur : violation de contrainte d''unicité.';
        END;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING              -- Déclaration du gestionnaire pour
        BEGIN                                                      -- toutes les autres erreurs ou avertissements
            SELECT 'Une erreur est survenue...';
        END;

    SELECT 'Début procédure';

    SELECT COALESCE(Race.prix, Espece.prix) INTO v_prix
    FROM Animal
    INNER JOIN Espece ON Espece.id = Animal.espece_id
    LEFT JOIN Race ON Race.id = Animal.race_id
    WHERE Animal.id = p_animal_id;

    INSERT INTO Adoption (animal_id, client_id, date_reservation, date_adoption, prix, paye)
    VALUES (p_animal_id, p_client_id, CURRENT_DATE(), p_date, v_prix, p_paye);

    SELECT 'Fin procédure';
END|
DELIMITER ;

SET @date_adoption = CURRENT_DATE() + INTERVAL 7 DAY;

CALL ajouter_adoption_exit(12, 3, @date_adoption, 1);        -- Violation unicité (animal 3 est déjà adopté)
CALL ajouter_adoption_exit(133, 6, @date_adoption, 1);       -- Violation clé étrangère (client 133 n'existe pas)
CALL ajouter_adoption_exit(NULL, 6, @date_adoption, 1);      -- Violation de contrainte NOT NULL

Cette procédure montre également que lorsque plusieurs gestionnaires d'erreur peuvent correspondre à l'erreur déclenchée (ou à l'avertissement), c'est le plus précis qui est utilisé. C'est la raison pour laquelle une violation de clé étrangère déclenche le gestionnaire FOR violation_cle_etrangere (numéro MySQL 1062), et non le gestionnaire FOR SQLEXCEPTION.

Curseurs

Nous avons vu qu'il était possible d'exploiter le résultat d'un SELECT dans un bloc d'instructions, en utilisant la commande SELECT colonne(s) INTO variable(s), qui assigne les valeurs sélectionnées à des variables. Cependant, SELECT ... INTO ne peut être utilisé que pour des requêtes qui ne ramènent qu'une seule ligne de résultats.

Les curseurs permettent de parcourir un jeu de résultats d'une requête SELECT, quel que soit le nombre de lignes récupérées, et d'en exploiter les valeurs.

Quatre étapes sont nécessaires pour utiliser un curseur.

• Déclaration du curseur : avec une instruction DECLARE.
• Ouverture du curseur : on exécute la requête SELECT du curseur et on stocke le résultat dans celui-ci.
• Parcours du curseur : on parcourt une à une les lignes.
• Fermeture du curseur.

Syntaxe

Déclaration du curseur

Comme toutes les instructions DECLARE, la déclaration d'un curseur doit se faire au début du bloc d'instructions pour lequel celui-ci est défini. Plus précisément, on déclare les curseurs après les variables locales et les conditions, mais avant les gestionnaires d'erreur.

1

DECLARE nom_curseur CURSOR FOR requete_select;

Un curseur est donc composé d'un nom, et d'une requête SELECT.

Exemple :

1
2
3

DECLARE curseur_client CURSOR 
    FOR SELECT * 
    FROM Client;

Ouverture et fermeture du curseur

En déclarant le curseur, on a donc associé un nom et une requête SELECT. L'ouverture du curseur va provoquer l'exécution de la requête SELECT, ce qui va produire un jeu de résultats.

Une fois qu'on aura parcouru les résultats, il n'y aura plus qu'à fermer le curseur. Si on ne le fait pas explicitement, le curseur sera fermé à la fin du bloc d'instructions.

1
2
3

OPEN nom_curseur;
  -- Parcours du curseur et instructions diverses
CLOSE nom_curseur;

Parcours du curseur

Une fois que le curseur a été ouvert et le jeu de résultats récupéré, le curseur place un pointeur sur la première ligne de résultats. Avec la commande FETCH, on récupère la ligne sur laquelle pointe le curseur, et on fait avancer le pointeur vers la ligne de résultats suivante.

1

FETCH nom_curseur INTO variable(s);

Bien entendu, comme pour SELECT ... INTO, il faut donner autant de variables dans la clause INTO qu'on a récupéré de colonnes dans la clause SELECT du curseur.

Exemple : la procédure suivante parcourt les deux premières lignes de la table Client avec un curseur.

 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 parcours_deux_clients()
BEGIN
    DECLARE v_nom, v_prenom VARCHAR(100);

    DECLARE curs_clients CURSOR
        FOR SELECT nom, prenom                                 -- Le SELECT récupère deux colonnes
        FROM Client
        ORDER BY nom, prenom;                                  -- On trie les clients par ordre alphabétique

    OPEN curs_clients;                                         -- Ouverture du curseur

    FETCH curs_clients INTO v_nom, v_prenom;                   -- On récupère la première ligne et on assigne les valeurs récupérées à nos variables locales
    SELECT CONCAT(v_prenom, ' ', v_nom) AS 'Premier client';

    FETCH curs_clients INTO v_nom, v_prenom;                   -- On récupère la seconde ligne et on assigne les valeurs récupérées à nos variables locales
    SELECT CONCAT(v_prenom, ' ', v_nom) AS 'Second client';

    CLOSE curs_clients;                                         -- Fermeture du curseur
END|
DELIMITER ;

CALL parcours_deux_clients();

Restrictions

FETCH est la seule commande permettant de récupérer une partie d'un jeu de résultats d'un curseur, et elle ne permet qu'une chose : récupérer la ligne de résultats suivante. Il n'est pas possible de sauter une ou plusieurs lignes, ni d'aller rechercher une ligne précédente. On ne peut que parcourir les lignes une à une, de la première à la dernière.

Ensuite, il n'est pas possible de modifier une ligne directement à partir d'un curseur. Il s'agit d'une restriction particulière à MySQL. D'autres SGBD vous permettent des requêtes d'UPDATE directement sur les curseurs.

Avec Oracle, la requête suivante modifiera la dernière ligne récupérée avec FETCH :

1
2
3

UPDATE nom_table
   SET colonne = valeur
 WHERE CURRENT OF nom_curseur;

Il vaut d'ailleurs mieux éviter tout UPDATE sur une table sur laquelle un curseur est ouvert, même sans faire de référence à celui-ci. Le résultat d'un tel UPDATE serait imprévisible.

Parcourir intelligemment tous les résultats d'un curseur

Pour récupérer une ligne de résultats, on utilise donc FETCH. Dans la procédure parcours_deux_clients(), on voulait récupérer les deux premières lignes, on a donc utilisé deux FETCH. Cependant, la plupart du temps, on ne veut pas seulement utiliser les deux premières lignes, mais toutes ! Or, sauf exception, on ne sait pas combien de lignes seront sélectionnées.

On veut donc parcourir une à une les lignes de résultats, et leur appliquer un traitement, sans savoir à l'avance combien de fois ce traitement devra être répété. Pour cela, on utilise une boucle ! WHILE, REPEAT ou LOOP. Il n'y a plus qu'à trouver une condition pour arrêter la boucle une fois tous les résultats parcourus.

Condition d'arrêt

Voyons ce qui se passe lorsque l'on fait un FETCH alors qu'il n'y a plus, ou pas, de résultats. Voici une procédure qui sélectionne les clients selon une ville donnée en paramètre. Les lignes sont récupérées et affichées grâce au FETCH, placé dans une boucle LOOP. Je rappelle que cette boucle ne définit pas de condition d'arrêt : il est nécessaire d'ajouter une instruction LEAVE pour l'arrêter. Ici, pour tester, on ne mettra pas d'instruction LEAVE.

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

DELIMITER |
CREATE PROCEDURE test_condition(IN p_ville VARCHAR(100))
BEGIN
    DECLARE v_nom, v_prenom VARCHAR(100);

    DECLARE curs_clients CURSOR
        FOR SELECT nom, prenom
        FROM Client
        WHERE ville = p_ville;

    OPEN curs_clients;                                    

    LOOP                                                  
        FETCH curs_clients INTO v_nom, v_prenom;                   
        SELECT CONCAT(v_prenom, ' ', v_nom) AS 'Client';
    END LOOP;

    CLOSE curs_clients; 
END|
DELIMITER ;

Voyons donc ce que ça donne pour une ville dans laquelle quelques clients habitent.

1

CALL test_condition('Houtsiplou');

1

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

Tentons ensuite l'expérience avec une ville qui ne donnera aucun résultat.

1

CALL test_condition('Bruxelles');

1

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

On a la même erreur dans les deux cas, lorsqu'on essaye de faire un FETCH alors qu'il n'y a pas ou plus de ligne à récupérer. Or, vous vous souvenez peut-être d'une condition prédéfinie pour les gestionnaires d'erreur, NOT FOUND, qui représente les erreurs dont l'identifiant SQL commence par '02', ce qui est le cas ici.

On va donc utiliser cette condition pour arrêter la boucle : on définit un gestionnaire pour la condition NOT FOUND, qui change la valeur d'une variable locale. Cette variable locale vaut 0 au départ, et passe à 1 quand le gestionnaire est déclenché (donc quand il n'y a plus de ligne). Il suffit alors d'ajouter une structure IF qui vérifie la valeur de la variable locale une fois le FETCH exécuté. Si elle vaut 1, on quitte la boucle.

Exemple : la procédure test_condition2() ci-dessous fait la même chose que test_condition(), mais inclut le gestionnaire d'erreur et le IF nécessaires pour stopper la boucle dès que toutes les lignes sélectionnées ont été parcourues :

 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
30
31

DELIMITER |
CREATE PROCEDURE test_condition2(IN p_ville VARCHAR(100))
BEGIN
    DECLARE v_nom, v_prenom VARCHAR(100);
    DECLARE fin TINYINT DEFAULT 0;                      -- Variable locale utilisée pour stopper la boucle

    DECLARE curs_clients CURSOR
        FOR SELECT nom, prenom
        FROM Client
        WHERE ville = p_ville;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = 1; -- Gestionnaire d'erreur pour la condition NOT FOUND

    OPEN curs_clients;                                    

    loop_curseur: LOOP                                                
        FETCH curs_clients INTO v_nom, v_prenom;

        IF fin = 1 THEN                                 -- Structure IF pour quitter la boucle à la fin des résultats
            LEAVE loop_curseur;
        END IF;

        SELECT CONCAT(v_prenom, ' ', v_nom) AS 'Client';
    END LOOP;

    CLOSE curs_clients; 
END|
DELIMITER ;

CALL test_condition2('Houtsiplou');
CALL test_condition2('Bruxelles');

Je vous laisse le soin, si vous le désirez, de réécrire cette procédure en utilisant une boucle WHILE ou une boucle REPEAT. C'est un excellent exercice !

Notez que ce n'est pas la seule solution pour arrêter la boucle. On pourrait par exemple compter le nombre de lignes récupérées, grâce à la fonction FOUND_ROWS() (dont on a déjà parlé dans le chapitre sur les fonctions). Cependant, cette utilisation du gestionnaire d'erreur est la solution la plus utilisée, et je la trouve personnellement très élégante.

Le cas des booléens chez MySQL

Un booléen, en informatique, est un type de donnée pouvant prendre deux états : vrai, ou faux. MySQL ne propose pas ce type de données. Avec MySQL, on représente la valeur "vrai" par 1, et la valeur "faux" par 0.

Démonstration :

1

SELECT 1 = 1, 1 = 2;    -- 1 = 1 est vrai, bien sûr. Contrairement à 1 = 2 (si si !)

Par conséquent, pour représenter un booléen, on utilise en général un TINYINT, valant soit 0, soit 1. C'est ce que l'on a fait pour la colonne paye de la table Client par exemple.

Une autre conséquence, est que la structure IF que l'on utilise dans la procédure test_condition2() peut être réécrite de la manière suivante :

1
2
3

IF fin THEN
    LEAVE loop_curseur;
END IF;

Et pour améliorer encore la lisibilité pour les données de ce genre, MySQL a créé plusieurs synonymes que l'on peut utiliser dans ces situations.

• BOOL et BOOLEAN sont synonymes de TINYINT(1).
• TRUE est synonyme de 1.
• FALSE est synonyme de 0.

On peut donc réécrire la procédure test_condition2() en utilisant ces synonymes.

 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 test_condition2;
DELIMITER |
CREATE PROCEDURE test_condition2(IN p_ville VARCHAR(100))
BEGIN
    DECLARE v_nom, v_prenom VARCHAR(100);
    DECLARE fin BOOLEAN DEFAULT FALSE;                     -- On déclare fin comme un BOOLEAN, avec FALSE pour défaut

    DECLARE curs_clients CURSOR
        FOR SELECT nom, prenom
        FROM Client
        WHERE ville = p_ville;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = TRUE; -- On utilise TRUE au lieu de 1

    OPEN curs_clients;                                    

    loop_curseur: LOOP                                                
        FETCH curs_clients INTO v_nom, v_prenom;

        IF fin THEN                                        -- Plus besoin de "= 1"
            LEAVE loop_curseur;
        END IF;

        SELECT CONCAT(v_prenom, ' ', v_nom) AS 'Client';
    END LOOP;

    CLOSE curs_clients; 
END|
DELIMITER ;

Utilisation avancée des blocs d'instructions

Vous avez maintenant vu les principales structures qu'il est possible d'utiliser dans un bloc d'instructions. Nous allons ici combiner ces structures avec des objets ou notions vues précédemment.

La puissance du langage SQL (et de tous les langages informatiques) réside dans le fait qu'on peut combiner différentes notions pour réaliser des traitements complexes. Voici quelques exemples de telles combinaisons. Notez que ces exemples utilisent tous des procédures stockées, mais la plupart sont adaptables à d'autres objets, comme les triggers, les fonctions stockées ou les événements.

Utiliser des variables utilisateur dans un bloc d'instructions

En plus des variables locales, il est tout à fait possible d'utiliser des variables utilisateur dans un bloc d'instructions. Mais n'oubliez pas qu'une variable utilisateur est définie pour toute la session, pas uniquement le bloc, même si elle est créée à l'intérieur de celui-ci.

Exemple : procédure stockée utilisant une variable utilisateur.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

DELIMITER |
CREATE PROCEDURE test_vu()
BEGIN
    SET @var = 15;
END|
DELIMITER ;

SELECT @var;    -- @var n'existe pas encore, on ne l'a pas définie
CALL test_vu(); -- On exécute la procédure
SELECT @var;    -- @var vaut maintenant 15, même en dehors de la procédure, puisqu'elle est définie partout dans la session

Voyant cela, on pourrait être tenté d'utiliser des variables utilisateur à la place des paramètres OUT et INOUT des procédures stockées.

Cependant, il convient d'être extrêmement prudent lorsque l'on utilise des variables utilisateur dans un bloc d'instructions. Si l'on reste par exemple dans le contexte des procédures stockées, un des intérêts de celles-ci est d'avoir une interface entre la base de données et l'utilisateur. L'utilisateur n'est donc pas nécessairement conscient des variables utilisateur qui sont définies ou modifiées dans la procédure. Il pourrait donc définir des variables utilisateur, puis exécuter une procédure et constater que certaines de ses variables ont été écrasées par la procédure.

Ainsi dans le code suivant :

1
2
3

SET @var = 'Bonjour';
CALL test_vu();
SELECT @var;           -- Donne 15 !

Un utilisateur inconscient du fait que test_vu() modifie @var pourrait bien y perdre des cheveux !

Un raisonnement similaire peut être tenu pour les autres objets utilisant les blocs d'instructions. Évitez donc les variables utilisateur dans les blocs quand c'est possible (et nous allons bientôt voir un cas où ce n'est pas possible).

Utiliser une procédure dans un bloc

CALL nom_procedure(); est une instruction. On peut donc parfaitement exécuter une procédure dans un bloc d'instructions.

Exemple : la procédure surface_cercle() calcule la surface d'un cercle à partir de son rayon. Elle exécute pour cela la procédure carre(), qui élève un nombre au carré. Pour rappel, on calcule la surface d'un cercle avec la formule suivante : $S = \pi\times r^2$

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

DELIMITER |
CREATE PROCEDURE carre(INOUT p_nb FLOAT) SET p_nb = p_nb * p_nb|

CREATE PROCEDURE surface_cercle(IN p_rayon FLOAT, OUT p_surface FLOAT)
BEGIN
    CALL carre(p_rayon);

    SET p_surface = p_rayon * PI();
END|
DELIMITER ;

CALL surface_cercle(1, @surface);   -- Donne environ pi (3,14...)
SELECT @surface;
CALL surface_cercle(2, @surface);   -- Donne environ 12,57...
SELECT @surface;

Transactions et gestion d'erreurs

Un usage classique et utile des gestionnaires d'erreur est l'annulation des transactions en cas d'erreur.

Exemple : la procédure suivante prend en paramètre l'id d'un client, et de deux animaux que le client veut adopter :

 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
30
31
32
33
34
35
36

DELIMITER |
CREATE PROCEDURE adoption_deux_ou_rien(p_client_id INT, p_animal_id_1 INT, p_animal_id_2 INT)
BEGIN
    DECLARE v_prix DECIMAL(7,2);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;  -- Gestionnaire qui annule la transaction et termine la procédure

    START TRANSACTION;

    SELECT COALESCE(Race.prix, Espece.prix) INTO v_prix
    FROM Animal
    INNER JOIN Espece ON Espece.id = Animal.espece_id
    LEFT JOIN Race ON Race.id = Animal.race_id
    WHERE Animal.id = p_animal_id_1;

    INSERT INTO Adoption (animal_id, client_id, date_reservation, date_adoption, prix, paye)
    VALUES (p_animal_id_1, p_client_id, CURRENT_DATE(), CURRENT_DATE(), v_prix, TRUE);

    SELECT 'Adoption animal 1 réussie' AS message;

    SELECT COALESCE(Race.prix, Espece.prix) INTO v_prix
    FROM Animal
    INNER JOIN Espece ON Espece.id = Animal.espece_id
    LEFT JOIN Race ON Race.id = Animal.race_id
    WHERE Animal.id = p_animal_id_2;

    INSERT INTO Adoption (animal_id, client_id, date_reservation, date_adoption, prix, paye)
    VALUES (p_animal_id_2, p_client_id, CURRENT_DATE(), CURRENT_DATE(), v_prix, TRUE);

    SELECT 'Adoption animal 2 réussie' AS message;

    COMMIT;
END|
DELIMITER ;

CALL adoption_deux_ou_rien(2, 43, 55);  -- L'animal 55 a déjà été adopté

La procédure s'interrompt, puisque la seconde insertion échoue. On n'exécute donc pas le second SELECT. Ici, grâce à la transaction et au ROLLBACK du gestionnaire, la première insertion a été annulée.

Préparer une requête dans un bloc d'instructions

Pour finir, on peut créer et exécuter une requête préparée dans un bloc d'instructions. Ceci permet de créer des requêtes dynamiques, puisqu'on prépare une requête à partir d'une chaîne de caractères.

Exemple : la procédure suivante ajoute la ou les clauses que l'on veut à une simple requête SELECT :

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

DELIMITER |
CREATE PROCEDURE select_race_dynamique(p_clause VARCHAR(255))
BEGIN
    SET @sql = CONCAT('SELECT nom, description FROM Race ', p_clause);

    PREPARE requete FROM @sql;
    EXECUTE requete;
END|
DELIMITER ;

CALL select_race_dynamique('WHERE espece_id = 2');  -- Affichera les races de chats
CALL select_race_dynamique('ORDER BY nom LIMIT 2'); -- Affichera les deux premières races par ordre alphabétique de leur nom

Il va sans dire que ce genre de construction dynamique de requêtes peut poser d'énormes problèmes de sécurité si l'on ne prend pas de précaution. Par ailleurs, il n'est pas possible de construire une requête préparée à partir d'une variable locale. Il est donc nécessaire d'utiliser une variable utilisateur.

En résumé

• Un gestionnaire d'erreur permet d'intercepter un ou plusieurs types d'erreurs (ou avertissements) SQL et de déclencher une série d'instructions en cas d'erreur.
• Les erreurs interceptées peuvent être représentées par un numéro d'erreur MySQL, un identifiant SQLSTATE ou une CONDITION.
• Il existe trois conditions prédéfinies : SQLEXCEPTION (pour tout type d'erreur SQL), SQLWARNING (pour tout avertissement) et NOT FOUND (en cas de FETCH sur un jeu de résultats vide ou épuisé).
• Un curseur est une structure qui permet de parcourir un jeu de résultats
• Dans un bloc d'instructions, on déclare d'abord les variables locales, puis les conditions, suivies des curseurs, et pour finir les gestionnaires d'erreurs. Toutes ces déclarations doivent être faites avant les instructions du bloc.

• Structurer ses instructions
• Sommaire
• Triggers