TP PL/SQL Exercice PL-SQL Oracle TD langage SQL

Soit la base de données suivante : 

Table Avion :

Table Pilote : 

Table Passager : 

Table Vol : 

Table Depart : 

Table Reservation : 

Table Escales :

1.  Généralités SQL

 
Créez une table VotreNom.Vol en utilisant

CREATE TABLE Vol (Numvol VARCHAR2(8), Heure_depart DATE,
                                   Heure_arrive DATE, Ville_depart VARCHAR2(20),
                                   Ville_arrivee VARCHAR2(20))

Ecrivez  les  instructions  qui  copient  la  table  Vol  dans  la  table  VotreNom.Vol  (les  2  tables  ont  la même structure).

Remarque : la fonction TO_DATE convertit des chaînes de caractères en dates, suivant le format (voir la documentation en ligne). 

A partir de maintenant, pour  éviter  que d’éventuelles  erreurs ne  se  répercutent  sur  l’ensemble du groupe, vous vous servirez exclusivement de vos tables. 

Insérez une nouvelle ligne (valeurs de votre choix) dans la table VotreNom.Vol en utilisant :

COMMIT;
BEGIN
             INSERT INTO VotreNom.Vol (valeurs de votre choix, attention aux types);
END
/ 

Quittez  SQL*Plus  en  fermant  brutalement  la  fenêtre.  Reconnectez-vous  et  vérifiez  si l’enregistrement que vous avez inséré est présent dans la table.

Refaites  l’insertion  et  quittez  ensuite  SQL*Plus  avec  exit.  Reconnectez-vous  et  vérifiez  si l’enregistrement que vous avez inséré est présent dans la table.

2.  Fonctions et procédures

Expliquez les résultats obtenus par l’exécution du programme PL/SQL suivant :

DECLARE
             n NUMBER := 10;
             PROCEDURE etudeNocopy
                        (n1 IN NUMBER, n2 IN OUT NUMBER, n3 IN OUT NOCOPY NUMBER) IS
             BEGIN
                        n2 := 20;
                        DBMS_OUTPUT.PUT_LINE(n1);
                        n3 := 30;
                        DBMS_OUTPUT.PUT_LINE(n1); 

             END etudeNocopy;
BEGIN

             etudeNocopy(n, n, n); 

             DBMS_OUTPUT.PUT_LINE(n);
END;

3.  Paquetages 

Reprendre  les  exercices  3  et  4  des  ED  PL/SQL  (voir  ci-dessous)  et  les  écrire  sous  forme  de procédures stockées avec comme paramètre la ville de départ.

1. Surcharger  la procédure de  l'exercice 4  en  lui  ajoutant  les deux bornes  supérieure  et  inférieure limitant le nombre d'escales.

2 Regrouper toutes les fonctions dans le même paquetage.

Exercice 3 (ED PL/SQL) : Ecrivez un programme PL/SQL qui calcule la moyenne des salaires des pilotes dont l’âge est entre 30 et 40 ans. 

Exercice 4  (ED PL/SQL) : Ecrivez un programme PL/SQL qui propose  les vols pour un  tour du monde au départ de Paris avec des escales et des durées d’escale prédéfinies dans la table Escales. 

Le nombre  d’escales  à  faire doit  être  demandé  à  l’utilisateur. Hypothèse  de  travail : pour  chaque escale il existe plusieurs vols satisfaisant les contraintes.

4.  Déclencheurs

Ecrivez un déclencheur qui, avant un INSERT dans  la  table Vol, vérifie si un vol retour est présent dans  la  table  (pour  que  la  destination  soit  desservie  dans  les  2  sens)  et,  sinon,  affiche  un avertissement à la console. 

Essayez de déclarer le déclencheur AFTER au lieu de BEFORE. Que se passe-t-il ? Pourquoi ?

Ecrivez un déclencheur qui, avant chaque insertion ou mise à jour du numéro de vol ou du numéro d’avion dans  la  table Depart, vérifie si  l’avion employé a une capacité au moins égale au nombre de  réservations  et  a  comme  entrepôt  la  ville  de  départ.  Si  les  deux  conditions  sont  satisfaites  la modification est faite. Si la capacité est insuffisante et/ou l’entrepôt n’est pas la ville de départ, des messages spécifiques sont affichés.