Les fichiers

/**********************************************************************************/
/**********************************************************************************/
/**********************************************************************************/

drop sequence seq_client;
 drop trigger  trigger_seq_client;
drop sequence seq_Enfant;
 drop trigger  trigger_seq_enfant;
drop sequence seq_Visa;
 drop trigger  trigger_seq_visa;
drop sequence seq_Passeport;
 drop trigger  trigger_seq_passeport;
drop trigger  trigger_unique_parent;
drop trigger  trigger_mauvaises_dates_pass;

DROP PACKAGE enfants;
DROP PACKAGE gestion_pays;

DROP TABLE Client    cascade constraints;
DROP TABLE Enfant    cascade constraints;
DROP TABLE Visa      cascade constraints;
DROP TABLE Passeport cascade constraints;
DROP TABLE Pays      cascade constraints;



DROP TYPE type_visa;
DROP TYPE type_passeport;
DROP TYPE type_pays;
DROP TYPE type_enfant;
DROP TYPE type_client;
DROP TYPE type_telephone;
DROP TYPE type_adresse;




/*==============================================================*/
/* Type: TYPE_ADRESSE                                          */
/*==============================================================*/

CREATE TYPE type_adresse AS OBJECT
(numero          NUMBER,
 libelle          VARCHAR2(30),
 mention_spec    VARCHAR2(50),
 code_postal      NUMBER(5),
 ville            VARCHAR2(20),
 pays            VARCHAR2(20)
 )
/



/*==============================================================*/
/* Type: TYPE_TELEPHONE                                        */
/*==============================================================*/

CREATE TYPE type_telephone AS OBJECT
(fixe          VARCHAR2(20),
 portable      VARCHAR2(20),
 fax            VARCHAR2(20)
 )
/



/*==============================================================*/
/* Type: TYPE_CLIENT                                            */
/*==============================================================*/

CREATE TYPE type_client AS OBJECT
(
 codeCli          NUMBER,
 titre            CHAR(3),
 nom              VARCHAR2(20),
 prenom            VARCHAR2(20),
 dateNaissance    DATE,
 nationalite      VARCHAR2(20),
 photo            VARCHAR2(100),
 adresse_cli      type_adresse,
 telephone_cli    type_telephone,
 
  STATIC PROCEDURE insererPersonne
  (
  titre_cli    IN CHAR,
  nom_cli      IN VARCHAR2,
  prenom_cli  IN VARCHAR2,
  dateNaiss    IN DATE,
  origine      IN VARCHAR2,
  image        IN VARCHAR2,
  adr_cli      IN TYPE_ADRESSE,
  lefixe      IN VARCHAR2,
  lemobile    IN VARCHAR2,
  lefax        IN VARCHAR2
  ),
  STATIC PROCEDURE insererPersonne
  (
  titre_cli    IN CHAR,
  nom_cli      IN VARCHAR2,
  prenom_cli  IN VARCHAR2,
  dateNaiss    IN DATE,
  origine      IN VARCHAR2,
  image        IN VARCHAR2,
  adr_cli      IN TYPE_ADRESSE,
  lefixe      IN VARCHAR2,
  lemobile    IN VARCHAR2
  ),
  STATIC PROCEDURE insererPersonne
  (
  titre_cli    IN CHAR,
  nom_cli      IN VARCHAR2,
  prenom_cli  IN VARCHAR2,
  dateNaiss    IN DATE,
  origine      IN VARCHAR2,
  image        IN VARCHAR2,
  adr_cli      IN TYPE_ADRESSE,
  lefixe      IN VARCHAR2
  ),
  MEMBER PROCEDURE majDateNaissance
  (
  nouvelleDateNaissance IN DATE
  ),
  MEMBER PROCEDURE setNouvelleAdresse
  (
  nouvelleAdresse  IN TYPE_ADRESSE
  ),
  MEMBER PROCEDURE setNouveauTelephone
  (
  nouveauTelephone IN TYPE_TELEPHONE
  )

 )
/



/*==============================================================*/
/* Type: TYPE_ENFANT                                            */
/*==============================================================*/

CREATE TYPE type_enfant AS OBJECT
(
 codeEnf            NUMBER,
 nomEnf              VARCHAR2(20),
 prenomEnf          VARCHAR2(20),
 dateNaissanceEnf    DATE,
 sexeEnf            VARCHAR2(1),
 parent              REF type_client,
 
  STATIC PROCEDURE insererEnfant
  (
  nom_Enf        IN VARCHAR2,
  prenom_Enf      IN VARCHAR2,
  dateNaissEnf    IN DATE,
  sexEnf          IN VARCHAR2,
  ref_parent      IN NUMBER
  ),
  STATIC PROCEDURE insererEnfant
  (
  nom_Enf          IN VARCHAR2,
  prenom_Enf        IN VARCHAR2,
  sexEnf            IN VARCHAR2,
  ref_parent        IN NUMBER
  ),
  MEMBER PROCEDURE majDateNaissanceEnf
  (
  nouvelleDateNaissance IN DATE
  ),
  MEMBER FUNCTION getAge RETURN NUMBER

 )
/



/*==============================================================*/
/* Type: TYPE_PASSEPORT                                        */
/*==============================================================*/

CREATE TYPE type_passeport AS OBJECT
(
 codePass            NUMBER,
 num_Pass            VARCHAR2(10),
 dateDelivrance_pass  DATE,
 dateExpiration_pass  DATE,
 client              REF type_client,
 
  STATIC PROCEDURE insererPasseport
  (
  id_Pass        IN VARCHAR2,
  dateDeliv_pass  IN DATE,
  dateExpir_pass  IN DATE,
  ref_client      IN NUMBER
  ),
  MEMBER PROCEDURE majDateDelivrance_pass
  (
  nouvelleDateDelivrance_pass IN DATE
  ),
  MEMBER PROCEDURE majDateExpiration_pass
  (
  nouvelleDateExpiration_pass IN DATE
  )

 )
/



/*==============================================================*/
/* Type: TYPE_PAYS                                              */
/*==============================================================*/

