Définition des procédures stockées

Les procédures stockées (routines ou stored procedures en anglais) sont disponibles depuis la version 5 de MySQL. Elles permettent d'automatiser des actions qui peuvent être très complexes.

Une procédure stockée est une série d'instructions SQL désignée par un nom.

Contrairement aux requêtes préparées, qui ne sont gardées en mémoire que pour la session courante, les procédures stockées sont, comme leur nom l'indique, stockées de manière durable, et font bien partie intégrante de la base de données dans laquelle elles sont enregistrées.

DELIMITER $ -- On change le délimiteur
CREATE PROCEDURE afficher_races()      
     -- procédure sans paramètres
BEGIN
    SELECT id, nom, espece_id, prix
    FROM Race;
END $

Sens des paramètres d'une procédure stockée

Un paramètre dans les parenthèses peut être de trois sens différents : entrant (IN), sortant (OUT), ou les deux (INOUT).

• IN : c'est un paramètre "entrant". C'est-à-dire qu'il s'agit d'un paramètre dont la valeur est fournie à la procédure stockée. Cette valeur sera utilisée pendant la procédure (pour un calcul ou une sélection, par exemple).
• OUT : il s'agit d'un paramètre "sortant", dont la valeur sera établie au cours de la procédure et qui pourra ensuite être utilisé en dehors de cette procédure.
• INOUT : un tel paramètre sera utilisé pendant la procédure, verra éventuellement sa valeur modifiée par celle-ci, et sera ensuite utilisable en dehors

Syntaxe des paramètres d'une procédure stockée

Lorsque l'on crée une procédure avec un ou plusieurs paramètres dans les parenthèses, chaque paramètre est défini par trois éléments.

• Son sens : entrant, sortant, ou les deux. Si aucun sens n'est donné, il s'agira d'un paramètre IN par défaut.
• Son nom : indispensable pour le désigner à l'intérieur de la procédure.
• Son type : INT, VARCHAR(10)...

Est déterministe

Les fonctions déterministes retournent toujours le même résultat quel que soit le moment auquel elles sont appelées avec un ensemble spécifique de valeurs d'entrée et sur la base du même état de la base de données. Les fonctions non déterministes peuvent retourner différents résultats chaque fois qu'elles sont appelées

Les fonctions qui appellent des procédures stockées sont non déterministes par défaut car les procédures stockées étendues peuvent provoquer des effets secondaires sur la base de données comme la mise à jour d'une table ou d'une ressource externe.

Ajuster les privilèges

Pour accorder des autorisations sur une procédure stockée (comme EXECUTE)

Type de sécurité

Deux types de sécurité peuvent être définis dans une procédure stockée

• DEFINER : le compte qui sert à créer la procédure est celui qui peut l'exécuter (le créateur).
• INVOKER : Cette instruction déporte la sécurité de l'exécution non pas au compte qui a créé la procédure (definer), mais au compte qui l'exécute.

La solution INVOKER est intéressante si le compte du créateur de la procédure à été supprimé.

Accès aux données SQL

Comment la procédure peut-elle accéder aux données SQL? Peut-elle modifier ou uniquement lire les données SQL ou n'a-t-elle aucun accès SQL (defaut)

Edition de procédure stockée

L'éditeur de procédure stockée PhpMyAdmin est uniquement accessible en sélectionnant votre base de données (et pas une table). Voir le menu "Procédures stockées" tout en haut.

Photo écran d'édition de procédure stockée dans phpMyAdmin par Easy-Micro.org


Photo écran d'éxécution de procédure stockée dans phpMyAdmin par Easy-Micro.org

On remarque ci-dessus la déclaration et l'affectation de la variable utilisateur @p0='Lopez'. La procédure est appelée avec un CALL avec comme paramètre la variable @p0 que j'ai renseignée lors de l'exécution de cette procédure via le bouton Exécuter.