Bonjour à tous,

Je travaille actuellement sur un projet PL/SQL Oracle dans le cadre de mes études, et je me trouve confronté à quelques difficultés. Le projet consiste à développer une interface pour une compagnie aérienne, permettant de gérer l’affectation des équipages aux vols. J'ai déjà un script initial (voir ci-dessous), mais j'ai besoin d'aide pour le finaliser et pour assurer qu'il répond bien aux exigences du projet. J'ai besoins d'aide quant à la correction de certains soucis, que je ne remarque pas, depuis il faut le dire 1 week-end complet... Je pensais avoir pensé à tout gérer mais visiblement, des soucis que je ne remarque pas, perdurent.

Voici un aperçu du script SQL que j'ai déjà :

BEGIN
   EXECUTE IMMEDIATE 'DROP PROCEDURE VolsLiaison';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 AND SQLCODE != -4043 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP FUNCTION DureeMoyVol';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 AND SQLCODE != -4043 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP PROCEDURE Programmer';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 AND SQLCODE != -4043 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE Equipage';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE DepartVol';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE Personnel';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE Vol';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

BEGIN
   EXECUTE IMMEDIATE 'DROP SEQUENCE seq_affectation';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 AND SQLCODE != -4043 AND SQLCODE != -2289 THEN
         RAISE;
      END IF;
END;
/

CREATE TABLE Vol (
    NoVol VARCHAR2(20) PRIMARY KEY,
    Depart VARCHAR2(50) NOT NULL,
    Arrivee VARCHAR2(50) NOT NULL
);

CREATE TABLE DepartVol (
    NoVol VARCHAR2(20),
    DateHeureDepart DATE NOT NULL,
    DureeVol NUMBER NOT NULL,
    PRIMARY KEY (NoVol, DateHeureDepart),
    FOREIGN KEY (NoVol) REFERENCES Vol(NoVol)
);

CREATE TABLE Personnel (
    Matricule NUMBER PRIMARY KEY,
    Nom VARCHAR2(50) NOT NULL,
    Categorie VARCHAR2(20) CHECK (Categorie IN ('Navigant', 'Non Navigant')),
    Fonction VARCHAR2(50) NOT NULL
);

CREATE SEQUENCE seq_affectation START WITH 1;

CREATE TABLE Equipage (
    NoAffectation NUMBER DEFAULT seq_affectation.NEXTVAL,
    NoVol VARCHAR2(20) NOT NULL,
    DateHeureDepart DATE NOT NULL,
    Matricule NUMBER NOT NULL,
    FOREIGN KEY (NoVol, DateHeureDepart) REFERENCES DepartVol(NoVol, DateHeureDepart),
    FOREIGN KEY (Matricule) REFERENCES Personnel(Matricule)
);


CREATE OR REPLACE PROCEDURE Programmer(noVol IN VARCHAR2, DateHeureDep IN DATE, DV IN NUMBER) IS
    departExiste NUMBER;
BEGIN
    -- Vérifier si le départ de vol existe déjà
    SELECT COUNT(*) INTO departExiste FROM DepartVol WHERE NoVol = noVol AND DateHeureDepart = DateHeureDep;

    IF DV IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'La durée de vol ne peut pas être NULL.');
    ELSIF DateHeureDep <= SYSDATE THEN
        RAISE_APPLICATION_ERROR(-20002, 'La date de départ ne peut pas être dans le passé.');
    ELSIF departExiste = 0 AND DV > 0 THEN
        -- Ajout d'un nouveau départ de vol
        INSERT INTO DepartVol(NoVol, DateHeureDepart, DureeVol) VALUES(noVol, DateHeureDep, DV);
    ELSIF departExiste > 0 AND DV > 0 THEN
        -- Mise à jour du départ de vol existant
        UPDATE DepartVol SET DureeVol = DV WHERE NoVol = noVol AND DateHeureDepart = DateHeureDep;
    ELSIF departExiste > 0 AND DV = 0 THEN
        -- Suppression du départ de vol et de son équipage
        DELETE FROM Equipage WHERE NoVol = noVol AND DateHeureDepart = DateHeureDep;
        DELETE FROM DepartVol WHERE NoVol = noVol AND DateHeureDepart = DateHeureDep;
    ELSE
        RAISE_APPLICATION_ERROR(-20003, 'Opération non valide.');
    END IF;

    COMMIT;
