Maîtrise du SQL Procédural avec MySQL

DROP FUNCTION IF EXISTS format_date_manuel;
DELIMITER $$ 
CREATE FUNCTION format_date_manuel(d DATE)
    RETURNS VARCHAR(50)
    DETERMINISTIC
BEGIN  
    DECLARE nom_mois VARCHAR(20);
    SET nom_mois = CASE MONTH(d) 
        WHEN 1 THEN 'janvier'   WHEN 7 THEN 'juillet'
        WHEN 2 THEN 'février'   WHEN 8 THEN 'août'
        WHEN 3 THEN 'mars'      WHEN 9 THEN 'septembre'
        WHEN 4 THEN 'avril'     WHEN 10 THEN 'octobre'
        WHEN 5 THEN 'mai'       WHEN 11 THEN 'novembre'
        WHEN 6 THEN 'juin'      WHEN 12 THEN 'décembre'
    END;
    RETURN CONCAT(DAY(d), ' ', nom_mois, ' ', YEAR(d));
END$$
DELIMITER ;

DROP FUNCTION IF EXISTS date_format_fr;
DELIMITER $$ 
CREATE FUNCTION date_format_fr (d DATE) 
    RETURNS VARCHAR(50) 
    DETERMINISTIC 
BEGIN 
	DECLARE resultat VARCHAR(50);
	DECLARE ancienne_locale VARCHAR(10);
	SET ancienne_locale = @@lc_time_names;
	SET lc_time_names = 'fr_FR';
	SET resultat = DATE_FORMAT(d, '%e %M %Y'); 
	SET lc_time_names = ancienne_locale;
	RETURN resultat;
END$$ 
DELIMITER ;

SELECT date_format_fr('2024-02-01');

DROP FUNCTION IF EXISTS calcul_ecart_date;
DELIMITER $$ 
CREATE FUNCTION calcul_ecart_date(
    date_debut DATETIME,
    date_fin DATETIME,
    unite VARCHAR(20)
)
	RETURNS BIGINT 
    DETERMINISTIC
BEGIN 
	DECLARE resultat BIGINT;
    SET unite = LOWER(unite); -- Rendre la comparaison insensible à la casse
    
    SET resultat = CASE unite
		WHEN 'annee' THEN TIMESTAMPDIFF(YEAR, date_debut, date_fin)
		WHEN 'mois' THEN TIMESTAMPDIFF(MONTH, date_debut, date_fin)
		WHEN 'jour' THEN TIMESTAMPDIFF(DAY, date_debut, date_fin)
        WHEN 'heure' THEN TIMESTAMPDIFF(HOUR, date_debut, date_fin)
        WHEN 'minute' THEN TIMESTAMPDIFF(MINUTE, date_debut, date_fin)
        WHEN 'seconde' THEN TIMESTAMPDIFF(SECOND, date_debut, date_fin)
		ELSE NULL -- Retourne NULL si l'unité est invalide
	END;
	RETURN resultat;
END$$    
DELIMITER ;

SELECT calcul_ecart_date('2025-10-10 12:15:00', '2025-10-11 14:18:03', 'heure') AS 'Ecart en Heures';
SELECT calcul_ecart_date('2025-10-10 12:15:00', '2026-11-15 12:15:00', 'Mois') AS 'Ecart en Mois';

DROP FUNCTION IF EXISTS q1_nbpilotes; 
DELIMITER $$
CREATE FUNCTION q1_nbpilotes (nb INT)
    RETURNS INT
    READS SQL DATA
BEGIN 
	DECLARE total INT;
		-- On utilise une sous-requête (ou une CTE) pour d'abord compter les vols par pilote
		SELECT COUNT(*) INTO total FROM (
            SELECT numpil
            FROM Vol 
            GROUP BY numpil
            HAVING COUNT(*) > nb
        ) AS PilotesFiltres;
    RETURN total;
END$$ 
DELIMITER ;

SELECT q1_nbpilotes(5); -- Pilote 1 a 6 vols > 5. Résultat: 1
SELECT q1_nbpilotes(3); -- Pilote 1 (6), Pilote 3 (5). Résultat: 2

