Syntaxe des procédures stockées

2.1 DELIMITER : pourquoi changer le delimiteur

C'est le point le plus deroutant pour les debutants. Voici l'explication complete.

Par defaut, MySQL utilise le point-virgule ; comme delimiteur : c'est le caractere qui indique a MySQL "cette instruction est terminee, execute-la".

Le probleme : une procedure stockee contient des points-virgules a l'interieur de son corps. Exemple :

CREATE PROCEDURE test()
BEGIN
    SELECT * FROM client;   -- point-virgule ici
    SELECT * FROM produit;  -- et ici
END;                        -- et ici

Quand MySQL lit la premiere ligne SELECT * FROM client;, il pense que l'instruction est terminee et essaie de l'executer immediatement. Il ne comprend pas que c'est a l'interieur d'une procedure.

La solution : on change temporairement le delimiteur pour que MySQL ne s'arrete plus au ; mais a un autre caractere (par convention // ou $$).

DELIMITER //

CREATE PROCEDURE test()
BEGIN
    SELECT * FROM client;   -- MySQL ne s'arrete plus ici
    SELECT * FROM produit;  -- ni ici
END //                      -- MAINTENANT MySQL sait que c'est fini

DELIMITER ;                 -- on remet le delimiteur par defaut

Regle : toujours utiliser DELIMITER // avant CREATE PROCEDURE et DELIMITER ; apres.

Attention : le DELIMITER est une commande du client MySQL (mysql en ligne de commande, phpMyAdmin). Ce n'est pas du SQL standard. Dans phpMyAdmin, on peut souvent omettre le DELIMITER car l'interface le gere automatiquement. En ligne de commande, c'est obligatoire.

2.2 CREATE PROCEDURE : syntaxe complete

DELIMITER //

CREATE PROCEDURE nom_procedure (
    [IN | OUT | INOUT] param1 TYPE,
    [IN | OUT | INOUT] param2 TYPE,
    ...
)
BEGIN
    -- corps de la procedure
END //

DELIMITER ;

• nom_procedure : nom unique dans la base de donnees.
• Les parametres sont optionnels. Une procedure peut n'avoir aucun parametre.
• Le corps est entre BEGIN et END.

2.3 Parametres : IN, OUT, INOUT

IN (par defaut)

Le parametre est une entree. La procedure recoit une valeur mais ne peut pas la modifier pour l'appelant. Si on ne precise rien, le parametre est IN par defaut.

DELIMITER //

CREATE PROCEDURE clients_par_ville(IN p_ville VARCHAR(100))
BEGIN
    SELECT id_client, nom, prenom, email
    FROM client
    WHERE ville = p_ville
    ORDER BY nom, prenom;
END //

DELIMITER ;

Appel :

CALL clients_par_ville('Paris');
CALL clients_par_ville('Lyon');

Convention de nommage : prefixer les parametres par p_ pour les distinguer des colonnes. Sinon, si le parametre et la colonne ont le meme nom, MySQL utilise le parametre dans la clause WHERE, ce qui donne WHERE ville = ville (toujours vrai).

OUT

Le parametre est une sortie. La procedure y ecrit une valeur que l'appelant pourra lire apres l'appel.

Besoin : calculer le montant total d'une commande et le recuperer dans une variable.

DELIMITER //

CREATE PROCEDURE total_commande(
    IN p_id_commande INT,
    OUT p_total DECIMAL(10,2)
)
BEGIN
    SELECT SUM(quantite * prix_unitaire)
    INTO p_total
    FROM ligne_commande
    WHERE id_commande = p_id_commande;
END //

DELIMITER ;

Appel et recuperation :

CALL total_commande(1, @resultat);
SELECT @resultat AS montant_total;

Explication : @resultat est une variable de session MySQL (prefixee par @). La procedure ecrit dans cette variable via le parametre OUT. Apres le CALL, on lit la variable avec SELECT.

INOUT

Le parametre est a la fois entree et sortie. La procedure recoit une valeur, la modifie, et la modification est visible par l'appelant.

DELIMITER //

CREATE PROCEDURE appliquer_remise(INOUT p_prix DECIMAL(10,2), IN p_pourcentage INT)
BEGIN
    SET p_prix = p_prix - (p_prix * p_pourcentage / 100);
END //

DELIMITER ;

Appel :

SET @prix = 100.00;
CALL appliquer_remise(@prix, 15);
SELECT @prix;
-- Resultat : 85.00

2.4 Variables locales : DECLARE et SET

Les variables locales existent uniquement a l'interieur de la procedure. Elles sont declarees avec DECLARE et doivent etre placees au debut du bloc BEGIN, avant toute autre instruction.

DELIMITER //

CREATE PROCEDURE stats_commande(IN p_id_commande INT)
BEGIN
    DECLARE v_nb_lignes INT DEFAULT 0;
    DECLARE v_total DECIMAL(10,2) DEFAULT 0;
    DECLARE v_moyenne DECIMAL(10,2);

    SELECT COUNT(*), SUM(quantite * prix_unitaire)
    INTO v_nb_lignes, v_total
    FROM ligne_commande
    WHERE id_commande = p_id_commande;

    SET v_moyenne = v_total / v_nb_lignes;

    SELECT v_nb_lignes AS nb_articles,
           v_total AS total,
           v_moyenne AS panier_moyen;
END //

DELIMITER ;

Points importants :

• DECLARE est obligatoire pour les variables locales (prefixees par convention v_).
• DEFAULT permet de donner une valeur initiale. Sans DEFAULT, la variable vaut NULL.
• SET permet de modifier la valeur d'une variable.
• SELECT ... INTO permet de stocker le resultat d'une requete dans des variables.
• Les DECLARE doivent TOUS etre avant la premiere instruction executable.

2.5 CALL : executer une procedure

CALL nom_procedure();
CALL nom_procedure(valeur1, valeur2);
CALL nom_procedure('Paris');
CALL total_commande(1, @montant);

2.6 DROP PROCEDURE : supprimer une procedure

DROP PROCEDURE nom_procedure;
DROP PROCEDURE IF EXISTS nom_procedure;

IF EXISTS evite une erreur si la procedure n'existe pas. Bonne pratique : toujours utiliser DROP PROCEDURE IF EXISTS avant CREATE PROCEDURE pour pouvoir recreer la procedure sans erreur.

DROP PROCEDURE IF EXISTS clients_par_ville;

DELIMITER //

CREATE PROCEDURE clients_par_ville(IN p_ville VARCHAR(100))
BEGIN
    SELECT * FROM client WHERE ville = p_ville;
END //

DELIMITER ;

2.7 Voir les procedures existantes

SHOW PROCEDURE STATUS WHERE Db = 'nom_base';
SHOW CREATE PROCEDURE nom_procedure;