Routines stockées

On peut utiliser du code stocké dans la base à partir de la version 5.0 de MySQL.
Le code peut être sous forme de procédure, fonction ou trigger (code déclenché sur événement). On parle de manière générale de ‘procédure stockées’ dans tous les cas, par abus de langage ou pour faire court…

Pourquoi des procédures stockées

  1. Ce sont des procédures ! c’est à dire que le code est réutilisable, par un simple appel de la procédure et personnalisable en changeant les paramètres d’entrée
  2. elles sont stockées ! c’est à dire que votre programme est inclus dans la base de données, comme une donnée ! On peut donc facilement exporter les données avec les programmes, les sauvegarder ensemble, etc.
  3. elles sont performantes : le code SQL est ‘précompilé’ dans la base, donc son exécution sera plus rapide à priori
  4. elles sont plus sures (pas tout à fait encore en MySQL 5) car on peut gérer des droits au niveau de la procédure? C’est à dire qu’on peut autoriser un utilisateur à executre seulement un code très précis qui accède à certaines données, sans lui donner d’accès à des tables coplètes, ou à fortiori à toute une base.

Fonctions, procédures et paramètres

Une routine stockée peut avoir de manière optionnelle des paramètres d’entrée et / ou de sortie.

Une fonction est simplement une procédure qui retourne une valeur (grace au mot clé ‘RETURN’ )

La clause RETURNS sert à indiquer le type de retour de la fonction, et le corps de la fonction doit contenir une instruction RETURN value
Une fonction peut être vue comme une procédure avec un seul paramètre de sortie obligatoire.

La liste de paramètre entre parenthèses est obligatoire. S’il n’y a pas de paramètre, une liste vide sous la forme () doit être utilisée. Chaque paramètre est un paramètre de type IN par défaut. Pour spécifier un autre type, utilisez les mots OUT ou INOUT avant le nom du paramètre. Spécifier IN, OUT ou INOUT n’est valable que pour une PROCEDURE.

Syntaxe de création des procédures et fonctions

CREATE PROCEDURE sp_name ([parameter[,...]])[characteristic ...]
routine_body
CREATE FUNCTION sp_name ([parameter[,...]])[RETURNS type]
[characteristic ...] routine_body
paramètre :
  [ IN | OUT | INOUT ] param_name type
type :
  Any valid MySQL data type
characteristic:
  | SQL SECURITY {DEFINER | INVOKER}
  | COMMENT string
routine_body :
  Commande(s) SQL valide(s)

Les différentes clauses du CREATE

L’attribut SQL SECURITY peut être utilisé pour spécifier si la routine doit être exécutée avec les droits de l’utilisateur qui l’a créé ou avec ceux de celui qui appelle la fonction. La valeur par défaut est DEFINER. Cette fonctionnalité est nouvelle en SQL:2003.
MySQL n’utilise pas le droit GRANT EXECUTE. Pour le moment, si une procédure p1() utilise la table t1, l’appelant doit avoir les droits sur la table t1 afin que la procédure p1() puisse réussir.

La clause COMMENT est une extension MySQL, et peut servir à décrire la procédure stockée.
MySQL permet aux routines de contenir des commandes DDL, telle que CREATE et DROP, et des transactions SQL, comme COMMIT. Ce n’est pas obligatoire selon le standard et c’est donc une extension spécifique.

Le corps de la routine contiendra une ou plusieurs instrctions SQL, séparées par des ‘;’.

ATTENTION !! : pour lever toute ambiguité entre le ‘;’ qui marque la fin du SQL et son exécution et le ‘;’ qui sépare des instructions SQL dans une routine, il sera nécessaire de changer le délimiteur SQL, avec la commande ‘delimiter’.

Informations sur les routines stockées

Il existe 2 instructions pour avoir des informations sur les routines : SHOW CREATE…et SHOW STATUS :

  • SHOW CREATE FUNCTION / PROCEDURE
    Renvoie le texte d’une fonction stockée précédemment définie, qui a été créée à l’aide de l’instruction CREATE FUNCTION.
  • SHOW FUNCTION / PROCEDURE STATUS
    Renvoie les caractéristiques d’une procédure stockée précédemment définie, notamment son nom, son type, son créateur, sa date de création et sa date de modification.

Les infos du référentiel sur les routines sont disponible dans la table INFORMATION_SCHEMA.ROUTINES et dans la table mysql.PROC

Un exemple simple de fonction stockée MySQL

Supposons que l’on veuille une fonction de concaténation de chaines. On a 1 paramètre d’entrée (la chaine à concaténer) et une variable chaine retournée qui est la concaténation de l’entrée et d’une chaine fixe (« bonjour »).
On pourra utiliser la fonction standard de concaténation mysql ‘concat()’ pour créer notre propre fonction.

