adplus-dvertising

Devoir N°2 PL/SQL - Requêtes, Fonctions et triggers

Devoir N°2 PL/SQL - Requêtes, Fonctions et triggers

Un gestionnaire de base de données d'une agence de location de voitures, souhaite préparer certaines requêtes qui seront utilisées dans la réservation en ligne, également écrire certaines fonctions (ou procédures) et triggers, pour garder les données dans un état cohérent.

Voici une partie de la base de données:

  •  Client(clt_id:integer, clt_nom:string, clt_email:string, clt_vip:boolean)
  •  Voiture(matricule:string, moteur_taille:integer, kilom:integer, marque :string, modele :integer))
  •  Reserver(#clt_id :integer, #matricule :string, date_res :date, date_ret:date, nb_jours:integer, etat_res:boolean)
  •  Maintenance(maint_id, #matricule:string, date_panne:date, etat_reparation:boolean)
  •  ClientRouge(#clt_id:integer, date_ajout:date, raison_ban:string)
Recommandé :  Veuillez d'abord résoudre l'exercice, avant de passer à la solution.

Travail à faire

Pour simplifier l'exploration de la base de données, il vous est demandé d'implémenter les fonctions ou procédures suivantes :

  1. Afficher tous les clients VIP
    SELECT * FROM Client WHERE clt_vip=1;
                                                
  2. Afficher toutes les voitures actuellement en réparation.
    SELECT v.* FROM Voiture v INNER JOIN Maintenance m ON v.matricule=m.matricule WHERE m.etat_reparation=0;
                                                
  3. Afficher le nom et l'e-mail des clients rouges.
    SELECT clt.clt_nom, clt.clt_email FROM Client clt INNER JOIN ClientRouge cltr ON clt.clt_id=cltr.clt_id
                                                
  4. Afficher le nom du client et la matricule de toutes les réservations effectuées en juillet 2020.
    SELECT clt.clt_nom, r.matricule FROM Client clt INNER JOIN Reserver r ON clt.clt_id=r.clt_id WHERE EXTRACT(month from r.date_res)=7 AND EXTRACT(year from r.date_res)=2020 
                                                
  5. Affiche les noms des clients et le nombre de voitures réservées pour chaque client en juillet 2020.
    SELECT clt.clt_nom, count(*) FROM Client clt INNER JOIN Reserver r ON clt.clt_id=r.clt_id WHERE EXTRACT(month from r.date_res)=7 AND EXTRACT(year from r.date_res)=2020 GROUP BY clt.clt_nom
                                                
  6. Ecrire une fonction nb_reservation(client) qui renvoie le nombre de voitures réservées du client spécifié.
    CREATE OR REPLACE FUNCTION nb_reservation(client IN Client%rowtype)
    RETURN number
    IS
        nb number;
    BEGIN
        select count(*) into nb from Reservation where clt_id=client.clt_id;
        return nb;
    END;
    /
                                                
  7. Ecrire une fonction enMaintennace(voiture), qui retourne true si la voiture donnée est actuellement en réparation, sinon retourne false.
    CREATE OR REPLACE FUNCTION enMaintenance(voiture IN Voiture%rowtype)
    RETURN boolean
    IS
        etat boolean;
    BEGIN
        select etat_reparation into etat from Maintenance where matricule=voiture.matricule and etat_reparation=1 limit 1;
        return etat;
    END;
    /
                                                
  8. Ecrire une procédure inserer_client (clt_id, raison) qui insère dans la table ClientRouge un nouvel enregistrement.
    CREATE OR REPLACE PROCEDURE inserer_client (clt_id Client.clt_id%type, raison ClientRouge.raison_ban%type)
    IS
    BEGIN
        INSERT INTO ClientRouge VALUES(clt_id,SYSDATE,raison)
    END;
    /
                                                
  9. Créer un trigger 'interdit_email' qui interdit le changement d'adresse email des clients.
    CREATE OR REPLACE TRIGGER interdit_email’
    BEFORE UPDATE OF clt_email ON Client FOR EACH ROW
    BEGIN
        RAISE_APPLICATION_ERROR(-20005,'Improssible de changer l adresse email.');
    END;
    /
                                                
  10. Créer un trigger 'enrepartion' qui interdit la réservation de voiture actuellement en réparation.
    CREATE OR REPLACE TRIGGER enrepartion
    BEFORE INSERT ON Reserver FOR EACH ROW
    DECLARE
        rep Maintenance%rowtype;
    BEGIN
        SELECT * into rep FROM Maintenance WHERE matricule=:NEW.matricule AND etat_reparation=1 LIMIT 1;
        IF rep IS NOT NULL THEN
            RAISE_APPLICATION_ERROR(-20005,'La voiture demandée est en réparation.');
        END IF;
    END;
    /
                                                
  11. Créer un trigger 'ancien_modele' interdisant la réservation d'une voiture de modèle antérieur à 2012 de plus d'une semaine.
    CREATE OR REPLACE TRIGGER ancien_modele
    BEFORE INSERT ON Reserver FOR EACH ROW
    WHEN nb_jours>7
    DECLARE
        v Voiture%rowtype;
    BEGIN
        SELECT * into v FROM Voiture WHERE matricule=:NEW.matricule;
        IF v.modele < 2012 THEN
            RAISE_APPLICATION_ERROR(-20005,'La voiture demandée est en réparation.');
        END IF;
    END;
    /
                                                
  12. Créez un trigger qui interdit aux clients rouges de réserver des voitures de plus de 3 jours.
    CREATE OR REPLACE TRIGGER client_rouge
    BEFORE INSERT ON Reserver FOR EACH ROW
    DECLARE
        v ClientRouge%rowtype;
    BEGIN
        SELECT * into v FROM ClientRouge WHERE clt_id=:NEW.clt_id;
        IF v IS NOT NULL THEN
            RAISE_APPLICATION_ERROR(-20005,'Un client rouge.');
        END IF;
    END;
    /
                                                
  13. Créez un trigger 'alert_vidange' qui interdit la réservation de voitures dont le kilométrage est supérieur à 100000 pour plus de 4 jours.
    CREATE OR REPLACE TRIGGER alert_vidange
    BEFORE INSERT ON Reserver FOR EACH ROW
    WHEN nb_jours>4
    DECLARE
        v Voiture%rowtype;
    BEGIN
        SELECT * into v FROM Voiture WHERE matricule=:NEW.matricule;
        IF v.kilom > 100000 THEN
            RAISE_APPLICATION_ERROR(-20005,'Alert de vidange.');
        END IF;
    END;
    /
                                                
Partager ce cours avec tes amis :
Rédigé par ESSADDOUKI Mostafa
ESSADDOUKI
The education of the 21st century opens up opportunities to not merely teach, but to coach, mentor, nurture and inspire.