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 :
- Afficher tous les clients VIP Voir la réponse
SELECT * FROM Client WHERE clt_vip=1;
- 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;
- 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
- 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
- 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
- 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; /
- 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; /
- 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; /
- 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; /
- 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; /
- 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; /
- 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; /
- 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; /
Partager ce cours avec tes amis :
Rédigé par
ESSADDOUKI
Mostafa