Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Ingénierie Bases de Données L2CS A.U.2021/2022 Page 1 sur 3 TP N°2 (L2CS) Ingén

Ingénierie Bases de Données L2CS A.U.2021/2022 Page 1 sur 3 TP N°2 (L2CS) Ingénierie Base de Données sous Oracle (PL/SQL)  Création d’un bloc PL/SQL.  Déclaration et utilisation de variables dans un programme PL/SQL.  Utilisation des structures conditionnelles et boucles en PL/SQL.  Utilisation des excptions.  Utilisation des curseurs. I- Application : Soit le schéma relationnel de la base de données (très simplifiée) de gestion du transport aérien : PILOTE (Nopilot, Nom, adresse, Salaire, Comm, Embauche) AVION (NuAvion, #type, annserv, nom, nbhvol) VOL (NoVol, VilDep, VilAr, dep_h, dep_mn, ar_h, ar_mn, ch_jour) APPAREIL (codetype, nbplace, design) AFFECTATION (#vol, datevol, #pilote, #avion, nbpass) Remarques et Recommandations : 1) Pour chacune des questions suivantes, créer un nouveau bloc note, écrire le bloc PL-SQL correspondant et l’exécuter sous l’environnement SQL+, en utilisant l’ordre SQL suivant : SQL> START c:\chemin\nom.txt Ou bien SQL> @ c:\chemin\nom.txt 2) Si des erreurs de syntaxe existent dans le bloc crée alors, le moteur SQL va afficher des messages d’erreurs qu’il faut corriger et ré-exécuter de la même manière que dans 1) jusqu’à ce que le message « Procédure PL/SQL terminée avec succès. » s’affiche. Si l’exécution ne retourne aucun résultat, le message d’erreur suivant s’affiche : « ORA-01403: Aucune donnée trouvée » 3) Pour qu’un commentaire ou bien une valeur soit affiché(e), il faut exécuter tout d’abord l’ordre SQL suivant sous l’environnement SQL+ : SQL> SET SERVEROUTPUT ON; 4) Chaque bloc crée se termine obligatoirement par un « / », si non le moteur d’exécution demandera indéfiniment la fin du bloc. Créer les différentes tables de la base de données et puis insérer les données en exécutant les deux fichiers scriptdecreationTP2_21_22.txt et scriptderemplissage21_22.txt  Déclaration d’un type simple 1) Ecrire un bloc PL/SQL qui permet d’afficher le nombre de pilotes ainsi que le nombre d’avions existants dans la base. Les résultats seront affichés comme suit : Le nombre de pilotes existants dans la base est de : 12 Le nombre d’avions existants dans la base est de : 7 Ingénierie Bases de Données L2CS A.U.2021/2022 Page 2 sur 3 2) Ecrire un bloc PL/SQL qui permet d’afficher pour le pilote numéro '8843' le nombre d’avion qu’il a piloté. Le résultat sera affiché comme suit : Le nombre d’avion pilotés est : 2  Déclaration d’un type faisant référence à une table 3) Ecrire un bloc PL/SQL qui permet d’afficher la ville de départ, l’heure de départ, la ville d’arrivée et l’heure d’arrivée du vol numéro 'IW201' Le résultat sera affiché comme suit : Le numéro de vol est: IW201 Ville Départ: LYON Heure Départ: 9h Ville Arrivée: FORT DE FRANCE Heure Arrivée: 15h 4) A- Ecrire un bloc PL/SQL qui permet d’afficher le nom de l’avion ayant effectué plus de cinq vols. B- Modifier le bloc PL/SQL pour écrire un traitement d’exception pour l’anomalie constatée afin de passer un message à l’utilisateur lorsque l’avion spécifié n’existe pas.  Déclaration d’un type composé 5) Ecrire un bloc PL/SQL qui permet d’afficher le nom et la commission du pilote ayant le salaire le moins élevé. Proposer deux solutions différentes, la première en se basant sur les types scalaires et la deuxième sur les types composés. Dans les deux solutions, le résultat sera affiché comme suit : Le nom du pilote est : MARTIN La commission de ce pilote est : 16000  Requêtes simples, Structures conditionnelles et exception 5) Ecrire un bloc PL/SQL anonyme qui permet de :  compter le nombre total de n-uplets dans la table Pilote et stocker le résultat dans une variable ;  compter le nombre des pilotes dont la commission est non nulle et stocker le résultat dans une deuxième variable ;  calculer la proportion (en pourcentage), stocker le résultat dans une troisième variable et afficher le résultat à l’écran. Inclure dans le programme précédent une exception pour détecter si la table Pilote est vide (c’est-à- dire que le nombre total de n-uplets dans Pilote égal à zéro), dans ce cas, déclencher une erreur fatale RAISE_APPLICATION_ERROR (on ne peut pas permettre une division par zéro). Tester le programme en effectuant les étapes suivantes :  valider les mises à jour précédentes à l’aide de la commande SQL COMMIT ;  effacer le contenu de la table Pilote ;  exécuter le bloc PL/SQL ;  annuler l’effacement de la table Pilote à l’aide de la commande SQL ROLLBACK. Ingénierie Bases de Données L2CS A.U.2021/2022 Page 3 sur 3  Utilisation des curseurs 6) Ecrire un bloc PL/SQL qui permet de majorer le salaire de 15% du pilote N° '6548', si et seulement si la valeur de sa commission est supérieure à la valeur de son salaire. Dans tous les cas, tenir compte d’un message indiquant si son salaire est majoré de 15% ou bien affiché « salaire inférieur à la commission ». Modifier le programme précédent en utilisant le dernier curseur implicite pour afficher le nombre des pilotes affectés par ce changement. 7) Ecrire un bloc PL-SQL permettant d’afficher les noms et les adresses des pilotes dont le salaire dépasse 22000 et la commission est non nulle, en utilisant un curseur explicite. Le résultat sera affiché de la manière suivante : Adresse du pilote FEDOI est: NANTES Adresse du pilote BARRE est: LYON 8) Ecrire un bloc PL-SQL permettant d’afficher toutes les informations concernant les avions dont le nombre des heures de vol dépassent la moyenne des nbhvol de tous les avions de la base. Utiliser la notion ‘%ROWTYPE’ avec un curseur explicite. Le résultat sera affiché de la manière suivante : Avion: 7693, Type: 741, Année de mise en service: 1988, Nom avion: Pacifique, Nombre heure de vol: 34000 Avion: 8432, Type: AB3, Année de mise en service: 1991, Nom avion: Malte, Nombre heure de vol: 106000 uploads/Voyage/ tp2-ing-bd-l2cs-s-tp.pdf