CREATE TYPE type_pays AS OBJECT
(
 code_Pays        VARCHAR2(3),
 nomPays          VARCHAR2(30),
 prixJour          NUMBER(4,2),
 nom_Org          VARCHAR2(50),
 adr_Org          type_adresse,
 nom_contact      VARCHAR2(30),
 tel_contact      type_telephone, 
 delais            NUMBER,
 
 
  STATIC PROCEDURE insererPays
  (
  codePays  IN VARCHAR2,
  nomP      IN VARCHAR2,
  prixJ    IN NUMBER,
  nomOrg    IN VARCHAR2,
  adrOrg    IN TYPE_ADRESSE,
  contact  IN VARCHAR2,   
  attente  IN NUMBER,
  lefixe    IN VARCHAR2,
  lemobile  IN VARCHAR2,
  lefax    IN VARCHAR2 
  ),
  STATIC PROCEDURE insererPays
  (
  codePays  IN VARCHAR2,
  nomP      IN VARCHAR2,
  prixJ    IN NUMBER,
  nomOrg    IN VARCHAR2,
  adrOrg    IN TYPE_ADRESSE,
  contact  IN VARCHAR2,   
  attente  IN NUMBER,
  lefixe    IN VARCHAR2,
  lemobile  IN VARCHAR2
  ),
  STATIC PROCEDURE insererPays
  (
  codePays  IN VARCHAR2,
  nomP      IN VARCHAR2,
  prixJ    IN NUMBER,
  nomOrg    IN VARCHAR2,
  adrOrg    IN TYPE_ADRESSE,
  contact  IN VARCHAR2,   
  attente  IN NUMBER,
  lefixe    IN VARCHAR2
  ),
  MEMBER PROCEDURE majPrix
  (
  nouveauPrix IN NUMBER
  ),
  MEMBER PROCEDURE setNouvelleAdresseOrg
  (
  nouvelleAdrOrg IN TYPE_ADRESSE
  ),
  MEMBER PROCEDURE setNouveauTelephoneOrg
  (
  nouveauTelOrg IN TYPE_TELEPHONE
  ),
  MEMBER PROCEDURE setNouveauContact
  (
  nouveauContact IN VARCHAR2
  ),
  MEMBER PROCEDURE setNouveauDelais
  (
  nouveauDelais IN NUMBER
  )

 )
/


/*==============================================================*/
/* Type: TYPE_VISA                                              */
/*==============================================================*/

CREATE TYPE type_visa AS OBJECT
(
 codeVisa          NUMBER,
 motif_visa        VARCHAR2(10),
 date_entree      DATE,
 date_sortie      DATE,
 date_demande      DATE,
 date_reception    DATE,
 date_livraison    DATE,
 ref_pays          REF type_pays,
 ref_passeport    REF type_passeport,
 etat_demande      VARCHAR2(8),
 
  STATIC PROCEDURE insererVisa
  (
  motif        IN VARCHAR2,
  entree      IN DATE,
  sortie      IN DATE,
  demande      IN DATE,
  pays_v      IN VARCHAR2,
  passeport_v  IN NUMBER,
  etat        IN VARCHAR2 
  ),
  MEMBER PROCEDURE majDateEntree
  (
  nouvelleDateEntree    IN DATE
  ),
  MEMBER PROCEDURE majDateSortie
  (
  nouvelleDateSortie    IN DATE
  ),
  MEMBER PROCEDURE majDateDemande
  (
  nouvelleDateDemande  IN DATE
  ),
  MEMBER PROCEDURE majDateReception
  (
  nouvelleDateReception IN DATE
  ),
  MEMBER PROCEDURE majDateLivraison
  (
  nouvelleDateLivraison IN DATE
  ),
  MEMBER PROCEDURE majEtatDemande
  (
  nouvelEtatDemande IN VARCHAR2
  ),
  MEMBER FUNCTION getJours RETURN NUMBER
 
 )
/








/*==============================================================*/
/* Table: CLIENT                                                */
/*==============================================================*/

CREATE TABLE Client OF type_client
(CONSTRAINT pk_ref PRIMARY KEY (codeCli)
);


ALTER TABLE Client
 ADD CONSTRAINT ck_nomPers      CHECK  (nom          IS NOT NULL)
 ADD CONSTRAINT ck_prennomPers  CHECK  (prenom        IS NOT NULL)
 ADD CONSTRAINT ck_titre        CHECK  (titre        IN ('MR','MME','MLE'))
 ADD CONSTRAINT ck_cp          CHECK  (adresse_cli.code_postal BETWEEN 1000 and 99000)
 ADD CONSTRAINT un_client      UNIQUE (titre,nom, prenom, dateNaissance, nationalite,photo)
;


CREATE SEQUENCE seq_client;
CREATE TRIGGER trigger_seq_client
BEFORE INSERT ON Client FOR EACH ROW
BEGIN
IF (:new.codeCli IS NULL) THEN
select seq_client.nextval INTO :new.codeCli FROM DUAL;
END IF;
END;
/


/*==============================================================*/
/* Table: ENFANT                                                */
/*==============================================================*/

CREATE TABLE Enfant OF type_enfant
(CONSTRAINT pk_enfant PRIMARY KEY (codeEnf),
 CONSTRAINT sc_enfant parent SCOPE IS Client
);



ALTER TABLE Enfant
 ADD CONSTRAINT ck_sexeEnf  CHECK (sexeEnf  IN ('M','F'))
 ADD CONSTRAINT ck_prenomEnf CHECK (prenomEnf IS NOT NULL)
 ADD CONSTRAINT ck_parent    CHECK (parent    IS NOT NULL)
   
;

--CONTRAINTE D'UNICITE DES ENFANTS (si pas même parent)
CREATE TRIGGER trigger_unique_parent
BEFORE INSERT OR UPDATE OF codeenf ON Enfant
FOR EACH ROW    --trigger associé à l'enfant
  DECLARE
  res NUMBER:=0;
  BEGIN
  Select Count(*) Into res
  From  Enfant e
  Where  e.parent          = :new.parent
      AND e.nomenf          = :new.nomenf
      AND e.prenomenf        = :new.prenomenf
      AND e.datenaissanceenf = :new.datenaissanceenf ;
  If res!=0 then
    Raise_Application_Error(-20300,'enfant (et parent) existant deja');
  End If;

  END;
/ 


CREATE SEQUENCE seq_Enfant;
CREATE TRIGGER trigger_seq_enfant
BEFORE INSERT ON Enfant FOR EACH ROW
BEGIN
IF (:new.codeEnf IS NULL) THEN
select seq_Enfant.nextval INTO :new.codeEnf FROM DUAL;
END IF;
END;
/


/*==============================================================*/
/* Table: VISA                                                  */
/*==============================================================*/

CREATE TABLE Visa OF type_visa
(CONSTRAINT pk_visa PRIMARY KEY(codeVisa)
);



ALTER TABLE Visa
 ADD CONSTRAINT ck_motif_visa  CHECK (motif_visa    IN ('tourisme','affaire','etude','travail','residence'))
 ADD CONSTRAINT ck_etat_demande CHECK (etat_demande  IN ('EN COURS','RECU','DELIVRE'))
 ADD CONSTRAINT ck_numPass      CHECK (ref_passeport  IS NOT NULL)
 ADD CONSTRAINT ck_nn_pays      CHECK (ref_pays      IS NOT NULL)
 ADD CONSTRAINT ck_nn_demande  CHECK (etat_demande  IS NOT NULL)
;


CREATE SEQUENCE seq_Visa;
CREATE TRIGGER trigger_seq_visa
BEFORE INSERT ON Visa FOR EACH ROW
BEGIN
IF (:new.codeVisa IS NULL) THEN
select seq_Visa.nextval INTO :new.codeVisa FROM DUAL;
END IF;
END;
/


