Créer une procédure stockée MySQL : DELIMITER, CALL, IN et OUT

Une procédure stockée se crée avec CREATE PROCEDURE nom(paramètres) BEGIN … END et s'exécute avec CALL nom(valeurs). Elle reçoit des valeurs par ses paramètres IN, en renvoie par ses paramètres OUT, et peut aussi renvoyer des lignes comme un SELECT. Ce chapitre écrit tes premières procédures sur la base parc, explique la commande DELIMITER qui déroute tous les débutants, et montre comment gérer les procédures existantes.

Ta première procédure

Le service informatique affiche chaque matin les tickets en attente, les plus urgents d'abord. Plutôt que de copier la requête dans chaque outil, on l'enregistre dans une procédure :

DELIMITER //

CREATE PROCEDURE tickets_en_attente()
BEGIN
  SELECT t.id_ticket, t.titre, t.priorite, t.statut,
         CONCAT(e.prenom, ' ', e.nom) AS demandeur
  FROM ticket t
  JOIN employe e ON e.id_employe = t.id_demandeur
  WHERE t.statut <> 'resolu'
  ORDER BY FIELD(t.priorite, 'critique', 'haute', 'normale', 'basse'), t.date_ouverture;
END //

DELIMITER ;

CALL tickets_en_attente();

FIELD(valeur, liste…) renvoie la position de la valeur dans la liste : 1 pour critique, 4 pour basse. Trier sur ce nombre range les priorités dans l'ordre métier au lieu de l'ordre alphabétique.

Ce que tu dois retenir de la syntaxe :

• les parenthèses suivent toujours le nom, même sans paramètre : tickets_en_attente() ;
• le corps est un bloc BEGIN … END qui contient une ou plusieurs instructions, chacune terminée par un point-virgule ;
• CREATE PROCEDURE enregistre la procédure dans la base courante (parc), il ne l'exécute pas ;
• CALL l'exécute. Les parenthèses de CALL sont facultatives sans paramètre, mais écris-les toujours.

DELIMITER : le piège du point-virgule

Le client mysql lit ce que tu tapes et l'envoie au serveur instruction par instruction : il coupe à chaque point-virgule. Or le corps d'une procédure contient lui-même des points-virgules. Sans précaution, le client coupe au premier et envoie au serveur une instruction tronquée :

CREATE PROCEDURE tickets_en_attente()
BEGIN
  SELECT id_ticket, titre FROM ticket WHERE statut <> 'resolu';
END;

ERROR 1064 (42000): You have an error in your SQL syntax; … near '' at line 3
ERROR 1064 (42000): You have an error in your SQL syntax; … near 'END' at line 1

Le serveur a reçu CREATE PROCEDURE … BEGIN SELECT … sans le END (première erreur), puis un END tout seul (seconde erreur). La commande DELIMITER change le caractère de fin d'instruction du client le temps de la création :

• DELIMITER // : désormais, une instruction se termine par // ;
• tout ce qui précède END //, points-virgules compris, part en une seule instruction ;
• DELIMITER ; rétablit le séparateur habituel. Ne l'oublie pas : sinon, tes requêtes suivantes attendent un // et le client affiche -> sans rien exécuter.

// et $$ sont les deux séparateurs les plus courants ; choisis une suite de caractères qui n'apparaît jamais dans ton code. DELIMITER est une commande du client, pas du SQL : le serveur ne la reçoit jamais. Conséquences pratiques :

• une procédure dont le corps tient en une seule instruction, sans BEGIN … END, n'a pas besoin de DELIMITER :

  sqlCopier le code de la section DELIMITER : le piège du point-virgule

  CREATE PROCEDURE nb_tickets_ouverts()
    SELECT COUNT(*) AS nb_ouverts FROM ticket WHERE statut <> 'resolu';
  

• dans le code d'une application (PDO, MySqlConnector, mysql2), on n'écrit jamais DELIMITER : le pilote envoie chaque chaîne au serveur comme une seule instruction, CREATE PROCEDURE … END compris. Un script de migration qui crée des procédures depuis une application se découpe donc à la main, une instruction par appel.

La syntaxe de CREATE PROCEDURE

CREATE [DEFINER = compte] PROCEDURE [IF NOT EXISTS] nom_procedure (
  [IN | OUT | INOUT] nom_parametre TYPE,
  …
)
[COMMENT 'texte']
[SQL SECURITY { DEFINER | INVOKER }]
BEGIN
  -- déclarations (DECLARE), puis instructions
