MySQL/Procédures stockées
MySQL peut enregistrer des requêtes pour les rappeler comme les fonctions d'un programme. Elles peuvent intégrer des contrôles de flux, des boucles et des curseurs. Il en existe trois sortes :
Les futures versions de MySQL pourraient même stocker des procédures écrites dans d'autres langages que SQL.
Délimiteur.
MySQL utilise un caractère comme délimiteur pour séparer ses requêtes, par défaut ';'. Quand on crée des procédures stockées avec plusieurs requêtes, on en crée en fait une seule : codice_2 de la procédure. Toutefois, si elles sont séparées par ';', il faut demander à MySQL de les ignorer pour estimer la fin du codice_2, puis remettre ";" à la fin.
Dans l'exemple suivant, '|' joue ce rôle :
 delimiter |
 CREATE ...
 delimiter ;
Procédures stockées.
Il en existe deux types :
Lister toutes les procédures stockées et fonctions.
SELECT db, name FROM mysql.proc;
Gestion des PROCEDURE et FUNCTION.
CREATE FUNCTION.
Création de fonction. Exemple sans paramètre :
CREATE FUNCTION `HelloWorld`() RETURNS VARCHAR(20) RETURN 'Hello World!';
Pour la rendre plus lisible, on peut faire exactement la même chose sur plusieurs lignes, mais comme elle peut contenir plusieurs instructions, cela nécessite d'indiquer le début et la fin de la procédure avec codice_8 et codice_9 : 
DELIMITER $$
CREATE FUNCTION `HelloWorld`()
RETURNS VARCHAR(20)
BEGIN
 RETURN 'Hello World!';
END $$
DELIMITER ;
SELECT HelloWorld();
Avec paramètre :
DELIMITER $$
CREATE FUNCTION `HelloWorld2`(_nom VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
 RETURN CONCAT('Hello World ', _nom, '!');
END $$
DELIMITER ;
SELECT HelloWorld2('monsieur');
CREATE PROCEDURE.
Création de procédure stockée :
CREATE PROCEDURE `Module1` ( ) OPTIMIZE TABLE wiki1_page;
CALL Module1();
Pour affiner les permissions, la création ci-dessous est liée à un compte :
 CREATE DEFINER = `root`@`localhost` PROCEDURE `Module2` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER OPTIMIZE TABLE wiki1_page;
Toutefois, s'il est supprimé elle ne fonctionne plus. Pour éviter cela, on peut la rattacher aux comptes qui vont l'exécuter :
CREATE DEFINER = `root`@`localhost` PROCEDURE `Module3`
SQL SECURITY INVOKER
BEGIN
 OPTIMIZE TABLE wiki1_page;
END;
CALL.
Invocation :
 CALL `Module1` ();
DROP PROCEDURE.
Suppression :
 DROP PROCEDURE `Module1` ;
Modification.
On est obligé de supprimer et de recréer le module :
 DROP PROCEDURE `Module1` ;
 CREATE DEFINER = `root`@`localhost` PROCEDURE `Module1` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
 BEGIN
 OPTIMIZE TABLE wiki1_page;
 OPTIMIZE TABLE wiki1_user;
 END 
Métadonnées des PROCEDURE et FUNCTION.
SHOW FUNCTION / PROCEDURE STATUS.
 SHOW PROCEDURE STATUS;
SHOW CREATE FUNCTION / PROCEDURE.
Pour obtenir la requête SQL qui avait créé la procédure :
 SHOW CREATE PROCEDURE Module1;
INFORMATION_SCHEMA.ROUTINES.
La base virtuelle INFORMATION_SCHEMA a une table `ROUTINES` avec les informations des procédures et fonctions.
INFORMATION_SCHEMA.PARAMETERS.
Cette table contient toutes les valeurs des fonctions stockées.
Déclencheurs.
Gestion des TRIGGER.
Disponibles depuis MySQL 5.0.2, ils fonctionnent sur les tables persistantes, mais pas les temporaires.
CREATE TRIGGER.
 CREATE TRIGGER `effacer_ancien` AFTER INSERT ON `wiki1_page`
 FOR EACH ROW
 DELETE FROM `wiki1_page` ORDER BY `page_id` ASC LIMIT 1
Cet exemple est une requête codice_10 appelée `effacer_ancien`, qui se lance après qu'un nouvel enregistrement soit inséré dans la table. Si un INSERT ajoute plusieurs lignes à une table, le déclencheur est appelé plusieurs fois.
Les conditions de déclenchement des triggers doivent être des commandes LMD basiques :
Un cas particulier est codice_18. Si codice_11 est exécuté, codice_20 ou codice_21 sont exécutés. Si codice_17 est exécuté à la place de codice_11, l'ordre des évènements est le suivant : codice_24.
Le déclencheur peut aussi s'appliquer à une table en particulier :
 ... ON `base1`.`table1` ...
Les noms des triggers doivent être unique pour chaque base.
Contrairement au standard SQL, tous les déclencheurs sont exécutés codice_25, et non pour chaque commande.
Une procédure stockée doit être spécifiée entre les mots codice_8 et codice_9 sauf s'il ne contient qu'une seule commande. Le SQL dynamique ne peut pas y être utilisé (codice_28) ; Une autre procédure stockée peut être appelée à la place.
Il est posible d'accéder à l'ancienne valeur d'un champ (avant l'exécution de la procédure) et à la nouvelle valeur :
 CREATE TRIGGER `use_values` AFTER INSERT ON `example_tab`
 FOR EACH ROW BEGIN
 UPDATE `changelog` SET `old_value`=OLD.`field1`, `new_value`=NEW.`field1` WHERE `backup_tab`.`id`=`example_tab`.`id`
 END
