# L3BD - Groupe 3 - TD11 - 06-04-21
#

/*1.a*/
create or replace view vue_village_sans_sejour
   as
   SELECT *
   FROM village
   WHERE  village.idv NOT IN(SELECT idv FROM sejour);

/*1.b*/
select * from village;
select * from sejour;
select * from vue_village_sans_sejour;






/* 2. */

select table_name from user_tables;
select view_name from user_views;
desc sejour;
select sequence_name from user_sequences;

select distinct name, type
from user_source;

select text from user_source
    where name = 'traitement2' and type = 'PROCEDURE'; /*code source procedure traitement 2*/



/*3. traitement2*/

-- traitement 2 :

create or replace procedure traitement2(
    l_idc client.idc%type,
    la_ville village.ville%type,
    le_jour sejour.jour%type,
    l_idv out village.idv%type,
    l_ids out sejour.ids%type,
    l_activite out village.activite%type)   
is
    cursor c is
        select idv, prix, activite 
            from village 
            where ville = la_ville
            order by prix desc;
    le_prix village.prix%type;
begin
    open c;
    fetch c into l_idv, le_prix, l_activite;
    if c%found then
        l_ids := seq_sejour.nextval; 
        insert into sejour 
            values(l_ids, l_idc, l_idv, le_jour);
        update client 
            set avoir = avoir - le_prix
            where idc = l_idc;
    else
        l_idv := -1;
	l_ids := -1;
	l_activite := 'neant';
    end if;
end;
/




/*pseudo code separation village*/
create or replace table nom_village
(
    idv int primary key,
    ville varchar2(12)
)

create or replace table act_village 
(
    idv int ,foreign key (idv) references nom_village,
    activite varchar2(10),
    prix int,
    capacite int
)

insert into nom_village (idv,ville) select idv, ville from village;
insert into act_village (idv,activite,prix,capacite) select idv, activite,prix,capacite from village;

/* verification SPI : les 2 select doivent avoir le meme resultat */
select * from village;
select n.idv,ville,activite,prix,capacite from nom_village n, act_village a where a.idv=n.idv;



/*remplir les tables nom_village et act_village avec village*/

select idv, prix, activite from village; /*tester si les deux requetes donne le même resultat*/
select idv, prix, activite from nom_village as v, act_village as a where a.idv = v.idv; 

drop table village;
/*maintenant la requete de traitement2 ne marche plus*/
select idv, prix, activite from village;


create or replace view view_village
as
select v.idv,prix,activite from nom_village as v, act_village as v2
where v.idv = v2.idv 
order by v2.prix desc;

/*la vue permet donc de modifier les tables nom_village,act_village
mais */
select * from view_village;