MySQL/Procédures stockées

Un livre de Wikilivres.
Aller à : navigation, rechercher


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 :

  • Déclencheurs (ou triggers) : programmes qui se déclenchent avant ou après un évènement impliquant une table (DELETE, INSERT, UPDATE) ;
  • Évènements : programmes exécutés à une certaine date, régulièrement ;
  • Procédures stockées : programmes invocable par la commande SQL CALL.

Les futures versions de MySQL pourraient même stocker des procédures écrites dans d'autres langages que SQL.

Déclencheurs[modifier | modifier le wikicode]

Gestion des TRIGGER[modifier | modifier le wikicode]

Disponibles depuis MySQL 5.0.2, ils fonctionnent sur les tables persistantes, mais pas les temporaires.

CREATE TRIGGER[modifier | modifier le wikicode]

 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 DELETE 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 :

  • INSERT, dont LOAD DATA et REPLACE ;
  • DELETE, incluant REPLACE, mais pas TRUNCATE ;
  • UPDATE

Un cas particulier est INSERT ... ON DUPLICATE KEY UPDATE. Si INSERT est exécuté, BEFORE INSERT ou AFTER INSERT sont exécutés. Si UPDATE est exécuté à la place de INSERT, l'ordre des évènements est le suivant : BEFORE INSERT, BEFORE UPDATE, AFTER UPDATE.

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 FOR EACH ROW, et non pour chaque commande.

Une procédure stockée doit être spécifiée entre les mots BEGIN et END sauf s'il ne contient qu'une seule commande. Le SQL dynamique ne peut pas y être utilisé (PREPARE) ; 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[modifier | modifier le wikicode]

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[modifier | modifier le wikicode]

SHOW CREATE TRIGGER[modifier | modifier le wikicode]

Disponible depuis MySQL 5.1. Affiche la commande pour recréer un déclencheur nommé :

SHOW CREATE TRIGGER effacer_ancien;
  • Trigger : Nom du déclencheur.
  • sql_mode : valeur du SQL_MODE au moment de l'exécution.
  • SQL Original Statement
  • character_set_client
  • collation_connection
  • Database Collation

SHOW TRIGGERS[modifier | modifier le wikicode]

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'
 il est impossible d'utiliser LIKE et WHERE ensemble.

Les colonnes du déclencheur sont :

  • Trigger : nom
  • Event : commande SQL qui le déclenche
  • Table : table associée
  • Statement : requête exécutée
  • Timing : BEFORE ou AFTER
  • Created : toujours NULL
  • sql_mode : SQL_MODE définit lors de sa création
  • Definer : créateur
  • character_set_client : valeur de la variable `character_set_client` lors de la création
  • collation_connection : valeur de la variable `collation_connection` lors de la création
  • Database Collation : COLLATION utilisée par la base du trigger.

INFORMATION_SCHEMA.TRIGGERS[modifier | modifier le wikicode]

La base virtuelle INFORMATION_SCHEMA a une table `TRIGGERS` avec les colonnes suivantes :

  • TRIGGER_CATALOG : catalogue contenant le trigger ;
  • TRIGGER_SCHEMA : SCHEMA (DATABASE) contenant le trigger ;
  • TRIGGER_NAME : nom du trigger ;
  • EVENT_MANIPULATION : INSERT, UPDATE ou DELETE ;
  • EVENT_OBJECT_CATALOG : pas encore implémenté ;
  • EVENT_OBJECT_SCHEMA : schéma contenant la table associée au trigger ;
  • EVENT_OBJECT_NAME : nom de la table associée au trigger ;
  • ACTION_ORDER : pas encore implémenté ;
  • ACTION_CONDITION : pas encore implémenté ;
  • ACTION_STATEMENT : commande exécutée lors de l'activation du trigger ;
  • ACTION_ORIENTATION : pas encore implémenté ;
  • ACTION_TIMING : BEFORE ou AFTER ;
  • ACTION_REFERENCE_OLD_TABLE : pas encore implémenté ;
  • ACTION_REFERENCE_NEW_TABLE : pas encore implémenté ;
  • ACTION_REFERENCE_OLD_ROW : pas encore implémenté ;
  • ACTION_REFERENCE_NEW_ROW : pas encore implémenté ;
  • CREATED : date et heure de création (pas encore implémenté) ;
  • SQL_MODE : SQL_MODE valide pour l'exécution du trigger ;
  • DEFINER : créateur du trigger, sous la forme 'utilisateur@hôte' ;
  • CHARACTER_SET_CLIENT : valeur de la variable `character_set_client` lors de la création ;
  • COLLATION_CONNECTION : valeur de la variable `collation_connection` lors de la création ;
  • DATABASE_COLLATION : COLLATION utilisée par la base.

