SGBD

2015/2016

TD2. PL/SQL
Exercices 1:
Structure et Donnes des Tables
SQL>desc departements

SQL>SELECT * FROM departements

SQL>desc Commandes

SQL>desc employes

SQL>SELECT * FROM employes

-1-

SGBD

2015/2016

Parmi les dclarations de variables suivantes, dterminer celles qui sont incorrectes :
AB-

DECLARE
v_id NUMBER(4);
DECLARE
v_x,v_y,v_z VARCHAR2(10);

C - DECLARE
v_date_naissance DATE NOT NULL;

DE-

DECLARE
v_en_stock

BOOLEAN := 1;

DECLARE
emp_record

emp_record_type;

F-

DECLARE
TYPE
type_table_nom IS TABLE OF
VARCHAR2(20)
INDEX BY BINARY_INTEGER;
dept_table_nom type_table_nom;

Exercice 2 :
2.1. Crer un bloc PL/SQL qui cherche et affiche le salaire le plus lev.
2.2. Crer un bloc PL/SQL qui dtermine les employs qui ont les plus haut salaires.

Exercices 3 :
Soit le schma relationnel de la base de donnes GESCOM suivant :
Articles (Refart, Libart, Coulart, Pvart, Qstart, Paart)
Clients (Codeclt, Nomclt, Prenonclt, Cateclt, Adrclt, Cpclt, Villeclt)
Commandes (Numcom, Datecom, #Codeclt)
LigCom (#Numcom, #Refart, Qtecom)
Refart :la reference de larticle ; Nomart : le libell de larticle; Coulart :la couleur de larticle ; Pvart, le prix de larticle;
Qstart, la quantit en stock de larticle ; Paart : le prix dachat de larticle.
Codeclt : le code client; Nomclt : le nom du client; Prenonclt : le prnom du client; Cateclt : catgorie du client; adrclt : la rue o
habite le client; Cpclt : le code postal de la ville du client; Villeclt : la ville ou habite le client.
Numcom : le numro de la commande; Datecom : La date de la cration de la commande; Qtecom : La quantit commande pour
un article.

-2-

SGBD

2015/2016

Travail faire :
1. Crer un bloc PL/SQL qui retourne la liste des numros des commandes dans lesquelles la quantit commande de
larticle A200 est suprieure 4.
2. Transformer le Bloc PL/SQL de la question 1 en procdure.
3. Crer un bloc PL/SQL qui retourne le nombre de commandes dans lesquelles la quantit commande de larticle
A200 est suprieure 4.
4. Transformer le Bloc PL/SQL de la question 3 en fonction.
5. Crer un bloc PL/SQL qui donne la liste des articles les moins chers lachat (rfrence, libell, prix dachat). Le
bloc PL/SQL doit faire appel une fonction qui retourne le prix dachat le moins cher.
6. Ecrire une procdure en PL/SQL procnbrCat qui cherche pour chaque catgorie de client le nombre total de clients
et retourne celles qui sont suprieures 50 clients.
7. Ecrire une procdure procMajpv (valdif Articles.pvart%TYPE, paugt NUMBER(2)) qui cherche les produits
dont la diffrence entre le prix de vente et le prix dachat est inferieur ou gale la variable valdif. Pour les produits
slectionns, le prix de vente doit augmenter de (paugt/100).

Exercices 4 :
Soit la table PERSONNEL (Nom, Role) qui rassemble les membres du personnel dun cirque. On souhaite dterminer
la proportion de jongleurs parmi eux.
1. crire un bloc PL/SQL anonyme permettant de :
Compter le nombre de n-uplets dans la table PERSONNEL et stocker le rsultat dans une variable ;
Compter le nombre demploys dont le rle est Jongleur dans la table PERSONNEL et stocker le rsultat
dans une deuxime variable ;
Calculer la proportion (en pourcentage), stocker le rsultat dans une troisime variable et afficher le rsultat
lcran.
2. Inclure dans le programme prcdent un traitement dexception permettant de dtecter si la table PERSONNEL est
vide, cest--dire, que le nombre total de n-uplets dans PERSONNEL est gal zro.

Exercices 5 :
Soit le schma suivant.
AVION (AvNum, AvNom, Capacite, Localisation)
PILOTE (PlNum, PlNom, PlPrenom, Ville, Salaire)
VOL (VolNum, #PlNum, #AvNum, VilleDep, VilleArr, HeureDep, HeureArr)
Les performances des avions de marque Airbus voluent, aussi souhaite t-on faire des mises jour de la table VOL.
Les temps de vol des avions de type A300 (avions n 1 et 4) doivent tre rduits de 10 % et ceux des avions de type
A310 (avions n 2 et 8) de 15 %. Il sagit de dfinir un programme PL/SQL permettant ces modifications.
1. Dans un bloc PL/SQL anonyme, dclarer un curseur permettant de lire les donnes suivantes : numro de vol,
numro davion, heure de dpart et heure darrive des vols pour lesquels lavion utilis est le n 1, 2, 4 ou 8. Pour
chaque vol lu par le curseur, calculer le temps de vol, le rduire dans la proportion voulue selon lavion utilis, puis
mettre jour lattribut HEUREARR de ce vol dans la table VOL.

-3-

SGBD

2015/2016

Exercices 6 :
----------------- Cration des Tables --------------------CREATE TABLE grade(legrade char(10) PRIMARY KEY, nomgrade char(30), hstat number(3));
CREATE TABLE enseignant(noinsee char(10) primary key, nomp char(10), prenomp char(10), legrade char(10)
REFERENCES grade(legrade), ville char(10));
CREATE TABLE type(letype number(2) PRIMARY KEY, nomtype char(10));
CREATE TABLE filiere(codef char(10) PRIMARY KEY, nomf char(30));
CREATE TABLE UNITE(codef char(10) REFERENCES filiere(codef), nunite char(30), coef number(2), PRIMARY
KEY(codef,nunite));
CREATE TABLEservice(noinsee char(10) references enseignant(noinsee), codef char(10), nunite char(30), letype
number(2) REFERENCES type(letype), heures number(3), PRIMARY KEY(noinsee,codef,nunite,letype));
ALTER TABLE service ADD CONSTRAINT fk_service FOREIGN KEY(codef,nunite) REFERENCES
UNITE(codef,nunite);
1. Ecrire une procdure en PL/SQL pour remplacer les horaires statutaires 192h par 200h.
2. Ecrire une procedure en PL/SQL pour rechercher les infos partir d'un grade.

Exercices 7 :
Soit la base de donnes suivante:
Immeuble (adresse, nbEtages, dateConstruction, nomPropritaire)
Appartement (adresse, nappartement, nomOccupant, type, superficie, tage)
Personne (nom, adresse, nappartement, dateArrive, dateDpart, ge, profession)
Ecrire les programmes suivants en Pl/SQL sous forme de procdures ou de fonctions:
a) Donner ladresse et le nom du propritaire pour les immeubles ayant plus de 5 tages et construits avant 1950.
b) Donner le nombre de personnes qui habitant un immeuble dont elles sont propritaires.
c) Donner les noms et adresses des personnes qui ne sont pas propritaires.
d) Donner le nom et la profession des propritaires dimmeubles o il y a des appartements vides.

