Cours MySQL procédures stockées en PDF (Intermédiaire)

MySQL – Procédures et fonctions stockées : Ce qu'il faut savoir. Ensemble d'instructions procédurales exécutées côté serveur pour encapsuler la logique métier, automatiser des traitements et manipuler les données directement dans la base MySQL. Ce mécanisme facilite la réutilisabilité, la maintenance et la centralisation des règles métier dans des contextes applicatifs et d'administration.

Le document est disponible en PDF gratuit pour consultation et pratique. Rédigé par Bertrand LIAUDET.

Avantages du format PDF

Le format PDF assure portabilité, lecture hors-ligne et conservation de la mise en forme des scripts. Il permet une recherche textuelle rapide, l'annotation locale et une distribution maîtrisée des versions entre équipes de développement et d'exploitation.

🎯 Ce que vous allez apprendre

• Définition et création de routines — syntaxe de CREATE PROCEDURE, gestion des délimiteurs et structuration du bloc BEGIN ... END pour encapsuler opérations DML/DDL.
• Passage de paramètres IN, OUT et INOUT — modes d'arguments et utilisation des variables utilisateur (@var) pour extraire des résultats.
• Triggers (déclencheurs) — automatisation d'actions sur INSERT, UPDATE, DELETE et cohérence avec les procédures stockées.
• Contrôle de flux et structures procédurales — IF/ELSEIF/CASE, boucles (WHILE, REPEAT, LOOP) et motifs pour implémenter logiques métier.
• Gestion des types, variables et assignations — DECLARE, SET, SELECT ... INTO et choix de types appropriés.
• SQL dynamique et statements préparés — construction sécurisée de requêtes, PREPARE / EXECUTE / DEALLOCATE PREPARE et précautions contre l'injection.
• Opérations d'administration et diagnostic — SHOW PROCEDURE STATUS, SHOW CREATE PROCEDURE, SHOW WARNINGS et validation via MySQL Workbench.

Variables locales vs Variables utilisateur

Les variables locales déclarées avec DECLARE sont limitées au scope d'une routine et doivent être listées en tête du bloc ; elles ont un typage explicite (INT, VARCHAR, DATETIME, etc.). Les variables préfixées par @ sont des variables de session persistantes pendant la connexion cliente : utiles pour récupérer des valeurs hors d'une procédure (ex. CALL p(..., @out) puis SELECT @out), mais susceptibles d'effets de bord si elles remplacent indûment des variables locales.

DELIMITER //
CREATE PROCEDURE exemple()
BEGIN
  SELECT NOW();
END //
DELIMITER ;

Cas d'usage : Automatisation et Performance

Favoriser une procédure stockée lorsqu'une logique lourde ou répétée doit s'exécuter côté serveur réduit les allers-retours réseau, centralise les règles métier et optimise le traitement de grands jeux de données. Les procédures conviennent aux traitements transactionnels, aux agrégations massives et aux opérations nécessitant des droits restreints. Pour des opérations dépendantes de bibliothèques applicatives ou orientées présentation, placer la logique côté application. Mesurer l'impact avec EXPLAIN et profiler les procédures pour valider les choix.

En production, la gestion fine des erreurs est cruciale : DECLARE HANDLER permet d'intercepter des conditions SQLSTATE et d'appliquer des comportements de secours (rollback, journalisation, relance). Documenter ces handlers facilite le support et le diagnostic.

Gestion des transactions et sécurité

Organiser les transactions dans et autour des procédures garantit l'intégrité des données. Les procédures stockées peuvent contenir des contrôles transactionnels (START TRANSACTION, COMMIT, ROLLBACK) ; les handlers doivent assurer un ROLLBACK en cas d'exception pour éviter des états partiels. En revanche, les fonctions et certains contexts (par ex. triggers selon la version) peuvent imposer des restrictions sur l'usage direct des commandes transactionnelles : vérifier la documentation de la version de MySQL utilisée.

Exemple de pattern robuste : encapsuler la logique modifiante dans une procédure qui démarre explicitement la transaction, déclare un EXIT HANDLER pour les exceptions et effectue COMMIT uniquement si tous les traitements réussissent.

CREATE PROCEDURE transfer_funds(acc_from INT, acc_to INT, amt DECIMAL(10,2))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    -- journaliser l'erreur pour le support
  END;
  START TRANSACTION;
  UPDATE accounts SET balance = balance - amt WHERE id = acc_from;
  UPDATE accounts SET balance = balance + amt WHERE id = acc_to;
  COMMIT;
END;

Comparaison : Procédures vs Logique Applicative