Évènements[modifier | modifier le wikicode]

Les évènements sont aussi appelé en anglais Scheduled Events ou Temporal Triggers. Ils sont planifiés pour s'exécuter à un moment donné, date ou intervalle de temps. Ils sont similaire aux crontab 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 LOCK 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 SHOW PROCESSLIST. Son utilisateur est 'event_scheduler'. Quand il est en sommeil, `State` est à 'Waiting for next activation'.

Gestion des EVENT[modifier | modifier le wikicode]

Les commandes sont CREATE EVENT, ALTER EVENT, DROP EVENT.

CREATE EVENT[modifier | modifier le wikicode]

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 CREATE EVENT.

Pour créer une tâche à exécuter une seule fois, utiliser AT. Pour ne pas spécifier la date et l'heure de manière absolue, mais relativement après un intervalle, utiliser AT CURRENT_TIMESTAMP + INTERVAL ....

Une tâche récurrente s'obtient avec EVERY :

 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 DO spécifie la commande à exécuter.

Si la tâche est composée par plus d'une commande, utiliser BEGIN ... END :

  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 EVENT du même nom existe déjà, le serveur renvoie une erreur. On peut l'éviter avec IF NOT EXISTS :

 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 ON COMPLETION :

 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 ENABLE, DISABLE, DISABLE ON SLAVES (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 : ALTER EVENT.

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 CURRENT_USER :

 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[modifier | modifier le wikicode]

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[modifier | modifier le wikicode]

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[modifier | modifier le wikicode]

SHOW CREATE EVENT[modifier | modifier le wikicode]

Cette commande retourne la commande CREATE EVENT 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 :

  • Event : nom
  • sql_mode : mode SQL utilisé (ex : NO_ENGINE_SUBSTITUTION)
  • time_zone : fuseau horaire du créateur (ex : SYSTEM)
  • Create Event : code qui a généré l'évènement
  • character_set_client (ex : utf8)
  • collation_connection (ex : utf8_general_ci)
  • Database Collation (ex : latin1_swedish_ci)

SHOW EVENTS[modifier | modifier le wikicode]

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 :

  • Db : nom de la base ;
  • Name : nom de l'évènement ;
  • Definer : créateur (user@host) ;
  • Time zone : fuseau horaire ;
  • Type : 'ONE TIME' ou 'RECURRING' selon la récurrence ;
  • Executed At : date de l'exécution, ou NULL pour les récursifs ;
  • Interval Value : nombre d'intervalle entre les exécutions, ou NULL pour les non récursifs ;
  • Interval Field : unités de mesure de l'intervalle (ex : 'SECOND'), ou NULL pour les non récursifs ;
  • Starts : date de première exécution, ou NULL pour les non récursifs ;
  • Ends : date de dernière exécution, ou NULL pour les non récursifs ;
  • Status : ENABLED, DISABLED, ou SLAVESIDE_DISABLED ;
  • Originator : identifiant du serveur créateur (0 pour le courant). Disponible depuis MySQL 5.1 ;
  • character_set_client
  • collation_connection
  • Database Collation

INFORMATION_SCHEMA.EVENTS[modifier | modifier le wikicode]

La base virtuelle INFORMATION_SCHEMA contient une table `EVENTS` depuis MySQL 5.1. Voici ses colonnes :

  • EVENT_CATALOG : toujours NULL (les CATALOG ne sont pas encore implémentés par MySQL) ;
  • EVENT_SCHEMA : nom de la base ;
  • EVENT_NAME : nom de l'évènement ;
  • DEFINER : créateur (user@host) ;
  • TIME_ZONE : fuseau horaire ;
  • EVENT_BODY : langage utilisé ;
  • EVENT_DEFINITION : routine à exécuter ;
  • EVENT_TYPE : 'ONE TIME' ou 'RECURRING' selon la récurrence ;
  • EXECUTE_AT : date de l'exécution, ou NULL pour les récursifs ;
  • INTERVAL_VALUE : nombre d'intervalle entre les exécutions, ou NULL pour les non récursifs ;
  • INTERVAL_FIELD : unités de mesure de l'intervalle (ex : 'SECOND'), ou NULL pour les non récursifs ;
  • SQL_MODE mode SQL ;
  • STARTS : date de première exécution, ou NULL pour les non récursifs ;
  • ENDS : date de dernière exécution, ou NULL pour les non récursifs ;
  • STATUS : ENABLED, DISABLED, ou SLAVESIDE_DISABLED ;
  • ON_COMPLETION : 'NOT PRESERVE' ou 'PRESERVE' ;
  • CREATED : date de création ;
  • LAST_ALTERED : date de dernière modification ;
  • LAST_EXECUTED : date de dernière exécution ;
  • EVENT_COMMENT : commentaires ;
  • ORIGINATOR : identifiant du serveur créateur (0 pour le courant). Disponible depuis MySQL 5.1 ;
  • character_set_client
  • collation_connection
  • Database Collation

Procédures stockées[modifier | modifier le wikicode]

L'ajout de procédure stockée sous phpMyAdmin nécessite de remplir tous les champs. Le code s'obtient en cliquant sur Exporter.

Les procédures stockées sont des modules SQL exécutables avec CALL.

Il en existe deux types :

  1. FUNCTION si elles retournent un résultat.
  2. PROCEDUREs si elles ne retournent rien après leur traitement.

Avantages[modifier | modifier le wikicode]

  • Elles réduisent le trafic du réseau car une seule commande permet de leur en faire exécuter plusieurs. Les appeler est donc plus rapide.
  • Ces modules peuvent être invoqués plusieurs fois depuis n'importe quel langage (PHP, Java...).
  • Elles conservent une logique entre les bases : le DBA peut les modifier sans toucher aux programmes qui les appellent.
  • Peut permettre aux utilisateurs qui n'ont pas accès à une table de récupérer ses données ou la modifier dans certaines circonstances.

Gestion des PROCEDURE et FUNCTION[modifier | modifier le wikicode]

CREATE PROCEDURE[modifier | modifier le wikicode]

Création de procédure stockée :

 CREATE DEFINER = `root`@`localhost` PROCEDURE `Module1` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER OPTIMIZE TABLE wiki1_page;

CALL[modifier | modifier le wikicode]

Invocation :

 CALL `Module1` ();

DROP PROCEDURE[modifier | modifier le wikicode]

Suppression :

 DROP PROCEDURE `Module1` ;

Modification[modifier | modifier le wikicode]

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[modifier | modifier le wikicode]

SHOW FUNCTION / PROCEDURE STATUS[modifier | modifier le wikicode]

 SHOW PROCEDURE STATUS;

SHOW CREATE FUNCTION / PROCEDURE[modifier | modifier le wikicode]

 SHOW CREATE PROCEDURE Module1;

INFORMATION_SCHEMA.ROUTINES[modifier | modifier le wikicode]

La base virtuelle INFORMATION_SCHEMA a une table `ROUTINES` avec les informations des procédures et fonctions.

INFORMATION_SCHEMA.PARAMETERS[modifier | modifier le wikicode]

Cette table contient toutes les valeurs des fonctions stockées.

Extensions au standard SQL[modifier | modifier le wikicode]

Délimiteur[modifier | modifier le wikicode]

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 : CREATE de la procédure. Toutefois, si elles sont séparées par ';', il faut demander à MySQL de les ignorer pour estimer la fin du CREATE.

Dans l'exemple suivant, '|' joue ce rôle :

 delimiter |
 CREATE EVENT évènement1
    ON SCHEDULE EVERY 1 DAY
    DO
      BEGIN
        TRUNCATE `wiki1`.`wiki1_page`;
        TRUNCATE `wiki1`.`wiki1_user`;
      END
 delimiter ;

Flow control[modifier | modifier le wikicode]

Les mots clés sont : IF, CASE, ITERATE, LEAVE LOOP, WHILE, REPEAT[1].

Loops[modifier | modifier le wikicode]

WHILE[modifier | modifier le wikicode]
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[modifier | modifier le wikicode]
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[modifier | modifier le wikicode]
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[modifier | modifier le wikicode]

Les curseurs 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[modifier | modifier le wikicode]

La déclaration d'un "handler" permet de spécifier un traitement en cas d'erreur[2] :

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()[modifier | modifier le wikicode]

Cette fonction renvoie le nombre de lignes impactées par la requête précédente[3].

Références[modifier | modifier le wikicode]

  1. http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html
  2. http://dev.mysql.com/doc/refman/5.7/en/declare-handler.html
  3. https://dev.mysql.com/doc/refman/5.7/en/mysql-affected-rows.html