Les instructions :if,elseif,then,case

IF,ELSEIF,ELSE

Les instructions IF, ELSEIF, et ELSE sont utilisées dans les fonctions stockées MySQL pour prendre des décisions conditionnelles.
Elles permettent d'exécuter différentes parties du code en fonction de certaines conditions.

Exemple

-- Crée une nouvelle fonction stockée nommée "evaluer_note"

DELIMITER //
CREATE FUNCTION evaluer_note(
    IN note DECIMAL(5, 2) -- Paramètre d'entrée : note à évaluer
)
RETURNS VARCHAR(255) -- La fonction renverra une chaîne de caractères
DETERMINISTIC
BEGIN
    DECLARE evaluation VARCHAR(255); -- Variable pour stocker l'évaluation
    
    -- Instructions conditionnelles
    IF note >= 16 THEN
        SET evaluation = 'Très bien';
    ELSEIF note >= 14 THEN
        SET evaluation = 'Bien';
    ELSEIF note >= 12 THEN
        SET evaluation = 'Assez bien';
    ELSE
        SET evaluation = 'Insuffisant';
    END IF;
    
    -- Renvoie l'évaluation
    RETURN evaluation;
END//

-- Exemple d'appel de la fonction

SELECT evaluer_note(18); -- Renvoie 'Très bien'
SELECT evaluer_note(13); -- Renvoie 'Bien'
SELECT evaluer_note(11); -- Renvoie 'Assez bien'
SELECT evaluer_note(9);  -- Renvoie 'Insuffisant'

CASE THEN

L'instruction CASE en SQL est utilisée pour effectuer une évaluation conditionnelle et renvoyer une valeur spécifique en fonction de différentes conditions.

Exemple1

SELECT nom,
       age,
       CASE
           WHEN age < 18 THEN 'Mineur'
           WHEN age >= 18 AND age < 65 THEN 'Adulte'
           ELSE 'Senior'
       END AS categorie_age
FROM personnes;

Résultats:

+--------+-----+--------------+
| nom    | age | categorie_age|
+--------+-----+--------------+
| Alice  | 15  | Mineur       |
| Bob    | 25  | Adulte       |
| Carol  | 68  | Senior       |
| David  | 40  | Adulte       |
+--------+-----+--------------+

Exemple2

Crée une nouvelle fonction stockée nommée "evaluer_note_avec_case"

DELIMITER 
CREATE FUNCTION evaluer_note_avec_case(
    IN note DECIMAL(5, 2) -- Paramètre d'entrée : note à évaluer
)
RETURNS VARCHAR(255) -- La fonction renverra une chaîne de caractères
DETERMINISTIC
BEGIN
    DECLARE evaluation VARCHAR(255); -- Variable pour stocker l'évaluation

    -- Utilisation de l'instruction CASE pour évaluer la note
    SET evaluation = CASE
        WHEN note >= 16 THEN 'Très bien'
        WHEN note >= 14 THEN 'Bien'
        WHEN note >= 12 THEN 'Assez bien'
        ELSE 'Insuffisant'
    END;

    -- Renvoie l'évaluation
    RETURN evaluation;
END//

Exemple d'appel de la fonction

SELECT evaluer_note_avec_case(18); -- Renvoie 'Très bien'
SELECT evaluer_note_avec_case(13); -- Renvoie 'Bien'
SELECT evaluer_note_avec_case(11); -- Renvoie 'Assez bien'
SELECT evaluer_note_avec_case(9);  -- Renvoie 'Insuffisant'

Exemples

1.Q Créez une fonction stockée nommée "tranchePrixProduit" qui prend en entrée l'ID d'un produit et renvoie une catégorie de prix ("Faible", "Moyen" ou "Élevé") en fonction du prix du produit.

CREATE FUNCTION tranchePrixProduit(idProduit INT) RETURNS VARCHAR(10)
BEGIN
    DECLARE prixProduit FLOAT;
    SELECT prix INTO prixProduit FROM produits WHERE id = idProduit;
    
    IF prixProduit < 50 THEN
        RETURN 'Faible';
    ELSEIF prixProduit >= 50 AND prixProduit <= 100 THEN
        RETURN 'Moyen';
    ELSE
        RETURN 'Élevé';
    END IF;
END;

2.Q Créer une fonction stockée nommée "nomClient" qui prend en entrée l'ID d'un client et renvoie le nom complet du client avec une salutation en fonction du sexe du client?

