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 :

Fonctions et procédures stockées : programmes invocables depuis les commandes SQL ;
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.

Fonctions

Regex

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 : 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, puis remettre ";" à la fin.

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

 delimiter |
 CREATE ...
 delimiter ;

Procédures stockées

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

Il en existe deux types :

FUNCTION si elles retournent un résultat. Elles sont appelées avec SELECT.
PROCEDURE si elles ne retournent rien après leur traitement. Elles sont exécutées avec CALL.

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 BEGIN et END :

DELIMITER $$
CREATE FUNCTION `HelloWorld`()
RETURNS VARCHAR(20)
BEGIN
    RETURN 'Hello World!';
END $$
DELIMITER ;

SELECT HelloWorld();
# Affiche "Hello World!'"

Avec paramètre :

DELIMITER $$
CREATE FUNCTION `HelloWorld2`(_nom VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
    RETURN CONCAT('Hello World ', _nom, '!');
END $$
DELIMITER ;

SELECT HelloWorld2('monsieur');
# Affiche "Hello World 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 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

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;

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

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

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

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

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

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

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

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

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

Avantages

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.

Extensions au standard SQL

Flow control

Les mots clés sont : IF, CASE, ITERATE, LEAVE LOOP, WHILE, REPEAT^[1]. 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 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

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()

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

Références

http://www.convert-in.com/mssql-to-mysql-stored-procedures.htm

[1] ttp://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html

[2] ttp://dev.mysql.com/doc/refman/5.7/en/declare-handler.html

[3] ttps://dev.mysql.com/doc/refman/5.7/en/mysql-affected-rows.html

[1]

[2]

[3]