Module 1Maîtriser le langage de programmation procédurale sous MySQL
Chapitre 6 sur 12
Les handlers EXIT et CONTINUE, les codes d'erreur, SQLSTATE, NOT FOUND, SQLEXCEPTION, les exceptions nommées, SIGNAL et RESIGNAL.
Objectifs
Avec ce chapitre, tu sauras…
EXIT ou CONTINUE ;NOT FOUND, SQLWARNING, SQLEXCEPTION ;DECLARE … CONDITION ;SIGNAL et RESIGNAL.Leçon 1
Sans précaution, un programme MySQL s'arrête à la première erreur d'une instruction SQL. Pour l'éviter, on prévoit les erreurs possibles et on associe à chacune un traitement : un handler (gestionnaire d'exception).
BEGIN … END, si un handler la prévoit.CONTINUE et EXIT.Leçon 2
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] …
statement
handler_action : {
CONTINUE
| EXIT
| UNDO
}
condition_value : {
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
}| Élément | Signification |
|---|---|
CONTINUE | après le traitement de l'erreur, le programme continue à l'instruction suivante |
EXIT | après le traitement, on sort du bloc BEGIN … END où le handler est déclaré |
UNDO | prévu par la norme, pas pris en charge par MySQL |
| code d'erreur MySQL | un numéro précis : 1048 (colonne NULL interdite), 1062 (doublon)… |
SQLSTATE 'xxxxx' | un code à 5 caractères qui couvre toutes les erreurs d'un même état ('23000' : violation de contrainte) |
| nom d'exception | une exception nommée avec DECLARE … CONDITION (section 5) |
SQLWARNING | tous les SQLSTATE qui commencent par 01 (avertissements) |
NOT FOUND | tous les SQLSTATE qui commencent par 02 : aucune ligne trouvée (SELECT … INTO, curseurs) |
SQLEXCEPTION | toutes les autres erreurs (ni SQLWARNING ni NOT FOUND) |
statement | une ou plusieurs instructions (un bloc, un CALL…) |
Vidéo · À regarder
FRBase de données : Gestion des erreurs en MySQL
Algomius · YouTube
Leçon 3
Vidéo · À regarder
FRBase de données : Gestion des erreurs en MySQL
Algomius · YouTube
Leçon 4
| EXIT | CONTINUE | |
|---|---|---|
| après le handler | sort du bloc courant | passe à l'instruction suivante |
| usage | arrêter proprement un traitement (et annuler une transaction) | ignorer une erreur prévue et poursuivre (curseurs, chapitre 7) |
Leçon 5
Leçon 6
Leçon 7
1Après un handler EXIT, le programme…
2NOT FOUND couvre les SQLSTATE qui commencent par…
3Le SQLSTATE réservé aux erreurs définies par l'utilisateur est…
4RESIGNAL s'utilise…
5L'erreur 1048 signifie…
6Dans un bloc, les handlers se déclarent…
7Pour donner un nom à une erreur, on utilise…
L'essentiel du chapitre
DECLARE CONTINUE | EXIT HANDLER FOR condition instruction; déclaré après les variables et curseurs.EXIT : sort du bloc (d'où le bloc imbriqué) ; CONTINUE : poursuit à l'instruction suivante.SQLSTATE '…' (23000 contrainte), NOT FOUND (02), SQLWARNING (01), SQLEXCEPTION (le reste).DECLARE nom CONDITION FOR 1062 | SQLSTATE '…';.SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '…'; : lever sa propre erreur, partout.RESIGNAL [SET MESSAGE_TEXT = '…'] : relancer l'erreur, seulement dans un handler.GET DIAGNOSTICS CONDITION 1 @code = MYSQL_ERRNO, @msg = MESSAGE_TEXT;.La liste des codes SQLSTATE est dans la documentation MySQL (Server Error Message Reference).
Dans un bloc, MySQL impose cet ordre : d'abord les variables et les conditions, puis les curseurs, et en dernier les handlers. Sinon : erreur Variable or condition declaration after cursor or handler declaration.
Ce que tu vas voir (10 min) : les chapitres « Présentation du modèle », « Erreur SQL ». Le formateur présente le modèle de données, puis provoque des erreurs SQL et lit leurs codes.
Exemple 1 : le nom obligatoire
On essaie d'insérer un client sans nom (colonne NOT NULL) : MySQL lève l'erreur 1048 (Column 'nom' cannot be null). On la gère avec un handler EXIT :
DELIMITER //
CREATE PROCEDURE ps_ajouter_client_Exception1(IN p_nom VARCHAR(200),
IN p_prenom VARCHAR(200),
IN p_adresse VARCHAR(200))
BEGIN
DECLARE flagNOTNULL BOOLEAN DEFAULT 0;
BEGIN -- début du bloc qui déclare l'exception
DECLARE EXIT HANDLER FOR 1048
SET flagNOTNULL := -1;
INSERT INTO clients (nom, prenom, adresse) VALUES (p_nom, p_prenom, p_adresse);
SELECT 'le client est ajouté avec succès';
END; -- fin du bloc qui déclare l'exception
IF flagNOTNULL THEN
SELECT CONCAT('Le champ nom doit être non null') AS 'Resultat ps_ajouter_client_Exception1';
END IF;
END //
DELIMITER ;
CALL ps_ajouter_client_Exception1(null, 'Saloua', 'Safi');
-- Le champ nom doit être non nullINSERT échoue avec l'erreur 1048 ;flagNOTNULL à -1 ; comme c'est un EXIT, on sort du bloc intérieur : le message « ajouté avec succès » n'est pas affiché ;IF affiche le message d'erreur.C'est pour cela que le handler est dans un bloc imbriqué : l'EXIT ne quitte que ce bloc, pas toute la procédure.
Le texte parle du code 1040, mais le code exact (et celui du programme) est 1048 ; 1040 signifie Too many connections. Le programme commence par DELEMITER//; : il faut DELIMITER //. Et -1 dans un BOOLEAN : MySQL stocke un booléen comme un petit entier, toute valeur non nulle est « vraie ».
Exemple 2 : aucun client trouvé (NOT FOUND)
La procédure gère l'erreur « aucun client ne porte ce nom » (NOT FOUND) :
DELIMITER //
CREATE PROCEDURE ps_exc_not_found_Exemple1(IN p_nom VARCHAR(200))
BEGIN
DECLARE flagNOTFOUND BOOLEAN DEFAULT 0;
DECLARE var1 VARCHAR(20);
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND
SET flagNOTFOUND := -1;
SELECT nom INTO var1 FROM clients
WHERE nom = p_nom;
SELECT CONCAT('Le seul client avec le nom ', p_nom, ' est ', var1)
AS 'Resultat ps_exc_not_found_Exemple1';
END;
IF flagNOTFOUND THEN
SELECT CONCAT('Il n''y a pas de client avec le nom ', p_nom)
AS 'Resultat ps_exc_not_found_Exemple1';
END IF;
END //
DELIMITER ;
CALL ps_exc_not_found_Exemple1('Dalaj'); -- Il n'y a pas de client avec le nom Dalaj
CALL ps_exc_not_found_Exemple1('Rami'); -- Le seul client avec le nom Rami est RamiLa procédure ne se termine pas correctement si plusieurs lignes sont trouvées : SELECT … INTO lève alors l'erreur 1172 Result consisted of more than one row, qui n'est pas gérée ici (exercice 6.3).
La procédure s'appelle ps_exc_not_found_Exemple à la création mais ps_exc_not_found_Exemple1 à l'appel : le nom doit être le même. Il manque aussi un ; après le END du bloc intérieur.
Exemple 3 : une erreur quelconque (SQLEXCEPTION)
DELIMITER //
CREATE PROCEDURE autreErreur()
BEGIN
SELECT 'Une autre erreur est survenue';
END //
CREATE PROCEDURE handlerdemoSQLEXCEPTION()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION CALL autreErreur();
INSERT INTO table_inexistante VALUES (1);
SET @x = 99;
END //
DELIMITER ;
CALL handlerdemoSQLEXCEPTION(); -- Une autre erreur est survenue
SELECT @x; -- NULL : le SET @x = 99 n'a pas été exécuté (EXIT)SQLEXCEPTION attrape n'importe quelle erreur (ici : la table n'existe pas). Le handler peut appeler une procédure avec CALL.
Le résumé dit que MySQL ne permet pas de récupérer dans le programme le code et le message de l'erreur. C'est possible depuis MySQL 5.6 avec GET DIAGNOSTICS :
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @code = MYSQL_ERRNO, @msg = MESSAGE_TEXT;
SELECT @code, @msg; -- 1146, Table 'eshop_app_db.table_inexistante' doesn't exist
END;
Ce que tu vas voir (19 min) : les chapitres « Gestion d'erreur et procédures ». Il gère les erreurs dans des procédures stockées : il déclare des handlers, utilise un drapeau et choisit entre EXIT et CONTINUE.
La procédure gère l'erreur de clé dupliquée (SQLSTATE '23000') :
CREATE TABLE test (code INT, PRIMARY KEY (code));
DELIMITER //
CREATE PROCEDURE handlerdemo()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
SET @x2 = 1;
SET @x = 1;
INSERT INTO test VALUES (1);
SET @x = 2;
INSERT INTO test VALUES (1); -- doublon : erreur 23000, le handler s'exécute…
SET @x = 3; -- … puis le programme CONTINUE ici
END //
DELIMITER ;
CALL handlerdemo();
SELECT @x; -- 3Bien que le deuxième INSERT provoque une erreur de doublon de clé primaire, l'exécution continue après lui : @x vaut 3 et @x2 vaut 1.
La table test a été créée au chapitre 4 avec une colonne VALUE ; ici, le résumé la recrée avec une colonne code et une clé primaire. Supprime d'abord l'ancienne : DROP TABLE IF EXISTS test;. Et DELIMITER // ; doit s'écrire DELIMITER // (sans ;).
Pour intercepter une erreur et lui donner un nom lisible, on utilise DECLARE CONDITION :
DECLARE nomException CONDITION FOR
{SQLSTATE [VALUE] 'valeur_sqlstate' | code_erreur_mysql};DECLARE doublon CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR doublon SET @erreur = 'déjà existant';Le code devient plus clair : FOR doublon se lit mieux que FOR 1062.
On utilise SIGNAL pour renvoyer une erreur (ou un avertissement) à l'appelant depuis une procédure, une fonction, un déclencheur ou un événement. On choisit l'information renvoyée : le SQLSTATE et le message.
SIGNAL SQLSTATE 'valeur' | nom_exception_nommee
SET info_1 = valeur_1,
info_2 = valeur_2, …;Les informations possibles : MESSAGE_TEXT, MYSQL_ERRNO, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME…
DELIMITER //
CREATE PROCEDURE ajouter_commande(IN commandeNo INT,
IN produitCode VARCHAR(45),
IN qte INT,
IN prix DOUBLE,
IN ligneNO INT)
BEGIN
DECLARE C INT;
SELECT COUNT(commandeNumber) INTO C
FROM commandes
WHERE commandeNumber = commandeNo;
-- teste si le numéro de la commande existe
IF (C != 1) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Commande introuvable';
END IF;
-- suite du code à exécuter s'il n'y a pas d'erreur
-- …
END //
DELIMITER ;
CALL ajouter_commande(99, 'P1', 1, 100, 1);
-- ERROR 1644 (45000): Commande introuvable'45000' est le SQLSTATE réservé aux erreurs définies par l'utilisateur (« exception non gérée »).
Le paramètre IN commandeNo n'a pas de type : il faut IN commandeNo INT. L'exemple utilise une table commandes avec une colonne commandeNumber, qui n'existe pas dans eshop_app_db : pour le tester, crée CREATE TABLE commandes2 (commandeNumber INT PRIMARY KEY); et remplace commandes par commandes2.
RESIGNAL a la même fonction et la même syntaxe que SIGNAL, sauf que :
SIGNAL, lui, s'utilise n'importe où ;DELIMITER $$
CREATE PROCEDURE Divide(IN numerator INT, IN denominator INT, OUT result DOUBLE)
BEGIN
DECLARE division_by_zero CONDITION FOR SQLSTATE '22012';
DECLARE CONTINUE HANDLER FOR division_by_zero
RESIGNAL SET MESSAGE_TEXT = 'Division by zero / Denominator cannot be zero';
IF denominator = 0 THEN
SIGNAL division_by_zero;
ELSE
SET result := numerator / denominator;
END IF;
END $$
DELIMITER ;
CALL Divide(10, 2, @r); SELECT @r; -- 5
CALL Divide(1, 0, @r);
-- ERROR 1644 (22012): Division by zero / Denominator cannot be zeroSIGNAL division_by_zero lève l'exception nommée (SQLSTATE 22012) ;RESIGNAL, en changeant le message ;CONDISTION doit s'écrire CONDITION, et le -IF en début de ligne a un tiret en trop.
Écris la procédure sp_modifier_prix(IN p_id INT, IN p_prix DECIMAL(10,2)) : si le prix est négatif ou nul, elle lève avec SIGNAL l'erreur '45000' « Le prix doit être positif » ; si le produit n'existe pas (ROW_COUNT() = 0 après l'UPDATE), elle lève « Produit introuvable ». Sinon, elle affiche « Prix modifié ».
Écris la procédure sp_ajouter_categorie(IN p_nom VARCHAR(50)) qui ajoute une catégorie. Ajoute d'abord un index unique : ALTER TABLE categorie ADD UNIQUE (nom_cat);. Si la catégorie existe déjà (erreur 1062), la procédure affiche « La catégorie existe déjà » au lieu de s'arrêter. Utilise une exception nommée doublon.
Complète ps_exc_not_found_Exemple1 pour gérer aussi le cas où plusieurs clients ont le même nom (erreur 1172) : la procédure affiche alors « Plusieurs clients portent le nom … ». Teste en ajoutant un deuxième client « Rami ».