mon master2 ISIFAR

Admin Nombre de messages : 418 Date d'inscription : 27/09/2005

Code:: SELECT table_name FROM user_object_tables;

DROP TABLE visa;

DROP TABLE pays;

DROP TABLE personne;

SELECT type_name FROM user_types;

DROP TYPE visa_type;

DROP TYPE pays_type;

DROP TYPE personne_type;

DROP TYPE adresse_type;

DROP TYPE telephone_vry_type;

DROP TYPE telephone_elt_vry_type;

Admin Nombre de messages : 418 Date d'inscription : 27/09/2005

On recree les TADS suivants :
.adresse_type
.pays_type
.telephone_vry_type
.telephone_elt_vry_type

du TP3

TP4 Tad12mv

Code:: 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;
/

Admin Nombre de messages : 418 Date d'inscription : 27/09/2005

dateNaisssance

Code:: CREATE 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
/

Code:: CREATE TYPE personne_type AS OBJECT
(
codePers NUMBER,
nomPers VARCHAR2(30),
prenomPers VARCHAR2(25),
sexePers VARCHAR2(1),
dateNaisssance 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,
dateNaissancePersonne IN DATE ),
MEMBER PROCEDURE majDateNaissance
(
nouvelleDateNaissance IN DATE),
MEMBER FUNCTION getAge RETURN NUMBER,
MEMBER PROCEDURE setNouvelleAdresse
(
nouvelleAdresse IN ADRESSE_TYPE )
)
/

-- La ou on implemente les methodes (1ere etape,
-- on verifie la syntaxe)
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,
dateNaissancePersonne 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 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
)
/

Sur une deuxieme fenetre, on se connecte en tant que SYSTEME

C:\Documents and Settings\ufrinfo>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Jeu. Nov. 10 10:01:53 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

SQL> connect sys/dbufr as sysdba
Connected.
SQL> GRANT CREATE SEQUENCE To dbufr;

Grant succeeded.

SQL>
et on se deconnecte

Code:: -- Pour pouvoir creer l'objet sequence, il faut avoir le
-- privilege "CREATE SEQUENCE"

CREATE SEQUENCE seqCodePErs INCREMENT BY 1 START WITH 7;

SELECT seqCodePers.NEXTVAL FROM dual;

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_visa2 PRIMARY KEY (codeVisa));

-- creation du corps avec implementation des methodes

CREATE OR REPLACE TYPE BODY personne_type AS
STATIC PROCEDURE insererPersonne
(
nomPersonne IN VARCHAR2,
prenomPersonne IN VARCHAR2,
adressePersonne IN ADRESSE_TYPE
)
IS
defaultValeur VARCHAR2(1):='M';
compteurPersonne NUMBER;
BEGIN
SELECT seqCodePers.NEXTVAL INTO compteurPersonne
FROM DUAL; --on met sequence ds le compteur
INSERT INTO personne(codePers,nomPers,prenomPers,sexePers,adresse)
VALUES (compteurPersonne,nomPersonne,prenomPersonne,defaultValeur,
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,
dateNaissancePersonne IN DATE
)
IS
BEGIN
INSERT INTO personne(codePers,nomPers,prenomPers,sexePers, dateNaisssance)
VALUES(seqCodePers.NEXTVAL,nomPersonne,prenomPersonne,'M',
dateNaissancePersonne);
END ajouterPersonne;

MEMBER PROCEDURE majDateNaissance
(
nouvelleDateNaissance IN DATE
)
IS
BEGIN
UPDATE personne SET dateNaisssance = 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.dateNaisssance, 'DD/MM/YYYY'), 'DD/MM/YYYY') )/365;
DBMS_OUTPUT.PUT_LINE('Age de la personne :' || agePersonne || 'ans');
RETURN (agePersonne);
END getAge; --pour pouvoir afficher, il faut mettre SET SERVEROUT ON

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

Admin Nombre de messages : 418 Date d'inscription : 27/09/2005

Code:: -- Afficher le definition des TAD crees (USER_SOURCE)

SET LINES 120
SET PAGES 500
-- SET LONG 1000
COL name FOR A30
COL text FOR A80 wrap
BREAK ON name SKIP 1

SELECT name, text
FROM user_Source
WHERE type = 'TYPE'
ORDER BY name, line;

-- Pour avoir le corps du body
SELECT name, text
FROM user_Source
WHERE type like '%BODY%'
ORDER BY name, line;

-- Afficher les TAD de type "Collection" (USER_TYPES)

COL type_name FOR A20
COL typecode FOR A20

SELECT type_name,
typecode,
methods
FROM user_types
WHERE typecode = 'COLLECTION';

-- les methodes sont a 0 car on ne les a pas definis

-- Afficher la liste des tables imbriquées (USER_NESTED TABLES)

COLUMN table_type_name FORAMT A20
COLUMN table_name FORMAT A30

SELECT table_name, table_type_name
FROM user_nested_tables;