CREATE FUNCTION nomClient(idClient INT) RETURNS VARCHAR(100)
BEGIN
    DECLARE nomComplet VARCHAR(100);
    DECLARE sexeClient CHAR(1);
    SELECT CONCAT(nom, ' ', prenom) INTO nomComplet, sexe INTO sexeClient FROM clients WHERE id = idClient;
    
    IF sexeClient = 'M' THEN
        RETURN 'Monsieur ' + nomComplet;
    ELSE
        RETURN 'Madame ' + nomComplet;
    END IF;
END;

3.Q Créez une fonction stockée nommée "disponibiliteProduit" qui prend en entrée l'ID d'un produit et renvoie "Disponible" si la quantité en stock est supérieure à zéro, sinon "Épuisé".

CREATE FUNCTION disponibiliteProduit(idProduit INT) RETURNS VARCHAR(10)
BEGIN
    DECLARE quantiteStock INT;
    SELECT quantiteStock INTO quantiteStock FROM produits WHERE id = idProduit;
    
    IF quantiteStock > 0 THEN
        RETURN 'Disponible';
    ELSE
        RETURN 'Épuisé';
    END IF;
END;

4.Q Créer une fonction stockée nommée "categorisationClient" qui prend en entrée l'ID d'un client et renvoie sa catégorie ("Nouveau", "Régulier", ou "Premium") en fonction du nombre total d'achats?

CREATE FUNCTION categorisationClient(idClient INT) RETURNS VARCHAR(10)
BEGIN
    DECLARE totalAchats INT;
    SELECT SUM(prixVente * QuantiteVendue) INTO totalAchats FROM ventes WHERE idClient = idClient;
    
    IF totalAchats < 100 THEN
        RETURN 'Nouveau';
    ELSEIF totalAchats >= 100 AND totalAchats < 500 THEN
        RETURN 'Régulier';
    ELSE
        RETURN 'Premium';
    END IF;
END;

5.Q Créez une fonction stockée nommée "remiseProduit" qui prend en entrée l'ID d'un produit et renvoie le pourcentage de remise applicable en fonction du prix du produit.

CREATE FUNCTION remiseProduit(idProduit INT) RETURNS DECIMAL(5,2)
BEGIN
    DECLARE prixProduit DECIMAL(5,2);
    SELECT prix INTO prixProduit FROM produits WHERE id = idProduit;
    
    IF prixProduit < 50 THEN
        RETURN 0.05; -- 5% de remise pour les produits à prix faible
    ELSEIF prixProduit >= 50 AND prixProduit < 100 THEN
        RETURN 0.1; -- 10% de remise pour les produits à prix moyen
    ELSE
        RETURN 0.15; -- 15% de remise pour les produits à prix élevé
    END IF;
END;

6.Q Créer une fonction stockée nommée "statutCommande" qui prend en entrée l'ID d'une commande et renvoie son statut ("En cours", "Livré" ou "Annulé") en fonction de la date de livraison prévue?

CREATE FUNCTION statutCommande(idCommande INT) RETURNS VARCHAR(10)
BEGIN
    DECLARE dateLivraisonPrevue DATE;
    SELECT dateLivraison INTO dateLivraisonPrevue FROM commandes WHERE id = idCommande;
    
    IF dateLivraisonPrevue > CURDATE() THEN
        RETURN 'En cours';
    ELSEIF dateLivraisonPrevue <= CURDATE() THEN
        RETURN 'Livré';
    ELSE
        RETURN 'Annulé';
    END IF;
END;

7.Q Créez une fonction stockée nommée "etiquetteProduit" qui prend en entrée l'ID d'un produit et renvoie une étiquette spéciale si le produit est en rupture de stock.

CREATE FUNCTION etiquetteProduit(idProduit INT) RETURNS VARCHAR(20)
BEGIN
    DECLARE quantiteStock INT;
    SELECT quantiteStock INTO quantiteStock FROM produits WHERE id = idProduit;
    
    IF quantiteStock = 0 THEN
        RETURN 'Rupture de stock';
    ELSE
        RETURN 'En stock';
    END IF;
END;

8.Q Créer une fonction stockée nommée "commandesClient" qui prend en entrée l'ID d'un client et renvoie le nombre total de commandes passées par ce client en fonction du statut des commandes?

CREATE FUNCTION commandesClient(idClient INT, statut VARCHAR(10)) RETURNS INT
BEGIN
    DECLARE totalCommandes INT;
    
    IF statut = 'En cours' THEN
        SELECT COUNT(*) INTO totalCommandes FROM commandes WHERE idClient = idClient AND statut = 'En cours';
    ELSEIF statut = 'Livré' THEN
        SELECT COUNT(*) INTO totalCommandes FROM commandes WHERE idClient = idClient AND statut = 'Livré';
    ELSE
        SELECT COUNT(*) INTO totalCommandes FROM commandes WHERE idClient = idClient;
    END IF;
    
    RETURN totalCommandes;