DROP FUNCTION IF EXISTS q2_duree_travail_annees;
DELIMITER $$
CREATE FUNCTION q2_duree_travail_annees(id_pilote INT)
RETURNS INT
READS SQL DATA
BEGIN
	DECLARE duree INT;
	SELECT TIMESTAMPDIFF(YEAR, datedebut, CURDATE()) INTO duree 
    FROM Pilote
    WHERE numpilote = id_pilote;
	RETURN duree;
END$$
DELIMITER ;

SELECT q2_duree_travail_annees(3); -- Pilote Youssef, a commencé en 2002

DROP FUNCTION IF EXISTS q3_avion_non_affectes;
DELIMITER $$
CREATE FUNCTION q3_avion_non_affectes()
RETURNS INT
READS SQL DATA 
BEGIN
	DECLARE total INT;
	SELECT COUNT(*) INTO total
	FROM Avion 
	WHERE numav NOT IN (SELECT DISTINCT numav FROM Vol);
    RETURN total;
END$$
DELIMITER ;

SELECT q3_avion_non_affectes(); -- Avion 4 n'a pas de vol. Résultat: 1

DROP FUNCTION IF EXISTS q4_plus_ancien_pilote_sur_avion;
DELIMITER $$
CREATE FUNCTION q4_plus_ancien_pilote_sur_avion(id_avion INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE id_pilote INT;
	SELECT p.numpilote INTO id_pilote
	FROM Pilote p
	JOIN Vol v ON p.numpilote = v.numpil
	WHERE v.numav = id_avion
	ORDER BY p.datedebut ASC 
	LIMIT 1;
    RETURN id_pilote;
END$$
DELIMITER ;

-- Sur l'avion 1, pilotes 1, 2, 3 ont volé. Le plus ancien est Youssef (3) qui a commencé en 2002.
SELECT q4_plus_ancien_pilote_sur_avion(1); 

-- 1. Nombre total d'employés
DROP FUNCTION IF EXISTS nombre_total_employes;
DELIMITER $$
CREATE FUNCTION nombre_total_employes() RETURNS INT READS SQL DATA
BEGIN RETURN (SELECT COUNT(*) FROM EMPLOYE); END$$
DELIMITER ;

-- 2. Somme totale des salaires
DROP FUNCTION IF EXISTS somme_salaires;
DELIMITER $$
CREATE FUNCTION somme_salaires() RETURNS FLOAT READS SQL DATA
BEGIN RETURN (SELECT SUM(SALAIRE) FROM EMPLOYE); END$$
DELIMITER ;

-- 3. Salaire minimum
DROP FUNCTION IF EXISTS salaire_minimum;
DELIMITER $$
CREATE FUNCTION salaire_minimum() RETURNS FLOAT READS SQL DATA
BEGIN RETURN (SELECT MIN(SALAIRE) FROM EMPLOYE); END$$
DELIMITER ;

-- 4. Salaire maximum
DROP FUNCTION IF EXISTS salaire_maximum;
DELIMITER $$
CREATE FUNCTION salaire_maximum() RETURNS FLOAT READS SQL DATA
BEGIN RETURN (SELECT MAX(SALAIRE) FROM EMPLOYE); END$$
DELIMITER ;

SELECT 
    nombre_total_employes() AS 'Nb Employés',
    somme_salaires() AS 'Masse Salariale',
    salaire_minimum() AS 'Salaire Min',
    salaire_maximum() AS 'Salaire Max';

-- 1. Nombre d'employés par département
DROP FUNCTION IF EXISTS q6_nb_s;
DELIMITER $$
CREATE FUNCTION q6_nb_s(nom_d VARCHAR(50)) RETURNS INT READS SQL DATA
BEGIN RETURN (SELECT COUNT(*) FROM EMPLOYE JOIN DEPARTEMENT USING(ID_DEP) WHERE NOM_DEP = nom_d); END$$
DELIMITER ;

-- 2. Somme des salaires par département
DROP FUNCTION IF EXISTS q7_sum_s;
DELIMITER $$
CREATE FUNCTION q7_sum_s(nom_d VARCHAR(50)) RETURNS FLOAT READS SQL DATA
BEGIN RETURN (SELECT SUM(SALAIRE) FROM EMPLOYE JOIN DEPARTEMENT USING(ID_DEP) WHERE NOM_DEP = nom_d); END$$
DELIMITER ;

-- Et ainsi de suite pour MIN et MAX...

-- Note: Les fonctions pour MIN(q8_min_s) et MAX(q9_max_s) doivent aussi être créées sur le même modèle.
SELECT 
    UPPER(NOM_DEP) AS 'Département', 
    q6_nb_s(NOM_DEP) AS 'Nb Employés', 
    q7_sum_s(NOM_DEP) AS 'Masse Salariale', 
    q8_min_s(NOM_DEP) AS 'Salaire Min', 
    q9_max_s(NOM_DEP) AS 'Salaire Max' 
FROM 
    DEPARTEMENT;

-- 1. Afficher tous les produits
DROP PROCEDURE IF EXISTS q1_prdt;
DELIMITER &&
CREATE PROCEDURE q1_prdt()
BEGIN
	SELECT * FROM produit;
END&&
DELIMITER ;
CALL q1_prdt();

-- 2. Afficher les produits avec stock faible
DROP PROCEDURE IF EXISTS q2_stockfaible;
DELIMITER &&
CREATE PROCEDURE q2_stockfaible()
BEGIN
	SELECT libelle FROM produit WHERE stock < 10;
END&&
DELIMITER ;
CALL q2_stockfaible();

-- 3. Afficher les détails d'un produit spécifique
DROP PROCEDURE IF EXISTS q3_aff_prod;
DELIMITER &&
CREATE PROCEDURE q3_aff_prod(num_prod INT)
BEGIN
	SELECT CONCAT('Libellé: ', libelle, ", PU: ", pu, ", Stock: ", stock) AS 'message'
	FROM produit
	WHERE numProduit = num_prod;
END&&
DELIMITER ;
CALL q3_aff_prod(3);

-- 4. Supprimer un produit (déjà vu au chapitre 6)
-- CALL q4_ps_supprimer_produit(9);

-- La solution est la procédure ex2_vente détaillée au chapitre 6.
DROP PROCEDURE IF EXISTS ex2_vente;
DELIMITER $$
CREATE PROCEDURE ex2_vente(IN num INT, IN qte INT)
BEGIN
	DECLARE stock_reste INT;
	SELECT stock INTO stock_reste FROM produit WHERE numproduit=num;
	IF stock_reste < qte THEN 
		SELECT 'Opération impossible' AS 'message';
	ELSE
		UPDATE produit SET stock= stock_reste - qte WHERE numproduit=num;
		IF stock_reste-qte <10 THEN 
			SELECT CONCAT('Besoin de réapprovisionnement, reste : ', stock_reste-qte) AS 'message';
		ELSE
			SELECT CONCAT('Opération effectuée avec succès, reste : ', stock_reste-qte) AS 'message';
		END IF;
	END IF;
END$$
DELIMITER ;

CALL ex2_vente(4, 11);

DROP PROCEDURE IF EXISTS ex3_moyenne;
DELIMITER $$
CREATE PROCEDURE ex3_moyenne(OUT moyenne FLOAT)
BEGIN
	SELECT AVG(pu) INTO moyenne FROM produit;
END$$
DELIMITER ;

CALL ex3_moyenne(@m);
SELECT @m AS 'Prix Unitaire Moyen';

DROP PROCEDURE IF EXISTS ex4_factoriel;
DELIMITER //
CREATE PROCEDURE ex4_factoriel(IN nb INT, OUT factoriel BIGINT)
BEGIN 
    DECLARE i INT DEFAULT 1;

    IF nb < 0 THEN 
		SET factoriel = NULL; -- Factoriel n'est pas défini pour les négatifs
	ELSE
        SET factoriel = 1;
		REPEAT 
			SET factoriel = i * factoriel;
            SET i = i + 1;
        UNTIL i > nb END REPEAT;
    END IF;
END//
DELIMITER ;

CALL ex4_factoriel(5, @f);
SELECT @f; -- Résultat: 120

DROP PROCEDURE IF EXISTS calcule;
DELIMITER $$ 
CREATE PROCEDURE calcule(IN a INT, IN b INT, IN op CHAR(1), OUT resultat FLOAT)
BEGIN 
	IF op IN ('/', '%') AND b=0 THEN
		SET resultat = NULL;
	ELSE
		SET resultat = CASE op 
			WHEN '+' THEN  a+b 
			WHEN '/' THEN  a/b 
			WHEN '*' THEN  a*b 
			WHEN '-' THEN  a-b 
			WHEN '%' THEN  a%b 
            ELSE NULL
		END;
	END IF;
END$$
DELIMITER ;

CALL calcule(5, 0, '/', @res);
SELECT @res; -- Résultat: NULL
CALL calcule(10, 3, '%', @res);
SELECT @res; -- Résultat: 1

DROP TRIGGER IF EXISTS after_insert_vol;
DELIMITER $$
CREATE TRIGGER after_insert_vol 
AFTER INSERT ON vol 
FOR EACH ROW
BEGIN 
    UPDATE pilote SET NBHV = ADDTIME(NBHV, TIMEDIFF(NEW.datea, NEW.dated))
    WHERE numpilote = NEW.numpil;
END$$
DELIMITER ;

DROP TRIGGER IF EXISTS after_delete_vol;
DELIMITER $$
CREATE TRIGGER after_delete_vol 
AFTER DELETE ON vol 
FOR EACH ROW
BEGIN 
	UPDATE pilote SET NBHV = SUBTIME(NBHV, TIMEDIFF(OLD.datea, OLD.dated))
    WHERE numpilote = OLD.numpil;
END$$
DELIMITER ;

DROP TRIGGER IF EXISTS after_update_vol;
DELIMITER &&
CREATE TRIGGER after_update_vol
AFTER UPDATE ON vol
FOR EACH ROW
BEGIN
    UPDATE pilote
    SET NBHV = ADDTIME(
                        SUBTIME(NBHV, TIMEDIFF(OLD.datea, OLD.dated)),
                        TIMEDIFF(NEW.datea, NEW.dated)
					)
    WHERE numpilote = NEW.numpil;
END&&
DELIMITER ;

-- Trigger pour INSERT
DROP TRIGGER IF EXISTS after_insert_employe;
DELIMITER $$
CREATE TRIGGER after_insert_employe AFTER INSERT ON employe FOR EACH ROW
BEGIN
    UPDATE departement SET salaire_moyen = (SELECT AVG(salaire) FROM employe WHERE id_dep = NEW.id_dep) 
    WHERE id_dep = NEW.id_dep;
END$$
DELIMITER ;

-- Trigger pour UPDATE
DROP TRIGGER IF EXISTS after_update_employe;
DELIMITER $$
CREATE TRIGGER after_update_employe AFTER UPDATE ON employe FOR EACH ROW
BEGIN
    -- Mise à jour de l'ancien département si l'employé a changé de département
    IF OLD.id_dep != NEW.id_dep THEN
        UPDATE departement SET salaire_moyen = (SELECT AVG(salaire) FROM employe WHERE id_dep = OLD.id_dep) 
        WHERE id_dep = OLD.id_dep;
    END IF;
    -- Mise à jour du nouveau département
    UPDATE departement SET salaire_moyen = (SELECT AVG(salaire) FROM employe WHERE id_dep = NEW.id_dep) 
    WHERE id_dep = NEW.id_dep;
END$$
DELIMITER ;

-- Trigger pour DELETE
DROP TRIGGER IF EXISTS after_delete_employe;
DELIMITER $$
CREATE TRIGGER after_delete_employe AFTER DELETE ON employe FOR EACH ROW
BEGIN
	UPDATE departement SET salaire_moyen = (SELECT AVG(salaire) FROM employe WHERE id_dep = OLD.id_dep) 
    WHERE id_dep = OLD.id_dep;
END$$
DELIMITER ;

-- Trigger pour INSERT
DROP TRIGGER IF EXISTS after_insert_composition;
DELIMITER $$
CREATE TRIGGER after_insert_composition AFTER INSERT ON composition_recette FOR EACH ROW 
BEGIN
    UPDATE recettes SET prix = get_recette_price(NEW.numrec) WHERE numrec = NEW.numrec;
END$$
DELIMITER ;

-- Trigger pour DELETE
DROP TRIGGER IF EXISTS after_delete_composition;
DELIMITER $$
CREATE TRIGGER after_delete_composition AFTER DELETE ON composition_recette FOR EACH ROW 
BEGIN
    UPDATE recettes SET prix = get_recette_price(OLD.numrec) WHERE numrec = OLD.numrec;
END$$
DELIMITER ;

-- Trigger pour UPDATE
DROP TRIGGER IF EXISTS after_update_composition;
DELIMITER $$
CREATE TRIGGER after_update_composition AFTER UPDATE ON composition_recette FOR EACH ROW 
BEGIN
    UPDATE recettes SET prix = get_recette_price(NEW.numrec) WHERE numrec = NEW.numrec;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS ps_transfert_chaises;
DELIMITER $$
CREATE PROCEDURE ps_transfert_chaises(salle_origine INT, salle_dest INT, nb_chaises INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
		BEGIN
			ROLLBACK;
            SELECT "Impossible d’effectuer le transfert : une contrainte a été violée." AS 'message';
		END;

	START TRANSACTION;
        UPDATE salle SET nombreChaises = nombreChaises - nb_chaises WHERE NumSalle = salle_origine;
        UPDATE salle SET nombreChaises = nombreChaises + nb_chaises WHERE NumSalle = salle_dest;
        INSERT INTO transfert VALUES (salle_origine, salle_dest, CURDATE(), nb_chaises);
	COMMIT;
    SELECT "Transfert effectué avec succès." AS 'message';
END$$
DELIMITER ;

-- Test 1 : Transfert valide (26-4=22 ; 26+4=30. OK)
CALL ps_transfert_chaises(2, 3, 4);
-- Test 2 : Transfert invalide (22-4=18. NON OK)
CALL ps_transfert_chaises(2, 3, 4);

USE commerce;
DROP PROCEDURE IF EXISTS ps_safe_add_product;
DELIMITER $$
CREATE PROCEDURE ps_safe_add_product(p_name VARCHAR(100), p_price DOUBLE)
BEGIN
    DECLARE err_msg TEXT;
    DECLARE err_code INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 err_code = MYSQL_ERRNO, err_msg = MESSAGE_TEXT;
        SELECT JSON_OBJECT(
            'statut', 'echec',
            'erreur_code', err_code,
            'message', err_msg
        ) AS 'resultat';
    END;
    
    INSERT INTO produit(nom, prix) VALUES (p_name, p_price);
    
    SELECT JSON_OBJECT(
        'statut', 'succes',
        'id_produit_insere', LAST_INSERT_ID()
    ) AS 'resultat';
END$$
DELIMITER ;

-- Test 1: Succès
CALL ps_safe_add_product('Ecran 4K', 4500);
-- Test 2: Nom dupliqué
CALL ps_safe_add_product('Ecran 4K', 4600);
-- Test 3: Prix négatif
CALL ps_safe_add_product('Webcam', -50);

USE bank;
DROP PROCEDURE IF EXISTS secure_transfert;
DELIMITER $$
CREATE PROCEDURE secure_transfert(acc_from INT, acc_to INT, amount DECIMAL(8,2))
BEGIN
    DECLARE funds_from DECIMAL(8,2);
    DECLARE acc_from_exists, acc_to_exists INT;
    
    -- Déclarer des conditions pour nos erreurs métier
    DECLARE err_no_funds CONDITION FOR SQLSTATE '45000';
    DECLARE err_acc_not_found CONDITION FOR SQLSTATE '45001';

    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'Erreur SQL inattendue. Transaction annulée.' AS 'statut'; END;

    START TRANSACTION;
    
    -- Vérifier l'existence des comptes
    SELECT COUNT(*) INTO acc_from_exists FROM account WHERE account_number = acc_from;
    SELECT COUNT(*) INTO acc_to_exists FROM account WHERE account_number = acc_to;

    IF acc_from_exists = 0 OR acc_to_exists = 0 THEN
        SIGNAL err_acc_not_found SET MESSAGE_TEXT = 'Compte source ou destination introuvable.';
    END IF;
    
    -- Vérifier les fonds (en bloquant la ligne pour éviter les lectures concurrentes)
    SELECT funds INTO funds_from FROM account WHERE account_number = acc_from FOR UPDATE;
    IF funds_from < amount THEN
        SIGNAL err_no_funds SET MESSAGE_TEXT = 'Fonds insuffisants sur le compte source.';
    END IF;
    
    -- Si tout est OK, on procède
    UPDATE account SET funds = funds - amount WHERE account_number = acc_from;
    UPDATE account SET funds = funds + amount WHERE account_number = acc_to;
    
    COMMIT;
    SELECT 'Transfert réussi.' AS 'statut';
END$$
DELIMITER ;

-- Test 1: Compte inexistant
CALL secure_transfert(1, 99, 100);
-- Test 2: Fonds insuffisants
CALL secure_transfert(1, 2, 50000);
-- Test 3: Succès
CALL secure_transfert(2, 1, 500);

USE cuisine;
DROP PROCEDURE IF EXISTS Ps9_rapport_complet;
DELIMITER $$
CREATE PROCEDURE Ps9_rapport_complet()
BEGIN
	DECLARE flag BOOLEAN DEFAULT FALSE;
	DECLARE idRec INT;
    DECLARE nameRec VARCHAR(50);
    DECLARE methode VARCHAR(250);
    DECLARE tpRec VARCHAR(20);
    DECLARE prix DOUBLE;
    
    DECLARE c CURSOR FOR SELECT numrec, nomrec, tempsPreparation, methodePreparation FROM recettes;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag=TRUE;
    
    OPEN c;
    l:LOOP
		FETCH c INTO idRec, nameRec, tpRec, methode;
		IF flag THEN 
			LEAVE l;
		END IF;
        
		SELECT CONCAT("Recette : ", nameRec, ", temps de préparation : ", tpRec, " min") AS "--- Message ---";
        
        SELECT NomIng, QteUtilisee FROM composition_recette JOIN ingredients USING (NumIng) WHERE NumRec=idRec;
		
        SELECT CONCAT("Sa méthode de préparation est : ", methode) AS "--- Méthode ---";
    
	    SELECT SUM(puIng * qteUtilisee) INTO prix FROM ingredients JOIN composition_recette USING(numIng) WHERE numRec=idRec;
	    IF prix < 50 THEN
		   SELECT CONCAT("Prix intéressant : ", FORMAT(prix, 2), " DH") AS "--- Avis Prix ---" ;
	    END IF;
    
    END LOOP l;
    CLOSE c;
END$$
DELIMITER ;
CALL Ps9_rapport_complet();

USE vols;
DROP PROCEDURE IF EXISTS rapport_vols_pilotes;
DELIMITER $$
CREATE PROCEDURE rapport_vols_pilotes()
BEGIN
	DECLARE flag_p BOOLEAN DEFAULT FALSE;
	DECLARE idpilote INT;
    DECLARE nomP VARCHAR(50);
    
    -- Curseur externe pour les pilotes
    DECLARE c_pilotes CURSOR FOR SELECT numpilote, nom FROM pilote;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag_p = TRUE;
    
    OPEN c_pilotes;
		boucle_pilotes: LOOP
			FETCH c_pilotes INTO idpilote, nomP;
			IF flag_p THEN LEAVE boucle_pilotes; END IF;
			
            SELECT CONCAT("--- Le pilote '", nomP, "' est affecté aux vols : ---") AS "Pilote";
            
            -- Bloc interne pour le curseur des vols
            BEGIN
				DECLARE flag_v BOOLEAN DEFAULT FALSE;
                DECLARE vd, va VARCHAR(50);
                
                -- Curseur interne pour les vols du pilote en cours
                DECLARE c_vols CURSOR FOR SELECT villed, villea FROM vol WHERE numpil = idpilote;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag_v = TRUE;
                
                OPEN c_vols;
					boucle_vols: LOOP
						FETCH c_vols INTO vd, va;
						IF flag_v THEN LEAVE boucle_vols; END IF;
                        SELECT CONCAT("     Départ : ", vd, " | Arrivée : ", va) AS "Vol";
                    END LOOP boucle_vols;
                CLOSE c_vols;
            END;
		END LOOP boucle_pilotes;
    CLOSE c_pilotes;
END$$
DELIMITER ;
CALL rapport_vols_pilotes();

ALTER TABLE pilote ADD salaire DOUBLE;
DROP PROCEDURE IF EXISTS maj_salaires_pilotes;
DELIMITER $$
CREATE PROCEDURE maj_salaires_pilotes()
BEGIN
	DECLARE flag BOOLEAN DEFAULT FALSE;
	DECLARE idpilote INT;
    DECLARE salaire_actuel, salaire_nv DOUBLE;
    DECLARE nb_vols INT;
    
    DECLARE c CURSOR FOR SELECT  numpilote, salaire FROM pilote;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
    
    OPEN c;
		b1: LOOP
			FETCH c INTO idpilote, salaire_actuel;
			IF flag THEN LEAVE b1; END IF;
			
            SELECT COUNT(*) INTO nb_vols FROM vol WHERE numPil = idpilote;
            
            CASE
                WHEN nb_vols = 0 THEN SET salaire_nv = 5000;
                WHEN nb_vols BETWEEN 1 AND 3 THEN SET salaire_nv = 7000;
                ELSE SET salaire_nv = 8000;
            END CASE;

            UPDATE pilote SET salaire = salaire_nv WHERE numpilote = idpilote;
            SELECT CONCAT("Pilote ID ", idpilote, ": Ancien salaire=", IFNULL(salaire_actuel, 0), ", Nouveau salaire=", salaire_nv) AS "Mise à jour";
		END LOOP b1;
    CLOSE c;
END$$
DELIMITER ;
CALL maj_salaires_pilotes();

DROP PROCEDURE IF EXISTS affecter_vacances;
DELIMITER $$
CREATE PROCEDURE affecter_vacances()
BEGIN
	DECLARE flag BOOLEAN DEFAULT FALSE;
	DECLARE mat INT;
    DECLARE c CURSOR FOR SELECT matricule FROM employe WHERE etat = 'fatigué';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
    
    OPEN c;
		b1: LOOP
			FETCH c INTO mat;
			IF flag THEN LEAVE b1; END IF;
			INSERT INTO groupe VALUES (mat, 'besoin vacances');
		END LOOP b1;
    CLOSE c;
END$$
DELIMITER ;
CALL affecter_vacances();

-- 1. Création des rôles
CREATE ROLE IF NOT EXISTS 
    'gestionnaire_cuisine'@'localhost', 
    'cuisinier'@'localhost', 
    'stagiaire_cuisine'@'localhost';

-- 2. Attribution des privilèges aux rôles
GRANT ALL PRIVILEGES ON cuisine.* TO 'gestionnaire_cuisine'@'localhost';

GRANT SELECT ON cuisine.* TO 'cuisinier'@'localhost';
GRANT INSERT, UPDATE ON cuisine.Recettes TO 'cuisinier'@'localhost';
GRANT INSERT, UPDATE ON cuisine.Composition_Recette TO 'cuisinier'@'localhost';

GRANT SELECT ON cuisine.Ingredients TO 'stagiaire_cuisine'@'localhost';
GRANT SELECT ON cuisine.Recettes TO 'stagiaire_cuisine'@'localhost';

-- 3. Création des utilisateurs
CREATE USER IF NOT EXISTS 'ali_gestionnaire'@'localhost' IDENTIFIED BY 'pass1';
CREATE USER IF NOT EXISTS 'fatima_cuisiniere'@'localhost' IDENTIFIED BY 'pass2';
CREATE USER IF NOT EXISTS 'karim_stagiaire'@'localhost' IDENTIFIED BY 'pass3';

-- 4. Attribution des rôles aux utilisateurs
GRANT 'gestionnaire_cuisine'@'localhost' TO 'ali_gestionnaire'@'localhost';
GRANT 'cuisinier'@'localhost' TO 'fatima_cuisiniere'@'localhost';
GRANT 'stagiaire_cuisine'@'localhost' TO 'karim_stagiaire'@'localhost';

-- 5. Activation des rôles
SET DEFAULT ROLE ALL TO 
    'ali_gestionnaire'@'localhost',
    'fatima_cuisiniere'@'localhost',
    'karim_stagiaire'@'localhost';

FLUSH PRIVILEGES;

USE vols;

-- 1. Vue pour les vols en Boeing
CREATE OR REPLACE VIEW v_vols_boeing AS
    SELECT v.numvol, v.villed, v.villea, p.nom AS 'nom_pilote'
    FROM vol v
    JOIN avion a ON v.numav = a.numav
    JOIN pilote p ON v.numpil = p.numpilote
    WHERE a.typeav = 'boeing';

-- Utilisation de la vue
SELECT * FROM v_vols_boeing;


-- 2. Vue pour les pilotes expérimentés
CREATE OR REPLACE VIEW v_pilotes_experimentes AS
    SELECT nom, villepilote, datedebut
    FROM pilote
    WHERE datedebut < '2010-01-01';

-- Utilisation de la vue
SELECT * FROM v_pilotes_experimentes;