TP N° 1: Langage PL/SQL & Les Vues
Cette base de données conçue pour la gestion (très simplifiée) du transport aérien, sert de
support aux exercices du TP1, TP2 et TP3
Dictionnaire des données
Nom attribut Signification
Relation vol
Nopilot numéro de matricule du pilote
Tableau A.1 – Structure relation VOL
Nom nom du pilote
Nom attribut Signification Adresse adresse du pilote, limitée à la
ville
novol numéro identification d’un vol
Sal Qte du salaire mensuel
vildep ville de départ
Comm qte commission mensuel qui peut
vilar ville d’arrivée ne pas exister
dep_h heure de départ (heure) Embauche date d'embauche
dep_mn heure de départ (minute)
Relation avion
ar_h heure d'arrivée (heure)
Tableau A.4 – Structure relation AVION
ar_mn heure d'arrivée (minute)
ch_jour jour d'arrivée (0: même jour, : Nom attribut signification
lendemain)
Nuavion numéro d’immatriculation d’un avion
Relation appareil
Type code normalisé désignant une famille
Tableau A.3 – Structure relation APPAREIL d’avions
Annserv année de mise en service
Nom attribut signification
Nom nom donné à un avion qui peut ne pas
être connu
codetype code normalisé désignant une
famille d’avions Nbhvol nombre heures de vol depuis la mise
en service
nbplace nombre de places
désign nom de la famille d’avions Relation affectation
Tableau A.5 – Structure relation AFFECTATION
Nom attribut signification
Relation pilote vol numéro identification d’un vol
Tableau A.2 – Structure relation PILOTE date_vol date du vol sous la forme :
[Link]
1
pilote numéro du pilote conduisant Nom colonne Type - contrainte
l’avion pour le vol
codetype CHAR(3) clé primaire
avion numéro d’immatriculation de
l’avion affecté au vol nbplace NUMBER(3)
nbpass nombre effectif de passagers design VARCHAR2(50)
LES TABLES Table avion
Table vol
Tableau A.9 – Structure table AVION
Tableau A.6 – Structure table VOL
Nom colonne Type - contrainte
Nom colonne Type – contrainte
nuavion CHAR(4) clé primaire
novol CHAR(6) clé primaire
type CHAR(3) clé étrangère référence
vildep VARCHAR2(30) [Link]
vilar VARCHAR2(30) annserv NUMBER(4)
dep_h NUMBER(2) nom VARCHAR2(50)
dep_mn NUMBER(2) nbhvol NUMBER(8)
ar_h NUMBER(2)
Table affectation
ar_mn NUMBER(2) Tableau A.10 – Structure table AFFECTATION
ch_jour NUMBER(1)
Nom colonne Type - contrainte
Table pilote
vol CHAR(6) clé primaire, clé étrangère
Tableau A.7 – Structure table PILOTE référence [Link]
Nom colonne Type – contrainte date_vol DATE clé primaire
pilote CHAR(4) clé étrangère référence
nopilot CHAR(4) clé primaire [Link]
nom VARCHAR2(35) nbpass NUMBER(3)
adresse VARCHAR2(30) avion CHAR(4) clé étrangère référence
[Link]
sal NUMBER(8,2)
comm NUMBER(8,2)
embauche DATE
Table appareil
Tableau A.8 – Structure table APPAREIL
Liaisons entre les tables
Figure A.1 – Liaisons entre tables
2
A. Création des tables
A1) Créer, sous l’éditeur WORKSHEET, les tables correspondant au modèle relationnel de
la figure A.1.
A2) Créer, toujours sous l’éditeur WORKSHEET, des données (des tuples) pour ces tables.
B. Compréhension des blocs PL/SQL
B1) Écrire un bloc PL/SQL permettant d’afficher les informations concernant les avions
dont le nombre des heures de vol dépassent de plus que 25 la moyenne des nbhvol de
toutes les avions de la base. Utiliser la notion ‘%ROWTYPE’.
B2) Écrire un bloc PL/SQL permettant d’afficher les noms et les adresses des pilotes dont
le salaire dépasse 25000 (Variable de substitution).
B3) Écrire un bloc PL/SQL permettant d’afficher toutes les informations concernant les
avions dont le nombre des heures de vol dépassent de plus que 25 la moyenne des
nbhvol de toutes les avions de la base.
B4) Écrire un bloc PL/SQL permettant d’afficher les noms des pilotes dont les salaires
dépassent 25000 et que la commission est inférieure à 20% du salaire. Afficher les
noms des pilotes et leurs dates d’embauche.
On désire afficher les mêmes informations en considérant le salaire et la commission
comme des paramètres (Curseur paramétré).
B5) Écrire un block PL/SQL permettant :
1. De récupérer la liste des pilotes qui ont été affectés un seul vol.
2. D’Insérer cette liste dans une table T1 dont le contenu est de type scalaire faisant
référence à la table [Link].
3. D’insérer la commission de chaque pilote de la liste dans une deuxième table T2 dont
le contenu est de type scalaire faisant référence à la table [Link].
4. De réduire la commission de chaque pilote de la liste de 5% et insérer le résultat de
chaque réduction dans une troisième table T3.
5. D’afficher le contenu des 3 tables T1, T2 et T3.
B6) Compléter le script suivant de façon à ajouter à chaque pilote sélectionné dans le
curseur C1 une commission de 500DH
3
DECLARE
CURSOR C1 IS SELECT nom, sal FROM pilote;
V_nom [Link]%type ;
V_sal [Link]%type;
BEGIN
. . .
END;
B7) PL/SQL offre la possibilité d’utiliser l’option CURRENT OF nom_curseur dans la
clause WHERE des instructions UPDATE et DELETE. Cette option permet de
modifier ou de supprimer la ligne distribuée par la commande FETCH. Pour utiliser
cette option il faut ajouter la clause FOR UPDATE à la fin de la définition du curseur.
Compléter le script suivant qui permet de modifiant le salaire d’un pilote avec les
contraintes suivantes :
- Si la commission est supérieure au salaire alors on rajoute au salaire la valeur de la
commission et la commission sera mise à la valeur nulle.
- Si la valeur de la commission est nulle alors supprimer le pilote du curseur.
DECLARE
CURSOR C_pilote IS
SELECT nom, sal, comm
FROM pilote
WHERE nopilot BETWEEN 1280 AND 1999 FOR UPDATE;
v_nom [Link]%type;
v_sal [Link]%type;
v_comm [Link]%type;
BEGIN
. . .
END ;
B8) Écrire une procédure PL/SQL qui réalise l’accès à la table PILOTE par l’attribut
nopilote. Si le numéro de pilote existe, elle envoie dans la table ERREUR, le message
« NOM PILOTE-OK » sinon le message « PILOTE INCONNU ». De plus si
sal<comm, elle envoie dans la table ERREUR le message « « NOM PILOTE, COMM
>SAL ».
Indication : une erreur utilisateur doit être explicitement déclenchée dans la procédure
PL/SQL par l’ordre RAISE. La commande RAISE arrête l’exécution normale du bloc
et transfert le contrôle au traitement de l’exception.
B9) Écrire un programme PL/SQL qui insère le vol AF110 partant de Paris à 06h40 et
arrivant à Dublin à 07h35.
Le programme doit utiliser une variable composite de type ROWTYPE.
Refaire le même programme à l’aide de variables de substitution.
C. Création des vues
C1) Créer une vue (v-pilote) constituant une restriction de la table pilote, aux pilotes qui
habitent Paris.
C2) Vérifier est ce qu’il est possible de modifier les salaires des pilotes habitant Paris à
travers la vue v-pilote.
C3) Créer une vue (dervol) qui donne la date du dernier vol réalisé par chaque avion.
C4) Une vue peut être utilisée pour contrôler l’intégrité des données grâce à la clause
‘CHECK OPTION’.
Créer une vue (cr_pilote) qui permette de vérifier lors de la modification ou de
l’insertion d’un pilote dans la table PILOTE les critères suivants :
- Un pilote habitant Paris a toujours une commission
- Un pilote qui n’habite pas Paris n’a jamais de valeur de commission.
4
C5) Créer une vue (nomcomm) qui permette de valider, en saisie et mise à jour, le montant
commission d’un pilote selon les critères suivant :
- Un pilote qui n’est affecté à au moins un vol, ne peut pas avoir de commission
- Un pilote qui est affecté à au moins un vol peut recevoir une commission.
Vérifier les résultats par des mises à jour sur la vue nomcomm.
