Mysql 5 Proc Stock Triggers
Mysql 5 Proc Stock Triggers
Mysql 5 Proc Stock Triggers
Procdures stockes
et
Triggers
Sommaire
Chapitre 1
PROCEDURES STOCKEES.........................................................3
1.1 Procdures stockes...................................................................................4
1.1.1
Gnralits.........................................................................................4
1.1.2
Connexion.........................................................................................5
1.1.3
Stockage............................................................................................6
1.1.4
Cration d'une procdure stocke......................................................7
1.1.5
Suppression d'une procdure stocke................................................7
1.1.6
Procdures stockes sans paramtre..................................................8
1.1.7
Procdures stockes avec des paramtres IN....................................9
1.1.8
Procdure stocke avec des paramtres OUT..................................10
1.2 Fonctions stockes...................................................................................12
1.3 Notions avances.....................................................................................15
1.3.1
Les instructions de contrles...........................................................15
1.3.1.1 Le IF.............................................................................................15
1.3.1.2 LE CASE......................................................................................16
1.3.1.3 La boucle LOOP...........................................................................17
1.3.1.4 La boucle WHILE........................................................................18
1.3.1.5 La boucle REPEAT......................................................................19
1.3.2
SQL dynamique...............................................................................20
1.3.2.1 Dmarche et Syntaxes..................................................................20
1.3.2.2 Un Count......................................................................................21
1.3.2.3 Un SELECT.................................................................................23
1.3.2.4 Un SELECT WHERE.............................................................25
1.3.2.5 Un SELECT gnrique.................................................................27
1.3.2.6 Un DELETE.................................................................................28
1.3.2.7 Un INSERT..................................................................................29
1.3.3
Les curseurs : une procdure sans retour........................................30
1.3.3.1 Exemple : Transfert d'une table vers une autre............................31
1.3.3.2 Exemple : Un tableau crois dynamique......................................32
1.3.4
Curseur qui renvoie un rsultat de type VARCHAR.......................33
1.3.5
Un SELECT donc un curseur..........................................................34
1.4 La gestion des ERREURS.......................................................................35
Chapitre 2
LES TRIGGERS..........................................................................40
2.1 Syntaxes...................................................................................................40
2.2 Exemple : archiver un enregistrement de la table villes..........................42
2.3 Exercice : journalisation..........................................................................43
2.4 Les instructions de contrles dans les Triggers.......................................44
Chapitre 3
ANNEXE : BD UTILISEE..........................................................45
Pascal Buguet
Page 2
Pascal Buguet
Page 3
CHAPITRE 1
Pascal Buguet
PROCEDURES STOCKEES
Page 4
Objectifs
Droits
Le droit CREATE ROUTINE est ncessaire pour crer une procdure ou une fonction
stocke.
Le droit ALTER ROUTINE est ncessaire pour pouvoir modifier ou supprimer une
procdure ou une fonction stocke. Le droit est fourni automatiquement au crateur
d'une routine.
Le droit EXECUTE est requis pour excuter une procdure stocke.
Ce droit est fourni automatiquement au crateur d'une routine.
Les procdures stockes et les fonctions stockes (depuis MySQL 5.0.3) sont stockes
dans la table mysql.proc.
Pascal Buguet
Page 5
1.1.2 Connexion
C:\\mysql\bin>mysql --host=localhost --user=root --password=
Pascal Buguet
--database=cours
Page 6
1.1.3 Stockage
Soit avec un outil comme MySQLQueryBrowser dans une page de Script (pas de
ResultSet) ou dans PhpMyAdmin ou avec l'utilitaire mysql.
MySQL Query Browser
--database=cours
Mysql>DELIMITER $$
Mysql>CREATE PROCEDURE .
Mysql>DELIMITER;
Pascal Buguet
Page 7
Pascal Buguet
Page 8
CALL villesSelect;
Pascal Buguet
Page 9
DELIMITER $$
DROP PROCEDURE IF EXISTS villesInsert $$
CREATE PROCEDURE villesInsert (IN asCp CHAR(5), IN asVille VARCHAR(50), IN asIdPays
CHAR(3))
BEGIN
INSERT INTO villes(cp, nom_ville, id_pays) VALUES(asCp, asVille, asIdPays);
END $$
DELIMITER;
DELIMITER $$
DROP PROCEDURE IF EXISTS villesDelete $$
CREATE PROCEDURE villesDelete(IN asCp CHAR(5))
BEGIN
DELETE FROM villes WHERE cp = asCp;
END $$
DELIMITER;
CALL villesDelete('75021');
Pascal Buguet
Page 10
DELIMITER $$
DROP PROCEDURE IF EXISTS villesNbProc $$
CREATE PROCEDURE villesNbProc(OUT aiVilles INT)
BEGIN
SELECT COUNT(*) INTO aiVilles FROM villes;
END $$
DELIMITER;
Avec mysql
mysql>CALL villesNbProc(@Resultat);
mysql>SELECT @Resultat;
CALL villesNbProc(@Resultat);
SELECT @Resultat;
Avec @ pour que ce ne soit pas pris pour champ mais bien une variable de session.
Pascal Buguet
Page 11
DELIMITER $$
DROP PROCEDURE IF EXISTS villesUneProc $$
CREATE PROCEDURE villesUneProc(IN asCp CHAR(5) , OUT asNomVille VARCHAR(50))
BEGIN
SELECT nom_ville INTO asNomVille FROM villes WHERE cp = asCp;
END $$
DELIMITER;
Pascal Buguet
Page 12
Le type des paramtres ou retourn par une fonction peut tre STRING, INTEGER ou
REAL ou un type SQL (CHAR(5), ).
Une fonction AGGREGATE se comporte comme une fonction agrgat native (SUM, COUNT,
).
Excution
SELECT nom_de_fonction(arguments);
Pascal Buguet
Page 13
La fonction
DELIMITER $$
DROP FUNCTION IF EXISTS addition $$
CREATE FUNCTION addition(aiX INT , aiY INT) RETURNS INT
BEGIN
DECLARE nTotal INT DEFAULT 0;
SET n_total = aiX + aiY;
RETURN nTotal;
END $$
DELIMITER;
Excution
SELECT addition(3,4);
La fonction
DELIMITER $$
DROP FUNCTION IF EXISTS TTC $$
CREATE FUNCTION TTC(aiPrixHT DOUBLE) RETURNS DOUBLE
[DETERMINISTIC]
BEGIN
RETURN aiPrixHT * 1.195;
END $$
DELIMITER;
Excution
SELECT prix "Prix HT", TTC(prix) "Prix TTC" FROM produits;
Pascal Buguet
Page 14
DELIMITER $$
DROP FUNCTION IF EXISTS villesNbFct $$
CREATE FUNCTION villesNbFct() RETURNS INT
BEGIN
DECLARE nVilles INT DEFAULT 0;
SELECT COUNT(*) INTO nVilles FROM villes;
RETURN nVilles;
END $$
DELIMITER;
DELIMITER $$
DROP FUNCTION IF EXISTS villesUneFct $$
CREATE FUNCTION villesUneFct(asCp CHAR(5)) RETURNS VARCHAR(50)
BEGIN
DECLARE lsNomVille VARCHAR(50);
SELECT nom_ville INTO lsNomVille FROM villes WHERE cp = asCp;
RETURN lsNomVille;
END $$
DELIMITER;
Exercices :
Pascal Buguet
Page 15
Le IF
IF condition THEN
Action;
ELSE
Action;
END IF;
Exemple
La mme fonction que prcdemment qui renvoie le nom de la ville en fonction du CP
mais avec un message si le CP n'existe pas.
Script de cration de la fonction stocke
DELIMITER $$
DROP FUNCTION IF EXISTS villesUneAvecIf $$
CREATE FUNCTION villesUneAvecIf(asCp CHAR(5)) RETURNS VARCHAR(50)
BEGIN
DECLARE nbVilles INT(1);
DECLARE lsNomVille VARCHAR(50);
SELECT COUNT(*) INTO nbVilles FROM villes WHERE cp = asCp;
IF nbVilles = 1 THEN
SELECT nom_ville INTO lsNomVille FROM villes WHERE cp = asCp;
ELSE
SET lsNomVille = 'Pas de ville pour ce CP';
END IF;
RETURN lsNomVille;
END $$
DELIMITER;
Test
SET CHARACTER_SET_SERVER = 'latin1';
SET COLLATION_SERVER ='latin1_general_ci';
SELECT villesUneAvecIf('75011') "La ville en question";
Problme avec les jeux de caractres sous MySQL Query Browser. A tester sous PHPMyADMIN.
Pascal Buguet
Page 16
1.3.1.2
LE CASE
CASE variable
WHEN valeur THEN action;
[WHEN valeur THEN action;]
[ELSE action;]
END CASE;
Exemple
La mme fonction que prcdemment qui renvoie le nom de la ville en fonction du CP
mais avec un message si le CP n'existe pas.
Script de cration de la fonction stocke
DELIMITER $$
DROP FUNCTION IF EXISTS villesUneAvecCase $$
CREATE FUNCTION villesUneAvecCase(asCp CHAR(5)) RETURNS VARCHAR(50)
BEGIN
DECLARE nbVilles INT(1);
DECLARE lsNomVille VARCHAR(50);
SET nbVilles = 0;
SELECT COUNT(*) INTO nbVilles FROM villes WHERE cp = asCp;
CASE nb_villes
WHEN 1 THEN SELECT nom_ville INTO lsNomVille FROM villes WHERE cp = asCp;
WHEN 0 THEN SET lsNomVille = 'Pas de ville pour ce CP';
END CASE;
RETURN lsNomVille;
END $$
DELIMITER;
Test
-- Pour ne pas avoir ce message-ci :
-- #1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and
(latin1_swedish_ci,IMPLICIT) for operation '='
SET CHARACTER_SET_SERVER = 'latin1';
SET COLLATION_SERVER ='latin1_general_ci';
SELECT villesUneAvecCase('75011') "La ville en question";
Pascal Buguet
Page 17
1.3.1.3
La boucle LOOP
tiquette: LOOP
instruction;
IF condition THEN LEAVE tiquette; END IF;
instruction;
END LOOP tiquette;
Exemple : factorielle.
Script
DELIMITER $$
DROP FUNCTION IF EXISTS fctLoop $$
CREATE FUNCTION fctLoop(aiN INT) RETURNS INT
BEGIN
DECLARE liC INT;
DECLARE liFactorielle INT;
SET liC = 2;
SET liFactorielle = 1;
calcul: LOOP
SET liFactorielle = liFactorielle * liC;
IF liC = aiN THEN LEAVE calcul; END IF;
SET liC = liC + 1;
END LOOP calcul;
RETURN liFactorielle;
END $$
DELIMITER;
Test
SELECT fctLoop(4);
Pascal Buguet
Page 18
1.3.1.4
La boucle WHILE
Ralise une boucle de type TANT QUE. Tant que la condition est Vrai on boucle.
WHILE condition DO
Instructions;
END WHILE;
Exemple : factorielle.
Script
DELIMITER $$
DROP FUNCTION IF EXISTS fctWhile $$
CREATE FUNCTION fctWhile(aiN INT) RETURNS INT
BEGIN
DECLARE liC INT;
DECLARE liFactorielle INT;
SET liC = 2;
SET liFactorielle = 1;
WHILE liC <= aiN DO
SET liFactorielle = liFactorielle * liC;
SET liC = liC + 1;
END WHILE;
RETURN liFactorielle;
END $$
DELIMITER;
Test
SELECT fctWhile(3);
Pascal Buguet
Page 19
1.3.1.5
La boucle REPEAT
Ralise une boucle de type FAIRE JUSQU'A. On boucle juqu' ce que la condition soit
VRAI.
REPEAT
Instructions;
UNTIL condition END REPEAT;
Script
DELIMITER $$
DROP FUNCTION IF EXISTS fctRepeat $$
CREATE FUNCTION fctRepeat(aiN INT) RETURNS INT
BEGIN
DECLARE liC INT;
DECLARE liFactorielle INT;
SET liC = 1;
SET liFactorielle = 1;
REPEAT
SET liFactorielle = liFactorielle * li_c;
SET liC = liC + 1;
UNTIL liC > aiN END REPEAT;
RETURN liFactorielle;
END $$
DELIMITER;
Test
SELECT fctRepeat(3);
Pascal Buguet
Page 20
1.3.2.1
Dmarche et Syntaxes
Dclarer une variable de type VARCHAR ou TEXT ou BLOB pour stocker la commande
SQL. C'est un objet!
Affecter une variable de session (@var) l'ordre SQL avec la fonction CONCAT(). La
variable NE DOIT PAS tre dclare au pralable.
Excuter la commande.
EXECUTE cmd;
Librer la mmoire.
Pascal Buguet
Page 21
1.3.2.2
Un Count
Pascal Buguet
Page 22
Pascal Buguet
Page 23
1.3.2.3
Un SELECT
Une procdure stocke qui renvoie un curseur sur n'importe quelle table.
Le paramtre IN de la PS est le nom de la table.
DELIMITER $$
DROP PROCEDURE IF EXISTS sqlDynTable $$
CREATE PROCEDURE sqlDynTable(IN asTable VARCHAR(50))
BEGIN
/* DECLARE lsSql VARCHAR(50);
SURTOUT PAS DE DECLARE de la variable qui contient le SQL qui doit tre de
session
donc avec @
*/
DECLARE cmd
VARCHAR(255);
Pascal Buguet
Page 24
Pascal Buguet
Page 25
1.3.2.4
Un SELECT WHERE
Objectif
Syntaxe
Number
DELIMITER $$
DROP PROCEDURE IF EXISTS sqlDynSelectWhereNum $$
CREATE PROCEDURE sqlDynSelectWhereNum(IN asTable VARCHAR(50), IN asCol VARCHAR(50),
IN asValeur VARCHAR(50))
BEGIN
DECLARE cmd VARCHAR(255);
SET @lsSql = CONCAT('SELECT * FROM ', asTable, ' WHERE ', asCol, '=' ,
asValeur);
PREPARE cmd FROM @lsSql;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
END $$
DELIMITER;
Pascal Buguet
Page 26
VARCHAR(255);
SET @lsSql = CONCAT('SELECT * FROM ', asTable, ' WHERE ', asCol, '=\'' ,
asValeur, '\'');
PREPARE cmd FROM @lsSql;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
END $$
DELIMITER;
Pascal Buguet
Page 27
1.3.2.5
Un SELECT gnrique
Cette PS fait un SELECT Dynamique avec ou sans condition sur une colonne numrique
ou String.
Permet de rcuprer toute une table ou une projection ou une restriction ou une
Projection/Restriction.
DELIMITER $$
DROP PROCEDURE IF EXISTS sqlDynSelect $$
CREATE PROCEDURE sqlDynSelect(IN asTable VARCHAR(50), IN asCols VARCHAR(200), IN
asColWhere VARCHAR(50), IN asValeur VARCHAR(100))
BEGIN
DECLARE cmd VARCHAR(255);
IF asColWhere = '' THEN
SET @lsSql = CONCAT('SELECT ', asCols, ' FROM ', asTable);
ELSE
SET @lsSql = CONCAT('SELECT ', asCols, ' FROM ', asTable, ' WHERE ',
asColWhere, '=\'' , asValeur, '\'');
END IF;
PREPARE cmd FROM @lsSql;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
END $$
DELIMITER;
Pascal Buguet
Page 28
1.3.2.6
Un DELETE
DELIMITER $$
DROP PROCEDURE IF EXISTS sqlDynDelete $$
CREATE PROCEDURE sqlDynDelete(IN asTable VARCHAR(50), IN asCol VARCHAR(50), IN
asValeur VARCHAR(50))
BEGIN
/* DECLARE lsSql VARCHAR(50);
SURTOUT PAS DE DECLARE de la variable qui contient le SQL qui doit tre de
session donc avec @
*/
DECLARE cmd
VARCHAR(255);
SET @lsSql = CONCAT('DELETE FROM ', asTable, ' WHERE ', asCol, '=' , asValeur);
PREPARE cmd FROM @lsSql;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
END $$
DELIMITER;
Pascal Buguet
Page 29
1.3.2.7
Un INSERT
DELIMITER $$
DROP PROCEDURE IF EXISTS cours.sqlDynInsert $$
CREATE PROCEDURE sqlDynInsert(IN asTable VARCHAR(50), IN asCols VARCHAR(50), IN
asValeurs VARCHAR(50))
BEGIN
/* DECLARE lsSql VARCHAR(50);
SURTOUT PAS DE DECLARE de la variable qui contient le SQL qui doit tre de
session donc avec @
*/
DECLARE cmd
VARCHAR(255);
SET @lsSql = CONCAT('INSERT INTO ', asTable, '(', asCols, ') VALUES(', asValeurs,
')');
PREPARE cmd FROM @lsSql;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
END $$
DELIMITER;
Pascal Buguet
Page 30
Syntaxes
OPEN nom_de_curseur;
Fermeture du curseur
CLOSE nom_de_curseur;
Pascal Buguet
Page 31
1.3.3.1
Objectif
Script
DELIMITER $$
DROP PROCEDURE IF EXISTS ajout_94 $$
CREATE PROCEDURE ajout_94()
BEGIN
DECLARE fin INTEGER DEFAULT 0;
DECLARE ls_cp, ls_nom_ville VARCHAR(50);
DECLARE c_villes_94 CURSOR FOR SELECT cp, nom_ville FROM villes WHERE cp LIKE
'94%';
-- Dclare un handler pour basculer l'indicateur "boolen" utilis dans la boucle
-- de balayage du curseur afin de sortir de la boucle
-- quand la fin du curseur est atteinte
-- il doit tre dclar juste aprs la dclaration du curseur
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET fin = 1;
OPEN c_villes_94;
FETCH c_villes_94 INTO ls_cp, ls_nom_ville;
WHILE fin = 0 DO
-- Parcours du curseur
INSERT INTO villes_94(cp, nom_ville) VALUES (ls_cp, ls_nom_ville);
FETCH c_villes_94 INTO ls_cp, ls_nom_ville;
END WHILE;
CLOSE c_villes_94;
END $$
DELIMITER;
Test
CALL ajout_94();
Pascal Buguet
Page 32
1.3.3.2
Objectif
Dmarche
Cration d'une fonction stocke qui renvoie l'ordre SQL SELECT ncessaire pour la
cration du TCD.
DELIMITER $$
DROP FUNCTION IF EXISTS tcdDynVentesVendeursProduits $$
CREATE FUNCTION tcdDynVentesVendeursProduits() RETURNS VARCHAR(1000)
BEGIN
DECLARE fin INTEGER DEFAULT 0;
DECLARE lsDesignation VARCHAR(50);
DECLARE lsSelectDesignations VARCHAR(1000) DEFAULT '';
DECLARE lsSelectTcd VARCHAR(1000) DEFAULT '';
DECLARE curseurDesignation CURSOR FOR SELECT DISTINCT designation FROM produits;
-- Dclare un handler pour basculer l'indicateur "boolen" utilis dans la boucle
-- de balayage du curseur afin de sortir de la boucle
-- quand la fin du curseur est atteinte
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET fin = 1;
-- Cration de la partie du SELECT avec les SUM bass sur la table produits et
la colonne designation
OPEN curseurDesignation;
FETCH curseurDesignation INTO lsDesignation;
WHILE fin= 0 DO
-- Parcours du curseur
SET lsSelectDesignations = CONCAT(lsSelectDesignations,"SUM(IF(designation =
'", lsDesignation,"', vente, 0)) AS """ , lsDesignation, """ ,");
FETCH curseurDesignation INTO lsDesignation;
END WHILE;
CLOSE curseurdesignation;
SET lsSelectDesignations = SUBSTRING(lsSelectDesignations, 1,
LENGTH(lsSelectDesignations) - 1);
SET lsSelectTcd =
CONCAT("SELECT nom, ", lsSelectDesignations, " FROM ventes ve, vendeurs v ,
produits p WHERE ve.id_vendeur = v.id_vendeur AND ve.id_produit = p.id_produit
GROUP BY nom" );
-- Retour du rsultat
RETURN lsSelectTcd;
END $$
DELIMITER;
Pascal Buguet
Page 33
Script MySQL
DELIMITER $$
DROP PROCEDURE IF EXISTS villesCurseurOut $$
CREATE PROCEDURE villesCurseurOut(OUT as_resultat VARCHAR(2000))
BEGIN
DECLARE fin INTEGER DEFAULT 0;
DECLARE v_cp, v_nom_ville VARCHAR(50);
DECLARE v_resultat VARCHAR(2000);
DECLARE curseur_villes CURSOR FOR SELECT cp, nom_ville FROM villes;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET fin = 1;
SET v_resultat = '';
OPEN curseur_villes;
FETCH curseur_villes INTO v_cp, v_nom_ville;
WHILE NOT fin DO
SET v_resultat = concat(v_resultat, v_cp, '-', v_nom_ville, ';');
FETCH curseur_villes INTO v_cp, v_nom_ville;
END WHILE;
SET as_resultat = v_resultat;
CLOSE curseur_villes;
END $$
DELIMITER;
<?php
$cn = new PDO("mysql:host=localhost;dbname=cours", "root", "");
$cmd = $cn->prepare("CALL villesCurseurOut(@o_param)");
$cmd->execute();
$rs = $cn->query("SELECT @o_param");
echo "CPs : ", $rs->fetchColumn();
$cn = null;
?>
Pascal Buguet
Page 34
La procdure
DELIMITER $$
DROP PROCEDURE IF EXISTS cours.villesCpsNoms $$
CREATE PROCEDURE cours.villesCpsNoms ()
BEGIN
SELECT cp, nom_ville FROM villes;
END $$
DELIMITER;
<?php
$mysqli = new mysqli("localhost", "root", "", "cours");
if ($cmd = $mysqli->prepare("CALL villesCpsNoms()"))
{
$cmd->execute();
$cmd->bind_result($col1, $col2);
while($cmd->fetch())
{
printf("%s-%s<br />\n", $col1, $col2);
}
$cmd->close();
}
$mysqli->close();
?>
<?php
$cn = new PDO("mysql:host=localhost;dbname=cours", "root", "");
$rs = $cn->query("CALL villesCpsNoms()");
while($enr = $rs->fetch())
{
echo "<br />$enr[0]-$enr[1]";
}
$cn = null;
?>
Pascal Buguet
Page 35
Objectif
Syntaxe
Pascal Buguet
Page 36
Exemples
Gestion standard
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET codeErreur = 1;
Qui signifie : si une exception SQL est leve alors la variable codeErreur passe 1 et le
script continue d'tre excut.
Gestion personnalise
En premier lieu cration d'une condition personnalise nomme CLE_DUPLIQUEE
correspondant l'erreur SQL 23000;
En second lieu affectation de la valeur 1 codeErreur et ordre de continuer l'excution du
script.
DECLARE CLE_DUPLIQUEE CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR CLE_DUPLIQUEE SET codeErreur = 1;
SQLSTATE (ANSI)
'00000'
'23000'
'23000'
'42S02'
'23000'
'21S01'
'HY000'
Code MySQL
0
1062
1452
1146
1048
1136
Pascal Buguet
Page 37
DELIMITER $$
DROP PROCEDURE IF EXISTS villesInsertErreur $$
CREATE PROCEDURE villesInsertErreur(IN asCp CHAR(5), IN asVille VARCHAR(50), IN
asIdPays CHAR(3))
BEGIN
-- Cette procdure gre les erreurs d'insertions
-- Dclaration de la variable qui permettra l'aiguillage en cas d'erreur
DECLARE codeErreur INT;
-- Dclaration de l'action entreprendre en cas d'exception SQL
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET codeErreur = 1;
-- Initialisation du code d'erreur 0
SET codeErreur = 0;
-- Ordre SQL majeur
INSERT INTO villes(cp, nom_ville, id_pays) VALUES(asCp, asVille, asIdPays);
-- Tests sur le code d'erreur et insertion dans la table des erreurs en
consquence
IF codeErreur = 0 THEN
INSERT INTO erreurs(texte, heure) VALUES('Insertion ville 0K', now());
END IF;
IF codeErreur = 1 THEN
INSERT INTO erreurs(texte, heure) VALUES('Insertion ville KO - Problme de Cl
primaire', now());
END IF;
END $$
DELIMITER;
Pascal Buguet
Page 38
La gestion est faite avec les codes MySQL, plus prcis, plutt qu'avec les codes ANSI
moins prcis.
DELIMITER $$
DROP PROCEDURE IF EXISTS villesInsertErreur $$
CREATE PROCEDURE villesInsertErreur(IN asCp CHAR(5), IN asVille VARCHAR(50), IN
asIdPays CHAR(3))
BEGIN
-- Cette procdure gre les erreurs d'insertions
-- Dclaration de la variable qui permettra l'aiguillage en cas d'erreur
DECLARE codeErreur INT;
-- Dclaration des erreurs
-DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET codeErreur = 1; -- Duplicate
Key
-DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET codeErreur = 2; -- Table
inexistante
DECLARE CONTINUE HANDLER FOR 1062 SET codeErreur = 1; -- Duplicate Key
DECLARE CONTINUE HANDLER FOR 1146 SET codeErreur = 2; -- Table inexistante
-- Initialisation du code d'erreur 0
SET codeErreur = 0;
-- Ordre SQL majeur
INSERT INTO ville(cp, nom_ville, id_pays) VALUES(asCp, asVille, asIdPays);
-- Tests sur le code d'erreur et insertion dans la table des erreurs en
consquence
IF codeErreur = 0 THEN
INSERT INTO erreurs(texte, heure) VALUES('0K', NOW());
END IF;
IF codeErreur = 1 THEN
INSERT INTO erreurs(texte, heure) VALUES('Insertion ville KO - Problme de
Cl primaire', NOW());
END IF;
IF codeErreur = 2 THEN
INSERT INTO erreurs(texte, heure) VALUES('Insertion ville KO - Problme de
Table inexistante', NOW());
END IF;
END $$
DELIMITER;
Exercice
Pascal Buguet
Page 39
Corrig
DELIMITER $$
DROP PROCEDURE IF EXISTS villesInsertErreur $$
CREATE PROCEDURE villesInsertErreur(IN asCp CHAR(5), IN asVille VARCHAR(50), IN
asIdPays CHAR(3))
BEGIN
-- Cette procdure gre les erreurs d'insertions
-- Dclaration de la variable qui permettra l'aiguillage en cas d'erreur
DECLARE codeErreur INT;
-- Dclaration des erreurs
-DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET codeErreur = 1; -- Duplicate
Key
-DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET codeErreur = 2; -- Table
inexistante
DECLARE
DECLARE
DECLARE
DECLARE
CONTINUE
CONTINUE
CONTINUE
CONTINUE
HANDLER
HANDLER
HANDLER
HANDLER
FOR
FOR
FOR
FOR
1062
1146
1452
1048
SET
SET
SET
SET
codeErreur
codeErreur
codeErreur
codeErreur
=
=
=
=
1;
2;
3;
4;
-----
Duplicate Key
Table inexistante
FK inexistante
Colonne NOT NULL
Pascal Buguet
Page 40
CHAPITRE 2
LES TRIGGERS
2.1 SYNTAXES
Pour crer un trigger
Notes :
BEGIN et END sont ncessaires si plusieurs instructions sont requises.
La clause obligatoire (!!!) FOR EACH ROW prcise que l'action SQL sera dclenche pour
chaque vnement (Ce n'est pas le cas avec Oracle).
Les rfrences OLD.nom_de_colonne et NEW.nom_de_colonne permettent d'accder aux
valeurs des colonnes de la table. OLD pour les triggers DELETE et UPDATE et NEW pour
les triggers INSERT et UPDATE.
Action / Variable
INSERT
Pascal Buguet
OLD
NEW
X
Page 41
X
X
Pascal Buguet
Page 42
Pascal Buguet
Page 43
Objectif
Cration du trigger
Test du trigger
Pascal Buguet
Page 44
Pascal Buguet
Page 45
Un traitement conditionnel
IF condition THEN
Action;
END IF;
L'objectif est de limiter le prix d'un produit 150 euros lors d'une augmentation de prix.
Le trigger
DELIMITER //
CREATE TRIGGER produits_avant_update
BEFORE UPDATE
ON produits
FOR EACH ROW
BEGIN
IF NEW.prix > 150 THEN
SET NEW.prix = 150;
END IF;
END //
DELIMITER;
La commande SQL de mise jour des prix.
UPDATE produits SET prix = prix * 1.1;
Pour vrifier
SELECT * FROM produits;
Pascal Buguet
Page 46
CHAPITRE 3
ANNEXE : BD UTILISEE
Pascal Buguet
Page 47
Pascal Buguet
Page 48
Pascal Buguet
Page 49