drop table visa;
drop table pays;
drop table personne;

drop type visa_type;
drop type personne_type;
drop type enfant_nt_type;
drop type enfant_elt_nt_type;
drop type pays_type;
drop type telephone_vry_type;
drop type telephone_elt_vry_type;
drop type adresse_type;

create or replace type adresse_type 
as object(
numero number,
rue varchar2(30),
ville varchar2(30),
codepostal varchar2(5)
);
/

create or replace type pays_type
as object(
codepays varchar2(3),
nompays varchar2(30)
);
/

create or replace type telephone_elt_vry_type
as object(
numtel varchar2(20)
);
/

create or replace type telephone_vry_type 
as varray(3) of
telephone_elt_vry_type;
/


create or replace type enfant_elt_nt_type
as object(
prenomenf varchar2(25),
ageenf number,
etude varchar2(30)
);
/

create type enfant_nt_type as table of enfant_elt_nt_type;
/


create or replace type personne_type
as object(
codepers number,
nompers varchar2(30),
prenompers varchar2(25),
sexepers varchar2(1),
datenaissance date,
adresse adresse_type,
telephone_vry telephone_vry_type,
enfant_nt enfant_nt_type,
static procedure insererpersonne(
nompersonne in varchar2,
prenompersonne in varchar2,
adressepersonne in adresse_type
),
static procedure insererpersonne(
nompersonne in varchar2,
prenompersonne in varchar2),

static procedure ajouterpersonne(
nompersonne in varchar2,
prenompersonne in varchar2,
datenaissance in date),

member procedure majdatenaissance(
nouvelledatenaissance in date),
member function getage
return number,
member procedure
setnouvelleadresse(nouvelleadresse in adresse_type)
);
/

create type visa_type as object
(codeVisa number,
dateDemande date,
dateEntree date,
dateSortie date,
numeroPasseport varchar2(15),
motifSejour varchar2(80),
ref_personne ref personne_type,
ref_pays ref pays_type
);
/

create table pays of pays_type
(constraint pk_pays primary key (codePays));

create table personne of personne_type
(constraint pk_personne primary key (codepers))
nested table enfant_nt store as enfant_tabnt;

create table visa of visa_type
(constraint pk_visa primary key (codevisa));

alter table personne
add constraint nn_nompers check (nompers is not null)
add constraint nn_prenompers check (prenompers is not null)
add constraint ck_sexepers check (sexepers in ('M','F'));

create or replace type body
personne_type as
static procedure insererpersonne
( nompersonne in varchar2,
prenompersonne in varchar2,
adressepersonne in adresse_type) is
begin null;
end insererpersonne;

static procedure insererpersonne
( nompersonne in varchar2,
prenompersonne in varchar2) is 
begin
null;
end insererpersonne;

static procedure ajouterpersonne
(nompersonne in varchar2,
prenompersonne in varchar2,
datenaissance in date
) is
begin
null;
end ajouterpersonne;

member procedure majdatenaissance
( nouvelledatenaissance in date)
is begin null;
end majdatenaissance;

member function getage return number is
begin 
null;
end getage;

member procedure setnouvelleadresse
( nouvelleadresse in adresse_type) is
begin
null;
end setnouvelleadresse;
end;
/

-- create sequence seqcodepers increment by 1 start with 7;
-- select seqcodepers.nextval from dual;

create or replace type body
personne_type as
static procedure insererpersonne
( nompersonne in varchar2,
prenompersonne in varchar2,
adressepersonne in adresse_type) is
defautvaleur varchar2(1):='M';
compteurpersonne number;
begin 
select seqcodepers.nextval into compteurpersonne
from dual;
insert into personne (codepers,nompers,prenompers,sexepers,adresse)
values (compteurpersonne,nompersonne,prenompersonne,defautvaleur,adressepersonne);
end insererpersonne;

static procedure insererpersonne
( nompersonne in varchar2,
prenompersonne in varchar2) is 
begin
insert into personne (codepers,nompers,prenompers,sexepers)
values (seqcodepers.nextval,nompersonne,prenompersonne,'M');
end insererpersonne;

static procedure ajouterpersonne
(nompersonne in varchar2,
prenompersonne in varchar2,
datenaissance in date
) is
begin
insert into personne(codepers,nompers,prenompers,sexepers,datenaissance)
values (seqcodepers.nextval,nompersonne,prenompersonne,'M',datenaissance);
end ajouterpersonne;

member procedure majdatenaissance
( nouvelledatenaissance in date) is
begin
update personne set datenaissance=nouvelledatenaissance
where nompers=self.nompers
and prenompers=self.prenompers;
end majdatenaissance;

member function getage return number is
agepersonne number(4,2);
datenaissancepers date;
begin 
agepersonne := (sysdate - to_date(to_char(
self.datenaissance,'DD/MM/YYYY'),'DD/MM/YYYY'))/365;
DBMS_OUTPUT.put_line('Age de la personne : '||agepersonne||' ans');
return(agepersonne);
end getage;

member procedure setnouvelleadresse
( nouvelleadresse in adresse_type) is
begin
update personne
set adresse=nouvelleadresse
where nompers=self.nompers
and prenompers=self.prenompers;
end setnouvelleadresse;
end;
/

insert into pays values ('fr','france');
insert into pays values ('vn','vietnam');
insert into pays values ('ca','canada');
insert into pays values ('cn','chine');
insert into pays values ('jp','japon');

-- commit;

insert into personne(codepers,nompers,prenompers,sexepers)
values ('1','Dupond','raymond','M');

insert into personne(codepers,nompers,prenompers,sexepers,datenaissance,adresse)
values ('2','Dupont','marcel','M','30/03/1985',
adresse_type('33','rue','bordeaux',NULL));

insert into personne(codepers,nompers,prenompers,sexepers,datenaissance,adresse)
values ('3','asian','raymond','M','30/03/1985',
adresse_type('33','rue','paris','75000'));

insert into personne (codepers,nompers,prenompers,sexepers,datenaissance,adresse,telephone_vry)
values ('4','aan','rnd','M','30/03/1985',NULL,
telephone_vry_type(
telephone_elt_vry_type('01.00.00.00.00'),
telephone_elt_vry_type('01.00.00.00.00'))
);