/*==============================================================*/
/* Table: PASSEPORT                                            */
/*==============================================================*/

CREATE TABLE Passeport OF type_passeport
(CONSTRAINT pk_passeport  PRIMARY KEY (codePass)
);


--CONTRAINTE D'INTEGRITE PASSEPORT (sur les dates)
CREATE TRIGGER trigger_mauvaises_dates_pass
BEFORE INSERT OR UPDATE ON passeport
FOR EACH ROW    --trigger associé au passeport
  BEGIN
  If ( (TO_DATE(TO_CHAR(:new.dateDelivrance_pass,'DD/MM/YYYY'),'DD/MM/YYYY')) >
        (TO_DATE(TO_CHAR(:new.dateExpiration_pass,'DD/MM/YYYY'),'DD/MM/YYYY'))  ) THEN
    Raise_Application_Error(-20301,'date delivrance et expiration incoherentes');

  End If;
END;
/ 


ALTER TABLE Passeport
 ADD CONSTRAINT ck_num_Pass  CHECK  (num_Pass IS NOT NULL)
 ADD CONSTRAINT un_passeport  UNIQUE (num_Pass, dateDelivrance_pass, dateExpiration_pass)
;


CREATE SEQUENCE seq_Passeport;
CREATE TRIGGER trigger_seq_passeport
BEFORE INSERT ON Passeport FOR EACH ROW
BEGIN
IF (:new.codePass IS NULL) THEN
select seq_Passeport.nextval INTO :new.codePass FROM DUAL;
END IF;
END;
/



/*==============================================================*/
/* Table: PAYS                                                  */
/*==============================================================*/

CREATE TABLE Pays OF type_pays
(CONSTRAINT pk_pays  PRIMARY KEY  (code_Pays)
);


ALTER TABLE Pays
 ADD CONSTRAINT ck_nomPays  CHECK  (nomPays  IS NOT NULL)
 ADD CONSTRAINT ck_prixJour CHECK  (prixJour IS NOT NULL)
 ADD CONSTRAINT ck_nom_Org  CHECK  (nom_Org  IS NOT NULL)
 ADD CONSTRAINT un_pays    UNIQUE (code_pays, nompays)
;

/*==============================================================*/
/* Type Body: CLIENT                                            */
/*==============================================================*/

CREATE OR REPLACE TYPE BODY type_client AS

 STATIC PROCEDURE insererPersonne
  (
  titre_cli    IN CHAR,
  nom_cli      IN VARCHAR2,
  prenom_cli  IN VARCHAR2,
  dateNaiss    IN DATE,
  origine      IN VARCHAR2,
  image        IN VARCHAR2,
  adr_cli      IN TYPE_ADRESSE,
  lefixe      IN VARCHAR2,
  lemobile    IN VARCHAR2,
  lefax        IN VARCHAR2
  ) IS
  BEGIN
    INSERT INTO Client (titre, nom, prenom, dateNaissance, nationalite,
                        photo, adresse_cli, telephone_cli)
    VALUES (titre_cli, nom_cli, prenom_cli, dateNaiss, origine, image,
            adr_cli, type_telephone(lefixe, lemobile, lefax))
    ;
  END insererPersonne;

 STATIC PROCEDURE insererPersonne
  (
  titre_cli    IN CHAR,
  nom_cli      IN VARCHAR2,
  prenom_cli  IN VARCHAR2,
  dateNaiss    IN DATE,
  origine      IN VARCHAR2,
  image        IN VARCHAR2,
  adr_cli      IN TYPE_ADRESSE,
  lefixe      IN VARCHAR2,
  lemobile    IN VARCHAR2
  ) IS
  BEGIN
    INSERT INTO Client (titre, nom, prenom, dateNaissance, nationalite,
                        photo, adresse_cli, telephone_cli)
    VALUES (titre_cli, nom_cli, prenom_cli, dateNaiss, origine, image,
            adr_cli, type_telephone(lefixe, lemobile, ''))
    ;
  END insererPersonne;

 STATIC PROCEDURE insererPersonne
  (
  titre_cli    IN CHAR,
  nom_cli      IN VARCHAR2,
  prenom_cli  IN VARCHAR2,
  dateNaiss    IN DATE,
  origine      IN VARCHAR2,
  image        IN VARCHAR2,
  adr_cli      IN TYPE_ADRESSE,
  lefixe      IN VARCHAR2
  ) IS
  BEGIN
    INSERT INTO Client (titre, nom, prenom, dateNaissance, nationalite,
                        photo, adresse_cli, telephone_cli)
    VALUES (titre_cli, nom_cli, prenom_cli, dateNaiss, origine, image,
            adr_cli, type_telephone(lefixe, '', ''))
    ;
  END insererPersonne;
  MEMBER PROCEDURE majDateNaissance
  (
  nouvelleDateNaissance IN DATE
  ) IS
  BEGIN
    UPDATE Client
    SET  dateNaissance = nouvelleDateNaissance
    WHERE nom    = SELF.nom
    AND  prenom = SELF.prenom;
  END majDateNaissance;

  MEMBER PROCEDURE setNouvelleAdresse
  (
  nouvelleAdresse IN TYPE_ADRESSE
  ) IS
  BEGIN
    UPDATE Client
    SET  adresse_cli = nouvelleAdresse
    WHERE nom    = SELF.nom
    AND  prenom = SELF.prenom;
  END setNouvelleAdresse;

  MEMBER PROCEDURE setNouveauTelephone
  (
  nouveauTelephone IN TYPE_TELEPHONE
  ) IS
  BEGIN
    UPDATE Client
    SET  telephone_cli = nouveauTelephone
    WHERE nom    = SELF.nom
    AND  prenom = SELF.prenom;
  END setNouveauTelephone;

END;
/


/*==============================================================*/
/* Type Body: ENFANT                                            */
/*==============================================================*/