END Programmer;
/

CREATE OR REPLACE FUNCTION DureeMoyVol(noVol IN VARCHAR2) RETURN NUMBER IS
    dureeMoyenne NUMBER;
    volExiste NUMBER;
BEGIN
    -- Vérifier si le vol existe
    SELECT COUNT(*) INTO volExiste FROM Vol WHERE NoVol = noVol;
    IF volExiste = 0 THEN
        RETURN -1; -- Vol n'existe pas
    END IF;

    -- Calculer la durée moyenne pour les vols programmés
    SELECT AVG(DureeVol) INTO dureeMoyenne FROM DepartVol WHERE NoVol = noVol;
    IF dureeMoyenne IS NULL THEN
        RETURN 0; -- Aucun vol programmé
    ELSE
        RETURN dureeMoyenne / 60; -- Convertir en heures
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN -1; -- Vol n'existe pas
END DureeMoyVol;
/

CREATE OR REPLACE PROCEDURE VolsLiaison(Depart IN VARCHAR2, Arrivee IN VARCHAR2) IS
    volTrouve BOOLEAN := FALSE;
    noVol VARCHAR2(20);
    dureeMoyenneVol NUMBER;
BEGIN
    FOR volRec IN (SELECT NoVol FROM Vol WHERE Depart = Depart AND Arrivee = Arrivee) LOOP
        volTrouve := TRUE;
        dureeMoyenneVol := DureeMoyVol(volRec.NoVol);
        DBMS_OUTPUT.PUT_LINE('Vol: ' || volRec.NoVol || ' Durée Moyenne: ' || dureeMoyenneVol || ' heures');
    END LOOP;

    IF NOT volTrouve THEN
        DBMS_OUTPUT.PUT_LINE('Pas de vol pour cette liaison');
    END IF;
END VolsLiaison;
/

Les exigences du projet sont les suivantes :

1. Concevoir un schéma physique de la base de données avec des contraintes d'intégrité robustes.
2. Effectuer des opérations en PL/SQL pour :
   • Programmer un départ de vol.
   • Affecter un personnel à un départ de vol.
   • Calculer, rechercher et afficher des informations spécifiques.
3. Toutes les procédures doivent être écrites de manière transactionnelle.

Voici un aperçu du script SQL de test qui nous ai fourni :

