17.2 Procédure stockée avec gestionnaire

Les étapes suivantes vous permettront de créer des procédures stockées utilisant les gestionnaires pour intercepter les exceptions. 

Dans cette démonstration, nous souhaitons créer une procédure stockée qui copie une table sous un autre nom. Cependant, elle ne doit pas planter si jamais il y avait déjà une table portant le nouveau nom.

1. Générer l’erreur manuellement pour connaître le code de l’erreur.

   Il existe deux types de code : le code d’erreur SQL ou le code d’erreur MySQL. Vous pouvez utiliser l'un ou l'autre, à votre convenance.

   Si vous reproduisez l’erreur dans PHPMyAdmin, c’est le code MySQL que vous obtiendrez.

   

   Dans phpMyAdmin, on voit que le code de l’erreur est 1050. Il s’agit du code MySQL.

   Si vous désirez obtenir les deux codes, vous devez reproduire l’erreur à la ligne de commande.

   

   On voit ici les deux codes d’erreur : le code MySQL est 1050 et le code SQL est 42S01.

2. Vérifier dans la liste officicelle des codes d'erreur (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html ou https://github.com/felixge/node-mysql/files/45625/mysql.5.7.errors.json.txt) si le code trouvé correspond bien à l'erreur que nous souhaitons attrapper. Dans notre exemple, nous voyons que c'est bien le cas.

   Error number: 1050; Symbol: ER_TABLE_EXISTS_ERROR; SQLSTATE: 42S01

   Message: Table '%s' already exists

3. Déclarer la procédure stockée ainsi que les variables locales dont elle aura besoin.

   Ex :

   MySQL

   CREATE PROCEDURE copier_table_mecaniciens(OUT code_erreur INT)

   BEGIN

       -- (entête standard)

       -- déclaration de variables locales

       DECLARE nombre INT;

       ...

   END$$

4. Comme nous aurons besoin de travailler avec un code d'erreur et que le code en soi n'indique pas la nature de l'erreur, nous allons utiliser une condition pour nommer l'erreur. Une condition n'est rien d'autre qu'un nom qu'on associe à un code d'erreur afin de faciliter la lecture de la procédure stockée.

   Dans la procédure stockée, déclarer une condition associée à cette erreur à l’aide de la commande suivante :

   Syntaxe MySQL

   DECLARE nom_condition CONDITION FOR valeur_condition;

   valeur_condition peut représenter :

   • le code de l’erreur MySQL (sans apostrophes ni guillemets)

     ou

   • le mot-clé SQLSTATE suivi du code de l’erreur SQL (code de l’erreur entre apostrophes ou guillemets).

   Ex :

   MySQL

   DECLARE table_existe_deja CONDITION FOR 1050;

   ou

   MySQL

   DECLARE table_existe_deja CONDITION FOR SQLSTATE '42S01';

5. Toujours dans la procédure stockée, déclarer un gestionnaire associé à la condition à l’aide de la commande suivante.

   Attention : il faut déclarer les gestionnaires APRÈS toute autre variable locale et AVANT toute autre instruction.

   Syntaxe MySQL

   DECLARE type_de_gestionnaire HANDLER FOR condition commande_à_exécuter;

   type_de_gestionnaire peut prendre les valeurs suivantes :

   • CONTINUE pour poursuivre l’exécution de la procédure stockée lorsque la condition survient
   • EXIT pour terminer l’exécution de la procédure stockée
   • UNDO : pas encore pris en compte. Si vous entrez UNDO, réagira comme CONTINUE

   condition peut prendre les valeurs suivantes :

   • nom de la condition (nom que vous avez utilisé dans la déclaration de la condition plus haut)
   • SQLSTATE suivi du numéro de l’erreur SQL
   • numéro de l’erreur MySQL
   • SQLWARNING pour que le gestionnaire s’occupe de toutes les erreurs dont le code SQL débute par 01
   • NOT FOUND pour que le gestionnaire s’occupe de toutes les erreurs dont le code SQL débute par 02
   • SQLEXCEPTION pour que le gestionnaire s’occupe de toutes les erreurs qui n'ont pas déjà été attrapées et dont le code SQL ne débute pas par 01 (SQLWARNING) ni 02 (NOT FOUND). Notez que SQLEXCEPTION ne tiendra pas compte non plus des codes débutant par 00 puisqu'ils indiquent un succès.

   commande_à_exécuter est une commande qui sera exécutée lorsque la condition surviendra. Il s’agit généralement d’assigner une valeur à une variable qui pourra être utilisée dans un programme PHP ou dans la procédure stockée, par exemple pour mettre fin à une boucle. Il est également possible d'effectuer plusieurs commandes, en les entourant de BEGIN et de END.

   Voir le manuel en ligne de MySQL pour la syntaxe complète :

   "13.6.7.2 DECLARE ... HANDLER Syntax". MySQL. http://dev.mysql.com/doc/refman/5.7/en/declare-handler.html

   Ex :

   MySQL

   DECLARE CONTINUE HANDLER FOR table_existe_deja SET continuer=0;

   ou

   MySQL

   DECLARE EXIT HANDLER FOR table_existe_deja SET code_erreur=1;

   Si vous désirez réagir à toute forme d'erreur qui ne soit ni un SQLWARNING, ni un NOT FOUND, vous pouvez utiliser SQLEXCEPTION.

   Ex :

   MySQL

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET code_erreur=-1;

6. Poursuivre le développement de la procédure stockée et utiliser au besoin la variable initialisée par le gestionnaire pour contrôler le traitement.

   Ex :

   MySQL

   CREATE PROCEDURE copier_table_mecaniciens(OUT code_erreur INT)

   BEGIN

       -- Copie la table mecaniciens dans copiemecaniciens

       -- Programmé par : Annie Gagnon

       -- Le : 2 novembre 2013

       -- Paramètres : OUT INT code de l'erreur rencontrée

       -- Valeurs possibles :

       --     0 : copie effectuée avec succès

       --    -1 : copie non effectuée, erreur non déterminée

       --    -2 : copie non effectuée car aucune donnée à copier

       --  1050 : copie non effectuée car la table copiemecaniciens existait déjà.

       -- Historique des modifications

       -- Par :

       -- Le :

       -- Modifications :

    

       -- déclaration de variables locales

       DECLARE nombre INT;

    

       -- déclaration de conditions

       DECLARE table_existe_deja CONDITION FOR 1050;

    

       -- déclaration de gestionnaires

       DECLARE CONTINUE HANDLER FOR table_existe_deja SET code_erreur = 1050;

       DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET code_erreur = -1;

    

       -- traitement

       SET code_erreur = 0;

    

       SELECT COUNT(*) INTO nombre FROM mecaniciens;

    

       IF nombre > 0 THEN

           CREATE TABLE copiemecaniciens AS (SELECT * FROM mecaniciens);

           IF code_erreur != 0 THEN

               INSERT INTO journalerreurs (message) VALUES (CONCAT('Erreur lors de la copie :', code_erreur));

           END IF;

       ELSE

          -- le programme PHP saura que la valeur -2 signifie que la table n'a pas été créée pcq rien à copier dedans

          SET code_erreur = -2;

       END IF;

   END$$

Pour plus d'information

« 13.6.7.2 DECLARE ... HANDLER Syntax ». MySQL. http://dev.mysql.com/doc/refman/5.7/en/declare-handler.html

« Appendix B Errors, Error Codes, and Common Problems ». MySQL. http://dev.mysql.com/doc/refman/5.7/en/error-handling.html

« MySQL Error Codes ». briandunning.com. http://www.briandunning.com/error-codes/?source=MySQL