Aller au contenu principal

Les procédures stockées

Automatiser des traitements dans la base de données avec les procédures stockées

Notions théoriques​

Une procédure stockée (stored procedure) est un ensemble d’instructions SQL enregistrées dans la base de données et pouvant être exécutées sur demande.

Une procédure stockée permet :

  • d’automatiser des traitements,
  • de rendre le code réutilisable
  • et de centraliser la logique métier côté serveur.

Avantages des procédures stockées​

  • Réduction de la duplication de code
  • Meilleures performances (le code est compilé une seule fois)
  • Sécurité renforcée (contrôle des accès aux données sensibles)
  • Maintenance facilitée (modification centralisée)
  • Moins de trafic entre l’application et la base de données

Syntaxe de base​

DELIMITER //

CREATE PROCEDURE nom_de_la_procédure(paramètres)
BEGIN
  -- instructions SQL
END;
//

DELIMITER ;
  • DELIMITER permet de définir un nouveau séparateur temporaire pour écrire plusieurs lignes de code.
  • Les paramètres peuvent être :
    • IN : valeur en entrée
    • OUT : valeur de sortie
    • INOUT : valeur en entrée et sortie

Appeler une procédure​

Une procédure stockée s’exécute avec la commande :

CALL nom_de_la_procédure(valeurs);

Exemple de paramètres​

CREATE PROCEDURE afficher_utilisateur(IN id INT)
BEGIN
  SELECT * FROM utilisateurs WHERE utilisateurs.id = id;
END;

Variables locales​

Il est possible de déclarer des variables internes à la procédure :

DECLARE nom_variable TYPE;
SET nom_variable = valeur;

Structures de contrôle​

Les procédures peuvent contenir des instructions de contrôle :

  • IF ... THEN ... ELSE ... END IF;
  • WHILE ... DO ... END WHILE;
  • REPEAT ... UNTIL ... END REPEAT;
  • CASE ... WHEN ... THEN ... ELSE ... END CASE;

Suppression d’une procédure​

DROP PROCEDURE IF EXISTS nom_de_la_procédure;

Limitations​

  • Une procédure ne peut pas être utilisée directement dans une requête SELECT.
  • Elle ne retourne pas de résultat comme une fonction, sauf via des paramètres OUT.

Exemple pratique​

Il est possible de créer une procédure stockée qui affiche tous les livres d’un auteur donné, en passant son identifiant en paramètre.

Étapes​

  1. Créer une procédure livres_par_auteur qui prend un paramètre id_auteur
  2. Utiliser une requête SELECT pour afficher les livres correspondant à cet auteur
  3. Appeler la procédure avec différents identifiants pour vérifier le fonctionnement

Code SQL​

DELIMITER //

CREATE PROCEDURE livres_par_auteur(IN id_auteur INT)
BEGIN
  SELECT titre, dateparu, prix
  FROM livres
  WHERE idauteur = id_auteur;
END;
//

DELIMITER ;

-- Appel de la procédure
CALL livres_par_auteur(1);
CALL livres_par_auteur(3);

Résultat attendu : une liste de livres pour chaque auteur donné.

Test de mémorisation/compréhension​


Quel mot-clé permet d’exécuter une procédure stockée ?


Quel mot-clé est utilisé pour définir une procédure ?


Quel mot-clé permet de supprimer une procédure ?


Quel est le rôle du mot-clé DELIMITER ?


Quelle est la bonne syntaxe pour appeler une procédure ?


Quel type de paramètre permet de renvoyer une valeur en sortie ?


Quel mot-clé permet de déclarer une variable locale ?


Quelle instruction permet une boucle dans une procédure ?


Une procédure peut-elle contenir des instructions IF ?


Peut-on utiliser SELECT dans une procédure stockée ?


TP pour réfléchir et résoudre des problèmes​

L’objectif de ce TP est de créer 3 procédures stockées différentes dans la base bibliotheque.

Télécharger le fichier suivant :
👉 Télécharger le fichier 'cdi.sql' - Télécharger le MCD

Étape 1 : Importer la base de données​

Importer le fichier cdi.sql dans votre environnement MariaDB (ligne de commande, phpMyAdmin, DBeaver, etc.) pour créer la base bibliotheque et ses tables.

Une solution

Étape 2 : Créer une procédure livres_par_theme pour afficher les livres d’un thème donné​

Créer une procédure stockée appelée livres_par_theme qui prend un paramètre d’entrée theme_livre (type VARCHAR(30)).
Elle doit afficher les titres, auteurs et dates de parution de tous les livres correspondant à ce thème.

Une solution

Étape 3 : Créer une procédure statistiques_auteur pour obtenir le nombre de livres d’un auteur​

Créer une procédure appelée statistiques_auteur qui prend un identifiant d’auteur (id_auteur) en paramètre d’entrée.
Elle doit afficher :

  • Le nom complet de l’auteur
  • Le nombre total de livres écrits par cet auteur
  • Le prix moyen de ses livres
Une solution

Étape 4 : Créer une procédure livres_non_rendus pour afficher les livres empruntés et non rendus​

Créer une procédure appelée livres_non_rendus qui n’a pas de paramètre.
Elle doit afficher :

  • Le titre du livre
  • Le nom et prénom de l’emprunteur
  • La date de prêt
  • Le nombre de jours de retard (calculé avec DATEDIFF(CURDATE(), datepret))
Une solution

Étape 5 : Tester les trois procédures​

Vérifier que les résultats sont corrects et lisibles.

Effectuer au moins un appel à chaque procédure avec des valeurs pertinentes :

-- Test de la procédure 1
CALL livres_par_theme('Science-fiction');

-- Test de la procédure 2
CALL statistiques_auteur(2);

-- Test de la procédure 3
CALL livres_non_rendus();

Résultats attendus :

  • Liste des livres de science-fiction
  • Statistiques sur Jules Verne
  • Liste des livres non rendus avec le nombre de jours depuis l’emprunt

Étape 6 : Supprimer les procédures si besoin​

Ajouter les commandes permettant de supprimer les procédures créées, si nécessaire :

DROP PROCEDURE IF EXISTS livres_par_theme;
DROP PROCEDURE IF EXISTS statistiques_auteur;
DROP PROCEDURE IF EXISTS livres_non_rendus;