DROP TRIGGER.
Pour supprimer un déclencheur :
 DROP TRIGGER `trigger1`
-- ou
 DROP TRIGGER `base1`.`trigger1`
-- ou
 DROP TRIGGER IF EXISTS `trigger1`
Pour modifier un trigger, il faut le supprimer puis le recréer.
Métadonnées des TRIGGER.
SHOW CREATE TRIGGER.
Disponible depuis MySQL 5.1. Affiche la commande pour recréer un déclencheur nommé :
SHOW CREATE TRIGGER effacer_ancien;
SHOW TRIGGERS.
Pour obtenir la liste des triggers de la base courante :
 SHOW TRIGGERS;
Pour obtenir la liste des triggers d'une autre base :
 SHOW TRIGGERS IN `base2`
-- ou
 SHOW TRIGGERS FROM `base2`
D'autres filtres sont possibles :
 SHOW TRIGGERS WHERE table='wiki1_page'
Les colonnes du déclencheur sont :
INFORMATION_SCHEMA.TRIGGERS.
La base virtuelle INFORMATION_SCHEMA a une table `TRIGGERS` avec les colonnes suivantes :
Évènements.
Les évènements sont aussi appelé en anglais ou . Ils sont planifiés pour s'exécuter à un moment donné, date ou intervalle de temps. Ils sont similaire aux UNIX.
Quand un évènement est lancé, il doit être complètement exécuté. S'il est réactivé avant la fin de son exécution, une nouvelle instance du même évènement est créée. Donc il est conseillé d'utiliser codice_31 pour éviter qu'ils interfèrent entre eux.
Le planificateur des évènements est un thread en permanence en exécution, afin d'être en mesure de lancer les évènements à tout moment. Il peut toutefois être désactivé en lançant MySQL avec ces options :
 mysqld --event-scheduler=DISABLED
Ou bien en ajoutant une ligne dans le fichier de configuration (my.cnf) :
 event_scheduler=DISABLED
Ou encore en cours d'utilisation :
 SELECT event_scheduler -- valeurs : ON / OFF / DISABLED
 SET GLOBAL event_scheduler = ON
 SET GLOBAL event_scheduler = OFF
Quand il est lancé, on peut vérifier son status avec codice_32. Son utilisateur est 'event_scheduler'. Quand il est en sommeil, `State` est à 'Waiting for next activation'.
Gestion des EVENT.
Les commandes sont codice_33.
CREATE EVENT.
Pour un évènement à exécuter le lendemain :
 CREATE EVENT `évènement1`
 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
 DO
 INSERT INTO `wiki1`.`news` (`title`, `text`) VALUES ('Example!', 'This is not a real news')
Son nom est obligatoire et doit être précisé après codice_34.
Pour créer une tâche à exécuter une seule fois, utiliser codice_35. Pour ne pas spécifier la date et l'heure de manière absolue, mais relativement après un intervalle, utiliser codice_36.
Une tâche récurrente s'obtient avec codice_37 :
 CREATE EVENT `évènement2`
 ON SCHEDULE EVERY 2 DAY
 DO
 OPTIMIZE TABLE `wiki1`.`news`
