--------------------------------------------------------------------------------
-- corr_pl_exceptions.sql

--------------------------------------------------------------------------------
-- exceptions pour robustesse

/*
On considere la procedure achat precedemment programmee. 
On considere toutes les contraintes declarees dans l'exercice sur les
contraintes sur le cahier des charges (vers le TD 2 ou 3). 
On determine toutes les contraintes de cet ensemble qui sont susceptible d'etre
violees par les mises a jour de la procedure achat.
Pour chaque telle contrainte, on rettrape l'erreur qu'elle genere, et on agit
(ici on affiche simplement son code). 
Voici ces contraintes.

client : check solde>=0 (par le update)
sejour : rien
voyage : idc fk, ids fk, check 1<=jour<=365, (idc,jour) unique (par le insert)
table absente
colonne absente ou de mauvais type (non gere ici)
  rem : on choisit ici (mais ce n'est pas obligatoire) de tester l'existence de
        ids lors du select, donc seul le idc pourra generer une violation de fk
        lors du insert
*/

CREATE OR REPLACE PROCEDURE achat(
  p client.idc%TYPE,
  s sejour.ids%TYPE,
  d voyage.jour%TYPE)
IS
  x sejour.prix%TYPE;
  pb_check EXCEPTION;
  cle_etrangere_absente EXCEPTION;
  pas_de_table EXCEPTION;
  PRAGMA EXCEPTION_INIT(pb_check, -02290);
  PRAGMA EXCEPTION_INIT(cle_etrangere_absente, -02291);
  PRAGMA EXCEPTION_INIT(pas_de_table, -942);
  -- rajouter gestion unique (dup_val_on_index ?) et pbs colonne, cf ci-dessus
BEGIN
  SELECT prix INTO x FROM sejour WHERE ids = s;
  UPDATE client SET solde = solde - x WHERE idc = p;
  INSERT INTO voyage VALUES (p, s, d);
EXCEPTION 
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('ECHEC : Pas de sejour correspondant a '||s);
  WHEN pb_check THEN
    dbms_output.put_line('Erreur : violation check');
  WHEN cle_etrangere_absente THEN
    dbms_output.put_line('Erreur : Pas de client correspondant a '||p);
  WHEN pas_de_table THEN
    dbms_output.put_line('Erreur : l''une des tables n''existe pas');
--  a commenter pour voir le trigger :
  WHEN OTHERS THEN
    dbms_output.put_line('Erreur : Une erreur autre que celles gerees '||
                           's''estproduite'); 
    -- ex : contrainte non SQL ci-dessous...
END;
/

select * from client;
select * from sejour;
select * from voyage;

/*
       IDC NOM		     AGE      SOLDE
---------- ---------- ---------- ----------
	 2 titi 	      21       2000

       IDS DEST       ACTIVITE	       PRIX   NBPLACES
---------- ---------- ---------- ---------- ----------
	 2 NY	      MOMA		100	    20
*/

exec achat(3,2,365);
-- Erreur : Pas de client correspondant a 3

exec achat(2,3,365);
-- ECHEC : Pas de sejour correspondant a 3

exec achat(2,2,366);
-- Erreur : violation check

-- exec achat(,,) 
-- unique : dup_val_on_index

-- solde toujours ok ici cause procedure le gere correctement
-- exec achat(,,) -- a tester apres contrainte non SQL codee : capacite depassee

--------------------------------------------------------------------------------
-- trigger et exception/erreur pour contrainte non SQL

CREATE OR REPLACE FUNCTION capacites_sejours_respectees
RETURN BOOLEAN
IS
  occupation INTEGER;
  capacite sejour.nbplaces%type;
  CURSOR c IS SELECT ids, jour FROM voyage;
BEGIN
  FOR x in c LOOP
    SELECT nbplaces INTO capacite FROM sejour WHERE ids = x.ids;
    SELECT count(*) INTO occupation FROM voyage WHERE ids = x.ids AND jour = x.jour;
    IF occupation > capacite THEN
      RETURN FALSE;
    END IF;
  END LOOP;

  -- beaucoup mieux : une seule requete ! (optimisation)
  /*
    select count(*) into nb_pbs
    from sejour, voyage
    where sejour.ids = voyage.ids
    group by ids, jour
      having count(*) > sejour.nbplaces;
    return nb_pbs = 0;
  */

  RETURN occupation <= capacite;
END;
/

-- exec dbms_output.put_line(capacites_sejours_respectees); 
-- boolean ne s'affiche pas

create or replace procedure gerer is
begin
    IF NOT capacites_sejours_respectees THEN 
      RAISE_APPLICATION_ERROR(-20001, 'ECHEC : Capacite du sejour depassee');
    END IF;
end;
/

CREATE OR REPLACE TRIGGER verifier_capacite_voyage
    AFTER INSERT 
       or update of ids 
       or update of jour
    ON voyage
BEGIN
    dbms_output.put_line('trigger verifier_capacite_respecte_voyage');
    gerer;
END;
/

CREATE OR REPLACE TRIGGER verifier_capacite_sejour
    AFTER update of nbplaces
    ON sejour
BEGIN
    dbms_output.put_line('trigger verifier_capacite_respecte_sejour');
    gerer;
END;
/

-- test basique trigger sans contrainte :
insert into voyage values(2,2,365);

-- une seule place
insert into client(idc, nom, age) values(seq_client.nextval, 'toto', 22);
insert into sejour values(1, 'NY', 'MOMA', 100, 1);
select * from sejour;
select * from client;
select * from voyage;
exec achat(2,1,360);
-- ok
exec achat(3,1,360);
-- ORA-20001: ECHEC : Capacite du sejour depassee
-- ORA-04088: error during execution of trigger

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