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
);
/