-- Test pour l'�valuation du projet PAIR --
-------------------------------------------------
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24MI';
prompt // D�but du TEST pour l'�valuation du projet Pair : Programmation des vols //
prompt 
prompt - Suppression des donn�es existantes...
DELETE FROM DepartVol;
DELETE FROM Equipage;
DELETE FROM Personnel;
DELETE FROM Vol;
prompt - Insertion des donn�es pour le test...
prompt - Vol
INSERT INTO Vol(NoVol, Depart, Arrivee) VALUES('V1', 'Paris CDG', 'New York JFK');
INSERT INTO Vol(NoVol, Depart, Arrivee) VALUES('V2', 'Paris CDG', 'Casablanca');
INSERT INTO Vol(NoVol, Depart, Arrivee) VALUES('V3', 'Paris CDG', 'New York JFK');
prompt - Personnel
INSERT INTO Personnel(Matricule, Nom, Categorie, Fonction) VALUES(101, 'Paul', 'Navigant', 'Pilote');
INSERT INTO Personnel(Matricule, Nom, Categorie, Fonction) VALUES(102, 'Pierre', 'Navigant', 'Personnel de bord');
INSERT INTO Personnel(Matricule, Nom, Categorie, Fonction) VALUES(103, 'Durand', 'Non Navigant', 'Technicien');
COMMIT;
prompt 
prompt // Procedure Programmer //
prompt - Ajout de 4 d�parts : 3 pour 'V1' et 1 pour 'V2' (4)
EXECUTE Programmer('V1','05/01/2024 12:00',360);
EXECUTE Programmer('V1','12/01/2024 12:00',360);
EXECUTE Programmer('V1','19/01/2024 12:00',345);
EXECUTE Programmer('V2','26/01/2024 09:00',180);
prompt - Modification de la dur�e du vol (3)
EXECUTE Programmer('V2','26/01/2024 09:00',200);
prompt - Erreur 1 : Date de d�part d�pass�e (3)
EXECUTE Programmer('V2','21/11/2023 09:00',180);
prompt - Erreur 2 : Dur�e de vol NULL (3)
EXECUTE Programmer('V2','14/02/2024 09:00',NULL);
prompt - Erreur 3 : Vol inexistant (3)
EXECUTE Programmer('V4','31/01/2024 08:00',360);
prompt - Les 4 d�parts de vols ins�r�s
SELECT * FROM DepartVol;
prompt - Insertion de l'�quipage du vol 'V2' du '26/01/2024 09:00'
INSERT INTO Equipage(NoAffectation,NoVol,DateHeureDepart,Matricule) VALUES(1,'V2','26/01/2024 09:00',101);
COMMIT
prompt - Suppression d'un d�part du vol 'V2' et de son �quipage (4)
EXECUTE Programmer('V2','26/01/2024 09:00',0);
prompt - Les 3 d�parts de vols restants
SELECT * FROM DepartVol;
prompt - Les �quipages (aucun)
SELECT * FROM Equipage;
prompt
prompt // Fonction DureeMoyVol //
prompt - Dur�e vol 'V1' = 5,92 (8)
SELECT DureeMoyVol('V1') FROM Dual;
prompt - Dur�e vol non programm� 'V3' = 0 (6)
SELECT DureeMoyVol('V3') FROM Dual;
prompt - Dur�e vol inexistant 'V4' = -1 (6)
SELECT DureeMoyVol('V4') FROM Dual;
prompt
prompt // Procedure VolsLiaison //
prompt - Vols 'Paris CDG' � 'New York JFK' : 'V1' 5,92 et 'V3' 0 (12)
EXECUTE VolsLiaison('Paris CDG', 'New York JFK');
prompt - Vols 'Paris CDG' � 'Casablanca' : Pas de vol pour cette liaison (8)
EXECUTE VolsLiaison('Paris CDG', 'Casablanca');
prompt
prompt ...Fin du test Pair

• Statement1

  ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI'

  Statement processed.
• Statement2

  DELETE FROM DepartVol

  0 row(s) deleted.
• Statement3

  DELETE FROM Equipage

  0 row(s) deleted.
• Statement4

  DELETE FROM Personnel

  0 row(s) deleted.
• Statement5

  DELETE FROM Vol

  0 row(s) deleted.
• Statement6

  INSERT INTO Vol(NoVol, Depart, Arrivee) VALUES('V1', 'Paris CDG', 'New York JFK')

  1 row(s) inserted.
• Statement7

  INSERT INTO Vol(NoVol, Depart, Arrivee) VALUES('V2', 'Paris CDG', 'Casablanca')

  1 row(s) inserted.
• Statement8

  INSERT INTO Vol(NoVol, Depart, Arrivee) VALUES('V3', 'Paris CDG', 'New York JFK')

  1 row(s) inserted.
• Statement9

  INSERT INTO Personnel(Matricule, Nom, Categorie, Fonction) VALUES(101, 'Paul', 'Navigant', 'Pilote')

  1 row(s) inserted.
• Statement10

  INSERT INTO Personnel(Matricule, Nom, Categorie, Fonction) VALUES(102, 'Pierre', 'Navigant', 'Personnel de bord')

  1 row(s) inserted.
• Statement11

  INSERT INTO Personnel(Matricule, Nom, Categorie, Fonction) VALUES(103, 'Durand', 'Non Navigant', 'Technicien')

  1 row(s) inserted.
• Statement12

  EXECUTE Programmer('V1','05/01/2024 12:00',360)

  Statement processed.
• Statement13

  EXECUTE Programmer('V1','12/01/2024 12:00',360)

  Statement processed.
• Statement14

  EXECUTE Programmer('V1','19/01/2024 12:00',345)

  Statement processed.
• Statement15

  EXECUTE Programmer('V2','26/01/2024 09:00',180)

  Statement processed.
• Statement16

  EXECUTE Programmer('V2','26/01/2024 09:00',200)

  Statement processed.
• Statement17

  EXECUTE Programmer('V2','21/11/2023 09:00',180)

  ORA-20002: La date de départ ne peut pas être dans le passé. ORA-06512: at "SQL_VGQEALUYQJWUMGURNTXSDBEDN.PROGRAMMER", line 10 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SQL", line 1721 
  
  More Details: https://docs.oracle.com/error-help/db/ora-20002
