TP3

CREATE TYPE adresse_type AS OBJECT
(
 numero    number,
 rue        varchar2(30),
 ville      varchar2(30),
 codePostal varchar2(5)
)
/

CREATE TYPE pays_type AS OBJECT
(
 codePays varchar2(3),
 nomPays  varchar2(30)
)
/

CREATE TYPE telephone_elt_vry_type AS OBJECT
(
 numTel VARCHAR2(20)
)
/

CREATE TYPE telephone_vry_type AS VARRAY(3)
OF telephone_elt_vry_type;
/

CREATE TYPE personne_type AS OBJECT
(
 codePers        NUMBER,
 nomPers        VARCHAR2(30),
 prenomPers      VARCHAR2(25),
 sexePers        VARCHAR2(1),
 dateNaissance  DATE,
 adresse        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
)
/

CREATE TABLE pays OF pays_type
( CONSTRAINT pk_pays PRIMARY KEY (codePays) );

CREATE TABLE personne OF personne_type
( CONSTRAINT pk_personne PRIMARY KEY (codePers) );

CREATE TABLE visa OF visa_type
( CONSTRAINT pk_visa PRIMARY KEY (codeVisa) );



DESC user_types
DESC user_type_attrs

SELECT type_name FROM user_types;


DESC visa
DESC personne
DESC pays

ALTER TABLE personne
ADD CONSTRAINT nn_nomPers    CHECK (nomPers IS NOT NULL)
ADD CONSTRAINT nn_prenomPers CHECK (prenomPers IS NOT NULL)
ADD CONSTRAINT nn_sexePers  CHECK (sexePers IN ('M','F'))
;





COL type_name FOR A20
COL typecode  FOR A20

SELECT type_name,
      typecode --,
      -- attributes,
      -- methods
FROM  user_types;




COL TYPE_NAME      format a20
COL ALTER_NAME    format a20
COL ATTR_TYPE_NAME format a20

BREAK ON type_name skip 1

SELECT TYPE_NAME, ATTR_NAME,
      ATTR_TYPE_NAME, LENGTH,
      PRECISION
FROM  USER_TYPE_ATTRS;





DESC user_object_tables


SET LINES 120 -- pour le nb de caract par lignes
SET PAGES 200

COL table_name      FOR a20
COL object_id_type  FOR a20
COL table_type_owner FOR a20
COL table_type      FOR a20

SELECT  table_name,
        table_type,
        table_type_owner,
        object_id_type
FROM    user_object_tables
ORDER BY table_name;



DESC user_contraints
DESC user_cons_columns

COL constraint_name FOR A25
BREAK ON constraint_name

SELECT  constraint_name,
        constraint_type,
        table_name
FROM    user_constraints
ORDER BY constraint_name;



COL column_name FOR A20
COL table_name  FOR A20
COL position    NOPRINT

BREAK ON table_name ON constraint_type

SELECT  table_name,
        constraint_name,
        position, column_name
FROM    user_cons_columns
ORDER BY constraint_name,
        position;




BREAK ON table_name ON constraint_name -
      ON constraint_type ON constrainte

SELECT x1.table_name, x1.constraint_name,
      x1.constraint_type,
      DECODE (x1.constraint_type, 'P', 'Primary Key',
                                  'C', 'Check',
                                  'U', 'Unique') constrainte,
      --x2.table_name, x2.constraint_name,
      x2.position, x2.column_name
FROM  user_constraints x1,
      user_cons_columns x2
WHERE  x1.table_name = x2.table_name
      AND x1.constraint_name = x2.constraint_name
ORDER BY x1.table_name;

--Insertion dans la table OR "personne"

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('34','rue victor hugo','bordeaux','30000'));

INSERT INTO personne(codePers, nomPers, prenomPers, sexePers, datenaissance, adresse)
VALUES ('3','asian','matthieux','M','15/08/1987',
        adresse_type('10','bld de malakoff','paris','75000'));


