TP PL/SQL Exercice corrigé Oracle langage PLSQL TD avec correction

Considérez les Tables de Données suivantes:

Table Avion :

Table Pilote : 

Table Passager : 

Table Vol : 

Table Depart : 

Table Reservation : 

Exercice 1 

Ecrivez  un  programme  PL/SQL  qui  insère  le  vol  AF110  partant  de  Paris  à  21h40  et  arrivant  à Dublin à 23h10 (hypothèse : le vol n’est pas déjà présent dans la table).

Exercice 2

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 un vol et un seul satisfaisant les contraintes. 

 

Escales : 

Exercice 3 

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

 
Exercice 4

 
Modifiez  le  programme  PL/SQL  de  l’exercice  2  pour  qu’il  fonctionne  même  si  plusieurs  vols satisfont les contraintes.

 
Exercice 5

 
Ecrivez  une  procédure PL/SQL  capable  de  faire  des  propositions  de  tours  du monde,  prenant  en entrée la ville de départ (qui est aussi la destination finale) et deux bornes (supérieure et inférieure) pour  le nombre d’escales. Dans ce cas il n’y a pas de  liste prédéfinie d’escales et on ne s’intéresse pas à la durée des escales. Cherchez à utiliser une procédure ou fonction récursive (PL/SQL admet la récursivité).

 
Exercice 6

Transformez  le  programme  du  dernier  exercice  du  précédent  ED  en  procédure  prenant  comme paramètre  le  nombre  d’escales. Regroupez-la  avec  la  procédure  de  l’exercice  précédent  dans  un paquetage. Pouvons-nous définir des curseurs et/ou variables communes entre les deux procédures ?

-------------------------------------------------------------------------------------------------------

Correction PL/SQL

-------------------------------------------------------------------------------------------------------

Corrigé Exercice 1 : PL/SQL

DECLARE
     heure1  CHAR(5) := ‘21-40’;      heure2  CHAR(5) := ‘23-10’;      ville1  VARCHAR2(20) := 'Paris';      ville2  VARCHAR2(20) := 'Dublin';
BEGIN      INSERT INTO Vol VALUES ('AF110', heure1, heure2, ville1, ville2);
END; 

Corrigé Exercice 2 :

SQL> ACCEPT s_nde PROMPT ‘Nombre d’escales : ’

DECLARE

     numEscaleCourante Escales.Numescale%TYPE;

     derniereEscale Escales.Numescale%TYPE;

     escaleCourante Escales.Ville_escale%TYPE;

     dureeEscaleCourante Escales.Duree_escale%TYPE;

     prochaineDestination Vol.Ville_arrivee%TYPE;

     numeroVol Vol.Numvol%TYPE;

     heureDepart Vol.Heure_depart%TYPE;

     destinationFinale Vol.Ville_arrivee%TYPE := ‘Paris’;

BEGIN

     numEscaleCourante := 1;

     derniereEscale := &s_nde;

     IF (derniereEscale > 8) THEN

          DBMS_OUTPUT.PUT_LINE(‘Au maximum 8 escales !’);

     ELSIF (derniereEscale < 1) THEN

          DBMS_OUTPUT.PUT_LINE(‘Au minimum 1 escale !’);

     ELSE

          LOOP

               SELECT Ville_escale, Duree_escale

                    INTO escaleCourante, dureeEscaleCourante

                    FROM Escales WHERE (Numescale = numEscaleCourante);

               IF (numEscaleCourante = derniereEscale) THEN

                    prochaineDestination := destinationFinale;

               ELSE

                    SELECT Ville_escale INTO prochaineDestination

                             FROM Escales

                             WHERE (Numescale = numEscaleCourante + 1);

               END IF;

               SELECT Numvol, Heure_depart INTO numeroVol, heureDepart

                              FROM Vol WHERE (Ville_depart = escaleCourante AND

                                             Ville_arrivee = prochaineDestination);

               DBMS_OUTPUT.PUT_LINE(‘A partir de ’ || escaleCourante ||

                              ‘ (durée’ || dureeEscaleCourante ||

                              ‘ jours) prendre vol ’ || numeroVol ||

                              ‘ à ’ || heureDepart);

               numEscaleCourante := numEscaleCourante + 1;

               EXIT WHEN (numEscaleCourante > derniereEscale);

          END LOOP;

     END IF;

END;

Corrigé Exercice 3 : 

DECLARE
     CURSOR curseur1 IS SELECT Salaire FROM Pilote
               WHERE (Age >= 30 AND Age <=40);
     salairePilote  Pilote.Salaire%TYPE;
     sommeSalaires  NUMBER(11,2) := 0;
     moyenneSalaires  NUMBER(11,2);