Le code est le suivant :

delimiter |
CREATE FUNCTION concatene (ch1 CHAR(25))
— le type du retour…
RETURNS CHAR(50)
— ici il n’y a pas d’instructions autres…
— mais le traitement est fait au moment du retour
RETURN concat(« Bonjour « , ch1);

pour voir comment s’exécute notre fonction on pourra, faire un SELECT …sans table :

SELECT concatene(« Didier Deléglise »)
==> Bonjour Didier Deléglise

Deux exemples simples de procédures stockées MySQL

Créons une procédure sans paramètre qui augmente inconditionnellement les salaires de 10% (bonne nouvelle !) :

mysql> create procedure augmente()
BEGIN
update emp set sal=sal*1.1;
END;
==> procédure créée avec succès
mysql> select ename , sal from emp where ename = ‘SMITH’;
==> SMITH 800

pour le moment nous n’avons fait que définir la procédure mais elle n’a pas été éxécutée. Il faut faire un ‘call’ pour effectuer le traitement de l aprocédure

mysql> call augmente()
==> SQL executée avec succès
mysql> select ename , sal from emp where ename = ‘SMITH’;
==> SMITH 880

L’exemple suivant est une procédure stockée simple, qui utilise un paramètre de sortie OUT. L’exemple utilise la commande delimiter du client mysql pour modifier le délimiteur de commande avant de définir la procédure. Cela permet au délimiteur ‘;‘ d’être utilisé dans le corps de la procédure, plutôt que d’être interprété par le client mysql.

mysql> delimiter |
mysql> CREATE PROCEDURE compte (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM emp;
END
|
mysql> show create procedure compte;
Procedure   sql_mode   	Create Procedure
compte      		CREATE DEFINER=`root`@`localhost`
    			PROCEDURE `compte`(OUT param1 INT)nBEGINrn
 		SELECT COUNT(*) INTO param1 FROM emp;rnEND
mysql> set @v=1;
mysql> call compte(@v);
mysql> select @v;
@v
--
14

Note : avec PHPMyAdmin, le changement de délimiteur se fait via l’interface graphique et par par une commande SQL.

Instructions « SQL » spécifiques pour les routines stockées

Hormis les ordres SQL, on peut utiliser un certain nombre d’instructions classiques de langage de programmation : des déclaration de variables, des conditionnelles, des boucles, des gestions de curseurs SQL (fetch).

Le tableau suivant décrit sommairement ces instructions :

CALL : Appelle une procédure stockée précédemment définie, qui a été créée à l’aide de l’instruction CREATE PROCEDURE.
BEGIN … END : Contient un groupe de plusieurs instructions en vue de leur exécution.
DECLARE : Sert à définir des variables locales, des conditions, des routines de gestion et des curseurs.
SET : sert à modifier les valeurs des variables locales et des variables serveur globales.
SELECT … INTO : Sert à stocker les colonnes indiquées directement dans des variables.
OPEN : Sert à ouvrir un curseur.
FETCH
Extrait la ligne suivante à l’aide du curseur spécifié et avance le curseur d’une ligne.
CLOSE : Sert à fermer un curseur ouvert.
IF : instruction conditionnelle IF-THEN-ELSE-END IF.
CASE … : construction conditionnelle d’une instruction CASE.
LOOP : Structure de boucle simple; la sortie se fait à l’aide de l’instruction LEAVE.
LEAVE : Sert à sortir des instructions IF, CASE, LOOP, REPEAT et WHILE.
ITERATE : Utilisée dans les boucles pour recommencer au début de la boucle.
REPEAT : Boucle avec le test conditionnel à la fin.
WHILE : Boucle avec le test conditionnel au début.
RETURNS : Renvoie une valeur d’une fonction stockée.

Exemple d’utilisation de variables :

CREATE PROCEDURE sp1 (x VARCHAR(2))
BEGIN
DECLARE nom VARCHAR(5) DEFAULT ‘toto’;
DECLARE y VARCHAR(5);
DECLARE z INT;

SELECT nom,no INTO y,z
FROM table1 WHERE nom = x;

END;

et utilisation d’une boucle simple pour calculer une factorielle :

CREATE function factorielle (x INT)
returns bigint
BEGIN
declare r bigint;
set r=1;
WHILE x >1 DO
SET r = r*x;
set x = x-1;
END WHILE ;
return r;
END

Infos complémentaires

D’autres exemples simples de proc avec paramètre ici :
http://www.databasejournal.com/features/mysql/article.php/3525581

remarque:on peut aussi créer des TRIGGERs voir : http://dev.mysql.com/doc/refman/5.0/fr/triggers.html

Toutes les informations sur les procédures stockées sont dans le manuel ICI :

http://dev.mysql.com/doc/refman/5.0/fr/stored-procedures.html