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 enregistré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 l'encapsulation, la réutilisabilité et la maintenance des opérations sur les données dans des contextes applicatifs et d'administration. Le document (27 pages) est disponible en PDF gratuit à télécharger pour consultation et pratique.

🎯 Ce que vous allez apprendre

• Définition et création de routines — comprendre la syntaxe de CREATE PROCEDURE et le rôle des délimiteurs; déclarer une procédure, documenter son COMMENT et structurer son bloc BEGIN ... END pour encapsuler opérations DML/DDL.
• Passage de paramètres IN, OUT et INOUT — maîtriser le mode de passage des arguments et l'utilisation des variables utilisateur (@var) pour récupérer des résultats (ex: appeler via CALL ma_proc(..., @res) puis SELECT @res).
• Triggers (déclencheurs) — création de triggers pour automatiser des actions sur INSERT, UPDATE et DELETE et interaction avec les procédures stockées.
• Contrôle de flux et structures procédurales — instructions conditionnelles (IF/ELSEIF/CASE) et boucles (WHILE, REPEAT, LOOP avec LEAVE) pour implémenter algorithmes et logiques métiers complexes.
• Gestion des types, variables et assignations — déclarer des variables locales avec DECLARE, affecter avec SET ou SELECT ... INTO et choisir types numériques/date/chaînes adaptés.
• SQL dynamique et statements préparés — construire des requêtes avec CONCAT, exécuter via PREPARE / EXECUTE / DEALLOCATE PREPARE et comprendre limites et risques liés à l'exécution dynamique.
• Opérations d'administration et diagnostic — usage de SHOW PROCEDURE STATUS, SHOW CREATE PROCEDURE, SHOW WARNINGS et intégration de scripts/TP MySQL Workbench pour valider les traitements en environnement réel.

Variables locales vs Variables utilisateur

Les variables locales déclarées avec DECLARE sont limitées au scope de la routine (bloc BEGIN...END) et doivent être déclarées en tête du bloc ; elles ont un typage explicite (par ex. INT, VARCHAR, DATETIME). Les variables préfixées par @ sont des variables utilisateur propres à la session client (session-scoped), dynamiques et persistantes pendant la durée de la session : elles sont pratiques pour transférer des valeurs hors d'une procédure (ex: CALL p(..., @out) puis SELECT @out), mais présentent des risques d'effets de bord et de concurrence si elles sont utilisées comme substitut aux variables locales dans la logique interne.

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

Cas d'usage : Automatisation et Performance

Privilégier une procédure stockée lorsqu'une logique lourde ou récurrente doit s'exécuter côté serveur permet de réduire les aller-retour réseau, de centraliser les règles métier et d'optimiser les opérations sur de grands jeux de données. Les procédures sont adaptées aux traitements transactionnels, aux agrégations massives ou aux opérations nécessitant des droits restreints au niveau base. En revanche, pour une logique métier très orientée présentation ou nécessitant des librairies applicatives externes, le traitement côté application reste pertinent. Mesurer l'impact via EXPLAIN et profiler les procédures aide à décider la stratégie.

Automatisation avec les Triggers et Procédures

Les Triggers (déclencheurs) automatisent des actions suite à des événements DML et peuvent appeler des procédures stockées pour maintenir la cohérence des données ou implémenter des règles transverses. Attention aux boucles infinies et aux effets de bord : limiter les opérations lourdes dans un trigger et éviter la récursivité non contrôlée. Documenter les interactions entre triggers et procédures facilite la maintenance et la traçabilité des impacts sur les tables.

• 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 sur un jeu de résultats ligne par ligne dans une procédure. Cycle typique : DECLARE cur CURSOR FOR SELECT ... ; déclarer un HANDLER FOR NOT FOUND pour détecter la fin du curseur ; OPEN cur ; FETCH cur INTO ... dans une boucle pour traiter chaque ligne ; puis CLOSE cur. Utiliser le handler NOT FOUND pour sortir proprement de la boucle et garantir la libération des ressources.

Syntaxe et Délimiteurs dans MySQL Workbench

MySQL Workbench supporte l'exécution de scripts contenant procédures si les délimiteurs sont correctement définis. Lors de l'import/export de scripts SQL, harmoniser les délimiteurs et tester l'exécution pas à pas pour repérer les erreurs de parsing. Utiliser la fenêtre de requête pour exécuter des blocs complets et la console de messages pour analyser warnings et erreurs. Conserver des scripts versionnés facilite le déploiement en environnement d'intégration.

📑 Sommaire du document

💡 Pourquoi choisir ce cours ?

Fiche concise (27 pages) orientée pratique avec nombreux scripts et snippets CREATE PROCEDURE et CALL pour une mise en œuvre rapide. Approche combinant rappel des types SQL, contrôle de flux, SQL dynamique et commandes d'administration. Présence d'un TP MySQL Workbench et d'exemples réels facilite l'apprentissage pratique et la validation en environnement de développement.

👤 À 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 : bonnes connaissances du SQL (SELECT, INSERT, UPDATE, DELETE), notions de schéma relationnel, familiarité avec MySQL (client ou MySQL Workbench) et compréhension basique des types de données SQL.

❓ 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 dans la procédure, appeler la routine avec une variable utilisateur, par exemple CALL my_sqrt(16, @res), puis lire la valeur avec SELECT @res. Le document illustre aussi l'usage de INOUT pour des paramètres en entrée-sortie.

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

Le SQL dynamique convient pour construire des requêtes dépendant d'identifiants ou d'attributs variables (colonnes, filtres). Composer la chaîne dans une variable utilisateur, exécuter via PREPARE req FROM @requete, puis EXECUTE req et DEALLOCATE PREPARE req. Contrôler les constructions pour éviter erreurs d'exécution et risques d'injection.