• Statement18

  EXECUTE Programmer('V2','14/02/2024 09:00',NULL)

  ORA-20001: La durée de vol ne peut pas être NULL. ORA-06512: at "SQL_VGQEALUYQJWUMGURNTXSDBEDN.PROGRAMMER", line 8 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SQL", line 1721 
  
  More Details: https://docs.oracle.com/error-help/db/ora-20001
• Statement19

  EXECUTE Programmer('V4','31/01/2024 08:00',360)

  ORA-02291: integrity constraint (SQL_VGQEALUYQJWUMGURNTXSDBEDN.SYS_C00140634513) violated - parent key not found ORA-06512: at "SQL_VGQEALUYQJWUMGURNTXSDBEDN.PROGRAMMER", line 13 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SQL", line 1721 
  
  More Details: https://docs.oracle.com/error-help/db/ora-02291
• Statement20

  SELECT * FROM DepartVol

  NOVOL	DATEHEUREDEPART	DUREEVOL
  V1	05/01/2024 12:00	360
  V1	12/01/2024 12:00	360
  V1	19/01/2024 12:00	345
  V2	26/01/2024 09:00	200

  Download CSV
  4 rows selected.
• Statement21

  INSERT INTO Equipage(NoAffectation,NoVol,DateHeureDepart,Matricule) VALUES(1,'V2','26/01/2024 09:00',101)

  1 row(s) inserted.
• Statement22

  EXECUTE Programmer('V2','26/01/2024 09:00',0)

  Statement processed.
• Statement23

  SELECT * FROM DepartVol

  NOVOL	DATEHEUREDEPART	DUREEVOL
  V1	05/01/2024 12:00	360
  V1	12/01/2024 12:00	360
  V1	19/01/2024 12:00	345

  Download CSV
  3 rows selected.
• Statement24

  SELECT * FROM Equipage

  no data found
• Statement25

  SELECT DureeMoyVol('V1') FROM Dual

  DUREEMOYVOL('V1')
  5.91666666666666666666666666666666666667

  Download CSV
• Statement26

  SELECT DureeMoyVol('V3') FROM Dual

  DUREEMOYVOL('V3')
  5.91666666666666666666666666666666666667

  Download CSV
• Statement27

  SELECT DureeMoyVol('V4') FROM Dual

  DUREEMOYVOL('V4')
  5.91666666666666666666666666666666666667

  Download CSV
• Statement28

  EXECUTE VolsLiaison('Paris CDG', 'New York JFK')

  Statement processed.
  Vol: V1 Durée Moyenne: 5.91666666666666666666666666666666666667 heures
  Vol: V2 Durée Moyenne: 5.91666666666666666666666666666666666667 heures
  Vol: V3 Durée Moyenne: 5.91666666666666666666666666666666666667 heures
  
• Statement29

  EXECUTE VolsLiaison('Paris CDG', 'Casablanca')

  Statement processed.
  Vol: V1 Durée Moyenne: 5.91666666666666666666666666666666666667 heures
  Vol: V2 Durée Moyenne: 5.91666666666666666666666666666666666667 heures
  Vol: V3 Durée Moyenne: 5.91666666666666666666666666666666666667 heures

Salut,

Ici j'ai rarement vu des forumeurs parlaient de PL/SQL et concernant ton sujet, d'une il est compliqué, de 2 quand tu parles de calculs spécifiques sans préciser ce qui doit être calculé, ça rend le sujet encore plus compliqué.

Maintenant, le cadre d'une compagnie aérienne je le définirais comme suit :

une compagnie a du personnel, des avions et des vols. Un avion et une partie du personnel est bloqué sur un seul vol avec une date de début et une date de fin. 

Je pense que les restrictions portent sur le fait qu'une personne ne peut pas être affectée sur le Paris New York de 14h à 20h et en même temps sur le Berlin Stockholm de 16h à 19h du même jour.

Je me suis renseigné sur PLSQL, langage propriétaire alors qu'il existe des milliers de langages libres dont Python et PHP pour gérer les contraintes SQL. Pour le bien de la population, tu devrais indiquer l'école qui t'enseigne afin qu'on la place en bas de la liste.

-
Edité par CristianoRolando 4 décembre 2023 à 1:24:24