V 1.
Module : Système de Gestion des Bases de Données (SGBD)
Auditoire : 3ème Année
TP2: PL/SQL
Contexte :
Le schéma relationnel des tables existantes au niveau du schéma HR est donné ci-dessous (pour y
accéder, il faut se connecter avec l’utilisateur HR) :
Travail demandé : Écrire les blocs anonymes PL/SQL permettant de :
PARTIE I : BLOCS ANONYMES et CURSEURS
1. Calculer et afficher la moyenne des salaires des employés qui travaillent dans les départements 20 et
30.
2. Afficher les détails du manager de l’employé 101 :
2.1. En utilisant une jointure.
2.2. En utilisant une sous-interrogation.
3. Créer une table EMPLOYEES2 copie de la table EMPLOYEES :
3.1. En utilisant une requête SQL.
3.2. En utilisant un bloc PL/SQL.
4. Au niveau de la table EMPLOYEES2 :
4.1. Échanger les salaires des employés 120 et 122.
1
V 1.2
4.2. Augmenter le salaire de l'employé 115 sur la base des conditions suivantes:
- Si l'expérience est plus de 10 ans, augmenter le salaire de 20%,
- Si l'expérience est supérieure à 5 ans, augmenter le salaire de 10%,
- Sinon l’augmenter seulement de 5%.
4.3. Changer le pourcentage de commission comme suit pour l’employé avec l'ID égale à 150 :
- Si le salaire est supérieur à 10000, la commission est de 0,4%,
- Si le salaire est inférieur à 10000, mais l'expérience est de plus de 10 ans, la commission est
de 0,35%,
- Si le salaire est inférieur à 3000 alors la commission est de 0,25%,
- Sinon la commission est de 0,15%.
5. Afficher pour chaque département, son identifiant, le nombre de ces employés, le salaire le plus
élevé, le salaire le plus faible ainsi que le salaire moyen tronqué à deux chiffres après la virgule. Le
résultat est ordonné en fonction de l’identifiant du département.
6. Afficher les rangs, les identifiants, noms et prénoms du cinquième et du dixième employé de la table
EMPLOYEES.
7. Afficher le top 6 (6 premiers) employés ayant les salaires les plus élevés, triés en fonction du salaire.
Pour chacun de ces employés, afficher leur rang, leur nom, leur JOB_ID ainsi que leur salaire.
8. Au niveau de la table EMPLOYEES2, mettre à jour le salaire de tous les employés en fonction du
service et du pourcentage de commission comme suit :
- Si l’identifiant du département est 40 alors augmentation salariale de 10%,
- Si département est de 70 alors 15%,
- Sinon 9% d’augmentation.
9. Afficher un état de tous les pays (COUNTRIES) et les villes correspondantes (LOCATIONS) pour
tous les départements. Pour ce faire utiliser deux curseurs :
- Dans un premier curseur, afficher les noms des pays ainsi que leurs identifiants, triés par
ordre croissant des noms.
- Dans un deuxième curseur paramétré, extraire les villes (CITY) et les états ou provinces
(STATE_PROVINCE) relatifs au pays dont l’identifiant est passé en paramètre, triés par
ordre décroissant des villes.
10. Afficher l’année ou les années durant lesquelles le nombre maximal d’employés a été embauché.
10.1.Préciser également le nombre de personnes embauchées chaque mois pour chacune de ces
années, en utilisant un curseur paramétré.
PARTIE II : FONCTIONS ET PROCEDURES
1. Créer une fonction stockée « FN_NBR_DEPARTEMENT » permettant de retourner le nombre
total de départements.
1.1. Utiliser cette fonction dans une requête SQL.
2
V 1.2
2. Créer une fonction stockée « FN_SALAIRE » qui prend en paramètre l’identifiant d’un employé et
retourne le nom du département auquel il appartient. La fonction retourne -1 si l’identifiant n’existe
pas.
2.1. Appeler la fonction au niveau d’une requête SQL.
3. Créer une fonction stockée « FN_NOMDEPT » qui prend en paramètre l’identifiant d’un employé
et retourne le nom du département auquel il appartient. La fonction retourne NULL si l’identifiant
n’existe pas.
4. Créer une fonction stockée « FN_NBR_SALARIE » qui prend en paramètre le numéro d’un
département et retourne le nombre de ces salariés.
4.1. Traduire cette fonction en une procédure stockée équivalente « PROC_NBR_SALARIE ».
4.2. Proposer deux appels différents pour la fonction FN_NBR_SALARIE.
4.3. Proposer un appel pour la procédure PROC_NBR_SALARIE.
5. Créer une fonction stockée « FN_MOY_SALAIRE » qui retourne le salaire moyen du département
auquel appartient l’employé dont l’identifiant est passé en paramètre.
6. Créer une fonction stockée « FN_SEMESTRE » permettant de retourner le numéro du semestre
d’une date passée en paramètre.
6.1. Utiliser cette fonction au niveau d’un bloc anonyme pour afficher les employés qui ont été
embauché le deuxième semestre de l’année 1998 en précisant leur identifiant et leur date
d’embauche.
7. Créer une procédure stockée « PROC_TEST_NBR_SALARIE » qui permet d’afficher les noms
des départements dont le nombre de salariés est supérieur à 40 en utilisant la fonction stockée
FN_NBR_SALARIE.
8. Créer une procédure stockée « PROC_SAL_SUP » qui permet d’afficher la liste des employés qui
ont un salaire supérieur à celui dont l’identifiant est donné en paramètre.
8.1. Utiliser cette procédure stockée dans un bloc anonyme pour afficher les employés qui ont un
salaire supérieur à l’employé 121.
PARTIE III : FONCTIONS ET PROCEDURES NON STOCKÉES
9. Ecrire une fonction non stockée « FN_EMP » permettant de retourner le nombre d’employés dirigés
par un manager dont le nom et le prénom sont passés en paramètre.
9.1. Tester cette fonction avec le manager dont le nom est 'Kaufling' et le prénom est 'Payam'.
10. Ecrire une procédure non stockée « PROC_DEPTS» permettant d’afficher les noms et les salaires
moyens des départements dont le nombre d’employés dépasse 20.