CREATE OR REPLACE TYPE BODY type_enfant AS

 STATIC PROCEDURE insererEnfant
  (
  nom_Enf        IN VARCHAR2,
  prenom_Enf      IN VARCHAR2,
  dateNaissEnf    IN DATE,
  sexEnf          IN VARCHAR2,
  ref_parent      IN NUMBER
  ) IS
  BEGIN
    INSERT INTO Enfant (nomEnf, prenomEnf, dateNaissanceEnf, sexeEnf,
                        parent)
    VALUES (nom_Enf, prenom_Enf, dateNaissEnf, sexEnf, 
            (select REF(xx) FROM Client xx WHERE xx.codeCli=ref_parent))
    ;
  END insererEnfant;

 STATIC PROCEDURE insererEnfant
  (
  nom_Enf          IN VARCHAR2,
  prenom_Enf        IN VARCHAR2,
  sexEnf            IN VARCHAR2,
  ref_parent        IN NUMBER
  ) IS
  BEGIN
    INSERT INTO Enfant (nomEnf, prenomEnf, sexeEnf, parent)
    VALUES (nom_Enf, prenom_Enf, sexEnf,
            (select REF(xx) FROM Client xx WHERE xx.codeCli = ref_parent))
    ;
  END insererEnfant;

 MEMBER PROCEDURE majDateNaissanceEnf
  (
  nouvelleDateNaissance IN DATE
  ) IS
  BEGIN
    UPDATE Enfant
    SET  dateNaissanceEnf = nouvelleDateNaissance
    WHERE nomEnf    = SELF.nomEnf
    AND  prenomEnf = SELF.prenomEnf;
  END majDateNaissanceEnf;

 MEMBER FUNCTION getAge RETURN NUMBER IS
    ageEnfant    NUMBER(3);
    dateNaissEnf  DATE;
  BEGIN
    ageEnfant := (SYSDATE - TO_DATE(TO_CHAR(
        SELF.dateNaissanceEnf,'DD/MM/YYYY'),'DD/MM/YYYY') )/365;
    DBMS_OUTPUT.PUT_LINE(ageEnfant);
    RETURN(ageEnfant);
  END getAge;

END;
/




/*==============================================================*/
/* Type Body: PASSEPORT                                        */
/*==============================================================*/

CREATE OR REPLACE TYPE BODY type_passeport AS

 STATIC PROCEDURE insererPasseport
  (
  id_Pass        IN VARCHAR2,
  dateDeliv_pass  IN DATE,
  dateExpir_pass  IN DATE,
  ref_client      IN NUMBER
  ) IS
  BEGIN
    INSERT INTO Passeport (num_Pass, dateDelivrance_pass, dateExpiration_pass,
            client)
    VALUES (id_Pass, dateDeliv_pass, dateExpir_pass,
            (SELECT REF(xx) FROM Client xx WHERE xx.codeCli = ref_client))
    ;
  END insererPasseport;

 MEMBER PROCEDURE majDateDelivrance_pass
  (
  nouvelleDateDelivrance_pass IN DATE
  ) IS
  BEGIN
    UPDATE Passeport
    SET  dateDelivrance_pass = nouvelleDateDelivrance_pass
    WHERE num_Pass    = SELF.num_Pass;

  END majDateDelivrance_pass;

 MEMBER PROCEDURE majDateExpiration_pass
  (
  nouvelleDateExpiration_pass IN DATE
  ) IS
  BEGIN
    UPDATE Passeport
    SET  dateExpiration_pass = nouvelleDateExpiration_pass
    WHERE num_Pass    = SELF.num_Pass;
  END majDateExpiration_pass;

END;
/




/*==============================================================*/
/* Type Body: PAYS                                              */
/*==============================================================*/

CREATE OR REPLACE TYPE BODY type_pays AS

 STATIC PROCEDURE insererPays
  (
  codePays  IN VARCHAR2,
  nomP      IN VARCHAR2,
  prixJ    IN NUMBER,
  nomOrg    IN VARCHAR2,
  adrOrg    IN TYPE_ADRESSE,
  contact  IN VARCHAR2,   
  attente  IN NUMBER,
  lefixe    IN VARCHAR2,
  lemobile  IN VARCHAR2,
  lefax    IN VARCHAR2 
  ) IS
  BEGIN
    INSERT INTO Pays (code_Pays, nomPays, prixJour, nom_Org, adr_Org,
                      nom_contact, delais, tel_contact)
    VALUES (codePays, nomP, prixJ, nomOrg, adrOrg, contact, attente,
            type_telephone(lefixe, lemobile, lefax))
    ;
 END insererPays;

 STATIC PROCEDURE insererPays
  (
  codePays  IN VARCHAR2,
  nomP      IN VARCHAR2,
  prixJ    IN NUMBER,
  nomOrg    IN VARCHAR2,
  adrOrg    IN TYPE_ADRESSE,
  contact  IN VARCHAR2,   
  attente  IN NUMBER,
  lefixe    IN VARCHAR2,
  lemobile  IN VARCHAR2 
  ) IS
  BEGIN
    INSERT INTO Pays (code_Pays, nomPays, prixJour, nom_Org, adr_Org,
                      nom_contact, tel_contact, delais)
    VALUES (codePays, nomP, prixJ, nomOrg, adrOrg, contact, 
            type_telephone(lefixe, lemobile, ''), attente)
    ;
 END insererPays;

 STATIC PROCEDURE insererPays
  (
  codePays  IN VARCHAR2,
  nomP      IN VARCHAR2,
  prixJ    IN NUMBER,
  nomOrg    IN VARCHAR2,
  adrOrg    IN TYPE_ADRESSE,
  contact  IN VARCHAR2,   
  attente  IN NUMBER,
  lefixe    IN VARCHAR2 
  ) IS
  BEGIN
    INSERT INTO Pays (code_Pays, nomPays, prixJour, nom_Org, adr_Org,
                      nom_contact, tel_contact, delais)
    VALUES (codePays, nomP, prixJ, nomOrg, adrOrg, contact, 
            type_telephone(lefixe, '', ''), attente)
    ;
 END insererPays;

 MEMBER PROCEDURE majPrix
  (
  nouveauPrix IN NUMBER
  ) IS
  BEGIN
    UPDATE Pays
    SET  prixJour = nouveauPrix
    WHERE code_Pays = SELF.code_Pays
    AND  nomPays  = SELF.nomPays;
  END majPrix;

 MEMBER PROCEDURE setNouvelleAdresseOrg
  (
  nouvelleAdrOrg IN TYPE_ADRESSE
  ) IS
  BEGIN
    UPDATE Pays
    SET  adr_Org = nouvelleAdrOrg
    WHERE code_Pays = SELF.code_Pays
    AND  nomPays  = SELF.nomPays;
  END setNouvelleAdresseOrg;

 MEMBER PROCEDURE setNouveauTelephoneOrg
  (
  nouveauTelOrg IN TYPE_TELEPHONE
  ) IS
  BEGIN
    UPDATE Pays
    SET  tel_contact = nouveauTelOrg
    WHERE code_Pays = SELF.code_Pays
    AND  nomPays  = SELF.nomPays;
  END setNouveauTelephoneOrg;

 MEMBER PROCEDURE setNouveauContact
  (
  nouveauContact IN VARCHAR2
  ) IS
  BEGIN
    UPDATE Pays
    SET  nom_contact = nouveauContact
    WHERE code_Pays = SELF.code_Pays
    AND  nomPays  = SELF.nomPays;
  END setNouveauContact;

 MEMBER PROCEDURE setNouveauDelais
  (
  nouveauDelais IN NUMBER
  ) IS
  BEGIN
    UPDATE Pays
    SET  delais = nouveauDelais
    WHERE code_Pays = SELF.code_Pays
    AND  nomPays  = SELF.nomPays;
  END setNouveauDelais;