END;

9.Q Créer une fonction stockée nommée "livraisonExpress" qui prend en entrée la date de livraison prévue d'une commande et renvoie "Oui" si la livraison est prévue dans les trois prochains jours, sinon "Non"?

CREATE FUNCTION livraisonExpress(dateLivraisonPrevue DATE) RETURNS VARCHAR(3)
BEGIN
    DECLARE dateActuelle DATE;
    SET dateActuelle = CURDATE();
    
    IF DATEDIFF(dateLivraisonPrevue, dateActuelle) <= 3 THEN
        RETURN 'Oui';
    ELSE
        RETURN 'Non';
    END IF;
END;

10.Q Créez une fonction stockée nommée "remiseCumulee" qui prend en entrée l'ID d'un client et renvoie le pourcentage total de réduction cumulée qu'il a reçu en fonction de ses commandes passées.

CREATE FUNCTION remiseCumulee(idClient INT) RETURNS DECIMAL(5,2)
BEGIN
    DECLARE totalRemise DECIMAL(5,2);
    SELECT SUM(prix * QuantiteVendue * remise) INTO totalRemise
    FROM ventes
    WHERE idClient = idClient;
    
    RETURN totalRemise;
END;

11.Q Créer une fonction stockée nommée "fraisLivraison" qui prend en entrée la distance de livraison en kilomètres et renvoie les frais de livraison en fonction de la distance?

CREATE FUNCTION fraisLivraison(distanceKm INT) RETURNS DECIMAL(8,2)
BEGIN
    DECLARE frais DECIMAL(8,2);
    
    IF distanceKm <= 10 THEN
        SET frais = 5.00;
    ELSEIF distanceKm <= 20 THEN
        SET frais = 10.00;
    ELSE
        SET frais = 15.00;
    END IF;
    
    RETURN frais;
END;

12.Q Créez une fonction stockée nommée "quantiteStockMinProduits" qui renvoie la quantité minimale en stock parmi tous les produits.

CREATE FUNCTION quantiteStockMinProduits() RETURNS INT
BEGIN
    DECLARE minQuantiteStock INT;
    SELECT MIN(quantiteStock) INTO minQuantiteStock FROM produits;
    RETURN minQuantiteStock;
END;

13.Q Créer une fonction stockée nommée "nomProduitMoinsVendu" qui renvoie le libellé du produit le moins vendu?

CREATE FUNCTION nomProduitMoinsVendu() RETURNS VARCHAR(100)
BEGIN
    DECLARE idProduitMoinsVendu INT;
    SELECT idProduit INTO idProduitMoinsVendu
    FROM ventes
    GROUP BY idProduit
    ORDER BY SUM(QuantiteVendue) ASC
    LIMIT 1;
    
    DECLARE libelleProduit VARCHAR(100);
    SELECT libelle INTO libelleProduit FROM produits WHERE id = idProduitMoinsVendu;
    
    RETURN libelleProduit;
END;

14.Q Créez une fonction stockée nommée "commandesNonLivrees" qui renvoie le nombre total de commandes en cours non encore livrées.

CREATE FUNCTION commandesNonLivrees() RETURNS INT
BEGIN
    DECLARE totalCommandesNonLivrees INT;
    SELECT COUNT(*) INTO totalCommandesNonLivrees FROM commandes WHERE statut = 'En cours';
    RETURN totalCommandesNonLivrees;
END;

15.Q Créez une fonction stockée nommée "remiseClientFidele" qui prend en entrée l'ID d'un client et renvoie le pourcentage de réduction applicable en fonction du nombre total d'achats et du statut du client.

CREATE FUNCTION remiseClientFidele(idClient INT) RETURNS DECIMAL(5,2)
BEGIN
    DECLARE totalAchats INT;
    DECLARE categorieClient VARCHAR(10);
    
    SELECT SUM(prixVente * QuantiteVendue) INTO totalAchats FROM ventes WHERE idClient = idClient;
    SELECT categorie INTO categorieClient FROM clients WHERE id = idClient;
    
    IF totalAchats >= 500 AND categorieClient = 'Régulier' THEN
        RETURN 0.1; -- 10% de réduction pour les clients réguliers avec plus de 500 d'achats
    ELSEIF totalAchats >= 1000 AND categorieClient = 'Premium' THEN
        RETURN 0.15; -- 15% de réduction pour les clients premium avec plus de 1000 d'achats
    ELSE
        RETURN 0.05; -- 5% de réduction pour les autres clients
    END IF;