BEGIN
     OPEN curseur1;
     LOOP
          FETCH curseur1 INTO salairePilote;
          EXIT WHEN (curseur1%NOTFOUND OR curseur1%NOTFOUND IS NULL);
          sommeSalaires := sommeSalaires + salairePilote;
     END LOOP;
     moyenneSalaires := sommeSalaires / curseur1%ROWCOUNT;
     CLOSE curseur1;
     DBMS_OUTPUT.PUT_LINE(‘Moyenne salaires (pilotes de 30 à 40 ans) : ’ || 
          moyenneSalaires);
END;

Corrigé Exercice 4 :  

SQL> ACCEPT s_nde PROMPT ‘Nombre d’escales : ’
DECLARE
nbMaxEscales NUMBER := 0;
escaleCourante Escales.Ville_escale%TYPE;
prochaineDestination Vol.Ville_arrivee%TYPE;
dureeEscaleCourante Escales.Duree_escale%TYPE;
numEscaleCourante Escales.Numescale%TYPE;
derniereEscale Escales.Numescale%TYPE;
volAPrendre Vol%ROWTYPE;
CURSOR curseur1 IS SELECT * FROM Vol
WHERE (Ville_depart = escaleCourante AND
 Ville_arrivee = prochaineDestination);
BEGIN
numEscaleCourante := 1;
derniereEscale := &s_nde;
SELECT COUNT(*) INTO nbMaxEscales FROM Pilotes;
IF (derniereEscale > nbMaxEscales) THEN
DBMS_OUTPUT.PUT_LINE(‘Au maximum ’ || nbMaxEscales || ‘ escales !’);
ELSIF (derniereEscale < 1) THEN
DBMS_OUTPUT.PUT_LINE(‘Au minimum 1 escale !’);
ELSE
<<boucleEscales>>
LOOP
SELECT Ville_escale, Duree_escale
INTO escaleCourante, dureeEscaleCourante
FROM Escales WHERE (Numescale = numEscaleCourante);
IF (numEscaleCourante = derniereEscale) THEN
prochaineDestination := destinationFinale;
ELSE
SELECT Ville_escale INTO prochaineDestination
FROM Escales
WHERE (Numescale = numEscaleCourante + 1);
END IF; 
OPEN curseur1;
FETCH curseur1 INTO volAPrendre;
IF (curseur1%NOTFOUND OR curseur1%NOTFOUND IS NULL) THEN
DBMS_OUTPUT.PUT_LINE(‘Aucun vol disponible de ’ ||
escaleCourante);
EXIT boucleEscales;
ELSE  -- retourner au maximum 10 propositions de vol
WHILE (curseur1%FOUND AND curseur1%ROWCOUNT <= 10) LOOP
DBMS_OUTPUT.PUT_LINE(‘A partir de ’ ||
escaleCourante || ‘ (durée’ ||
dureeEscaleCourante ||
‘ jours) prendre vol ’ ||
volAPrendre.Numvol ||
‘ à ’ || volAPrendre.Heure_depart);
FETCH curseur1 INTO volAPrendre;
END LOOP;
END IF;
CLOSE curseur1;
numEscaleCourante := numEscaleCourante + 1;
EXIT WHEN (numEscaleCourante > derniereEscale);
END LOOP;
END IF;
EXCEPTION
WHEN TOO_MANY_ROWS THEN OR NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘La table des escales est mal définie !’);
END; 
     
Corrigé Exercice 5 :   

PROCEDURE trouverEscales(villeBase IN Vol.Ville_depart%TYPE,
                                                nbMinEscales IN INTEGER, nbMaxEscales IN INTEGER)
IS
              FUNCTION tourValide(destCourante IN Vol.Ville_depart%TYPE,
                                                    villeDepart IN Vol.Ville_depart%TYPE,                                                     minEscales IN INTEGER, maxEscales IN INTEGER)
                       RETURN BOOLEAN
IS                        valeurRetour BOOLEAN := FALSE;                        quelVol Vol%ROWTYPE;                        CURSOR curseurVol IS SELECT * FROM Vol                                               WHERE (Ville_arrivee = destCourante);
BEGIN                        FOR quelVol IN curseurVol LOOP                                    IF (((quelVol.Ville_depart = villeDepart) AND (minEscales>0)) 
                       OR                                    ((quelVol.Ville_depart != villeDepart) AND (maxEscales<1)))
THEN
          NULL;
