Module 1Maîtriser le langage de programmation procédurale sous MySQL
Chapitre 2 sur 12
Ce qu'apporte le langage procédural, les blocs, le délimiteur, les fonctions définies par l'utilisateur et les procédures stockées avec leurs paramètres IN, OUT et INOUT.
Objectifs
Avec ce chapitre, tu sauras…
BEGIN … END et changer le délimiteur ;CALL et lui passer des paramètres IN, OUT et INOUT ;Leçon 1
Introduction
Les structures de contrôle habituelles d'un langage de programmation (IF, WHILE…) ne font pas partie de la norme SQL de base. Elles sont dans une sous-partie optionnelle de la norme (ISO/IEC 9075-5:1996, Flow-control statements).
Cette extension de SQL fait cohabiter les structures de contrôle (IF, CASE, WHILE, REPEAT, LOOP) avec les instructions SQL (surtout SELECT, INSERT, UPDATE et DELETE).
Deux directives supplémentaires, à utiliser avec modération :
| Directive | Rôle | Équivalent PHP, Java, C |
|---|---|---|
LEAVE | sort d'une boucle (ou d'un bloc étiqueté) | break |
ITERATE | force à refaire un tour de boucle depuis le début | continue |
Définition
| Type de programme | Appelé… | Renvoie | Chapitre |
|---|---|---|---|
| fonction (UDF) | dans une requête : SELECT ma_fonction(…) | une seule valeur (RETURN) | 2 et 8 |
| procédure stockée | avec CALL ma_procedure(…) | des résultats SELECT et des paramètres OUT | 2 et 8 |
| déclencheur (trigger) | automatiquement lors d'un INSERT, UPDATE ou DELETE | rien | 9 |
| événement (event) |
Leçon 2
Vidéo · À regarder
FRBase de données : Procédures stockées structurées
Algomius · YouTube
Leçon 3
| Déclaration | Signification |
|---|---|
DETERMINISTIC | renvoie toujours le même résultat pour les mêmes arguments |
READS SQL DATA | lit des données de la base, mais ne les modifie pas |
MODIFIES SQL DATA | modifie des données de la base |
CONTAINS SQL | contient du SQL qui ne lit ni ne modifie de données (ex. SELECT CURRENT_DATE();) |
Leçon 4
| Avantage | Pourquoi |
|---|---|
| réduire le trafic réseau | au lieu d'envoyer plusieurs requêtes, l'application envoie seulement le nom de la procédure et ses paramètres |
| facile à maintenir | la logique métier est réutilisable par plusieurs applications ou modules ; une modification se fait à un seul endroit ; la base est plus cohérente |
| sécurité | plus sûres que les requêtes écrites à la volée ; on peut donner le droit d'exécuter la procédure sans donner de droits sur les tables (chapitre 11) ; elles aident à se protéger de l'injection SQL |
Vidéo · À regarder
FR1LEAVE correspond en PHP à…
2Une fonction MySQL peut renvoyer…
3On appelle une procédure stockée avec…
4DELIMITER $$ sert à…
5Le mode de paramètre par défaut est…
6Pour récupérer une valeur calculée par la procédure, on utilise un paramètre…
7Une fonction qui lit la base sans la modifier se déclare…
8Pour modifier une procédure en SQL, on…
L'essentiel du chapitre
IF, CASE), boucles (LOOP, WHILE, REPEAT), LEAVE (= break), ITERATE (= continue) et gestion des erreurs.SELECT f(), une valeur), procédure (CALL p()), déclencheur (automatique sur INSERT/UPDATE/DELETE), événement (planifié).BEGIN [DECLARE …] … END, imbricable ; toujours dans un programme stocké.DELIMITER $$ … END $$ DELIMITER ;.CREATE FUNCTION f(p type) RETURNS type DETERMINISTIC | READS SQL DATA | MODIFIES SQL DATA | CONTAINS SQL BEGIN … RETURN … END ; jamais de table en retour.CREATE PROCEDURE p(IN a, OUT b, INOUT c) BEGIN … END ; CALL p(…, @var) puis SELECT @var ; avantages : moins de trafic, maintenance, sécurité.DROP PROCEDURE IF EXISTS ; modifier = supprimer + recréer ; lister : SHOW PROCEDURE STATUS, information_schema.routines.| automatiquement selon un calendrier |
| rien |
| 5 |
Vidéo · À regarder
ENMySQL Functions | Create Function | MySQL Tutorial
Knowledge Thrusters · YouTube
Base de données : Procédures stockées
Algomius · YouTube
| Mode | Sens | Explication |
|---|---|---|
| IN | appelant → procédure | la valeur est transmise à la procédure, qui l'utilise : c'est une entrée. C'est le mode par défaut, passage par valeur |
| OUT | procédure → appelant | la procédure renvoie une valeur dans une variable de l'appelant : c'est une sortie (par référence). La procédure ne voit pas la valeur de départ |
| INOUT | dans les deux sens | l'appelant donne une valeur, la procédure la modifie, et l'appelant récupère la nouvelle valeur |
Vidéo · À regarder
FRBase de données : Procédures stockées
Algomius · YouTube
Vidéo · À regarder
FRBase de données : Procédures stockées
Algomius · YouTube
La diapositive « Définition » du résumé est vide (son contenu n'a pas été mis dans le PDF) ; la définition ci-dessus la remplace. La diapositive de la section 3 (« Distinguer les différents types des programmes MySQL ») est vide elle aussi : le tableau suivant la résume.
Un sous-programme est un bloc d'instructions composé de :
BEGIN : indique le début du bloc ;DECLARE (optionnel) : déclare une variable, un curseur, une exception, etc. ;END : ferme le bloc.Un bloc peut être imbriqué dans un autre bloc :
BEGIN
SELECT 'Bloc d''instructions principal';
BEGIN
SELECT 'Bloc d''instructions 2, imbriqué dans le bloc principal';
BEGIN
SELECT 'Bloc d''instructions 3, imbriqué dans le bloc d''instructions 2';
END;
END;
BEGIN
SELECT 'Bloc d''instructions 4, imbriqué dans le bloc principal';
END;
END;Dans une chaîne, l'apostrophe se double : 'Bloc d''instructions' affiche « Bloc d'instructions ».
Dans MySQL, un bloc BEGIN … END ne peut pas être tapé directement dans la console : il doit être dans une procédure, une fonction, un déclencheur ou un événement. Pour tester l'exemple, mets-le dans une procédure : CREATE PROCEDURE bloc_demo() BEGIN … END puis CALL bloc_demo(); (corrigé 2.3).
Ce que tu vas voir (5 min) : les chapitres « Les éléments structurants », « Bloc d'instruction ». Le formateur présente les éléments qui structurent un programme et le bloc d'instructions BEGIN … END.
Définition
Les fonctions créent une logique de programmation personnalisée pour ta base. Elles sont utiles quand le même code revient à plusieurs endroits : on l'écrit une seule fois.
MySQL a de nombreuses fonctions intégrées : CURRENT_DATE(), AVG(), SUM(), ABS(), CONCAT()… utilisables dans les requêtes, les vues et les procédures. On peut aussi créer ses propres fonctions : les fonctions définies par l'utilisateur (User Defined Functions, UDF).
Syntaxe
USE nom_bd;
DROP FUNCTION IF EXISTS nom_fonction;
CREATE FUNCTION nom_fonction ([parameter(s)])
RETURNS type_retour
déclaration informative
InstructionsUSE eshop_app_db;
DROP FUNCTION IF EXISTS uf_nombre_clients_par_ville;
DELIMITER $$
CREATE FUNCTION uf_nombre_clients_par_ville (ville VARCHAR(50))
RETURNS INT
READS SQL DATA
BEGIN
DECLARE nombre INT;
SET nombre = (SELECT COUNT(*)
FROM clients
WHERE adresse = ville);
RETURN nombre;
END $$
DELIMITER ;
SELECT uf_nombre_clients_par_ville('agadir'); -- 2La déclaration informative dit à MySQL ce que fait la fonction :
Définition
IF, CASE) et des boucles.Un cas d'utilisation
Une application de vente doit enregistrer une vente. Elle doit :
Toutes ces tâches peuvent être placées dans une seule procédure stockée : l'application l'appelle en une ligne.
Tableau: Les avantages
La syntaxe
DELIMITER $$
CREATE PROCEDURE nom_procedure([paramètres])
BEGIN
le code SQL
END $$
DELIMITER ;
-- exécuter une procédure :
CALL nom_procedure([paramètres]);DELIMITER $$
CREATE PROCEDURE sp_client_par_ville(IN ville VARCHAR(50))
BEGIN
SELECT *
FROM clients
WHERE UPPER(adresse) = UPPER(ville);
END $$
DELIMITER ;
-- Appel
CALL sp_client_par_ville("agadir");Dans eshop_app_db : (1) sp_produits_par_categorie(IN p_cat VARCHAR(50)) affiche les produits d'une catégorie (par son nom) ; (2) sp_stats_produits(OUT p_nb INT, OUT p_prix_moyen DECIMAL(10,2)) renvoie le nombre de produits et le prix moyen ; (3) sp_augmenter_prix(INOUT p_prix DECIMAL(10,2), IN p_taux INT) augmente un prix d'un pourcentage. Appelle chacune et affiche les résultats. Puis liste les procédures de la base.
(1) Écris la fonction uf_age(d DATE) qui renvoie l'âge en années (TIMESTAMPDIFF). (2) Utilise-la pour afficher le nom et l'âge de chaque client. (3) Écris la fonction uf_prix_ttc(pu DECIMAL(10,2)) qui renvoie le prix TTC (TVA 20 %). Quelle déclaration informative choisis-tu pour chacune ?
Mets l'exemple des quatre blocs imbriqués (section 2) dans une procédure bloc_demo et exécute-la. Dans quel ordre s'affichent les messages ?
Le résumé écrit COUNT (*) avec un espace. Dans MySQL, un espace entre le nom d'une fonction intégrée et la parenthèse provoque l'erreur « FUNCTION eshop_app_db.COUNT does not exist » : écris COUNT(*), collé.
Sur un serveur MySQL 8 avec la journalisation binaire activée (le cas par défaut), une fonction sans déclaration informative est refusée avec l'erreur 1418 (This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA…). Mets toujours DETERMINISTIC ou READS SQL DATA (ou, sur ton propre PC seulement, SET GLOBAL log_bin_trust_function_creators = 1;).
Ce que tu vas voir (jusqu'à la fin) : les chapitres « Function Syntax », « Create Function », « Create Function in phpMyAdmin », « Call Function ». Le formateur montre la syntaxe de CREATE FUNCTION, crée une fonction en SQL puis dans phpMyAdmin, et l'appelle dans un SELECT. La vidéo est en anglais : active les sous-titres français.
Le délimiteur : DELIMITER $$
;.;. Sans précaution, MySQL s'arrêterait au premier ; et la création échouerait.DELIMITER $$ change le délimiteur : tout le code jusqu'à $$ est envoyé comme une seule unité. Après END $$, on remet le délimiteur normal avec DELIMITER ;.Ce que tu vas voir (15 min) : les chapitres « Qu'est-ce qu'une procédure stockée ? », « Création d'une procédure stockée », « Utiliser une procédure stockée ». Le formateur explique ce qu'est une procédure stockée, en crée une (avec DELIMITER) puis l'exécute avec CALL.
Tableau: Les trois modes de passage des paramètres
Le mode IN : le programme appelant doit passer un argument, transmis par valeur (l'exemple sp_client_par_ville ci-dessus).
Le mode OUT : on passe une variable de session (@total, chapitre 3) que la procédure remplit :
DELIMITER $$
CREATE PROCEDURE sp_nb_client_par_ville(IN ville VARCHAR(50), OUT nombre_clients INT)
BEGIN
SELECT COUNT(*) INTO nombre_clients
FROM clients
WHERE UPPER(adresse) = UPPER(ville);
END $$
DELIMITER ;
-- Appel
CALL sp_nb_client_par_ville("agadir", @total); -- @total est une variable de session
SELECT @total; -- 2SELECT … INTO variable met le résultat de la requête dans la variable.
Le résumé donne le même nom sp_client_par_ville à la procédure IN et à la procédure OUT : la deuxième création échoue (PROCEDURE sp_client_par_ville already exists). On renomme la seconde sp_nb_client_par_ville (ou on supprime la première avant).
Le mode INOUT :
DELIMITER $$
CREATE PROCEDURE SetCounter (INOUT counter INT, IN inc INT)
BEGIN
SET counter = counter + inc;
END $$
DELIMITER ;
-- Appel
SET @counter = 1;
CALL SetCounter(@counter, 1); -- 2
CALL SetCounter(@counter, 1); -- 3
CALL SetCounter(@counter, 5); -- 8
SELECT @counter; -- 8Les appels sont écrits CALL SetCounter (@counter’, 1); : l'apostrophe après @counter est une erreur de recopie (erreur de syntaxe). Les commentaires écrits - - (avec un espace) ne sont pas des commentaires : en SQL, c'est -- (deux tirets collés, puis un espace).
Ce que tu vas voir (16 min) : les chapitres « Paramètres d'une procédure stockée », « Exemple avec un paramètre OUT », « Exemple avec un paramètre IN et un paramètre OUT ». Il présente les paramètres d'une procédure, puis deux exemples : un paramètre OUT, et un paramètre IN avec un paramètre OUT récupéré dans une variable de session.
Supprimer, modifier et lister les procédures
DROP PROCEDURE [IF EXISTS] nom_procedure_stockée;
DROP PROCEDURE IF EXISTS SetCounter;Pour modifier une procédure, on la supprime puis on la recrée (DROP PROCEDURE puis CREATE PROCEDURE). MySQL Workbench a un outil pour modifier rapidement une procédure (clic droit → Alter Stored Procedure).
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition];
SHOW PROCEDURE STATUS; -- toutes les procédures
SHOW PROCEDURE STATUS WHERE db = 'eshop_app_db'; -- celles de la base eshop_app_db
SHOW PROCEDURE STATUS LIKE '%clients%'; -- celles dont le nom contient « clients »SELECT routine_name
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
AND routine_schema = 'eshop_app_db';information_schema est une base système qui décrit toutes les autres : ses tables listent les bases, les tables, les colonnes, les procédures (routines)…
Ce que tu vas voir (5 min) : les chapitres « Possibilités et limites des procédures stockées », « Avantages et inconvénients ». Il termine par les possibilités, les limites, les avantages et les inconvénients des procédures stockées.