Les procédures stockées déplacent le traitement côté serveur, réduisent la latence et exploitent la proximité des données pour des agrégations ou des mises à jour massives. La logique applicative reste préférable pour des opérations dépendantes de bibliothèques externes, de frameworks ou lorsque la portabilité entre SGBD est une contrainte. La décision dépendra de critères tels que la maintenance, la visibilité des logs, la scalabilité et la sécurité. Pour des migrations ou intégrations multi-SGBD, noter que la programmation procédurale est parfois appelée « programmation PL-SQL » dans d'autres systèmes ; adapter alors les patterns pour garantir portabilité.

Considérations pratiques : privilégier l'optimisation requêtes SQL côté base pour les gros volumes, utiliser l'automatisation base de données pour tâches répétitives et versionner les scripts pour faciliter le déploiement et les tests d'intégration.

Automatisation avec les Triggers et Procédures

Les Triggers automatisent des actions suite à des événements DML et peuvent appeler des procédures pour maintenir la cohérence des données ou appliquer des règles transverses. Limiter les opérations lourdes dans un trigger pour éviter les boucles ou la récursivité non contrôlée. Documenter les interactions entre triggers et procédures améliore la traçabilité.

• BEFORE INSERT / AFTER INSERT
• BEFORE UPDATE / AFTER UPDATE
• BEFORE DELETE / AFTER DELETE

CREATE PROCEDURE safe_insert(...)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- gestion des erreurs, rollback, log, etc.
  END;
  -- corps de la procédure
END;

Utilisation des curseurs (Cursors)

Les curseurs permettent d'itérer ligne par ligne sur un jeu de résultats. Pattern recommandé : DECLARE cur CURSOR FOR SELECT ... ; déclarer un HANDLER FOR NOT FOUND ; OPEN cur ; FETCH cur INTO ... dans une boucle ; puis CLOSE cur. Garantir la libération des ressources et utiliser des handlers pour une sortie propre.

Syntaxe et Délimiteurs dans MySQL Workbench

MySQL Workbench exécute des scripts contenant des procédures si les délimiteurs sont définis correctement. Lors de l'import/export, harmoniser les délimiteurs et tester l'exécution pas à pas pour repérer les erreurs de parsing. Utiliser la console de messages pour analyser warnings et erreurs, et versionner les scripts pour simplifier les déploiements.

Optimisation des requêtes

Valider les plans d'exécution avec EXPLAIN avant et après encapsulation dans une procédure permet d'identifier scans complets et usages d'index. Éviter les requêtes non-déterministes dans les boucles et limiter le SQL dynamique non contrôlé pour préserver le cache d'instructions.

Avantages des procédures stockées en production

Les procédures favorisent la centralisation des règles métier, réduisent la latence et renforcent la sécurité via des droits fins. En déploiement, appliquer des bonnes pratiques limite les risques de verrous et améliore la stabilité.

• Favoriser des transactions courtes pour limiter la durée de détention des verrous.
• Ordonnancer les accès aux tables pour réduire les conflits entre transactions.
• Éviter les requêtes longues ou non indexées dans les boucles ; privilégier le traitement en lot.
• Choisir des niveaux d'isolation adaptés et tester l'impact sur la concurrence.
• Surveiller et journaliser les deadlocks (SHOW ENGINE INNODB STATUS) et prévoir une stratégie de retry pour opérations idempotentes.
• Contrôler strictement le SQL dynamique et valider les entrées pour réduire les risques d'injection.

Pourquoi utiliser ce tutoriel MySQL PDF ?

Fiche concise orientée pratique avec scripts et snippets CREATE PROCEDURE et CALL pour une mise en œuvre rapide. Approche combinant rappels sur les types SQL, contrôle de flux, SQL dynamique et commandes d'administration. Les TP MySQL Workbench et exemples en conditions réelles facilitent la validation et le déploiement en intégration continue.

À qui s'adresse ce cours ?

• Public cible : développeurs back-end, administrateurs de bases de données et étudiants en bases de données qui implémentent ou maintiennent de la logique métier côté serveur dans MySQL.
• Prérequis : solides connaissances SQL (SELECT, INSERT, UPDATE, DELETE), notions de schéma relationnel et familiarité avec MySQL ou MySQL Workbench.

Foire Aux Questions (FAQ)

Comment récupérer la valeur calculée par une procédure via un paramètre OUT ?

Déclarer le paramètre en OUT, appeler la routine avec une variable utilisateur (ex. CALL my_sqrt(16, @res)) puis lire la valeur avec SELECT @res. Le document illustre également l'usage de INOUT pour paramètres entrée-sortie.

Quand recourir au SQL dynamique avec PREPARE/EXECUTE dans une procédure ?

Le SQL dynamique sert pour des requêtes dépendant d'identifiants ou d'attributs variables (noms de colonnes, filtres). Composer la chaîne et exécuter via PREPARE ... FROM, EXECUTE puis DEALLOCATE PREPARE. Contrôler la construction pour éviter erreurs d'exécution et risques d'injection. Pour aller plus loin, consultez notre cours PHP avancé : Gérer une DB avec PDO pour sécuriser vos accès.