ELSIF (quelVol.Ville_depart = villeDepart) THEN         DBMS_OUTPUT.PUT_LINE(‘De ’ || quelVol.Ville_depart ||         ‘ à ’ || destCourante || ‘ : ’ ||         quelVol.Numvol);         valeurRetour := TRUE;
ELSIF tourValide(quelVol.Ville_depart, villeDepart,         minEscales-1, maxEscales-1) THEN         DBMS_OUTPUT.PUT_LINE(‘De ’ || quelVol.Ville_depart ||         ‘ à ’ || destCourante || ‘ : ’ ||         quelVol.Numvol);         valeurRetour := TRUE;
END IF;
END LOOP;         RETURN valeurRetour;
EXCEPTION         WHEN OTHERS THEN RETURN FALSE;
END tourValide;
BEGIN
IF ((nbMinEscales > nbMaxEscales) OR (nbMaxEscales < 1)) THEN
        DBMS_OUTPUT.PUT_LINE(‘Vérifier contraintes pour nombre d’escales’);
ELSIF tourValide(villeBase, villeBase, nbMinEscales, nbMaxEscales) THEN         DBMS_OUTPUT.PUT_LINE(‘Faites votre choix !’);
ELSE         DBMS_OUTPUT.PUT_LINE(‘Aucun tour valide n’a été trouvé !’);
END FOR;
END trouverEscales; 

PL/SQL - Corrigé Exercice 6 :  

CREATE OR REPLACE PACKAGE paquetageEscales
AS
PROCEDURE proposerVols(nbEscales IN Escales.Numescale%TYPE);
PROCEDURE trouverEscales(villeBase IN Vol.Ville_depart%TYPE,
  nbMinEscales IN INTEGER,
  nbMaxEscales IN INTEGER);
END paquetageEscales;

CREATE OR REPLACE PACKAGE BODY paquetageEscales
AS
PROCEDURE proposerVols(nbEscales IN Escales.Numescale%TYPE)
IS
nbMaxEscales NUMBER := 0;
escaleCourante Escales.Ville_escale%TYPE;
prochaineDestination Vol.Ville_arrivee%TYPE;
dureeEscaleCourante Escales.Duree_escale%TYPE;
numEscaleCourante Escales.Numescale%TYPE;
volAPrendre Vol%ROWTYPE;
CURSOR curseur1 IS SELECT * FROM Vol
WHERE (Ville_depart = escaleCourante AND
 Ville_arrivee = prochaineDestination);
BEGIN
numEscaleCourante := 1;
nbEscales:= &s_nde;
SELECT COUNT(*) INTO nbMaxEscales FROM Pilotes; 
IF (nbEscales > nbMaxEscales) THEN
DBMS_OUTPUT.PUT_LINE(‘Au maximum ’ || nbMaxEscales || ‘ escales !’);
ELSIF (nbEscales < 1) THEN
DBMS_OUTPUT.PUT_LINE(‘Au minimum 1 escale !’);
ELSE
<<boucleEscales>>
LOOP
SELECT Ville_escale, Duree_escale
INTO escaleCourante, dureeEscaleCourante
FROM Escales WHERE (Numescale = numEscaleCourante);
IF (numEscaleCourante = nbEscales) THEN
prochaineDestination := destinationFinale;
ELSE
SELECT Ville_escale INTO prochaineDestination
FROM Escales
WHERE (Numescale = numEscaleCourante + 1);
END IF;
OPEN curseur1;
FETCH curseur1 INTO volAPrendre;
IF (curseur1%NOTFOUND OR curseur1%NOTFOUND IS NULL) THEN
DBMS_OUTPUT.PUT_LINE(‘Aucun vol disponible de ’ ||
escaleCourante);
EXIT boucleEscales;
ELSE  -- retourner au maximum 10 propositions de vol
WHILE (curseur1%FOUND AND curseur1%ROWCOUNT <= 10) LOOP
DBMS_OUTPUT.PUT_LINE(‘A partir de ’ ||
escaleCourante || ‘ (durée’ ||
dureeEscaleCourante ||
‘ jours) prendre vol ’ ||
volAPrendre.Numvol ||
‘ à ’ || volAPrendre.Heure_depart);
FETCH curseur1 INTO volAPrendre;
END LOOP;
END IF;
CLOSE curseur1;
numEscaleCourante := numEscaleCourante + 1;
EXIT WHEN (numEscaleCourante > nbEscales);
END LOOP;
END IF;
EXCEPTION
WHEN TOO_MANY_ROWS THEN OR NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘La table des escales est mal définie !’);
END proposerVols; 
PROCEDURE trouverEscales(villeBase IN Vol.Ville_depart%TYPE,
  nbMinEscales IN INTEGER, nbMaxEscales IN INTEGER)
IS
…
END trouverEscales;

END paquetageEscales;