Créer une procédure stockée

Les procédures stockées sont des morceaux de code SQL présent dans une base de données et qui peut être appelé via une requête SQL.

MySQL supporte les procédures stockées depuis la version 5. Le nom officiel des procédures stockées dans MySQL est : stored routine.

Techniquement elles ne permettent pas de faire plus de choses que des requêtes SQL traditionnelles, mais elles présentent quand même des intérêts de taille :

Performance

Les procédures stockées sont pré compilé pas le serveur, du coup on économise le temps nécessaire à l'analyse et au décodage d'une requête SQL normale.

Ensuite, on économise les échanges d'information entre le client (par exemple : PHP) et le serveur SQL, ce qui procure un gain de temps supplémentaire.

Sécurité

Contrairement aux clauses (SELECT, INSERT, UPDATE) qui ont un champ d'application très large, une procédure stockée ne fait que ce pour quoi elle a été conçue. Du coup on peut gérer les droits des utilisateurs avec beaucoup plus de finesse, en autorisant l'utilisation de telle procédure plutôt que d'autoriser les INSERT et/ou les UPDATE.

Simplicité

Les procstock (c'est le petit surnom des procédures stockées, dans le jargon) embarquent une partie de code métier, qui a été déplacé de l'applicatif (ex: PHP) vers la base de données. Du coup on obtient un certain niveau d'abstraction par rapport au modèle physique de donnée, qui est plus simple à manipuler pour les développeurs.

Création d'une procédure stockée

Comme pour les vues, je ne saurais trop vous conseiller d'utiliser un outil d'administration MySQL comme phpMyAdmin ou l'excellent HeidiSQL.

Le champ principal contient le code source de la procstock. Ce bloc commence toujours par BEGIN et finit par END.

Ensuite il y a d'autres champs comme le nom, les droits (permettant de restreindre l'accès aux donnéesà la procédure) :

Au final, voici à quoi ressemble le code SQL de création d'une procédure stockée :

CREATE DEFINER=`root`@`localhost` PROCEDURE `sr_update_connectes`(IN `param_older_than` INT, IN `param_ip` VARCHAR(15), IN `param_time` INT, OUT `retour_nb_co` INT)
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT 'Ma première procstock !'
BEGIN
	DELETE FROM stats_connectes WHERE `timestamp` < param_older_than;
	
	INSERT INTO stats_connectes (ip, `timestamp` , cpt_vues) VALUES (param_ip, param_time, 1)
	ON DUPLICATE KEY UPDATE `timestamp` = param_time, cpt_vues = cpt_vues + 1;
	
	SELECT COUNT(ip) INTO retour_nb_co FROM stats_connectes;
END

Et voilà comment exécuter une procédure stockée :

CALL sr_update_connectes(1352053498, '127.0.0.1', 1352053798, @nb_connectes);

Différence entre Procedure et Function

Il existe 2 types de procédures stockées : les procédures (Procedure) qui ne retournent rien, et les fonctions (Function), qui retournent toujours une variable.

En général on se sert surtout des procédures.

Les fonctions ne sont utiles que pour étendre les fonctions natives MtSQL (comme LEFT, LENGTH...) en y ajoutant ses propres fonctions (comme les expressions régulières).

Les paramètres : IN, OUT, INOUT

Une procédure peut prendre plusieurs paramètres qui peuvent être de 3 types : IN, OUT et INOUT.

Un paramètre IN est une variable transmise à la procédure lorsqu'elle est appelée en SQL via l'instruction CALL.

Un paramètre OUT est rempli par la procédure (au cours de son exécution), on peut ensuite la lire avec une simple requête SQL, exemple :

SELECT @nb_connectes;

Et INOUT, c'est tout simplement un mix des deux : la variable est passée en paramètre à la procédure lors de l'appel, et est ensuite modifiée par la procédure.

Voici un billet similaire qui vous permettera d'en savoir plus sur supprimer les doublons avec excel.

Les variables ainsi créées par les procédures sont stockées dans la session, c'est-à-dire qu'elles demeurent accessibles tant que la connexion au serveur MySQL reste établie.

Elles sont donc détruites avec la session lors de la déconnexion.

Qu'est-ce qu’un Resultset ?

À côté des paramètres OUT ou INOUT (qui nécessitent un SELECT pour y accéder, après le CALL), une procédure stockée peut aussi retourner directement des résultats, tout comme n'importe quelle requête SELECT. On appelle les données ainsi retournées un Resultset, ou Recordset.

Attention, il ne faut pas confondre ce retour avec les retours des fonctions dont on a parlé plus haut. Un resultset n'est pas une variable atomique d'un type primitif (comme entier, chaine de caractère...), c'est une liste de tuples.

Pour qu'une procédure stockée produise un resultset, il suffit de faire un SELECT à l'intérieur (dans le code de la procédure).

Et si une procédure contient plusieurs SELECT, alors elle retournera plusieurs Resultset.

Mais attention, les procédures qui retournent plusieurs resulter peuvent poser des problèmes côté client avec certains langages de programmation.

En PHP, seules les extensions mysqli (avec $mysqli->next_result()) et PDO (via PDOStatement::nextRowset) permettent de gérer de multiples résultset.

Vous trouverez plus d'informations là dessus ici :

• Using Stored Procedures & MySQLI in PHP 5
• PHP PDO error in multi-statement query
• PDOStatement::nextRowset

Encore faim ? allez lire ça : dessiner une tablette graphique !