-- Afficher les objets crees (package, type...) (USER_OBJECTS)
COLUMN object_type FORMAT A20
COLUMN object_name FORMAT A30
COLUMN status FORMAT A10

BREAK ON object_type SKIP 1

SELECT object_type, object_name, status
FROM user_objects
WHERE object_type IN
('PACKAGE','PACKAGE BODY','FUNCTION,'PROCEDURE','TYPE',
'TYPE BODY','TRIGGER')
ORDER BY object_type, status, object_name;

-- Afficher les dependance de type 'REF' (USER_DEPENDENCIES)
DESC USER_DEPENDENCIES

COLUMN type FOR A20
COLUMN name FOR A20
COLUMN referenced_name FOR A20
COLUMN referenced_type FOR A20
COLUMN depedency_type FOR A20

SELECT name, type, referenced_name, referenced_type, depedency_type
FROM user_depedencies
WHERE depedency_type = 'REF';

SELECT type_name, attr_name, attr_type_name
FROM user_type_attrs
WHERE attr_type_MOD = 'REF';

[...]

Admin Nombre de messages : 418 Date d'inscription : 27/09/2005

Code:: COMMIT;

Code:: INSERT INTO pays(codePays, nomPays)
VALUES('ca','canada');

INSERT INTO pays(codePays, nomPays)
VALUES('cn','chine');

INSERT INTO pays(codePays, nomPays)
VALUES('fr','france');

Code:: INSERT INTO personne (codePers, nomPers, prenomPers,sexePers)
VALUES (1, 'DUPONT', 'raymond','M');

--dans cet exemple, on ne peut inserer que 3 telephones, pas plus
INSERT INTO personne ( codePers, nomPers, prenomPers, sexePErs,
datenaisssance, adresse, telephone_vry )
VALUES ( '4', 'rivoire', 'luc', 'M', '01/05/1980',
adresse_type ( '4', 'rue du coin', 'malakoff', NULL),
telephone_vry_type( telephone_elt_vry_type
('01.46.00.10.21'),
telephone_elt_vry_type
('06.12.23.49.80') )
);

INSERT INTO personne ( codePers, nomPers, prenomPers, sexePErs,
datenaisssance, adresse, telephone_vry,enfant_nt )
VALUES ( '5', 'coui', 'muali', 'M', '01/05/1985',
adresse_type ( '4', 'rue vanves', 'clichy', NULL),
telephone_vry_type( telephone_elt_vry_type
('01.46.00.10.81'),
telephone_elt_vry_type
('06.12.23.49.82')),
enfant_nt_type (
enfant_elt_nt_type ('athur', 18,'droit'),
enfant_elt_nt_type ('remi',17,'bac'),
enfant_elt_nt_type ('martin',11,'cm2')
)
);

/*
nettoyage:
clear screen
commit;
*/

COMMIT;

-- Afficher la liste des enfants pour la personne num 5
-- cette methode n'est utilisee que pour les nested table

SELECT * FROM TABLE ( SELECT x3.enfant_nt
FROM personne x3
WHERE x3.codepers = '5');

SELECT * FROM TABLE ( SELECT x3.enfant_nt
FROM personne x3
--WHERE x3.codepers = '5'
);

-- Afficher nom, prenom, sexe, date de naissance avec la liste
-- des enfants pour l'objet "5" (utiliser la directive "TABLE")
-- (TABLE : seulement pour les tables imbriquees)
BREAK ON nomPers ON prenomPers -
ON sexePers ON datenaisssance

SELECT x1.nomPErs, x1.prenomPers, x1.sexePers, x1.datenaisssance, x3.*
FROM personne x1,
TABLE ( SELECT enfant_nt
FROM personne
WHERE codepers = '5') x3
WHERE x1.codepers ='5';

-- afficher la liste des personnes avec ou sans enfants il fo mettre le (+)

COL nomPers FOR A15
COL prenomPers FOR A15
COL sexePers FOR A10
BREAK ON nomPers ON prenomPers -
ON sexePers ON dateNaissance

SELECT x1.nomPers, x1.prenomPers, x1.sexePers, x1.datenaisssance, x3.*
FROM personne x1, TABLE(x1.enfant_nt) x3;

SELECT x1.nompers, x1.prenomPers, x1.datenaisssance, x3.*
FROM personne x1, TABLE(x1.enfant_nt) (+) x3;

-- Afficher la liste des personnes avec ous ans enfants (utliser un cursor)
SELECT x1.nompers, x1.prenompers, x1.datenaisssance,
CURSOR( SELECT * FROM TABLE(x1.enfant_nt))
FROM personne x1
ORDER BY x1.nompers desc;

Admin Nombre de messages : 418 Date d'inscription : 27/09/2005

Modifier le prénom de l'enfant "arthur" par celui de "victor" :

Code:: /* Modifications des données enfants (nested table)
On ne peut pas modifier plusieurs collections
nested table dans la même instruction "update" */

-- modification d'une colonne ou plusieurs
UPDATE TABLE (SELECT enfant_nt
FROM personne
WHERE codePers=5) x1
SET x1.prenomenf = 'victor'
WHERE x1.prenomenf = 'arthur';