END;
/


/*==============================================================*/
/* Type Body: VISA                                              */
/*==============================================================*/

CREATE OR REPLACE TYPE BODY type_visa AS

 STATIC PROCEDURE insererVisa
  (
  motif        IN VARCHAR2,
  entree      IN DATE,
  sortie      IN DATE,
  demande      IN DATE,
  pays_v      IN VARCHAR2,
  passeport_v  IN NUMBER,
  etat        IN VARCHAR2
  ) IS
  BEGIN
    INSERT INTO Visa (motif_visa, date_entree, date_sortie, date_demande,
            ref_pays, ref_passeport, etat_demande)
    VALUES (motif, entree, sortie, demande,
            (SELECT REF(xx) FROM Pays      xx WHERE xx.code_Pays  = pays_v),
            (SELECT REF(xx) FROM Passeport xx WHERE xx.codePass  = passeport_v),
            etat)
    ;
  END insererVisa;

 MEMBER PROCEDURE majDateEntree
  (
  nouvelleDateEntree    IN DATE
  ) IS
  BEGIN
    UPDATE Visa
    SET  date_entree = nouvelleDateEntree
    WHERE codeVisa    = SELF.codeVisa;
  END majDateEntree;

 MEMBER PROCEDURE majDateSortie
  (
  nouvelleDateSortie    IN DATE
  ) IS
  BEGIN
    UPDATE Visa
    SET  date_sortie = nouvelleDateSortie
    WHERE codeVisa    = SELF.codeVisa;
  END majDateSortie;
 
 MEMBER PROCEDURE majDateDemande
  (
  nouvelleDateDemande  IN DATE
  ) IS
  BEGIN
    UPDATE Visa
    SET  date_demande = nouvelleDateDemande
    WHERE codeVisa    = SELF.codeVisa;
  END majDateDemande;

 MEMBER PROCEDURE majDateReception
  (
  nouvelleDateReception IN DATE
  ) IS
  BEGIN
    UPDATE Visa
    SET  date_reception = nouvelleDateReception
    WHERE codeVisa      = SELF.codeVisa;
  END majDateReception;
 
 MEMBER PROCEDURE majDateLivraison
  (
  nouvelleDateLivraison IN DATE
  ) IS
  BEGIN
    UPDATE Visa
    SET  date_livraison = nouvelleDateLivraison
    WHERE codeVisa      = SELF.codeVisa;
  END majDateLivraison;

 MEMBER PROCEDURE majEtatDemande
  (
  nouvelEtatDemande IN VARCHAR2
  ) IS
  BEGIN
    UPDATE Visa
    SET  etat_demande = nouvelEtatDemande
    WHERE codeVisa    = SELF.codeVisa;
  END majEtatDemande;

 MEMBER FUNCTION getJours RETURN NUMBER IS
    nbJours      NUMBER(5);
  BEGIN
    nbJours := TO_DATE(TO_CHAR(
              SELF.date_sortie,'DD/MM/YYYY'),'DD/MM/YYYY')
              -
              TO_DATE(TO_CHAR(
              SELF.date_entree,'DD/MM/YYYY'),'DD/MM/YYYY') ;
    DBMS_OUTPUT.PUT_LINE(nbJours);
    RETURN(nbJours);
  END getJours;

END;
/




/*==============================================================*/
/* Package : ENFANT                                            */
/*==============================================================*/

CREATE PACKAGE enfants AS -- spécifications du package
 PROCEDURE supprime_enfant (no NUMBER);
 ---
END;
/

CREATE PACKAGE BODY enfants AS -- le corps du package
 PROCEDURE supprime_enfant (no NUMBER) IS
 BEGIN
  DELETE FROM Enfant WHERE codeenf = no;
 END;
 ---
END; -- du package
/



/*==============================================================*/
/* Package : GESTION_PAYS                                      */
/*==============================================================*/

CREATE PACKAGE gestion_pays AS -- spécifications du package
 PROCEDURE supprime_pays (indx VARCHAR2);
 ---
END;
/

CREATE PACKAGE BODY gestion_pays AS -- le corps du package
 PROCEDURE supprime_pays (indx VARCHAR2) IS
 BEGIN
  DELETE FROM Pays WHERE code_Pays = indx;
 END;
 ---
END; -- du package
/






show err





commit;
/* quit; */



--CREATE ROLE visa;
--GRANT CREATE session, CREATE table, CREATE view,
--      CREATE procedure, CREATE synonym,
--      CREATE type, EXECUTE ANY PROCEDURE,
--      CREATE SEQUENCE, CREATE TRIGGER
--TO visa;
--GRANT visa to mikachu;






/**********************************************************************************/
/**********************************************************************************/
/**********************************************************************************/

/***********************/
set pages 100
DELETE FROM Client;
DELETE FROM Enfant;
DELETE FROM Pays;
DELETE FROM Organisme;
DELETE FROM Passeport;
DELETE FROM Visa;


-- INSERTION DE CLIENTS
BEGIN
 type_client.insererPersonne('MME','Fonda','linda','20/05/2006','francaise',
    'c:/pouic.jpg', type_adresse(5,'rue du mips', 'Appt 354',94213,'Creteil','France'),
    '01.40.12.45.60','06 45 22 45 60'
 );

 type_client.insererPersonne('MR','Babu','leon','20/05/1951','suisse',
    'c:/maphoto25.jpg', type_adresse(6,'rue du ciel', '',75013,'Paris','France'),
    '01-45-62-40-28'
 );

 type_client.insererPersonne('MME','Kawul','gisele','20/10/1941','francaise',
    'c:/maphoto35.jpg', type_adresse(18,'boulevard bleu', '',75001,'Paris','France'),
    '01-48-42-40-28'
 );

 type_client.insererPersonne('MLE','Jiloz','Myriam','20/05/1976','francaise',
    'c:/maphoto38.jpg', type_adresse(5,'rue du Gal De Gaulle', 'Chez Mr Frnacis',
    78000,'Versailels','France'),
    '01.48.10.22.87','06-27-21-00-98'
 );

 type_client.insererPersonne('MR','Comas','denis','10/06/1963','francaise',
    'c://maphoto38.jpg', type_adresse(8,'rue de Paris', '',94213,'Creteil','France'),
    '01.40.12.45.69','','01.48.89.77.08'
 );
END;
/

DECLARE
 personneAModifier type_client;
BEGIN
 SELECT VALUE(P) INTO personneAModifier
 FROM client P
 WHERE P.codeCli=1;
 personneAModifier.majDateNaissance('22/02/1942');
END;
/