END;

16.Q Créer une fonction stockée nommée "produitsEnRuptureAlerte" qui renvoie la liste des produits en rupture de stock avec une alerte si la quantité en stock est inférieure à un seuil critique?

CREATE FUNCTION produitsEnRuptureAlerte() RETURNS TEXT
BEGIN
    DECLARE produitsRupture TEXT;
    DECLARE seuilCritique INT;
    SET seuilCritique = 10; -- Seuil critique de stock
    
    SELECT GROUP_CONCAT(libelle SEPARATOR ', ') INTO produitsRupture FROM produits WHERE quantiteStock <= 0;
    
    IF produitsRupture IS NULL THEN
        RETURN 'Aucun produit en rupture.';
    ELSEIF produitsRupture IS NOT NULL AND quantiteStock <= seuilCritique THEN
        RETURN 'Alerte : ' + produitsRupture;
    ELSE
        RETURN produitsRupture;
    END IF;
END;

17.Q Créez une fonction stockée nommée "classementClients" qui renvoie un classement des clients en fonction du nombre total d'achats, avec des catégories comme "Or", "Argent" et "Bronze".

CREATE FUNCTION classementClients() RETURNS VARCHAR(10)
BEGIN
    DECLARE classement VARCHAR(10);
    
    SELECT COUNT(*) INTO classement FROM clients;
    
    IF classement IS NULL THEN
        RETURN 'Aucun client.';
    ELSEIF classement <= 10 THEN
        RETURN 'Or';
    ELSEIF classement <= 50 THEN
        RETURN 'Argent';
    ELSE
        RETURN 'Bronze';
    END IF;
END;

18.Q Créer une fonction stockée nommée "promotionClientVIP" qui prend en entrée l'ID d'un client et renvoie le pourcentage de réduction applicable en fonction du statut VIP du client?

CREATE FUNCTION promotionClientVIP(idClient INT) RETURNS DECIMAL(5,2)
BEGIN
    DECLARE statutVIP CHAR(1);
    SELECT statut INTO statutVIP FROM clients WHERE id = idClient;
    
    IF statutVIP = 'O' THEN
        RETURN 0.2; -- 20% de réduction pour les clients VIP
    ELSE
        RETURN 0.0; -- Aucune réduction pour les autres clients
    END IF;
END;

19.Q Créez une fonction stockée nommée "classementProduit" qui renvoie la catégorie de classement d'un produit en fonction de ses ventes, comme "Meilleur vendeur", "Populaire" ou "Moins vendu".

CREATE FUNCTION classementProduit(idProduit INT) RETURNS VARCHAR(15)
BEGIN
    DECLARE ventesProduit INT;
    
    SELECT SUM(QuantiteVendue) INTO ventesProduit FROM ventes WHERE idProduit = idProduit;
    
    IF ventesProduit >= 100 THEN
        RETURN 'Meilleur vendeur';
    ELSEIF ventesProduit >= 50 THEN
        RETURN 'Populaire';
    ELSE
        RETURN 'Moins vendu';
    END IF;
END;

20.Q Créer une fonction stockée nommée "promotionLivraison" qui prend en entrée la distance de livraison en kilomètres et renvoie les frais de livraison avec une réduction si la distance est supérieure à 50 km?

CREATE FUNCTION promotionLivraison(distanceKm INT) RETURNS DECIMAL(8,2)
BEGIN
    DECLARE frais DECIMAL(8,2);
    
    IF distanceKm <= 50 THEN
        SET frais = 10.00;
    ELSE
        SET frais = 10.00 - (distanceKm - 50) * 0.25; -- Réduction de 0.25 par kilomètre excédentaire
    END IF;
    
    IF frais < 0 THEN
        SET frais = 0.00; -- Frais minimum de 0
    END IF;
    
    RETURN frais;
END;

21.Q Créez une fonction stockée nommée "statutCommandeLivraison" qui prend en entrée l'ID d'une commande et renvoie son statut de livraison ("En cours", "Livré" ou "Retardé") en fonction de la date de livraison prévue et de la date actuelle.

CREATE FUNCTION statutCommandeLivraison(idCommande INT) RETURNS VARCHAR(10)
BEGIN
    DECLARE dateLivraisonPrevue DATE;
    DECLARE dateActuelle DATE;
    SELECT dateLivraison INTO dateLivraisonPrevue FROM commandes WHERE id = idCommande;
    SET dateActuelle = CURDATE();
    
    IF dateLivraisonPrevue > dateActuelle THEN
        RETURN 'En cours';
    ELSEIF dateLivraisonPrevue = dateActuelle THEN
        RETURN 'Livré';
    ELSE
        RETURN 'Retardé';
    END IF;
