USTHB – Faculté d'Informatique
N. ABDAT
Section L3 ISIL - A Module : Base de données (BDD2) Nov 2024
TD PL/SQL
Exercice :
Considérons la base de données prenant en charge une gestion des prêts consentis auprès des clients d'une banque:
Client(CodCl, Nom, Prénom, Adresse, Daten, Lieu, Tel, Email, DateInscription, TypCl)
Compte(Numéro, CodCl, Montant)
Prêt(CodP, CodCl, Montp, TypP, DatP, Datdeb, Datfin):datedeb_datefin indiquent la période de
remboursement.
Garantie(CodP, CodB, DatB, MontEstimé): garantie des prêts par des biens à des dates fixées avec des montants.
Echéancier(CodP, Datpr, Montpr, Datpa, Montpa): L’échéancier de remboursement suit un calendrier fixe. A
une date donnée (datpr), on doit rembourser le prêt (identifié par codp) en versant un montant (montpr). On notifie
le montant du client remboursé (montpa) avec la date correspondante (datpa).
Type_prêt(TypP, Minp, Maxp, Durp): les types de prêt offerts par la banque ont un montant compris entre un
montant minimum (min) et un montant maximum (max) avec une durée de remboursement (durp).
Bien(Codb, Designation, Description, CodCl, Dimension, DateEvaluation, Montbien)
1. Ecrire une fonction Nbre_Prets (x) qui donne le nombre de prêts accordés au client x.
2. Ecrire une fonction Total_Estimé (x) qui donne pour le prêt x, le montant total estimé des biens sous garantie.
3. Ecrire une procédure Biens_Clients (cl, t, g) qui calcule dans t le nombre total de biens et dans g le
nombre de biens sous garantie du client n° cl.
4. Ecrire un bloc PL/SQL qui affiche pour chaque Client son nom et prénom, le nombre de ses prêts, le
nombre total de biens, le nombre de biens sous garantie. L'affichage sera comme suit
Le client nom prénom a eu … prêts , il a … biens dont …. sont sous garantie.
5. On définit les contraintes suivantes concernant les montants accordés aux prêts :
-Le montant d'un prêt ne doit pas dépasser le montant total estimé des biens sous garantie.
-Le montant d'un prêt de type p est compris entre les montants min et max (Minp, Maxp) correspondant à ce type
Ecrire une Procédure Vérifier_Prets (x) qui, si ces contraintes ne sont pas vérifiées gèrent des exceptions.
« montant du prêt dépasse le montant total estimé des biens sous garantie » ou
« montant du prêt dépasse le montant max pour le type p »
L3 ISIL A 2024
[Link]
Manuel PL/SQL
BLOC PL/SQL : Exemples de déclarations et initialisations
[DECLARE … déclarations et initialisation] DECLARE
BEGIN mot CHAR(5); note NUMBER (4,2) := 10;
… instructions exécutables x NUMBER(4) := 0;
[EXCEPTION … interception des erreurs] v1 table%ROWTYPE; (type du tuple d'une table)
END; v2 [Link]%TYPE; ( type d'un attribut d'une table)
si-alors si-alors-sinon Imbrications de conditions
IF condition IF condition IF condition1 THEN instructions;
THEN instructions; THEN instructions; ELSIF condition2 THEN instructions;
END IF; ELSE instructions; ELSIF ….. ;
END IF; ELSE instructions;
END IF;
Boucle Pour : Boucle Tant que : Boucle Répéter : Affichage à l'écran :
FOR i IN 1..10 WHILE condition LOOP instructions;
LOOP instructions; LOOP instructions; EXIT WHEN condition; DBMS_OUTPUT.PUT_LINE
END LOOP; END LOOP; END LOOP ; (…. || …… || …….);
EXTRAIRE UN TUPLE A PARTIR D'UNE TABLE:
SELECT liste_attributs INTO liste_variables FROM nomTable ….…. ;
Exemples:
SELECT * INTO v1 FROM Bateau WHERE nbat=103; ------ v1= (103, 'EL BAHDJA', 'BNA')
SELECT nombat INTO v2 FROM Bateau WHERE nbat=104; ------- v2= 'LA COLOMBE '
PRODEDURE ET FONCTIONS
CREATE [OR REPLACE] PROCEDURE nomProcédure
[(paramètre [ IN | OUT | IN OUT ] typeSQL [:= | DEFAULT] .... ) ] IS Bloc PL/SQL ;
CREATE [OR REPLACE ] FUNCTION nomFonction
[(paramètre [ IN | OUT | IN OUT ] typeSQL ..... ) ] RETURN typeSQL IS
Bloc PL/SQL contenant Return;
LES CURSEURS (CURSOR) : EXTRAIRE PLUSIEURS TUPLES :
Déclaration du curseur : CURSOR nomCurseur IS requêteSelect;
Dans partie instruction : FOR nomvariable IN nomCurseur LOOP -- parcourir les éléments de curseur un à un
…………………..
END LOOP;
EXCEPTION
Déclaration: e EXCEPTION ;
Dans partie instruction : RAISE e ;
Dans Partie EXCEPTION : WHEN e THEN ……;