Faculté des sciences
Départementd’Informatique Année universitaire : 2021/2022
BD II –TP2
Partie I
Préparation de l’environnement :
a- Lancer SQL DEVELOPER
b- Créer une nouvelle connexion en cliquant sur le signe + vert àgauche de votre écran :
- Nom connexion : con
- Rôle :sysdba
- SID : orcl
- User/pwd : sys/root
1. Nous considérons la table « employee » décrite ci-dessous :
Create table employee (
enum varchar2(5) not null primary key,
ename varchar2(20),
salary number (9, 2),
address varchar2(10),
dept varchar2(5)
);
a- Exécuter le script de création de la table :
b- Exécuter le script pour remplir la table :
INSERT INTO employee (enum, ename, salary, address, dept) VALUES ('Enum7', 'Amine', 7500, 'Fes', 'Dep2');
INSERT INTO employee (enum, ename, salary, address, dept) VALUES ('Enum6', 'Aziz', 8500, 'Casa', 'Dep1');
INSERT INTO employee (enum, ename, salary, address, dept) VALUES ('Enum5', 'Amina',8000, 'Rabat', 'Dep3');
INSERT INTO employee (enum, ename, salary, address, dept) VALUES ('Enum4', 'Said', 5000, 'Agadir', 'Dep3');
INSERT INTO employee (enum, ename, salary, address, dept) VALUES ('Enum3', 'Fatima', 7000, 'Tanger', 'Dep2');
INSERT INTO employee (enum, ename, salary, address, dept) VALUES ('Enum2', 'Ahmed', 6000, 'Casa', 'Dep1');
INSERT INTO employee (enum, ename, salary, address, dept) VALUES ('Enum1', 'Ali', 8000, 'Rabat', 'Dep1');
INSERT INTO employee (enum, ename, salary, address, dept) VALUES ('Enum8', 'Ahmed', 4000, 'Casa', 'Dep4') ;
c- Donner le code PL/SQL pour afficher le nom, le salaire et l’adresse d’un employé donné. (Utilisation
d’une requête : SELECT…INTO). Remarque : || est un opérateur de concaténation.
d- Donner le code PL/SQL pour afficher le nom, le salaire et l’adresse d’un employé donné. (Utilisation
de l’opérateur &)
&Num_Emp est utilisée ici pour initialiser la variable PLSQL : var0 à la déclaration.
Attention : Celle-ci est ensuite utilisée dans la clause « where » sans apostrophes.
e- Ecrire le code PL/SQL pour afficher le salaire moyen d’un département donné sous le format
suivant :
{"nom du département" : valeur, "moyenne des salaires" : valeur}
f- (Idem) Ecrire le code PL/SQL pour afficher :
Le salaire maximum et le salaire minimum des employés d’un département donné.
Le nombre moyen des employés d’un département donné.
g- Donner le code PL/SQL pour afficher le nom, le département et l’adresse d’un employé ayant un
salaire donné. (Attention : Pour un salaire donné, il peut y avoir 0, un ou plusieurs employés qui
répondent à ce critère).
Exemple1 : Exécution pour la valeur 5000 (Retour d’un seul résultat)
Exemple2 : Exécution pour la valeur 8000 (Retour plusieurs résultats)
1 BDII - Pr. M.E.H CHARAF
Faculté des sciences
Départementd’Informatique Année universitaire : 2021/2022
Attention : PL/SQL signale une erreur, "retour de plusieurs lignes pour « into », et le
« prog » s'arrête.
h- Donner un code PL/SQL amélioré qui prends en considération cette erreur dans la zone des
exceptions. Utiliser la clause : « When TOO_MANY_ROWS Then ».
i- Donner un code PL/SQL amélioré qui prends en considération le cas où la requête « SELECT » ne
retourne aucun résultat. Utiliser la clause : « WhenNO_DATA_FOUNDThen » dans la zone des
exceptions.
j- On peututiliser aussi la clause : « When OTHERS Then » dans la zone des exceptions pour capter une
certaine erreur et ensuite tester son code : SQLCODE pour pouvoir personnaliser le message
d’erreur. Donner un code PL/SQL qui teste selon le code de l’erreur en utilisant :
« WhenOTHERSThen ».
k- La notation %ROWTYPE est un type qui permet de déclarer un schéma de relation. Dans ce cas, on
n’aura pas besoin de déclarer des variables supplémentaires de type %TYPE. Refaire la question
« c » en utilisant la notation %ROWTYPE
l- Les curseurs permettent de gérer les requêtes SQL qui retournent plusieurs lignes. Donner un code
PL/SQL qui affiche le nom, le salaire et l’adresse de tous les employés en utilisant un curseur.
m- En utilisant un curseur, refaire la question : « f » pour calculer par exemple le salaire maximum de
tous les employéssans utiliser la fonction « MAX » de SQL.(Idem pour calculer le salaire maximum
au sein du département : ‘Dep2’).
n- Refaire la question précédente en utilisant cette fois ci la boucle : « While » puis la
boucle : « FOR ».(Penser à utiliser « %FOUND »pour itérer tant qu'un tuple est trouvé (par fetch)
Quelques remarques :
Absence de : open et close
Pas besoin de : fetch, ni au début ni en cours de boucle non plus à la condition d'arrêt.
La variable de contrôle « EmployEnr » est déclarée automatiquement du type du schéma
de résultat de select.
2 BDII - Pr. M.E.H CHARAF
