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)

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  Voir la réponse 

   SELECT * FROM Client WHERE clt_vip=1;
                                               

2. Afficher toutes les voitures actuellement en réparation.  Voir la réponse 

   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.  Voir la réponse 

   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.  Voir la réponse 

   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.  Voir la réponse 

   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é.  Voir la réponse 

   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.  Voir la réponse 

   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.  Voir la réponse 

   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.  Voir la réponse 

   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.  Voir la réponse 

    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.  Voir la réponse 

    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.  Voir la réponse 

    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.  Voir la réponse 

    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;
    /