-- INSERTION D'ENFANTS
BEGIN
 type_enfant.insererEnfant('Babu','pierre','30/03/1992','M',2);
 type_enfant.insererEnfant('Babu','sylvie','03/03/1989','F',2);
 type_enfant.insererEnfant('Babu','michel','25/01/1998','M',2);
 type_enfant.insererEnfant('Comas','jean luc','25/01/1991','M',5);
END;
/

 --pour voir si meme nom, prenom et datenaiss, mais pas meme parent marche
 BEGIN
  type_enfant.insererEnfant('Babu','pierre','30/03/1992','M',1);
 END;
 /


-- SUPPRESSION D'ENFANTS (le n°5)
BEGIN
 enfants.supprime_enfant(5);
END;
/


--INSERTION DE PASSEPORTS
BEGIN
 type_passeport.insererPasseport('12345678AA','30/03/1998','30/03/2008',1);
 type_passeport.insererPasseport('12335668AB','10/01/2000','10/01/2010',2);
 type_passeport.insererPasseport('14446668BA','10/10/2003','10/10/2013',3);
 type_passeport.insererPasseport('12398788CW','08/12/2003','08/12/2013',4);
 type_passeport.insererPasseport('14577897AE','21/03/2003','21/03/2013',5);
END;
/

 --test pour voir contrainte d'integrite (date)
 BEGIN
  type_passeport.insererPasseport('12345CC','30/03/2018','30/03/2008',1);
 END;
 /




--INSERTION DE PAYS
BEGIN
 type_pays.insererPays('fr','france'  ,2.32, 'consulat de france',
                      type_adresse(10,'bd voltaire', '',75016,'Paris','France'),
                      'goemont',15,'01.48.62.45.72' 
                      );
 type_pays.insererPays('jp','japon'    ,10.54, 'consulat du japon',
                      type_adresse(5,'rue de la paix', '',75008,'Paris','France'),
                      'tanaka',10,'01.45.92.45.62' 
                      );
 type_pays.insererPays('pa','paraguay' ,50.32, 'consulat du paraguay',
                      type_adresse(10,'rue de la paix', '',75008,'Paris','France'),
                      'guantanamo',13,'01.45.92.45.62','','01.45.90.25.60' 
                      );
 type_pays.insererPays('ge','allemagne',3.30, 'consulat allemand',
                      type_adresse(28,'bd voltaire', '',75016,'Paris','France'),
                      'siegfrid',13,'01.40.92.40.60','','01.47.97.27.67' 
                      );
END;
/

--MAJ DU PRIX
DECLARE
 paysAModifier type_pays;
BEGIN
 SELECT VALUE(P) INTO paysAModifier
 FROM Pays P
 WHERE P.code_pays='fr';
 paysAModifier.majPrix(5.30);
END;
/

-- SUPPRESSION DE PAYS (allemagne)
BEGIN
 gestion_pays.supprime_pays('ge');
END;
/



--INSERTION DE VISA
BEGIN
 type_visa.insererVisa('tourisme','03/04/2005','24/04/2005', '02/03/2005',
                      'jp',1,'EN COURS');
 type_visa.insererVisa('affaire', '01/05/2006','20/05/2006', '02/03/2006',
                      'fr',1,'EN COURS');
 type_visa.insererVisa('tourisme','01/05/2006','22/05/2006', '04/03/2006',
                      'pa',2,'EN COURS');
 type_visa.insererVisa('travail','03/04/2004','28/01/2005', '02/03/2006',
                      'jp',3,'EN COURS');
 type_visa.insererVisa('etude', '21/10/2006','10/10/2007', '10/03/2006',
                      'fr',4,'EN COURS');
 type_visa.insererVisa('tourisme','10/05/2003','29/05/2003', '04/03/2003',
                      'pa',5,'EN COURS');
END;
/

--VISA RECU
DECLARE
 visaAModifier type_visa;
BEGIN
 SELECT VALUE(V) INTO visaAModifier
 FROM Visa V
 WHERE V.codevisa=1;
 visaAModifier.majDateReception('20/03/2006');
 visaAModifier.majEtatDemande('RECU');
END;
/

DECLARE
 visaAModifier type_visa;
BEGIN
 SELECT VALUE(V) INTO visaAModifier
 FROM Visa V
 WHERE V.codevisa=3;
 visaAModifier.majDateReception('22/03/2006');
 visaAModifier.majEtatDemande('RECU');
END;
/

DECLARE
 visaAModifier type_visa;
BEGIN
 SELECT VALUE(V) INTO visaAModifier
 FROM Visa V
 WHERE V.codevisa=5;
 visaAModifier.majDateReception('20/03/2003');
 visaAModifier.majEtatDemande('RECU');
END;
/



--VISA LIVRE
DECLARE
 visaAModifier type_visa;
BEGIN
 SELECT VALUE(V) INTO visaAModifier
 FROM Visa V
 WHERE V.codevisa=1;
 visaAModifier.majDateLivraison('17/03/2006');
 visaAModifier.majEtatDemande('DELIVRE');
END;
/

DECLARE
 visaAModifier type_visa;
BEGIN
 SELECT VALUE(V) INTO visaAModifier
 FROM Visa V
 WHERE V.codevisa=5;
 visaAModifier.majDateLivraison('22/03/2003');
 visaAModifier.majEtatDemande('DELIVRE');
END;
/

/*Affiche tous les pays et le prix journalier*/
SELECT code_pays, nompays, prixjour, nom_org,
      P.adr_org.numero AS No, P.adr_org.libelle AS libelle,
      P.adr_org.code_postal AS CP, P.adr_org.ville AS ville,
      nom_contact, P.tel_contact.fixe AS fixe,
      P.tel_contact.portable AS portable, P.tel_contact.fax AS fax,
      delais
FROM Pays P;


/*Affiche tous les enfants*/
SELECT E.codeEnf, E.nomEnf, E.prenomEnf, E.dateNaissanceEnf, E.getAge() AS AGE, sexeEnf
FROM Enfant E;


/*Affiche le client 1 sans enfants*/
SELECT C.codeCli, C.titre, C.nom, C.prenom, C.dateNaissance, C.photo,
      C.adresse_cli.numero AS No, C.adresse_cli.libelle AS libelle,
      C.adresse_cli.code_postal AS CP, C.adresse_cli.ville AS ville,
      C.adresse_cli.pays AS pays,
      C.telephone_cli.fixe AS fixe, C.telephone_cli.portable AS portable,
      C.telephone_cli.fax AS fax
FROM Client C
WHERE C.codecli = 1;
 

/*Affiche les clients et les enfants*/
BREAK ON NOM ON PRENOM ON code_cli ON titre ON datenaiss
SELECT E.parent.codeCli AS code_cli, E.parent.titre AS titre,
      E.parent.nom AS nom, E.parent.prenom AS prenom,
      E.parent.dateNaissance AS datenaiss, E.codeEnf, E.nomEnf,
      E.prenomEnf,  E.getAge() AS AGE
FROM Enfant E;


