Tp Plsql N3

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, si 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');