Je vais tenter une petite introduction aux procédures stockées, bien que certains aspects restent encore un peu flous pour moi.

• Avantages et inconvénients
• Les outils
• Appels et prototype
• Les paramètres
• Les curseurs
• Dynamic SQL

Avantages et inconvénients

Il y a des avantages, il y a des inconvénients, comme dans toute technologie.

Parmi les avantages, je citerai le fait de pouvoir exécuter de multiples processus en une seule requête côté serveur, pouvoir passer des paramètres de type IN (entrée), OUT (sortie) et INOUT (entrée et sortie) tout en pouvant retourner un resultset, simplifier le code côté langage de script, ou encore sécuriser des transactions complexes avec commit et rollback.

Dans les inconvénients, il n'est pas possible d'exécuter certaines actions uniquement en SQL, et il n'est pas nécessaire de forcément les utiliser pour arriver à ses fins.

Vous trouverez ici un résumé des pour et des contre, et de la logique à adopter pour tel ou tel type de structure de données et d'application.

Les outils

Tout d'abord, je ne pourrais que conseiller de télécharger les outils MySQL contenant "MySQL Administrator" et "MySQL Query Browser". Ces 2 outils sont efficaces et très utiles pour travailler sur MySQL5.

Une fois connecté au MySQL Administrator, vous pouvez sélectionner votre base de données et passer par l'onglet Stored Procedure pour ajouter, éditer et supprimer vos procédures stockées.

Appels et prototype

L'appel à une procédure stockée se fait via la méthode CALL :

CALL procedure_name()

Une procédure stockée doit être dans un prototype du type :

CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_name`()
BEGIN
 
END

Les paramètres

Il existe ensuite 3 types de paramètre possible à passer à une procédure :

• IN : paramètre entrant, valorisé avant l'appel, passé à l'appel de méthode et utilisé dans la procédure stockée
• OUT : paramètre sortant, valorisé durant le passage dans la procédure stockée et renvoyé à l'utilisateur
• INOUT : paramètre entrant ET sortant, valorisé avant l'appel, passé à l'appel de méthode, modifié dans la procédure stockée et renvoyé à l'utilisateur

Selon vos besoins, vous pouvez utiliser ces 3 types de paramètres pour passer ou récupérer des données via votre procédure stockée.

Exemple avec IN (récupération des infos de tables de la base de données @name dans la base de données "information_schema":

CREATE DEFINER=`root`@`localhost` PROCEDURE `testIN`( IN name VARCHAR(20))
BEGIN
 
select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = name;
 
END

Et l'appel de procédure :

CALL testIN( 'information_schema' );

Enfin la sortie :

'CHARACTER_SETS'
'COLLATIONS'
'COLLATION_CHARACTER_SET_APPLICABILITY'
'COLUMNS'
'COLUMN_PRIVILEGES'
'KEY_COLUMN_USAGE'
'PROFILING'
'ROUTINES'
'SCHEMATA'
'SCHEMA_PRIVILEGES'
'STATISTICS'
'TABLES'
'TABLE_CONSTRAINTS'
'TABLE_PRIVILEGES'
'TRIGGERS'
'USER_PRIVILEGES'
'VIEWS'

Ici, exemple tout simple, je passe une valeur de type varchar qui est utilisée dans la formation de la requête SQL.

Exemple avec OUT (affectation d'une valeur récupérée à partir d'une requête exécutée dans une procédure stockée)
Reprenons le code précédent pour rajouter un paramètre en sortie :

CREATE DEFINER=`root`@`localhost` PROCEDURE `testOUT`( IN name VARCHAR(20), OUT length INT)
BEGIN
 
select count(*) into length from information_schema.TABLES where TABLE_SCHEMA = name;
 
END

Et bien entendu l'appel :

CALL testOUT( 'information_schema', @length );
select @length;

et enfin la sortie :

no resultset returned 
'17'

Cette fois-ci, le principe est le même avec le paramètre entrant, et je récupère le nombre de lignes que je place dans le paramètre "length" (select count(*) into length). C'est le mot clé "into" qui me permet d'injecter la valeur dans le paramètre.
Par contre, petit problème ici : avec MySQL query browser, impossible d'obtenir la valeur correcte de "length" en exécutant les 2 requêtes de suite. Il faut passer par le mode "transaction", débuter une transaction, exécuter une requête après l'autre et de cette manière les résultats sont gardés en mémoire, et je vois que le paramètre "length" est égal à 17. Je peux stopper la transaction.

Enfin, un exemple avec INOUT (modification d'une valeur passée en paramètre par une valeur récupérée à partir d'une requête exécutée dans une procédure stockée)
Reprenons le code précédent pour modifier le paramètre OUT en paramètre INOUT :

CREATE DEFINER=`root`@`localhost` PROCEDURE `testINOUT`( IN name VARCHAR(20), INOUT length INT)
BEGIN
 
select (count(*) + length) into length from information_schema.TABLES where TABLE_SCHEMA = name;
 
END

Et bien entendu l'appel :

SET @length = 100;
select @length;
CALL testINOUT( 'information_schema', @length );
select @length;

et enfin la sortie :

Query returned no result
'100'
no resultset returned 
'117'

Ici le paramètre @length est valorisé à 100 puis utilisé dans l'appel de requête afin d'incrémenter le nombre représentant la totalité des enregistrements concernés. Au retour, valeur initiale 100 + nombre d'enregistrements concernés 17 = 117.
Idem que pour la précédente procédure stockée, il faut passer par une transaction et exécuter chaque ligne de l'appel à la suite pour obtenir les bons résultats.

Les curseurs

Les curseurs permettent de pouvoir itérer sur un jeu d'enregistrement récupéré dans une procédure stockée. Plutôt que de tenter une explication textuelle, un bout de code sera certainement plus parlant :

CREATE DEFINER=`root`@`localhost` PROCEDURE `testCursor`(OUT list CHAR(255))
BEGIN
 
DECLARE done INT DEFAULT 0;
DECLARE tname CHAR(20);
DECLARE s CHAR(255);
 
DECLARE tnames CURSOR for
  select TABLE_NAME
    from information_schema.TABLES
  where TABLE_SCHEMA = 'information_schema';
 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
SET s = '';
OPEN tnames;
REPEAT
  FETCH tnames INTO tname;
  if NOT done THEN
    set s = concat( s, ', ', tname );
  END IF;
UNTIL done END REPEAT;
CLOSE tnames;
select substring( s, 3 ) into length;
END

Ici le code est un peu plus complexe. On déclare différentes variables utilisées par la procédure.

"done" est utilisée pour savoir quand la boucle s'arrête (en liaison avec la ligne "DECLARE CONTINUE HANDLER" qui est exécuté lorsque une erreur NOT_FOUND (02000) est déclenchée.
"tname" est utilisée pour stocker le nom de la table dans la ligne en cours lors de l'itération.
"s" est utilisée pour stocker une chaine de caractères (tous les noms de tables récupéré du resultSet "tnames").

S'ensuit la déclaration d'un curseur en lui passant une requête pour avoir accès à un lot de donnée et un gestionnaire d'évènement basé sur la fin de l'itération.

Pour itérer sur le curseur, on utilise la méthode OPEN cursor_name; puis la méthode REPEAT. La méthode FETCH permet à chaque itération d'aller prendre l'enregistrement suivant. On fait un test sur la valeur "done" et si elle est toujours ok on effectue notre traitement, ici concaténation de la variable s et du nom de la table.
On n'oublie pas de refermer les différentes opération avec leur blocs respectifs : END IF; pour fermer la condition, UNTIL done END REPEAT; pour gérer la boucle et son arrêt et CLOSE tnames; pour fermer l'accès au curseur.

Enfin, je place le contenu de la variable "s" dans mon paramètre de retour "length" et mon retour contiendra les données que je veux.

Dynamic SQL

Le SQL dynamique permet de créer des requêtes dynamiquement, sous forme de chaines de caractères. Ici encore, plutôt que des explications, voici un bout de code pour démontrer le concept :

CREATE DEFINER=`root`@`localhost` PROCEDURE `testDynamic`(IN nid INT, OUT length CHAR(255))
BEGIN
 
DECLARE done INT DEFAULT 0;
DECLARE tname CHAR(20);
DECLARE s CHAR(255);
DECLARE sSql CHAR(100);
 
DECLARE tnames CURSOR for
  select TABLE_NAME
    from information_schema.TABLES
  where TABLE_SCHEMA = 'newff'
  and TABLE_NAME NOT LIKE '%_has_contents'
  and TABLE_NAME LIKE '%_contents';
 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
SET s = '';
OPEN tnames;
 
REPEAT
  FETCH tnames INTO tname;
  if NOT done THEN
 
 
    set @sSql = concat( 'select * from ', tname );
    PREPARE STMT FROM @sSql;
    EXECUTE STMT;
 
    set s = concat( s, ', ', tname );
 
  END IF;
UNTIL done END REPEAT;
 
CLOSE tnames;
 
select substring( s, 3 ) into length;
 
END

Le code précédent démontre l'utilisation des méthodes PREPARE et EXECUTE. Une variable @sSql est valorisée avec une chaine construite à la volée correspondant à une requête SQL, puis cette chaine est passée à la méthode PREPARE, qui va s'occuper de parser la requête.
Enfin, la méthode EXECUTE permet d'exécuter la requête.
Dans cet exemple, l'utilité est moindre car en itérant sur le résultat du curseur, je ne sais pas comment récupérer tous les résultats et les unir dans un seul résultat final à retourner à l'utilisateur, mais ça démontre qu'il est possible de créer sa requête sous forme de chaine de caractères, et donc d'y inclure différents paramètres récupérés à partir d'autres requêtes \ curseurs.

Voilà un bon petit aperçu des procédures stockées, en espérant que ça vous motive à vous familiariser avec ce type de fonctionnalités.


Pour de plus amples informations sur les procédures stockées, vous pouvez aller faire un tour sur la Documentation officielle MySQL 5.0

Dans l'exemple précédent, j'ai tenté de faire de la récupération de multiples résultats (plusieurs requêtes à la suite et faire un "merge" des résultats) mais je n'y suis malheureusement pas arrivé. Si quelqu'un a des idées sur le sujet qu'il laisse un commentaire

Bon courage, à vos gestionnaire DB