/*Affiche le passeport et le client qui le possede*/
SELECT codePass, num_pass, dateDelivrance_pass, dateExpiration_pass,
      P.client.codeCli AS code_cli, P.client.nom AS nom, P.client.prenom AS prenom
FROM Passeport P;




/*Affiche les visas*/
SELECT DISTINCT codeVisa, motif_visa, date_entree, date_sortie,
 date_demande,
 date_reception,
 date_livraison,
 V.ref_pays.nomPays AS pays,
 V.ref_passeport.codePass AS codePass,
 V.ref_passeport.num_pass AS num_pass,
 etat_demande,
 P.client.codeCli AS ID_cli,
 P.client.nom AS nom_cli, P.client.prenom AS prenom
FROM Visa V, Client C, Passeport P
WHERE P.codePass = V.ref_passeport.codePass;


/*petit resume des visas (AVEC PRIX)*/
COLUMN PAYS    FOR A10
SELECT DISTINCT codeVisa, motif_visa,  V.ref_pays.nomPays AS pays,
                date_entree, date_sortie, V.getJours() AS Nb_Jours,
                prixjour * V.getJours() AS PRIX
FROM Pays, Visa V
WHERE code_pays =  V.ref_pays.code_pays;



/****************************************************************************/

--Requete KAVI PASSEPORT ET CLIENT
SELECT num_pass, P.client.codeCli AS code_cli, P.client.nom AS nom,
      P.client.prenom        AS prenom,
      P.client.dateNaissance AS datenaiss,
      P.client.nationalite  AS nationalite
FROM Passeport P;



--Requete KAVI VISA EN COURS ET RECU
SELECT DISTINCT  V.ref_passeport.num_pass AS num_Pass, P.client.codecli AS num_cli,
                P.client.nom AS nom_cli, P.client.prenom AS prenom,
                codevisa,  V.ref_pays.nomPays AS pays,
                date_demande,  etat_demande
FROM  Visa V, Client C, Passeport P
WHERE P.codePass = V.ref_passeport.codePass
      AND codevisa NOT IN (SELECT codevisa
                          FROM  Visa
                          WHERE etat_demande = 'DELIVRE')
;

--Requete KAVI VISA EN COURS
SELECT DISTINCT  V.ref_passeport.num_pass AS num_Pass, P.client.codecli AS num_cli,
                P.client.nom AS nom_cli, P.client.prenom AS prenom,
                codevisa,  V.ref_pays.nomPays AS pays,
                date_demande,  etat_demande
FROM  Visa V, Client C, Passeport P
WHERE P.codePass = V.ref_passeport.codePass
      AND etat_demande = 'EN COURS'
;

--Requete KAVI VISA RECU
SELECT DISTINCT  V.ref_passeport.num_pass AS num_Pass, P.client.codecli AS num_cli,
                P.client.nom AS nom_cli, P.client.prenom AS prenom,
                codevisa,  V.ref_pays.nomPays AS pays,
                date_demande, date_reception, etat_demande
FROM  Visa V, Client C, Passeport P
WHERE P.codePass = V.ref_passeport.codePass
      AND etat_demande = 'RECU'
;

--Requete KAVI VISA LIVRE
SELECT DISTINCT  V.ref_passeport.num_pass AS num_Pass, P.client.codecli AS num_cli,
                P.client.nom AS nom_cli, P.client.prenom AS prenom,
                codevisa,  V.ref_pays.nomPays AS pays,
                date_demande, date_livraison, etat_demande
FROM  Visa V, Client C, Passeport P
WHERE P.codePass = V.ref_passeport.codePass
      AND etat_demande = 'DELIVRE'
;



--Requete KAVI CLIENT sans enfant n°1 (codeCli = 1)
BREAK ON titre    ON nom      ON prenom ON datenaiss ON nationalite -
      ON No      ON libelle  ON CP    ON ville    ON pays        -
      ON numtel  ON codeVisa ON visite ON date_demande            -
      ON date_entree ON date_sortie ON etat_demande
SELECT DISTINCT V.ref_passeport.client.titre        AS titre,
      V.ref_passeport.client.nom          AS nom,
      V.ref_passeport.client.prenom        AS prenom,
      V.ref_passeport.client.dateNaissance AS datenaiss,
      V.ref_passeport.client.nationalite  AS nationalite,
      V.ref_passeport.client.adresse_cli.numero      AS No,
      V.ref_passeport.client.adresse_cli.libelle      AS libelle,
      V.ref_passeport.client.adresse_cli.mention_spec AS mention_spec,
      V.ref_passeport.client.adresse_cli.code_postal  AS CP,
      V.ref_passeport.client.adresse_cli.ville        AS ville,
      V.ref_passeport.client.adresse_cli.pays        AS pays,
      C.telephone_cli.fixe AS fixe, C.telephone_cli.portable AS portable,
      C.telephone_cli.fax AS fax   
FROM  Visa V, Client C
WHERE V.ref_passeport.client.codeCli = 1
  AND V.ref_passeport.client.codeCli = C.codeCli;


--Requete KAVI PASSEPORT pour client n°1 (codeCli = 1)
SELECT num_Pass,
      dateDelivrance_pass AS delivre_le,
      dateExpiration_pass AS expire_le
FROM  Passeport P
WHERE P.client.codeCli = 1;

--Requete KAVI AFFICHE Enfant pour le client d'ID=2
SELECT E.codeEnf, E.nomEnf, E.prenomEnf, sexeEnf,
      E.dateNaissanceEnf, E.getAge() AS AGE
FROM Enfant E
WHERE E.parent.codeCli = 2;


--Affiche visas pour client N°1
SELECT DISTINCT codeVisa,  V.ref_pays.nomPays AS pays, motif_visa,
      V.ref_pays.prixjour * V.getJours() AS PRIX,  date_demande,
      date_entree, date_sortie, etat_demande
FROM Visa V, Client C, Passeport P
WHERE P.codePass = V.ref_passeport.codePass
      AND V.ref_passeport.client.codecli = 1;


--Requete 1ere page pays
SELECT code_pays, nompays, prixjour
FROM  Pays P;


--Affiche pays et formalité (PAR PAYS)
SELECT nompays, nom_org,
      P.adr_org.numero AS No, P.adr_org.libelle AS libelle,
      P.adr_org.code_postal AS CP, P.adr_org.ville AS ville,
      nom_contact, P.tel_contact.fixe AS fixe,
      P.tel_contact.portable AS portable, P.tel_contact.fax AS fax,
      delais, prixjour
FROM  Pays P
WHERE code_pays='jp';












/*********************************************************************/
/*                                                                  */
/*                      POUR KAVI                                    */
/*                    -----------                                  */
/*                                                                  */
/*********************************************************************/







/********************************/
--INSERT PAYS
INSERT INTO Pays (code_Pays, nomPays, prixJour, nom_Org, adr_Org,
                      nom_contact, delais, tel_contact)