END;

22.Q Créez une fonction stockée nommée "promotionProduit" qui prend en entrée l'ID d'un produit et renvoie le prix du produit avec une réduction en fonction de sa catégorie.

CREATE FUNCTION promotionProduit(idProduit INT) RETURNS DECIMAL(8,2)
BEGIN
    DECLARE categorieProduit VARCHAR(15);
    DECLARE prixProduit DECIMAL(8,2);
    
    SELECT categorie INTO categorieProduit FROM produits WHERE id = idProduit;
    SELECT prix INTO prixProduit FROM produits WHERE id = idProduit;
    
    RETURN
    CASE
        WHEN categorieProduit = 'Électronique' THEN prixProduit * 0.9 -- Réduction de 10% pour les produits électroniques
        WHEN categorieProduit = 'Vêtements' THEN prixProduit * 0.8 -- Réduction de 20% pour les produits vestimentaires
        ELSE prixProduit
    END;
END;

23.Q Créez une fonction stockée nommée "classementClient" qui renvoie la catégorie de classement d'un client en fonction du nombre total d'achats, avec des catégories telles que "Or", "Argent" ou "Bronze".

CREATE FUNCTION classementClient(idClient INT) RETURNS VARCHAR(10)
BEGIN
    DECLARE totalAchats INT;
    
    SELECT SUM(prixVente * QuantiteVendue) INTO totalAchats FROM ventes WHERE idClient = idClient;
    
    RETURN
    CASE
        WHEN totalAchats >= 1000 THEN 'Or'
        WHEN totalAchats >= 500 THEN 'Argent'
        ELSE 'Bronze'
    END;
END;

24.Q Créez une fonction stockée nommée "statutCommandeLivraison" qui prend en entrée l'ID d'une commande et renvoie son statut de livraison en fonction de la date de livraison prévue et de la date actuelle.

CREATE FUNCTION statutCommandeLivraison(idCommande INT) RETURNS VARCHAR(10)
BEGIN
    DECLARE dateLivraisonPrevue DATE;
    DECLARE dateActuelle DATE;
    
    SELECT dateLivraison INTO dateLivraisonPrevue FROM commandes WHERE id = idCommande;
    SET dateActuelle = CURDATE();
    
    RETURN
    CASE
        WHEN dateLivraisonPrevue > dateActuelle THEN 'En cours'
        WHEN dateLivraisonPrevue = dateActuelle THEN 'Livré'
        ELSE 'Retardé'
    END;
END;

25.Q Créez une fonction stockée nommée "remiseProduit" qui prend en entrée l'ID d'un produit et renvoie le pourcentage de réduction applicable en fonction de la catégorie du produit à l'aide de CASE ... WHEN.

CREATE FUNCTION remiseProduit(idProduit INT) RETURNS DECIMAL(5,2)
BEGIN
    DECLARE categorieProduit VARCHAR(15);
    DECLARE remise DECIMAL(5,2);
    
    SELECT categorie INTO categorieProduit FROM produits WHERE id = idProduit;
    
    SET remise =
    CASE categorieProduit
        WHEN 'Électronique' THEN 0.1 -- 10% de réduction pour les produits électroniques
        WHEN 'Vêtements' THEN 0.2 -- 20% de réduction pour les produits vestimentaires
        ELSE 0.0 -- Aucune réduction pour les autres catégories de produits
    END;
    
    RETURN remise;
END;

26.Q Créez une fonction stockée nommée "evaluationCommande" qui prend en entrée l'ID d'une commande et renvoie une évaluation en fonction du montant total de la commande en utilisant CASE ... WHEN.

CREATE FUNCTION evaluationCommande(idCommande INT) RETURNS VARCHAR(20)
BEGIN
    DECLARE montantCommande DECIMAL(8,2);
    
    SELECT SUM(prixVente * QuantiteVendue) INTO montantCommande FROM ventes WHERE idCommande = idCommande;
    
    RETURN
    CASE
        WHEN montantCommande >= 500 THEN 'Excellente'
        WHEN montantCommande >= 200 THEN 'Bonne'
        WHEN montantCommande >= 100 THEN 'Moyenne'
        ELSE 'Insuffisante'
    END;
END;

Fonctions et procédures

Les triggers

la Sécurité de données

Exercices

Mysql

SELECT

DataTypes

UPDATE

DELETE

CREATE

DROP

RENAME

ALTER

Tables

Les instructions :if,elseif,then,case

IF,ELSEIF,ELSE

CASE THEN