Easy-Micro [ SQL - Routines SQL ] - Formations informatiques pour entreprises

SQL
Routines SQL
Procédure stockée

Définition des procédures stockées

Les procédures stockées (routines 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;  -- Cette fois, le ; ne nous embêtera pas
END$

⚠ Attention au caractère qui permet de délimiter les instructions. Par défaut ce délimiteur est un ; (a changer par un autre caractère avec l'invite DELEMITER)

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

Voir la création de procédures stockées dans phpMyAdmin

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)...

Exemple de procédure avec un seul paramètre entrant Voici une procédure qui, selon l'id de l'espèce qu'on lui passe en paramètre, affiche les différentes races existant pour cette espèce.

DELIMITER |
CREATE PROCEDURE afficher_race_selon_espece (IN p_espece_id INT)  
    -- Définition du paramètre p_espece_id
BEGIN
    SELECT id, nom, espece_id, prix 
    FROM Race
    WHERE espece_id = p_espece_id;  -- Utilisation du paramètre
END |
DELIMITER ;  -- On remet le délimiteur par défaut

Pour l'utiliser, il faut donc passer une valeur en paramètre de la procédure, soit directement, soit par l'intermédiaire d'une variable utilisateur (@mavariable).

CALL afficher_race_selon_espece(1);

SET @espece_id := 2;
CALL afficher_race_selon_espece(@espece_id);

Voir le résultat sur OpenClassRoom

Création de fonction

Une fonction est comme une procédure stockée mais qui s'exécute avec une commande SELECT

Exemple de fonction :

mysql> USE mabase;
mysql> delimiter //
mysql> CREATE FUNCTION nbrecommande(codeclient char(6))
-> returns int
-> READS SQL DATA
-> BEGIN
-> declare retour int;
-> select count(*) into retour from commandes where idclient=codeclient;
-> return retour;
-> END
-> //

S'exécute avec un simple select :

mysql> SELECT nbrecommande('DUR001');

< >