-- modification de toutes les colonnes de
-- "enfant_nt" avec la directive VALUE
UPDATE TABLE (SELECT enfant_nt
FROM personne
WHERE codePers=5) x1
SET VALUE(x1) = enfant_elt_nt_type('victor','25','ingenieur')
WHERE x1.prenomenf = 'arthur';

Supprimer l'objet enfant "remi" de la "nested table" :

Code:: --Suppresion d'un enregistrement de table imbriquée "enfant_nt"

DELETE TABLE (SELECT enfant_nt
FROM personne
WHERE codePers=5) x1
WHERE x1.prenomenf = 'remi';

Code:: -- Modifier les numéros de téléphone de l'objet "4". Affecter
-- "01 46 00 12 01" à l'élément 1
-- "06 12 23 49 00" à l'élément 2

DECLARE
tableauTel telephone_vry_type;

BEGIN
SELECT telephone_vry INTO tableauTel
FROM personne
WHERE codepers = 4
FOR UPDATE OF telephone_vry;

tableauTel(1).numTel := '01 46 00 12 01';
tableauTel(2).numTel := '06 12 23 49 00';

UPDATE personne SET telephone_vry = tableauTel
WHERE codepers = 4;
END;
/

SELECT x1.nomPers,
x1.prenomPers,
x1.sexePers,
x1.datenaisssance,
x1.adresse.numero,
x1.adresse.rue,
x1.adresse.ville,
x2.*
FROM personne x1, TABLE ( SELECT x2.telephone_vry
FROM personne x2
WHERE x2.codepers = '4' ) x2
WHERE x1.codepers = '4';

SELECT COUNT(*)
FROM TABLE (SELECT telephone_vry
FROM personne
WHERE codePers = 4);

Code:: INSERT INTO visa (codeVisa, datedemande, dateEntree, dateSortie, numeroPasseport,motifSejour, refPersonn )
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, refPersonn )
VALUES ('2','29/03/2005','12/04/2005','12/05/2005','00 XP 00000', 'tourisme',
(SELECT REF(x1) FROM personne x1 WHERE x1.codePers = '2' )
);

INSERT INTO visa (codeVisa, datedemande, dateEntree, dateSortie, numeroPasseport,motifSejour, refPersonn )
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, refPersonn )
VALUES ('4','29/07/2004','16/08/2005','16/11/2005','00 XP 00001', 'tourisme',
(SELECT REF(x1) FROM personne x1 WHERE x1.codePers = '3' )
);

Afficher les données insérées sous forme de tableau
(nom, prénom, date de naissance, date de demande...) :

Code:: COL ref_personne.nomPers FOR A10;
COL ref_personne.prenomPers FOR A10;
COL ref_personne.sexePers FOR A10;
COL numeroPasseport FOR A12
COL motifSejour FOR A15

BREAK ON ref_personne.nomPers -
ON ref_personne.prenomPers -
ON ref_personne.sexepers

SELECT x1.ref_personne.nomPers,
x1.ref_personne.prenomPers,
x1.ref_personne.sexePers,
x1.ref_personne.datenaisssance,
x1.dateDemande,
x1.dateEntree
x1.dateSortie
x1.numeroPasseport
x1.motifSejour
FROM visa x1;

Ajouter un nouveau attrbut au TAD "visa_type".
Celui-ci fait référence au TAD "pays_type".
Affecter aux enregistrements (objets) les pays suivants :

Code:: 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 ='cn')
WHERE x1.ref_personne = (SELECT REF(x3)
FROM personne x3
WHERE x3.codePers ='1');

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

Admin Nombre de messages : 418 Date d'inscription : 27/09/2005

--Ecrire des scripts PL/SQL pour appeler et executer ces methodes

Code:: /*appel de la 1ere methode insererPersonne*/
BEGIN
personne_type.insererPersonne('fonda','henri', adresse_type('1',
'place dIt','paris',NULL));
END;
/

/*appel de la 2eme methode insererPersonne*/
DECLARE
nomPersonne VARCHAR2(20) :='gosselin';
prenomPersonne VARCHAR2(20) := 'michel';
BEGIN
personne_type.insererPersonne(nomPersonne,prenomPersonne);
END;
/

SELECT * FROM personne;
COMMIT;

/*appel de la methode majdatenaissance*/
DECLARE
personneAModifier personne_type;
dateNaisssance personne.datenaisssance%TYPE :=
TODATE('25/12/1975','DD/MM/YYYY');
BEGIN
SELECT VALUE(x1) INTO personneAModifier
FROM personne x1
WHERE x1.codePers = 1;

personneAModifier.majDateNaissance (datenaisssance);
END;
/

/*Appel de la fonction getAge() dans un ordre SQL */
SET SERVEROUT ON --affichage de la partie age
SELECT x1.nomPers, x1.prenomPers,
TO_CHAR(x1.datenaisssance,'DD/MM/YYYY') "ne(e) le",
x1.getAge() age
FROM personne x1;