On peut aussi spécifier la date et l'heure du début et/ou de la fin. La tâche sera exécutée à intervalle régulier entre ces dates :
 CREATE EVENT `évènement2`
 ON SCHEDULE EVERY INTERVAL 1 DAY
 DO
 OPTIMIZE TABLE `wiki1`.`news`
 STARTS CURRENT_TIMESTAMP + 1 MONTH
 ENDS CURRENT_TIMESTAMP + 3 MONTH
Les unités autorisées sont :
 YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND
La clause codice_38 spécifie la commande à exécuter.
Si la tâche est composée par plus d'une commande, utiliser codice_39 :
 delimiter |
 CREATE EVENT `évènement3`
 ON SCHEDULE
 EVERY 1 DAY
 DO
 BEGIN
 DELETE FROM `logs`.`user` WHERE `deletion_time` < CURRENT_TIMESTAMP - 1 YEAR;
 DELETE FROM `logs`.`messages` WHERE `deletion_time` < CURRENT_TIMESTAMP - 1 YEAR;
 UPDATE `logs`.`activity` SET `last_cleanup` = CURRENT_TIMESTAMP;
 END |
 delimiter ;
Si un codice_40 du même nom existe déjà, le serveur renvoie une erreur. On peut l'éviter avec codice_41 :
 CREATE EVENT `évènement2`
 IF NOT EXISTS
 ON SCHEDULE EVERY 2 DAY
 DO
 OPTIMIZE TABLE `wiki1`.`news`
Après expiration de l'évènement, MySQL le supprimer par défaut. Pour éviter cela afin de pouvoir le réutiliser son code ultérieurement, utiliser codice_42 :
 CREATE EVENT `évènement2`
 ON SCHEDULE EVERY 2 DAY
 ON COMPLETION PRESERVE
 DO
 OPTIMIZE TABLE `wiki1`.`news`
On peut aussi dire explicitement à MySQL de le supprimer :
 CREATE EVENT `évènement2`
 ON SCHEDULE EVERY 2 DAY
 ON COMPLETION NOT PRESERVE
 DO
 OPTIMIZE TABLE `wiki1`.`news`
En précisant une date de lancement antérieure, l'évènement expire immédiatement après sa création, c'est pourquoi le serveur prévient avec un warning 1588, normalement.
Pour préciser si un évènement est activé lors de sa création, les mots sont codice_43 (ce dernier ne se réplique pas sur les bases de données esclaves). Par défaut, il est activé :
 CREATE EVENT `évènement2`
 ON SCHEDULE EVERY 2 DAY
 ON COMPLETION NOT PRESERVE
 DISABLE
 DO
 OPTIMIZE TABLE `wiki1`.`news`
Pour le modifier : codice_44.
On peut aussi commenter l'évènement dans une limite de 64 caractères :
 CREATE EVENT `évènement2`
 ON SCHEDULE EVERY 2 DAY
 ON COMPLETION NOT PRESERVE
 DISABLE
 COMMENT 'let\'s optimize some tables!'
 DO
 OPTIMIZE TABLE `wiki1`.`news`
Par ailleurs, on peut modifier l'utilisateur de l'évènement pour obtenir d'autres permissions. Par exemple depuis celui voulu avec codice_45 :
 CREATE DEFINER = CURRENT_USER
 EVENT `évènement2`
 ON SCHEDULE EVERY 2 DAY
 DO
 OPTIMIZE TABLE `wiki1`.`news`
Spécifier un autre utilisateur nécessite les droits root :
 CREATE DEFINER = 'allen@localhost'
 EVENT `évènement2`
 ON SCHEDULE EVERY 2 DAY
 DO
 OPTIMIZE TABLE `wiki1`.`news`
ALTER EVENT.
Renommage d'un évènement :
 CREATE EVENT `évènement2`
 ON SCHEDULE EVERY 2 DAY
 ON COMPLETION NOT PRESERVE
 RENAME TO `évènement3`
 DISABLE
 COMMENT 'let\'s optimize some tables!'
 DO
 OPTIMIZE TABLE `wiki1`.`news`