END

Les clauses DEFINER et SQL SECURITY décident avec quels droits la procédure s'exécute : le chapitre 11 leur est consacré. COMMENT documente la procédure ; le commentaire s'affiche dans SHOW PROCEDURE STATUS.

Des conventions de nommage évitent bien des erreurs : un verbe pour une procédure qui agit (cloturer_ticket, installer_logiciel), un nom pour une procédure qui affiche (tickets_en_attente), le préfixe p_ pour les paramètres et v_ pour les variables locales. Le préfixe n'est pas une coquetterie : il évite le piège décrit plus bas.

Pour créer une procédure, ton compte a besoin du privilège CREATE ROUTINE sur la base. MySQL donne ensuite automatiquement à son créateur les droits EXECUTE (l'appeler) et ALTER ROUTINE (la modifier ou la supprimer).

Les paramètres IN, OUT et INOUT

Un paramètre a un mode qui dit dans quel sens circule la valeur.

Un paramètre IN. La liste des tickets d'un demandeur :

DELIMITER //
CREATE PROCEDURE tickets_du_demandeur(IN p_id_employe INT)
BEGIN
  SELECT id_ticket, titre, statut, date_ouverture
  FROM ticket
  WHERE id_demandeur = p_id_employe
  ORDER BY date_ouverture;
END //
DELIMITER ;

CALL tickets_du_demandeur(3);   -- Julie Lefèvre

Des paramètres OUT. Le nombre d'interventions et le temps passé par un technicien. SELECT … INTO range le résultat d'une requête dans des variables ou des paramètres, dans l'ordre des colonnes :

DELIMITER //
CREATE PROCEDURE charge_technicien(
  IN  p_id_technicien    INT,
  OUT p_nb_interventions INT,
  OUT p_minutes          INT
)
BEGIN
  SELECT COUNT(*), COALESCE(SUM(duree_min), 0)
  INTO p_nb_interventions, p_minutes
  FROM intervention
  WHERE id_technicien = p_id_technicien;
END //
DELIMITER ;

CALL charge_technicien(11, @nb, @minutes);   -- Chloé Lambert
SELECT @nb AS interventions, @minutes AS minutes;

À l'appel, on passe des variables de session, préfixées par @ : elles appartiennent à ta connexion et gardent leur valeur jusqu'à sa fermeture. Le SELECT qui suit lit ce que la procédure y a écrit. Avec Moussa Diallo (12), tu obtiendrais 7 interventions et 450 minutes ; avec Antoine Mercier (10), 4 et 510. Pour un employé qui n'est jamais intervenu, COUNT(*) vaut 0 et COALESCE transforme le SUM vide (NULL) en 0.

Un paramètre INOUT. Une procédure qui ajoute à un total le temps passé sur un ticket. L'appelant l'appelle plusieurs fois avec la même variable :

DELIMITER //
CREATE PROCEDURE cumuler_duree(INOUT p_total INT, IN p_id_ticket INT)
BEGIN
  DECLARE v_duree INT;

  SELECT COALESCE(SUM(duree_min), 0) INTO v_duree
  FROM intervention
  WHERE id_ticket = p_id_ticket;

  SET p_total = p_total + v_duree;
END //
DELIMITER ;

SET @total = 0;
CALL cumuler_duree(@total, 1);   -- + 65 minutes
CALL cumuler_duree(@total, 9);   -- + 570 minutes
SELECT @total;                   -- 635

DECLARE crée une variable locale, qui n'existe que pendant l'exécution de la procédure ; SET lui affecte une valeur. Le chapitre 3 les détaille.

Le piège du paramètre qui porte le nom d'une colonne

Que renvoie cette procédure pour le ticket 1, qui a deux interventions ?

DELIMITER //
CREATE PROCEDURE nb_interventions(IN id_ticket INT)
BEGIN
  SELECT COUNT(*) AS nb FROM intervention WHERE id_ticket = id_ticket;
END //
DELIMITER ;

CALL nb_interventions(1);

Dix-sept : toutes les interventions de la base. Dans une routine, un nom ambigu désigne d'abord la variable locale, puis le paramètre, et seulement ensuite la colonne. Les deux côtés du WHERE désignent donc le paramètre, et 1 = 1 est toujours vrai. Avec un DELETE ou un UPDATE, ce piège efface ou modifie toute la table. Préfixe tes paramètres (p_id_ticket), ou qualifie les colonnes par leur table (intervention.id_ticket).

Une procédure qui renvoie plusieurs résultats

Chaque SELECT sans INTO d'une procédure renvoie un jeu de résultats à l'appelant. Une procédure peut en renvoyer plusieurs :

DELIMITER //
CREATE PROCEDURE bilan_ticket(IN p_id_ticket INT)
BEGIN
  SELECT id_ticket, titre, statut FROM ticket WHERE id_ticket = p_id_ticket;

  SELECT date_intervention, duree_min, compte_rendu
  FROM intervention
  WHERE id_ticket = p_id_ticket
  ORDER BY date_intervention;
END //
DELIMITER ;

CALL bilan_ticket(9);

Le client mysql affiche les deux tableaux l'un après l'autre. Une application doit les lire dans l'ordre et passer de l'un à l'autre explicitement (chapitre 12). C'est pratique, mais un appelant qui oublie un jeu de résultats bloque la connexion : préfère des paramètres OUT quand tu renvoies quelques valeurs isolées.

Lister, afficher, modifier et supprimer

SHOW PROCEDURE STATUS WHERE Db = 'parc';     -- les procédures de la base
SHOW CREATE PROCEDURE charge_technicien;     -- le code complet, tel qu'il a été enregistré

-- Même information, en SQL normalisé : information_schema
SELECT routine_name AS nom, routine_type AS type, definer, security_type
FROM information_schema.routines
WHERE routine_schema = 'parc'
ORDER BY routine_name;

Pour supprimer une procédure : DROP PROCEDURE IF EXISTS charge_technicien;. IF EXISTS évite une erreur si elle n'existe pas, ce qui rend un script rejouable.

Pour modifier son code, il n'y a pas d'autre moyen que de la supprimer puis de la recréer. ALTER PROCEDURE ne change que ses caractéristiques (commentaire, SQL SECURITY) :

ALTER PROCEDURE charge_technicien COMMENT 'Nombre d''interventions et minutes d''un technicien';

Un script de déploiement commence donc par DROP PROCEDURE IF EXISTS, puis CREATE PROCEDURE, et se termine par les GRANT EXECUTE : supprimer une procédure supprime aussi les droits donnés dessus, et la recréer ne les rétablit pas. Depuis MySQL 8.0.29, CREATE PROCEDURE IF NOT EXISTS crée la procédure seulement si elle n'existe pas encore : si elle existe, MySQL ne la remplace pas et se contente d'une note (1304, PROCEDURE … already exists). Ce n'est donc pas un moyen de mettre à jour du code.

Exercices

DELIMITER //
CREATE PROCEDURE postes_du_service(IN p_code VARCHAR(8))
BEGIN
  SELECT p.num_inventaire, p.type_poste, p.marque, p.modele,
         CONCAT(e.prenom, ' ', e.nom) AS utilisateur
  FROM poste p
  JOIN employe e ON e.id_employe = p.id_employe
  JOIN service s ON s.id_service = e.id_service
  WHERE s.code = p_code
  ORDER BY p.num_inventaire;
END //
DELIMITER ;

CALL postes_du_service('COMM');

DELIMITER //
CREATE PROCEDURE licences_logiciel(
  IN  p_id_logiciel INT,
  OUT p_installees  INT,
  OUT p_restantes   INT
)
BEGIN
  SELECT COUNT(*) INTO p_installees
  FROM installation
  WHERE id_logiciel = p_id_logiciel;

  SELECT nb_licences - p_installees INTO p_restantes
  FROM logiciel
  WHERE id_logiciel = p_id_logiciel;
END //
DELIMITER ;

CALL licences_logiciel(5, @inst, @rest);
SELECT @inst, @rest;   -- 7 et 1
CALL licences_logiciel(3, @inst, @rest);
SELECT @inst, @rest;   -- 14 et NULL

DELIMITER //
CREATE PROCEDURE cloturer(id_ticket INT)
BEGIN
  UPDATE ticket SET statut = 'resolu', date_cloture = NOW()
  WHERE id_ticket = id_ticket;
END;
DELIMITER ;

DELIMITER //
CREATE PROCEDURE doubler(IN p_a INT, OUT p_b INT, INOUT p_c INT)
BEGIN
  SET p_a = p_a * 2;
  SET p_b = p_b * 2;
  SET p_c = p_c * 2;
END //
DELIMITER ;

SET @a = 5, @b = 5, @c = 5;
CALL doubler(@a, @b, @c);
SELECT @a, @b, @c;