---- TP sur PL/SQL -- Correction

---------------------------
-- 1.1 Creation des tables:
---------------------------

drop table ETUDIANT;
drop table ENS;

create table ENS( NOM_ENS VARCHAR2(50) PRIMARY KEY, 
		  NB_ETU NUMBER(2) default 0 check (nb_etu >=0));

create table ETUDIANT( ID_ETUDIANT integer PRIMARY KEY,
                       NOM_ETUDIANT VARCHAR2(50),
                       TUTEUR varchar2(50) REFERENCES ENS(NOM_ENS));

create sequence seq_etu;
	  
---------------------------
-- 1.2 SQL*Plus et PL/SQL:
---------------------------


set serveroutput on

declare
  x varchar2(50);
begin
  x:= 'Hello World';
  dbms_output.put_line(x);
end;
/

-- Pour réexecuter:

-- terminer par un point et taper run
run

-- S'il y a des erreurs:

show errors

--------------------------------------------------
-- 2: Blocs, procédures et fonctions non stockées:
--------------------------------------------------


create or replace procedure Nouvel_Etudiant(nom_etu in etudiant.nom_etudiant%type) as
  begin
    insert into etudiant values(seq_etu.nextval, nom_etu, null);
  end;
/

-- test:
begin
  nouvel_etudiant('Omar');
end;
/

create or replace function Nb_Etudiants(nom_ens in ens.nom_ens%type) RETURN number as
     nb_etu number(2);
  begin
    select count(*) into nb_etu 
    from etudiant
    where tuteur = nom_ens;
    return nb_etu;
  end;
/


insert into ens values('Abiteboul', null);
insert into ens values('Vianu', null);
insert into etudiant values(seq_etu.nextval, 'Omar', 'Abiteboul');
insert into etudiant values(seq_etu.nextval, 'Manu', 'Abiteboul');


begin
  nouvel_etudiant('Benjie');
  dbms_output.put_line('Serge a ' || nb_etudiants('Abiteboul') || ' étudiants.');
end;
/


-----------------------------
-- 3 Triggers et contraintes:
-----------------------------

-- Ce trigger gère aussi les modifications

create or replace trigger t1 
  after insert or delete or update of TUTEUR on ETUDIANT
for each row
  begin
    -- on rajoute un étudiant pour le nouveau tuteur
    if (:new.tuteur is not null) then
      update ens 
      set nb_etu = nb_etu+1
      where nom_ens = :new.tuteur;
    end if;
    -- on en enlève un pour l'ancien
    if (:old.tuteur is not null) then
      update ens 
      set nb_etu = nb_etu-1
      where nom_ens = :old.tuteur;
    end if;
  end;
/

--test:
-- On initialise Abiteboul et Vianu
update ens set nb_etu = 0 where nom_ens = 'Vianu';
update ens set nb_etu= 2 where nom_ens='Abiteboul';
-- puis on lui rajoute un étudiant
update etudiant set tuteur='Abiteboul' where nom_etudiant='Benjie';
-- on peut meme changer le tuteur de Benjie
update etudiant set tuteur='Vianu' where nom_etudiant='Benjie';
-- et supprimer Omar
delete from etudiant where nom_etudiant='Omar';

--A chaque fois on peut vérifier que la table ens suit bien...


-----------------------------
-- 4 Packages
-----------------------------


create or replace package etu_ens as

procedure Nouvel_Etudiant(nom_etu in etudiant.nom_etudiant%type) ;
procedure Nouvel_Enseignant(nom_ens in ens.nom_ens%type);
procedure Affecter(nom_ens in ens.nom_ens%type, nom_etu in etudiant.nom_etudiant%type);
function tuteur(nom_etu in etudiant.nom_etudiant%type) return ens.nom_ens%type;
function tuteur(id_etu in etudiant.id_etudiant%type) return ens.nom_ens%type;
procedure dec_etu(nom ens.nom_ens%type);
procedure inc_etu(nom ens.nom_ens%type);

end;
/

create or replace package body etu_ens as

procedure Nouvel_Etudiant(nom_etu in etudiant.nom_etudiant%type) 
as
  begin
    insert into etudiant values(seq_etu.nextval, nom_etu, null);
  end;


procedure Nouvel_Enseignant(nom_ens in ens.nom_ens%type) 
as
  existe_deja exception;
  pragma exception_init(existe_deja, -1);
  begin
    insert into ens values(nom_ens, null);
  exception
    when existe_deja then
    dbms_output.put_line('L''enseignant '||  nom_ens || ' existe deja');
  end;


-- Il se peut que l'enseignat n'existe pas, ou l'étudiant
--  On peut vouloir les insérer si c'est le cas
procedure Affecter(nom_ens in ens.nom_ens%type, nom_etu in etudiant.nom_etudiant%type)
as
 begin
   update etudiant
   set tuteur = nom_ens
   where nom_etudiant = nom_etu;
 end;

function tuteur(nom_etu in etudiant.nom_etudiant%type) return ens.nom_ens%type
as
  le_nom ens.nom_ens%type;
begin
  select tuteur into le_nom 
  from etudiant 
  where nom_etudiant = nom_etu;
  return le_nom;
end;


function tuteur(id_etu in etudiant.id_etudiant%type) return ens.nom_ens%type
as
  le_nom ens.nom_ens%type;
begin
  select tuteur into le_nom 
  from etudiant 
  where id_etudiant = id_etu;
  return le_nom;
end;


-- On pourra gérer les erreurs dues au check >=0  ici
procedure dec_etu(nom ens.nom_ens%type)
as
begin
  update ens
  set nb_etu = nb_etu - 1
  where nom_ens = nom;
end;

procedure inc_etu(nom ens.nom_ens%type)
as
begin
  update ens
  set nb_etu = nb_etu + 1
  where nom_ens = nom;
end;

end;
/

-- question 2: il y a plein de manières de le faire...