On peut aussi ne définir que la clause à modifier :
 CREATE EVENT `évènement2` ENABLE;
DROP EVENT.
Avec les permissions sur l'évènement à supprimer :
 DROP EVENT `évènement3`
S'il n'existe pas l'erreur 1517 survient. Pour l'éviter :
 DROP EVENT IF EXISTS `évènement3`
Métadonnées des EVENT.
SHOW CREATE EVENT.
Cette commande retourne la commande codice_34 utilisée pour créer le trigger, et sur les paramètres l'impactant.
 SHOW CREATE EVENT évènement2
Les colonnes du résultat sont :
SHOW EVENTS.
Pour afficher tous les évènements de la base courante :
 SHOW EVENTS
Pour une base en particulier :
 SHOW EVENTS FROM `wiki1`
-- Ou
 SHOW EVENTS IN `wiki1`
Autres filtres :
 SHOW EVENTS LIKE 'év%'
 SHOW EVENTS WHERE definer LIKE 'admin@%'
Types de résultat :
INFORMATION_SCHEMA.EVENTS.
La base virtuelle INFORMATION_SCHEMA contient une table `EVENTS` depuis MySQL 5.1. Voici ses colonnes :
Extensions au standard SQL.
Flow control.
Les mots clés sont : codice_52. Ils ne sont utilisables que dans les procédures stockées.
Loops.
WHILE.
DELIMITER $$
CREATE PROCEDURE compteur()
 BEGIN
 DECLARE x INT;
 SET x = 1;
 WHILE x <= 5 DO
 SET x = x + 1;
 END WHILE;
 SELECT x; -- 6
 END$$
DELIMITER ;
LOOP.
DELIMITER $$
CREATE PROCEDURE compteur2()
 BEGIN
 DECLARE x INT;
 SET x = 1;
 boucle1: LOOP
 SET x = x + 1;
 IF x > 5 THEN
 LEAVE boucle1;
 END IF;
 END LOOP boucle1;
 SELECT x; -- 6
 END$$
DELIMITER ;
REPEAT.
DELIMITER $$
CREATE PROCEDURE compteur3()
 BEGIN
 DECLARE x INT;
 SET x = 1;
 REPEAT
 SET x = x + 1; UNTIL x > 5
 END REPEAT;
 SELECT x; -- 6
 END$$
DELIMITER ;
Curseurs.
Les permettent de traiter chaque ligne différemment, mais cela ralentit considérablement les requêtes.
DELIMITER $$
CREATE PROCEDURE curseur1()
BEGIN
 DECLARE resultat varchar(100) DEFAULT "";
 DECLARE c1 CURSOR FOR
 SELECT page_title
 FROM wiki1.wiki1_page
 WHERE page_namespace = 0;
 OPEN c1;
 FETCH c1 INTO resultat;
 CLOSE c1;
 SELECT resultat;
END;$$
DELIMITER ;
Ils doivent être déclaré puis ouvert avant le début de la boucle qui traite chaque enregistrement. Pour connaitre la fin de la table parcourue, il faut crée un "handler" après le curseur :
-- Concatène toutes les valeurs d'une colonne sur une ligne 
DELIMITER $$
CREATE PROCEDURE curseur2()
BEGIN
 DECLARE resultat varchar(100) DEFAULT "";
 DECLARE total text DEFAULT "";
 DECLARE fin BOOLEAN DEFAULT 0;
 DECLARE c2 CURSOR FOR
 SELECT page_title
 FROM wiki1.wiki1_page
 WHERE page_namespace = 0;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = TRUE;
 OPEN c2;
 REPEAT
 FETCH c2 INTO resultat;
 set total = concat(total, resultat);
 UNTIL fin END REPEAT;
 CLOSE c2;
 SELECT total; -- AccueilMySQLPHPPHP
END;$$
DELIMITER ;
Gestion des erreurs.
La déclaration d'un "" permet de spécifier un traitement en cas d'erreur :
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
De plus, le type d'erreur peut être précisé :
DECLARE CONTINUE HANDLER FOR SQLSTATE [VALEUR]
DECLARE CONTINUE HANDLER FOR SQLWARNING
DECLARE CONTINUE HANDLER FOR NOT FOUND
mysql_affected_rows().
Cette fonction renvoie le nombre de lignes impactées par la requête précédente.