# MySQL/Procédures stockées

* Introduction
* PhpMyAdmin
* Parcourir les bases de données
* Spécifier les noms
* LDD, LMD et LCD
* Variables utilisateurs
* Alias
* Types de données
* Manipulation de base
** mysqldump
* Manipulation de table
* Manipulation de données
* Requêtes
* NULL
* Opérateurs
* Fonctions
* Procédures stockées
* Regex
* Bases de données spatiales
* Importer et exporter
* Réplication
* Optimisation
* API
* Sécurité
* Mots réservés
* Débogage
* Introduction
* PhpMyAdmin
* Parcourir les bases de données
* Spécifier les noms
* LDD, LMD et LCD
* Variables utilisateurs
* Alias
* Types de données
* Manipulation de base
** mysqldump
* Manipulation de table
* Manipulation de données
* Requêtes
* NULL
* Opérateurs
* Fonctions
* Procédures stockées
* Regex
* Bases de données spatiales
* Importer et exporter
* Réplication
* Optimisation
* API
* Sécurité
* Mots réservés
* Débogage
[ Modifier le sommaire ]
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.
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 :

## Procédures stockées

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.

### Gestion des PROCEDURE et FUNCTION
#### CREATE FUNCTION

Création de fonction. Exemple sans paramètre :
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 :
Avec paramètre :

#### CREATE PROCEDURE

Création de procédure stockée :
Pour affiner les permissions, la création ci-dessous est liée à un compte :
Toutefois, s'il est supprimé elle ne fonctionne plus. Pour éviter cela, on peut la rattacher aux comptes qui vont l'exécuter :

#### CALL

Invocation :

#### DROP PROCEDURE

Suppression :

#### Modification

On est obligé de supprimer et de recréer le module :

### Métadonnées des PROCEDURE et FUNCTION
#### SHOW CREATE FUNCTION / PROCEDURE

Pour obtenir la requête SQL qui avait créé la procédure :

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

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

#### DROP TRIGGER

Pour supprimer un déclencheur :
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é :
* 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 :
Pour obtenir la liste des triggers d'une autre base :
D'autres filtres sont possibles :
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 :
Ou bien en ajoutant une ligne dans le fichier de configuration (my.cnf) :
Ou encore en cours d'utilisation :
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 :
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 :
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 :
Les unités autorisées sont :
La clause DO spécifie la commande à exécuter.
Si la tâche est composée par plus d'une commande, utiliser BEGIN ... END :
Si un EVENT du même nom existe déjà, le serveur renvoie une erreur. On peut l'éviter avec IF NOT EXISTS :
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 :
On peut aussi dire explicitement à MySQL de le supprimer :
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é :
Pour le modifier : ALTER EVENT.
On peut aussi commenter l'évènement dans une limite de 64 caractères :
Par ailleurs, on peut modifier l'utilisateur de l'évènement pour obtenir d'autres permissions. Par exemple depuis celui voulu avec CURRENT_USER :
Spécifier un autre utilisateur nécessite les droits root :

#### ALTER EVENT

Renommage d'un évènement :
On peut aussi ne définir que la clause à modifier :

#### DROP EVENT

Avec les permissions sur l'évènement à supprimer :
S'il n'existe pas l'erreur 1517 survient. Pour l'éviter :

### 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.
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 :
Pour une base en particulier :
Autres filtres :
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. Ils ne sont utilisables que dans les procédures stockées.

### Curseurs

Les curseurs permettent de traiter chaque ligne différemment, mais cela ralentit considérablement les requêtes.
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 :

### Gestion des erreurs

La déclaration d'un "handler" permet de spécifier un traitement en cas d'erreur :
De plus, le type d'erreur peut être précisé :

### mysql_affected_rows

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

## Références

* ↑ http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html
* ↑ http://dev.mysql.com/doc/refman/5.7/en/declare-handler.html
* ↑ https://dev.mysql.com/doc/refman/5.7/en/mysql-affected-rows.html
* http://www.convert-in.com/mssql-to-mysql-stored-procedures.htm