Module :

Système de Gestion des Bases de Donneés

Travaux Pratiques N°3

« Procédures/Fonctions/Packages/Triggers »

On se place toujours au niveau de la base de données très simplifiée « Gestion du transport aérien ».(voir schéma relationnel TP N°1).

*Procédures

1. Ecrire une procédure stockée « supprimer_pilote » permettant de supprimer un pilote dont

on spécifie le numéro. a. Exécuter la procédure dans l’environnement SQL+ avec nopilot=’3465’. b. Vérifier la suppression en utilisant l’ordre SQL correspondant.

2. Ecrire une procédure stockée « Comm_pilote » permettant d’identifier la commission

d’un pilote dont le numéro est introduit par l’utilisateur. -Si la commission est nulle alors le message « commission nulle » est affiché. -Si non majorer la commission du pilote par un taux introduit par l’utilisateur. La

commission est affichée avant et après la mise à jour. c. Exécuter la procédure dans l’environnement SQL+ avec les paramètres effectifs

(nopilot=’6589’, txcomm=1.1) et avec ('3452', 1.2). d. Vérifier la mise à jour en utilisant l’ordre SQL correspondant.

3. Afficher le code source de la procédure « supprimer_pilote » créée en 1. Utiliser la vue USER_SOURCE(NAME, TYPE, LINE, TEXT) .

4. Afficher la liste des objets de l’utilisateurs en cours utiliser la vue ‘user_objects’ 5. Afficher toutes les tables de l’utilisateur en cours 6. Afficher la liste des procédures stockées par l’utilisateur en cours.

7. Afficher la liste des procédures stockées par tous les utilisateurs.

*Fonction

8. Ecrire une fonction stockée «max_h_v_type » permettant de calculer le maximum des

heures de vol des avions dont le type est introduit par l’utilisateur. e. Déclarer une variable max_nbhvol de type NUMBER dans l’environnement

SQL+ f. Exécuter cette fonction dans l’environnement SQL+ avec type = ‘734’. g. Afficher le contenu max_nbhvol.

9. Ecrire une fonction stockée «pilote_avion_piloté » qui retourne le nombre d’avion qu’un pilote donné a piloté.

h. Déclarer une variable nbravion de type INTEGER dans l’environnement SQL+ i. Exécuter cette fonction dans l’environnement SQL+ avec nopilot = ‘6723’. j. Afficher le contenu cpté_avion

10. Afficher la liste des fonctions stockées créées par tous les utilisateurs. 11. Afficher la liste des fonctions stockées par l’utilisateur en cours.

*Packages

12. Créer un package « package_Série3 » permettant de regrouper les procédures et les

fonctions créées en 1., 2., 8. et 9.

13. Afficher la liste des packages de l’utilisateur en cours.

*Triggers

14. Créer un trigger « pilote_insert » qui permet de vérifier si le salaire d’un pilote est <300. Si le cas se présente, il insère automatiquement 700. Par exemple, si l’utilisateur exécute l’ordre SQL suivant :

insert into pilote (NOPILOT,NOM,ADRESSE,SAL,COMM,EMBAUCHE)

values('1235', 'ali', 'tunis' ,123.25, 1200.00,'12/02/02');

Le déclencheur « pilote_insert » sera exécuté et dans la base, l’uplet suivant est inséré :

NOPI NOM ADRESSE SAL COMM EMBAUCHE

---- ------------------------------ ------------------------------ --------- 1234 ali tunis 700 1200 12/02/02

15. Créer un trigger « pilote_embauche » qui permet vérifier, lors de l’insertion d’un pilote, s i

la date d’embauche est inférieure à la date système, dans le cas échéant, afficher un

message d’erreur « La date est supérieure à la date système ».

k. Insérer dans la base l’enregistrement suivant pour vérifier votre solution :

insert into pilote (NOPILOT,NOM,ADRESSE,SAL,COMM,EMBAUCHE)

values('1236', 'ali', 'tunis' ,123.25, 1200.00,'10/07/05');