--on les affiche "jolis"
COL nomPers FOR A10
COL prenomPers FOR A10
SELECT codePers, nomPers, prenomPers, sexePers, datenaissance, p.adresse.numero as num, p.adresse.rue as rue, p.adresse.ville as ville, p.adresse.codePostal as codePostal
FROM  personne p;

--Insertion dans la table OR "pays"

INSERT INTO pays(codePays, NomPays)
VALUES ('ca','canada');
INSERT INTO pays(codePays, NomPays)
VALUES ('cn','chine');
INSERT INTO pays(codePays, NomPays)
VALUES ('fr','france');
INSERT INTO pays(codePays, NomPays)
VALUES ('jp','japon');
INSERT INTO pays(codePays, NomPays)
VALUES ('vn','vietnam');

SELECT * FROM pays;


COMMIT;

--Insertion dans la table OR "visa"

INSERT INTO visa(codeVisa, datedemande, dateEntree, dateSortie, numeroPasseport, motifSejour, ref_Personne)
VALUES ('1','29/10/2004','12/12/2004','12/01/2005','00 XP 00000','tourisme',
(SELECT REF(X1)
 FROM  personne x1
 WHERE  x1.codePers='2')
);

INSERT INTO visa(codeVisa, datedemande, dateEntree, dateSortie, numeroPasseport, motifSejour, ref_Personne)
VALUES ('2','29/03/2005','12/04/2004','12/04/2005','00 XP 00000','tourisme',
(SELECT REF(X1)
 FROM  personne x1
 WHERE  x1.codePers='2')
);

INSERT INTO visa(codeVisa, datedemande, dateEntree, dateSortie, numeroPasseport, motifSejour, ref_Personne)
VALUES ('3','29/03/2005','12/04/2005','12/05/2005','00 XP 00001','tourisme',
(SELECT REF(X1)
 FROM  personne x1
 WHERE  x1.codePers='3')
);

INSERT INTO visa(codeVisa, datedemande, dateEntree, dateSortie, numeroPasseport, motifSejour, ref_Personne)
VALUES ('4','29/07/2005','16/08/2005','16/11/2005','00 XP 00001','tourisme',
(SELECT REF(X1)
 FROM  personne x1
 WHERE  x1.codePers='3')
);

SELECT * FROM visa;

-- pour eviter l affichage des OID
SELECT V.ref_personne.nomPers,
      V.ref_personne.prenomPers,
      V.ref_personne.sexePers,
      V.ref_personne.datenaissance,
      V.datedemande,
      V.dateEntree,
      V.dateSortie,
      V.numeroPasseport,
      V.motifSejour
FROM  visa V;

ALTER TYPE visa_type
ADD ATTRIBUTE ref_pays REF pays_type CASCADE;

UPDATE visa x1 SET x1.ref_pays = (SELECT REF(x2)
                                  FROM  pays x2
                                  WHERE  x2.codePays = 'jp')
WHERE x1.ref_personne = (SELECT REF(x3)
                        FROM  personne x3
                        WHERE  x3.codePers = '3');

UPDATE visa x1 SET x1.ref_pays = (SELECT REF(x2)
                                  FROM  pays x2
                                  WHERE  x2.codePays = 'vn')
WHERE x1.codevisa = '2';

UPDATE visa x1 SET x1.ref_pays = (SELECT REF(x2)
                                  FROM  pays x2
                                  WHERE  x2.codePays = 'cn')
WHERE x1.codevisa = '1';

ALTER TYPE personne_type
ADD ATTRIBUTE telephone_vry telephone_vry_type CASCADE;

DESC personne

INSERT INTO personne ( codePers, nomPers, prenomPers, sexePErs,
                      datenaissance, adresse, telephone_vry )
VALUES ( '4', 'rivoire', 'luc', 'M', '01/05'1980',
        adresse_type ( '4', 'rue du coin', 'malakoff', '92000'),
        telephone_vry_type( telephone_elt_vry_type
                            ('01.46.00.10.21'),
                            telephone_elt_vry_type
                            ('06.12.23.49.80'),        )
      );


COMMIT;