Exercices 8 :
crire une procdure stocke PL/SQL qui permet de vrifier, avant lajout dune personne dans la base, si les contraintes suivantes
sont vrifies : dateConstruction de lappartement occup dateArrive occupant < dateDpart occupant (la dateDpart peut avoir
la valeur NULL).
Si une contrainte nest pas vrifie, la procdure doit lindiquer par un message spcifique. Si les contraintes sont vrifies, la
personne est ajoute dans la base.
La procdure prendra comme paramtres les attributs de la table Personne correspondant la personne ajouter (nom, adresse,
nappartement, dateArrive, dateDpart, ge, profession).
Remarques :
1. Dautres solutions peuvent tre correctes.
2. Ladresse est cl primaire de Immeuble et ne peut donc prendre plusieurs fois une mme valeur !
3. Un traitement spcifique est prvu seulement pour lexception NO_DATA_FOUND.

-4-

SGBD

2015/2016

Exercices 9 :
Soit la table employer:

Emp (empno, ename, job, sal, #deptno)

Les dclencheurs sur TABLE

1. Crer un dclencheur basique qui ne fait qu'afficher le numro et le nom d'un employ quon veut supprimer de la
table EMP.
Exemple dexcution :
SQL> delete from emp where empno = 7369 ;
Suppression de l'employ n 7369 -> SMITH
1 ligne supprime.
2. La DRH annonce que dsormais, tout nouvel employ devra avoir un numro suprieur ou gal 10000. Il faut
donc interdire toute insertion qui ne reflte pas cette nouvelle directive.
Exemple dexcution :
SQL> insert into emp (empno, ename, job) values ( 9999, 'Burger', 'CLERK' ) ;
insert into emp (empno, ename, job) values( 9999, 'Burger', 'CLERK' )
*
ERREUR la ligne 1 :
ORA-20010: Numro employ infrieur 10000
3. Donner les instructions qui doivent tre excutes avant lexcution des instructions de lexemple ci dessous :
Exemple dexcution :
SQL> insert into emp (empno, ename, job) values( 9993, 'Burger', 'CLERK') ;
Insertion dans la table EMP
1 ligne cre.
SQL> update emp set sal = 5000 where empno = 9993 ;
Mise jour de la table EMP
1 ligne mise jour.
SQL> delete from emp where empno = 9993 ;
Suppression dans la table EMP
Suppression de l'employ n 9993 -> Burger
1 ligne supprime.

-5-



Les dclencheurs sur VUE
Nous mettons la disposition de certains utilisateurs une vue permettant de slectionner les employs qui ont le
job CLERK
SQL> CREATE OR REPLACE VIEW VW_EMP_CLERK AS
2 Select empno "Numro", ename "Nom", deptno "Dept.", sal "Salaire"
3 From EMP
4 Where JOB = 'CLERK';
Vue cre.
SQL> select * from VW_EMP_CLERK ;
Numro Nom
Dept. Salaire
---------- ---------- ---------- ---------7369 SMITH
20
880
7876 ADAMS
20
1210
7900 JAMES
30
1045
7934 MILLER
10
1430
9991 Dupontont
9992 Duboudin
6 ligne(s) slectionne(s).
A travers cette vue, ces utilisateurs peuvent insrer des lignes
SQL> Insert into VW_EMP_CLERK values( 9994, 'Schmoll', 20, 2500 ) ;
Insertion dans la table EMP
1 ligne cre.
Cependant, ils ne peuvent pas voir leurs insertions car la colonne job (inutile dans ce cas) ne fait pas partie de la
vue et donc de l'insertion !
SQL> select * from VW_EMP_CLERK ;
Numro Nom
Dept. Salaire
---------- ---------- ---------- ---------7369 SMITH
20
880
7876 ADAMS
20
1210
7900 JAMES
30
1045
7934 MILLER
10
1430
9991 Dupontont
9992 Duboudin

6 ligne(s) slectionne(s).
4. Crer un dclencheur sur vue qui va rsoudre ce problme
5. Excuter la slection (b)
(a)
SQL> Insert into VW_EMP_CLERK values( 9994, 'Schmoll', 20, 2500 ) ;
Insertion dans la table EMP
1 ligne cre.
(b)
SQL> select * from VW_EMP_CLERK ;
O.Lamouchi



Exercices 10 :
I. La modlisation relationnelle suivante reprsente la structure statique de la base de donnes de la bibliothque.

ADHERENT(noAdh,nom, prnom, adresse, dateAdh)
CATALOGUE(titre, nomAut, prnomAut, anEd)
FONDS(cote, #titre)
RESERVATION(#titre, #noAdh, dateRes, dateEmp)
EMPRUNT(#cote, dateEmp, #noAdh, dateRet)

II. Les rgles dintgrit :

Tous les attributs dun adhrent ne peuvent prendre des valeurs inconnues.
Tous les attributs dun catalogue ne peuvent prendre des valeurs inconnues. Lan ddition doit aprs 1950.
Tous les attributs dun fonds ne peuvent prendre des valeurs inconnues.
Toute rservation, la date de rservation doit tre prcdente de celle demprunt.
Tout emprunt, la date demprunt doit tre prcdente de celle de retour.
On ne peut rserver ou emprunter un titre quaprs avoir adhr la bibliothque. (La date dadhrent est prcdente
de celle de rservation ou demprunt)
Un emprunt ne doit pas dpasser un mois.
Un adhrent ne peut emprunter quau plus 5 titres la fois et un seul exemplaire pour chaque titre.
Lorsquun emprunt est fait partir dune rservation, on enregistre la date demprunt la rservation. (La date
demprunt dans une rservation est claire, alors il existe un emprunt tel que la cote est un exemplaire du titre rserv et
lemprunteur est ladhrent qui rserve ce titre)

III. Exemples dinsertion des donnes:

insert into ADHERENT values ('A001','RENAULT','Robert','Lausanne','08/04/01');
insert into ADHERENT values ('A002','VOLVO','Robert','Geneve','01/01/02');
insert into ADHERENT values ('A003','AQUA','Robert','Sion','25/06/01');

insert into CATALOGUE values ('Base de donnees','ADIBA','Michel',1995);
insert into CATALOGUE values ('Base des objets','COLLAND','Rollet',1990);
insert into CATALOGUE values ('OO DMBS','ADIBA','Michel',1998);
insert into CATALOGUE values ('ORACLE SQL','LINDEN','Brian',2000);
insert into CATALOGUE values ('SQL*Plus reference','LINDEN','Brian',2001);
insert into CATALOGUE values ('Web Database','BUYENS','Jim',2000);

insert into FONDS values('BDD_01','Base de donnees');
insert into FONDS values('BDD_02','Base de donnees');
insert into FONDS values('BD0_01','Base des objets');
insert into FONDS values('OOMS_01','OO DMBS');
insert into FONDS values('OOMS_02','OO DMBS');
insert into FONDS values('ORA.SQL_01','ORACLE SQL');
insert into FONDS values('SQLP_01','SQL*Plus reference');
insert into FONDS values('WDB_00','Web Database');

insert into RESERVATION values('Base de donnees','A001','09/04/11,NULL);
insert into RESERVATION values('Base de donnees','A002','10/01/12', NULL);
insert into RESERVATION values('OO DMBS','A002','02/01/02',NULL);

insert into EMPRUNT values('BDD_02','21/04/11','A001', '03/05/11');
insert into EMPRUNT values('BDD_01','01/06/12','A001', NULL);
insert into EMPRUNT values('BD0_01','03/06/12','A001', NULL);
insert into EMPRUNT values('OOMS_02','03/06/12','A001', NULL);
insert into EMPRUNT values('ORA.SQL_01','01/06/12','A001', NULL);
insert into EMPRUNT values('SQLP_01','01/06/12','A001', NULL);
insert into EMPRUNT values('OOMS_02','05/06/12','A002', NULL);
O.Lamouchi



insert into EMPRUNT values('OOMS_01','05/01/12','A002', '10/01/12');

update RESERVATION set dateEmp='21/04/11' where titre='Base de donnees' and noAdh='A001' and
dateRes='09/04/11';
update RESERVATION set dateEmp='05/01/12' where titre='OO DMBS' and noAdh='A002' and dateRes='02/01/12';

IV. Triggers:
Inter-relations : ADHERENT, RESERVATION, EMPRUNT

ADHERENT :
1. Modifier la date dadhsion (dateAdh) : Refuser la modification et afficher une erreur sil existe dj des
rservations ou des emprunts de cet adhrent dont la date est antrieure la nouvelle date.
RESERVATION :
2. Crer une rservation : Si la date de rservation (dateRes) est antrieure la date dadhsion (dateAdh), affecter
dateAdh dateRes.
EMPRUNT
3. Crer un emprunt : Si la date demprunt (dateEmp) est antrieure la date dadhsion (dateAdh), affecter dateAdh
dateRes.
4. Modifier le numro dadhrent : Refuser la modification et afficher une erreur si le nouveau numro dadhrent
ne satisfait pas la rgle.
Intra-relation (EMPRUNT), Ensembliste

EMPRUNT :
5. Crer un emprunt : Refuser cette insertion et afficher une erreur si le nombre des emprunts qui ne sont pas
encore rendus est dj 5.
6. Modifier la date de retour : Refuser la modification et afficher une erreur si elle transgresse la rgle.
Inter-relations (EMPRUNT, FONDS)

EMPRUNT :
7. Crer un emprunt : Refuser cette insertion et afficher une erreur si cet adhrent a emprunt (pas encore rendu)
un exemplaire du mme titre.
8. Modifier la date de retour : Refuser la modification et afficher une erreur si elle transgresse la rgle.
FONDS :
9.Modifier le titre : Refuser la modification et afficher une erreur sil existe dj des emprunts de cet exemplaire.
Inter-relations (FONDS, RESERVATION, EMPRUNT)

FONDS :
10. Modifier le titre : Refuser la modification et afficher une erreur sil existe dj des emprunts de cet exemplaire.
11. Supprimer un fonds : Refuser la suppression. (Cette opration est dj vrifie grce la cl trangre)
RESERVATION :
12. Crer une rservation : la date demprunt doit tre inconnue lorsquune nouvelle rservation est faite (insre).
13. Modification la date demprunt : refuser la modification et afficher une erreur si elle transgresse la rgle.
EMPRUNT :
14. Modifier ladhrent : Refuser la modification et afficher une erreur si elle transgresse la rgle.
15. Supprimer un emprunt : Si cet emprunt concerne une rservation, refuser la suppression et afficher une erreur.

V. Tester les triggers

RI.2.
INSERT INTO catalogue VALUES ('Constraint database','REVESZ','Peter', 1949);

RI.4.
INSERT INTO reservation VALUES('SQL*Plus reference','A002','28/06/01','26/06/11');

RI.5.
INSERT INTO emprunt VALUES('SQLP_01','27/05/02','A003','20/05/12');
O.Lamouchi




RI.6.
ADHERENT : maj dateAdh
UPDATE adherent SET dateAdh='10/04/11' WHERE noAdh ='A001';

RESERVATION : crer
INSERT INTO reservation VALUES('SQL*Plus reference','A001','08/03/11',NULL);

EMPRUNT : crer
INSERT INTO emprunt VALUES('SQLP_01','24/06/11','A003',NULL);

EMPRUNT : maj noAdh
UPDATE emprunt SET noAdh='A003' WHERE noAdh='A001' AND cote='BDD_02';

RI.7.
INSERT INTO emprunt VALUES('SQLP_01','27/04/12','A003','28/05/12');

RI.8.1.
EMPRUNT : crer
INSERT INTO emprunt VALUES('WDB_00','10/06/12', 'A001', NULL);

EMPRUNT : maj dateRet
UPDATE emprunt SET dateRet=NULL WHERE cote='BDD_02' AND noAdh='A001';

RI.8.2.
EMPRUNT : crer
INSERT INTO emprunt VALUES('OOMS_01','10/06/12', 'A002', NULL);

EMPRUNT : maj dateRet
UPDATE emprunt SET dateRet=NULL WHERE cote='OOMS_01' AND noAdh='A002';

FONDS : maj titre
UPDATE fonds SET titre='Base de donnees 1' WHERE titre='Base de donnees';

RI.9.
FONDS : maj titre
UPDATE fonds SET titre='Base de donnees 1' WHERE titre='Base de donnees';

RESERVATION : crer
INSERT INTO reservation VALUES('SQL*Plus reference','A002','28/06/11','30/06/01');

RESERVATION : maj dateEmp
UPDATE reservation SET dateEmp='30/06/11' WHERE titre='SQL*Plus reference' AND noAdh='A002';

EMPRUNT : maj noAdh
UPDATE emprunt SET noAdh='A003' WHERE cote='OOMS_01' AND dateEmp='05/01/12';

EMPRUNT : supprimer
DELETE FROM emprunt WHERE cote='OOMS_01' AND dateEmp='05/01/12';

O.Lamouchi