Module 1Maîtriser le langage de programmation procédurale sous MySQL
Chapitre 8 sur 12
Fonction ou procédure, l'appel d'une fonction dans une procédure, et le TP de synthèse « enregistrer une vente ».
Objectifs
Avec ce chapitre, tu sauras…
SHOW CREATE ;:::savoir Ce chapitre dans le résumé La section 9 du résumé (« Créer les procédures stockées et les fonctions ») ne contient qu'une diapositive vide : les fonctions et les procédures ont déjà été présentées à la section 3 (chapitre 2 de ce cours). Ce chapitre est donc un chapitre de synthèse : il réunit les notions des chapitres 2 à 7 dans le cas d'utilisation donné par le résumé, l'enregistrement d'une vente. :::
Leçon 1
| Fonction | Procédure stockée | |
|---|---|---|
| création | CREATE FUNCTION f(p type) RETURNS type … | CREATE PROCEDURE p(IN …, OUT …, INOUT …) |
| appel | dans une expression : SELECT f(x), WHERE f(x) > 0, SET v = f(x) | CALL p(…), seule |
| résultat | une seule valeur, avec RETURN (obligatoire) | des résultats SELECT et des paramètres OUT / INOUT |
| paramètres | seulement en entrée (pas de mot IN) | IN, OUT, INOUT |
transactions (COMMIT, ROLLBACK) | interdites | autorisées |
| renvoyer une table | impossible | possible (un SELECT dans la procédure) |
| déclaration informative | DETERMINISTIC, READS SQL DATA… (exigée en MySQL 8) | facultative |
Leçon 2
Leçon 3
| Instruction | Affiche |
|---|---|
SHOW CREATE PROCEDURE nom; | le code complet de la procédure |
SHOW CREATE FUNCTION nom; | le code complet de la fonction |
SHOW PROCEDURE STATUS WHERE db = 'eshop_app_db'; | la liste des procédures |
SHOW FUNCTION STATUS WHERE db = 'eshop_app_db'; | la liste des fonctions |
DROP FUNCTION IF EXISTS nom; | supprime une fonction |
Leçon 4
Vidéo · À regarder
FRBase de données : Gestion des erreurs en MySQL
Algomius · YouTube
1Pour l'utiliser dans un WHERE, il faut écrire…
2Une fonction peut faire un COMMIT.
3Pour renvoyer la liste des produits d'une catégorie, on écrit…
4SHOW CREATE PROCEDURE p; affiche…
5Une procédure peut appeler une fonction.
6Les paramètres d'une fonction sont…
L'essentiel du chapitre
RETURN), s'utilise dans une expression, paramètres en entrée, pas de transaction.CALL, paramètres IN / OUT / INOUT, peut renvoyer des lignes et gérer une transaction.SHOW CREATE PROCEDURE | FUNCTION nom;, SHOW PROCEDURE | FUNCTION STATUS WHERE db = '…';, DROP … IF EXISTS.EXIT … SQLEXCEPTION (ROLLBACK; RESIGNAL;) → START TRANSACTION → contrôles avec SIGNAL → modifications → COMMIT.Une procédure peut appeler une fonction (et une autre procédure avec CALL) : le code est modulaire. On écrit le calcul une seule fois dans une fonction, et on le réutilise partout.
DELIMITER $$
CREATE FUNCTION uf_montant_ttc(p_ht DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN ROUND(p_ht * 1.20, 2); -- TVA de 20 %
END $$
DELIMITER ;
-- dans une procédure :
SET p_ttc = uf_montant_ttc(v_pu * p_qte);
-- dans une requête :
SELECT designation, pu, uf_montant_ttc(pu) AS pu_ttc FROM produit;Dans MySQL Workbench, les programmes stockés sont dans le panneau Navigator, sous la base : dossiers Stored Procedures et Functions ; clic droit → Alter pour modifier.
On reprend le cas d'utilisation du résumé (chapitre 2) : enregistrer une vente demande de calculer le montant, la taxe, de retirer l'article du stock, de vérifier le stock et de produire un reçu. Tout est réuni dans une procédure.
Les tables
DROP TABLE IF EXISTS ligne_commande;
DROP TABLE IF EXISTS commandes;
CREATE TABLE commandes (
id INT PRIMARY KEY AUTO_INCREMENT,
date_cmd DATE NOT NULL,
statut VARCHAR(20) DEFAULT 'en cours',
id_client INT,
montant DECIMAL(10,2) DEFAULT 0,
FOREIGN KEY (id_client) REFERENCES clients(id)
) ENGINE = InnoDB;
CREATE TABLE ligne_commande (
id_cmd INT,
id_produit INT,
qte INT NOT NULL,
pu DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id_cmd, id_produit),
FOREIGN KEY (id_cmd) REFERENCES commandes(id),
FOREIGN KEY (id_produit) REFERENCES produit(id)
) ENGINE = InnoDB;Le cahier des charges de la procédure
Écris la procédure sp_enregistrer_vente(IN p_id_client INT, IN p_id_produit INT, IN p_qte INT, OUT p_id_cmd INT, OUT p_ttc DECIMAL(10,2)). Elle doit :
uf_montant_ttc).La solution complète est dans le corrigé 8.4 : essaie d'abord seul, en reprenant un exemple de chaque chapitre.
Ce que tu vas voir (14 min) : les chapitres « Erreur dans une transaction ». Il gère une erreur dans une transaction : le handler annule tout avec ROLLBACK. C'est le modèle de la procédure sp_enregistrer_vente.
Réalise la procédure sp_enregistrer_vente de la section 4. Teste : (1) une vente correcte (client 1, 3 souris) ; (2) un stock insuffisant (100 smartphones) ; (3) un produit inexistant ; (4) un client inexistant. Vérifie après chaque test le contenu des tables commandes et produit.
Pour chaque besoin, dis s'il faut une fonction ou une procédure, puis écris-la : (1) le nombre de commandes d'un client ; (2) passer toutes les commandes « en cours » de plus de 30 jours au statut « annulée » et renvoyer le nombre de commandes modifiées ; (3) le chiffre d'affaires (somme des montants) d'un mois donné.
Écris la procédure sp_recu(IN p_id_cmd INT) qui affiche le reçu d'une commande : nom du client, date, chaque ligne (désignation, quantité, prix, sous-total), puis le total HT et le total TTC (avec uf_montant_ttc).