VALUES ('po','portugal',2.32, 'consulat de france',
        type_adresse(10,'bd voltaire', '',75016,'Paris','France'),
        'goemont',15,type_telephone('01.48.62.45.72','','')); 

--INSERT CLIENT
INSERT INTO Client (titre, nom, prenom, dateNaissance, nationalite,
                        photo, adresse_cli, telephone_cli)
VALUES  ('MR','Gaou','michou','20/05/1942','francaise',
    'c:/pouic12.jpg', type_adresse(5,'rue du chas', 'Tour AAA',94300,'Villejuif','France'),
    type_telephone('01.40.12.45.60','06 45 22 45 60',''));

--INSERT ENFANT (Client 6)
INSERT INTO Enfant (nomEnf, prenomEnf, dateNaissanceEnf, sexeEnf, parent)
VALUES ('Gaou','cynthia','30/10/1995','F',
        (select REF(xx) FROM Client xx WHERE xx.codeCli = 6));

--INSERT PASSEPORT (Client 6)
INSERT INTO Passeport (num_Pass, dateDelivrance_pass, dateExpiration_pass,
            client)
VALUES ('19921788AA','30/03/1998','30/03/2008',
        (SELECT REF(xx) FROM Client xx WHERE xx.codeCli = 6));

--INSERT VISA (Passeport 7)
INSERT INTO Visa (motif_visa, date_entree, date_sortie, date_demande,
            ref_pays, ref_passeport, etat_demande)
VALUES ('tourisme','10/04/2005','10/05/2005',SYSDATE,
        (SELECT REF(xx) FROM Pays      xx WHERE xx.code_Pays  = 'jp'),
        (SELECT REF(xx) FROM Passeport xx WHERE xx.codePass  = 7),
        'EN COURS');


--Requete renvoyant les clients sans passeports
SELECT codeCli, titre, nom, prenom, datenaissance
FROM  Client
WHERE codeCli NOT IN (SELECT P.client.codeCli
                      FROM  Passeport P)
;


--CHANGEMENT ETAT "en cours" -> "recu"
UPDATE Visa
SET  date_reception = SYSDATE
WHERE codeVisa      = 7;
UPDATE Visa
SET etat_demande = 'RECU'
WHERE codeVisa      = 7;


--CHANGEMENT ETAT "en cours" -> "recu"
UPDATE Visa
SET  date_livraison = SYSDATE
WHERE codeVisa      = 7;
UPDATE Visa
SET etat_demande = 'DELIVRE'
WHERE codeVisa      = 7;


--SUPPRESSION PAYS (portugal)
DELETE FROM pays WHERE code_Pays ='po';

--SUPPRESION ENFANT (n°6)
DELETE FROM Enfant WHERE codeenf =6;






/***************************************************************/
/*    RECHERCHES                                              */
/***************************************************************/


--Par Num de passeport
SELECT num_pass, P.client.codeCli AS code_cli, P.client.nom AS nom,
      P.client.prenom        AS prenom,
      P.client.dateNaissance AS datenaiss,
      P.client.nationalite  AS nationalite
FROM Passeport P
WHERE num_pass = '12345678AA';



--Par Nom & prenom
SELECT num_pass, P.client.codeCli AS code_cli, P.client.nom AS nom,
      P.client.prenom        AS prenom,
      P.client.dateNaissance AS datenaiss,
      P.client.nationalite  AS nationalite
FROM Passeport P
WHERE P.client.nom like 'CO%'
  OR UPPER(P.client.prenom) = ''
;


--Par Nom & prenom
SELECT V.ref_passeport.num_pass AS num_pass,
      V.ref_passeport.client.codeCli      AS code_cli,
      V.ref_passeport.client.nom          AS nom,
      V.ref_passeport.client.prenom        AS prenom,
      V.ref_passeport.client.dateNaissance AS datenaiss,
      V.ref_passeport.client.nationalite  AS nationalite
FROM Visa V
WHERE V.codevisa = 3
;








commit;
















/*********************************************************************/
/*                                                                  */
/*                      REQUETES STATISTIQUES                        */
/*                                                                  */
/*                                                                  */
/*********************************************************************/
----pays et motif des visas
--SELECT DISTINCT codeVisa, V.ref_pays.nomPays AS pays, motif_visa
--FROM Visa V
--;



--classement pays les plus demandes
SELECT V.ref_pays.code_pays, COUNT(*) AS NbVisa
FROM  visa V
GROUP BY V.ref_pays.code_pays
ORDER BY COUNT(*) DESC
;



--type de visas par pays (ICI 'japon')
SELECT  motif_visa, COUNT(*) AS NB
FROM  Visa V
WHERE V.ref_pays.code_pays = 'jp'
GROUP BY motif_visa
ORDER BY COUNT(*) DESC
;
 


--Demande des visas au cours des 6 derniers mois
SELECT DISTINCT date_demande, V.ref_pays.nomPays AS pays,date_entree,date_sortie, motif_visa
FROM Visa V
WHERE date_demande > SYSDATE - (INTERVAL '6' MONTH)
ORDER BY date_demande DESC
;


--MOIS OU LES GENS PARTENT LE PLUS
SELECT TO_CHAR(date_entree,'Month') AS MOIS_depart, COUNT(*) AS Nb
FROM Visa V
GROUP BY TO_CHAR(date_entree,'Month')
ORDER BY Nb DESC
;


--ANNEE OU LE + DE VISAS ONT ETE DEMANDE
SELECT TO_CHAR(date_demande,'yyyy') AS annee, COUNT(*) AS NB
FROM Visa V
GROUP BY TO_CHAR(date_demande,'yyyy')
ORDER BY Nb DESC, ANNEE DESC
;


--Moyenne de la durée d'un visa de "tourisme"
SELECT motif_visa, ROUND(AVG(V.getJours())) AS Nbjours
FROM Visa V
WHERE motif_visa='tourisme'
GROUP BY motif_visa
;

--Moyenne de la durée d'un visa de "affaire"
SELECT motif_visa, ROUND(AVG(V.getJours())) AS Nbjours
FROM Visa V
WHERE motif_visa='affaire'
GROUP BY motif_visa
;

--Moyenne de la durée d'un visa de "etude"
SELECT motif_visa, ROUND(AVG(V.getJours())) AS Nbjours
FROM Visa V
WHERE motif_visa='etude'
GROUP BY motif_visa
;

--Moyenne de la durée d'un visa de "travail"
SELECT motif_visa, ROUND(AVG(V.getJours())) AS Nbjours
FROM Visa V
WHERE motif_visa='travail'
GROUP BY motif_visa
;

--Moyenne de la durée d'un visa de "residence"
SELECT motif_visa, ROUND(AVG(V.getJours())) AS Nbjours
FROM Visa V
WHERE motif_visa='residence'
GROUP BY motif_visa
;




commit;