Chapitre 831 minAvancé
Exercices d'examen corrigés
Par Josselyn C., prof de BTS SIO. Mis à jour le .
Exercice 1 : procedure simple
Enonce : Ecrire une procedure rechercher_clients qui prend en parametre un nom (recherche partielle) et retourne tous les clients dont le nom contient cette chaine.
Correction :
DELIMITER //
CREATE PROCEDURE rechercher_clients(IN p_recherche VARCHAR(100))
BEGIN
SELECT id_client, nom, prenom, email, ville
FROM client
WHERE nom LIKE CONCAT('%', p_recherche, '%')
ORDER BY nom, prenom;
END //
DELIMITER ;
Test :
CALL rechercher_clients('Dupo');
-- Retourne Dupont, Dupond, etc.
Exercice 2 : procedure avec OUT
Enonce : Ecrire une procedure nombre_commandes_client qui prend en entree un identifiant client et retourne en sortie le nombre de commandes de ce client.
Correction :
DELIMITER //
CREATE PROCEDURE nombre_commandes_client(
IN p_id_client INT,
OUT p_nb_commandes INT
)
BEGIN
SELECT COUNT(*)
INTO p_nb_commandes
FROM commande
WHERE id_client = p_id_client;
END //
DELIMITER ;
Test :
CALL nombre_commandes_client(1, @nb);
SELECT @nb AS nombre_commandes;
Exercice 3 : procedure avec IF
Enonce : Ecrire une procedure appliquer_augmentation qui prend en entree un identifiant produit et un pourcentage. Si le pourcentage est superieur a 50, la procedure refuse l'augmentation (message d'erreur). Sinon, elle applique l'augmentation au prix HT.
Correction :
DELIMITER //
CREATE PROCEDURE appliquer_augmentation(
IN p_id_produit INT,
IN p_pourcentage DECIMAL(5,2),
OUT p_message VARCHAR(200)
)
BEGIN
DECLARE v_ancien_prix DECIMAL(10,2);
IF p_pourcentage > 50 THEN
SET p_message = 'Refus : augmentation superieure a 50%';
ELSEIF p_pourcentage <= 0 THEN
SET p_message = 'Refus : pourcentage invalide';
ELSE
SELECT prix_ht INTO v_ancien_prix
FROM produit
WHERE id_produit = p_id_produit;
IF v_ancien_prix IS NULL THEN
SET p_message = 'Erreur : produit inexistant';
ELSE
UPDATE produit
SET prix_ht = prix_ht * (1 + p_pourcentage / 100)
WHERE id_produit = p_id_produit;
SET p_message = CONCAT('Prix modifie de ', v_ancien_prix,
' a ', ROUND(v_ancien_prix * (1 + p_pourcentage / 100), 2));
END IF;
END IF;
END //
DELIMITER ;
Test :
CALL appliquer_augmentation(1, 10, @msg);
SELECT @msg;
CALL appliquer_augmentation(1, 60, @msg);
SELECT @msg;
-- 'Refus : augmentation superieure a 50%'
Exercice 4 : fonction stockee
Enonce : Ecrire une fonction montant_ligne qui prend en parametre un identifiant de ligne de commande et retourne le montant de cette ligne (quantite * prix unitaire).
Correction :
DELIMITER //
CREATE FUNCTION montant_ligne(p_id_ligne INT)
RETURNS DECIMAL(10,2)
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE v_montant DECIMAL(10,2);
SELECT quantite * prix_unitaire
INTO v_montant
FROM ligne_commande
WHERE id_ligne = p_id_ligne;
RETURN COALESCE(v_montant, 0);
END //
DELIMITER ;
Utilisation :
SELECT id_ligne, montant_ligne(id_ligne) AS montant
FROM ligne_commande
WHERE id_commande = 1;
Note : READS SQL DATA est une bonne pratique pour indiquer que la fonction lit des donnees en base sans les modifier. COALESCE(v_montant, 0) retourne 0 si la ligne n'existe pas (au lieu de NULL).
Exercice 5 : trigger d'audit
Enonce : Ecrire un trigger qui, a chaque suppression d'un client, enregistre l'evenement dans une table journal_suppression(id_journal INT AUTO_INCREMENT, table_concernee VARCHAR(50), id_enregistrement INT, date_suppression DATETIME, utilisateur VARCHAR(100)).
Correction :
D'abord, creer la table de journal si elle n'existe pas :
CREATE TABLE IF NOT EXISTS journal_suppression (
id_journal INT AUTO_INCREMENT PRIMARY KEY,
table_concernee VARCHAR(50),
id_enregistrement INT,
date_suppression DATETIME DEFAULT CURRENT_TIMESTAMP,
utilisateur VARCHAR(100)
);
Le trigger :
DELIMITER //
CREATE TRIGGER trg_journal_suppression_client
AFTER DELETE
ON client
FOR EACH ROW
BEGIN
INSERT INTO journal_suppression
(table_concernee, id_enregistrement, utilisateur)
VALUES
('client', OLD.id_client, CURRENT_USER());
END //
DELIMITER ;
Test :
DELETE FROM client WHERE id_client = 5;
SELECT * FROM journal_suppression;
-- table_concernee = 'client', id_enregistrement = 5
Exercice 6 : trigger de validation
Enonce : Ecrire un trigger qui empeche l'insertion d'une commande si le client n'existe pas dans la table client.
Correction :
DELIMITER //
CREATE TRIGGER trg_verifier_client_commande
BEFORE INSERT
ON commande
FOR EACH ROW
BEGIN
DECLARE v_existe INT;
SELECT COUNT(*) INTO v_existe
FROM client
WHERE id_client = NEW.id_client;
IF v_existe = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Impossible de creer la commande : client inexistant';
END IF;
END //
DELIMITER ;
Note : en pratique, la cle etrangere FOREIGN KEY (id_client) REFERENCES client(id_client) fait deja cette verification. Mais l'exercice peut demander de le faire avec un trigger, par exemple pour afficher un message d'erreur personnalise.
Test :
-- Client 999 n'existe pas
INSERT INTO commande (id_client) VALUES (999);
-- Erreur : Impossible de creer la commande : client inexistant
Exercice 7 : trigger de calcul automatique avec gestion du stock
Enonce : Ecrire un trigger qui, a chaque insertion dans ligne_commande, verifie que le stock est suffisant. Si oui, diminue le stock. Si non, refuse l'insertion.
Correction :
DELIMITER //
CREATE TRIGGER trg_verifier_et_diminuer_stock
BEFORE INSERT
ON ligne_commande
FOR EACH ROW
BEGIN
DECLARE v_stock_actuel INT;
SELECT stock INTO v_stock_actuel
FROM produit
WHERE id_produit = NEW.id_produit;
IF v_stock_actuel IS NULL THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Produit inexistant';
ELSEIF v_stock_actuel < NEW.quantite THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Stock insuffisant pour cette commande';
ELSE
UPDATE produit
SET stock = stock - NEW.quantite
WHERE id_produit = NEW.id_produit;
END IF;
END //
DELIMITER ;
Attention : on utilise ici BEFORE INSERT (et non AFTER INSERT) car on veut pouvoir refuser l'insertion. Un trigger AFTER ne peut pas annuler l'operation (la ligne est deja inseree).
Note : ce trigger fait la verification ET la mise a jour du stock dans le meme trigger. C'est une bonne pratique car cela garantit l'atomicite.
Test :
-- Supposons produit 1 avec stock = 10
INSERT INTO ligne_commande (id_commande, id_produit, quantite, prix_unitaire)
VALUES (1, 1, 5, 25.00);
-- OK, stock passe a 5
INSERT INTO ligne_commande (id_commande, id_produit, quantite, prix_unitaire)
VALUES (1, 1, 8, 25.00);
-- Erreur : Stock insuffisant pour cette commande
Exercice 8 : procedure avec curseur
Enonce : Ecrire une procedure rapport_clients_inactifs qui parcourt tous les clients n'ayant passe aucune commande depuis plus de 6 mois et les insere dans une table client_inactif(id_client INT, nom VARCHAR(100), prenom VARCHAR(100), derniere_commande DATE).
Correction :
CREATE TABLE IF NOT EXISTS client_inactif (
id_client INT PRIMARY KEY,
nom VARCHAR(100),
prenom VARCHAR(100),
derniere_commande DATE
);
DELIMITER //
CREATE PROCEDURE rapport_clients_inactifs()
BEGIN
DECLARE v_id INT;
DECLARE v_nom VARCHAR(100);
DECLARE v_prenom VARCHAR(100);
DECLARE v_derniere DATE;
DECLARE v_fini BOOLEAN DEFAULT FALSE;
DECLARE cur_inactifs CURSOR FOR
SELECT c.id_client, c.nom, c.prenom, MAX(co.date_commande) AS derniere
FROM client c
LEFT JOIN commande co ON c.id_client = co.id_client
GROUP BY c.id_client, c.nom, c.prenom
HAVING derniere IS NULL
OR derniere < DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_fini = TRUE;
-- Vider la table avant de la remplir
DELETE FROM client_inactif;
OPEN cur_inactifs;
boucle: LOOP
FETCH cur_inactifs INTO v_id, v_nom, v_prenom, v_derniere;
IF v_fini THEN
LEAVE boucle;
END IF;
INSERT INTO client_inactif (id_client, nom, prenom, derniere_commande)
VALUES (v_id, v_nom, v_prenom, v_derniere);
END LOOP boucle;
CLOSE cur_inactifs;
-- Afficher le resultat
SELECT * FROM client_inactif;
END //
DELIMITER ;
Test :
CALL rapport_clients_inactifs();
Exercice 9 : procedure avec transaction
Enonce : Ecrire une procedure passer_commande qui prend en parametre un identifiant client, un identifiant produit et une quantite. La procedure doit :
- Creer une commande.
- Creer une ligne de commande.
- Mettre a jour le stock.
- Mettre a jour le montant total de la commande.
- Si une erreur survient, tout annuler.
Correction :
DELIMITER //
CREATE PROCEDURE passer_commande(
IN p_id_client INT,
IN p_id_produit INT,
IN p_quantite INT,
OUT p_id_commande INT,
OUT p_message VARCHAR(200)
)
BEGIN
DECLARE v_prix DECIMAL(10,2);
DECLARE v_stock INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_id_commande = NULL;
SET p_message = 'Erreur technique : commande annulee';
END;
START TRANSACTION;
-- Recuperer le prix et le stock du produit (avec verrouillage)
SELECT prix_ht, stock INTO v_prix, v_stock
FROM produit
WHERE id_produit = p_id_produit
FOR UPDATE;
IF v_prix IS NULL THEN
ROLLBACK;
SET p_id_commande = NULL;
SET p_message = 'Erreur : produit inexistant';
ELSEIF v_stock < p_quantite THEN
ROLLBACK;
SET p_id_commande = NULL;
SET p_message = CONCAT('Erreur : stock insuffisant (disponible : ', v_stock, ')');
ELSE
-- 1. Creer la commande
INSERT INTO commande (id_client, montant_total)
VALUES (p_id_client, 0);
SET p_id_commande = LAST_INSERT_ID();
-- 2. Creer la ligne de commande
INSERT INTO ligne_commande (id_commande, id_produit, quantite, prix_unitaire)
VALUES (p_id_commande, p_id_produit, p_quantite, v_prix);
-- 3. Mettre a jour le stock
UPDATE produit
SET stock = stock - p_quantite
WHERE id_produit = p_id_produit;
-- 4. Mettre a jour le montant total
UPDATE commande
SET montant_total = p_quantite * v_prix
WHERE id_commande = p_id_commande;
COMMIT;
SET p_message = 'Commande creee avec succes';
END IF;
END //
DELIMITER ;
Note : dans cet exercice, on fait les mises a jour manuellement dans la procedure (stock, montant total). Si les triggers des exercices precedents sont actifs, il y aurait un double traitement. En examen, adapter selon le contexte : soit la procedure fait tout, soit les triggers font une partie du travail.
Test :
CALL passer_commande(1, 1, 3, @id_cmd, @msg);
SELECT @id_cmd, @msg;
SELECT * FROM commande WHERE id_commande = @id_cmd;
SELECT * FROM ligne_commande WHERE id_commande = @id_cmd;
SELECT stock FROM produit WHERE id_produit = 1;
Exercice 10 : identifier les erreurs
Enonce : Le code suivant contient 6 erreurs. Les identifier et les corriger.
CREATE PROCEDURE ajouter_produit(
nom VARCHAR(200),
prix DECIMAL(10,2),
OUT resultat VARCHAR(100)
)
BEGIN
INSERT INTO produit (designation, prix_ht, stock)
VALUES (nom, prix, 0)
SET resultat = 'Produit ajoute';
SELECT * FROM produit WHERE designation = nom;
END;
Correction :
Erreur 1 : DELIMITER // manquant avant la creation.
Erreur 2 : le parametre nom n'a pas le mot-cle IN (meme si IN est le defaut, il est recommande de le preciser a l'examen). Surtout, nom est un nom de parametre qui peut entrer en conflit avec un mot reserve ou une colonne. Il faudrait ecrire IN p_nom VARCHAR(200).
Erreur 3 : le parametre prix a le meme probleme. Ecrire IN p_prix DECIMAL(10,2).
Erreur 4 : il manque un point-virgule apres la ligne VALUES (nom, prix, 0).
Erreur 5 : dans le WHERE, designation = nom risque de ne pas fonctionner correctement si nom est interprete comme la colonne et non le parametre. En renommant le parametre p_nom, on ecrit WHERE designation = p_nom.
Erreur 6 : DELIMITER ; manquant apres la creation. Et le END doit etre suivi de //, pas de ;.
Code corrige :
DELIMITER //
CREATE PROCEDURE ajouter_produit(
IN p_nom VARCHAR(200),
IN p_prix DECIMAL(10,2),
OUT p_resultat VARCHAR(100)
)
BEGIN
INSERT INTO produit (designation, prix_ht, stock)
VALUES (p_nom, p_prix, 0);
SET p_resultat = 'Produit ajoute';
SELECT * FROM produit WHERE designation = p_nom;
END //
DELIMITER ;
Exercice 11 : trigger avec UPDATE et OLD/NEW
Enonce : Ecrire un trigger qui, lorsqu'on modifie le prix d'un produit, empeche une augmentation de plus de 25% en une seule fois.
Correction :
DELIMITER //
CREATE TRIGGER trg_limiter_augmentation_prix
BEFORE UPDATE
ON produit
FOR EACH ROW
BEGIN
IF NEW.prix_ht > OLD.prix_ht * 1.25 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Augmentation refusee : depassement de 25% du prix actuel';
END IF;
END //
DELIMITER ;
Test :
-- Supposons un produit avec prix_ht = 100.00
UPDATE produit SET prix_ht = 120.00 WHERE id_produit = 1;
-- OK (augmentation de 20%)
UPDATE produit SET prix_ht = 200.00 WHERE id_produit = 1;
-- Erreur : Augmentation refusee : depassement de 25% du prix actuel
Exercice 12 : procedure avec WHILE et calcul iteratif
Enonce : Ecrire une procedure simuler_interets qui prend en parametre un capital initial, un taux annuel (en pourcentage) et un nombre d'annees, et affiche l'evolution du capital annee par annee (interets composes).
Correction :
DELIMITER //
CREATE PROCEDURE simuler_interets(
IN p_capital DECIMAL(12,2),
IN p_taux DECIMAL(5,2),
IN p_annees INT
)
BEGIN
DECLARE v_annee INT DEFAULT 0;
DECLARE v_capital_courant DECIMAL(12,2);
-- Table temporaire pour stocker les resultats
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_simulation (
annee INT,
capital DECIMAL(12,2)
);
DELETE FROM tmp_simulation;
SET v_capital_courant = p_capital;
INSERT INTO tmp_simulation VALUES (0, v_capital_courant);
WHILE v_annee < p_annees DO
SET v_annee = v_annee + 1;
SET v_capital_courant = ROUND(v_capital_courant * (1 + p_taux / 100), 2);
INSERT INTO tmp_simulation VALUES (v_annee, v_capital_courant);
END WHILE;
SELECT * FROM tmp_simulation ORDER BY annee;
DROP TEMPORARY TABLE tmp_simulation;
END //
DELIMITER ;
Test :
CALL simuler_interets(10000, 5, 10);
-- Affiche le capital de l'annee 0 a l'annee 10
-- Annee 0 : 10000.00
-- Annee 1 : 10500.00
-- Annee 2 : 11025.00
-- ...
-- Annee 10 : 16288.95
Exercice 13 : completer une procedure
Enonce : Completer la procedure suivante qui doit retourner le client ayant le plus gros chiffre d'affaires.
DELIMITER //
CREATE PROCEDURE meilleur_client(
OUT p_id_client INT,
OUT p_nom VARCHAR(100),
OUT p_ca DECIMAL(12,2)
)
BEGIN
-- A COMPLETER
END //
DELIMITER ;
Correction :
DELIMITER //
CREATE PROCEDURE meilleur_client(
OUT p_id_client INT,
OUT p_nom VARCHAR(100),
OUT p_ca DECIMAL(12,2)
)
BEGIN
SELECT c.id_client, c.nom, SUM(co.montant_total)
INTO p_id_client, p_nom, p_ca
FROM client c
INNER JOIN commande co ON c.id_client = co.id_client
GROUP BY c.id_client, c.nom
ORDER BY SUM(co.montant_total) DESC
LIMIT 1;
END //
DELIMITER ;
Test :
CALL meilleur_client(@id, @nom, @ca);
SELECT @id AS id_client, @nom AS nom, @ca AS chiffre_affaires;
Résumé des syntaxes
Procedure
DELIMITER //
CREATE PROCEDURE nom(IN p1 TYPE, OUT p2 TYPE, INOUT p3 TYPE)
BEGIN
DECLARE v_var TYPE [DEFAULT valeur];
-- instructions
SET v_var = expression;
SELECT ... INTO v_var FROM ...;
END //
DELIMITER ;
CALL nom(valeur, @sortie, @entree_sortie);
DROP PROCEDURE IF EXISTS nom;
Fonction
DELIMITER //
CREATE FUNCTION nom(p1 TYPE, p2 TYPE)
RETURNS type_retour
DETERMINISTIC
BEGIN
DECLARE v_var TYPE;
-- calculs
RETURN v_var;
END //
DELIMITER ;
SELECT nom(valeur1, valeur2);
DROP FUNCTION IF EXISTS nom;
Trigger
DELIMITER //
CREATE TRIGGER nom
{BEFORE|AFTER} {INSERT|UPDATE|DELETE}
ON table
FOR EACH ROW
BEGIN
-- NEW.colonne (INSERT, UPDATE)
-- OLD.colonne (UPDATE, DELETE)
-- SET NEW.colonne = valeur (BEFORE seulement)
-- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'message';
END //
DELIMITER ;
DROP TRIGGER IF EXISTS nom;
Transaction
START TRANSACTION;
-- operations
COMMIT; -- ou ROLLBACK;
Curseur
DECLARE v_var TYPE;
DECLARE nom_curseur CURSOR FOR SELECT ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fini = TRUE;
OPEN nom_curseur;
boucle: LOOP
FETCH nom_curseur INTO v_var;
IF v_fini THEN LEAVE boucle; END IF;
-- traitement
END LOOP boucle;
CLOSE nom_curseur;