S3 TD 1

CREATE PACKAGE pksorties AS

    FUNCTION les_minibus RETURN NUMBER;
    FUNCTION affectations_minibus(p sortie.nosortie%TYPE) RETURN NUMBER;

END;


CREATE OR REPLACE PACKAGE BODY pksorties AS
  TYPE type_tabminibus IS TABLE OF minibus%ROWTYPE INDEX BY BINARY_INTEGER;
  v_err VARCHAR2(255);
  v_tableminibus type_tabminibus;


  FUNCTION les_minibus
    RETURN NUMBER AS

    CURSOR c_minibus IS SELECT *
                        FROM MINIBUS
                        ORDER BY CAPACITE DESC;
    v_i NUMBER(4) := 1;

    BEGIN

      FOR v_tab IN c_minibus LOOP
        v_tableminibus(les_minibus.v_i) := v_tab;
        les_minibus.v_i := les_minibus.v_i + 1;
      END LOOP;

      RETURN v_i;

      EXCEPTION
      WHEN OTHERS
      THEN
        v_err := CURRENT_DATE || '\t' || SQLERRM || '\t' || SQLCODE;
        INSERT INTO ERREURS VALUES (v_err);
        RETURN -1;


    END;

  FUNCTION affectations_minibus(p IN sortie.nosortie%TYPE)
    RETURN NUMBER AS

    CURSOR c_users IS SELECT *
                      FROM INSCRIPTION_SORTIE
                      WHERE INSCRIPTION_SORTIE.NOSORTIE = p
                      ORDER BY INSCRIPTION_SORTIE.DATE_INSCRIPTION ASC FOR UPDATE;

    v_nb_minibus  NUMBER(4) := les_minibus();

    v_nb_occupant NUMBER(4);

    v_i           NUMBER(4) := 1;

    v_user        INSCRIPTION_SORTIE%ROWTYPE;


    v_err         ERREURS.MESSAGE%TYPE;

    noInput EXCEPTION ;

    BEGIN
      OPEN c_users;
      FETCH c_users INTO v_user;



      WHILE NOT (c_users%NOTFOUND) AND v_i <= v_nb_minibus
      LOOP
        SELECT COUNT(*)  INTO v_nb_occupant FROM INSCRIPTION_SORTIE WHERE NOMINIBUS = v_tableminibus(v_i).NOMINIBUS;

        IF(v_nb_minibus = 0) THEN
          RETURN 0;
        END IF;

        IF v_nb_occupant >= v_tableminibus(v_i).CAPACITE
        THEN
          v_i := v_i + 1;
        END IF;


        IF v_i <= v_nb_minibus
        THEN

          UPDATE INSCRIPTION_SORTIE SET INSCRIPTION_SORTIE.NOMINIBUS = v_tableminibus(v_i).NOMINIBUS WHERE CURRENT OF c_users;

        END IF;
        FETCH c_users INTO v_user;

      END LOOP;

      CLOSE c_users;

      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
        v_err := SYSDATE || '\t' || SQLERRM || '\t' || SQLCODE;
        INSERT INTO ERREURS VALUES (v_err);
        RETURN -1;

      WHEN OTHERS
      THEN
        v_err := SYSDATE || '\t' || SQLERRM || '\t' || SQLCODE;
        INSERT INTO ERREURS VALUES (v_err);
        RETURN -2;

    END;

END;


  BEGIN
    DBMS_OUTPUT.put_line(pksorties.AFFECTATIONS_MINIBUS(1));
  END;