| mon master2 ISIFAR ISIFAR |
| | TP4 | |
| | Auteur | Message |
---|
Admin Admin
Nombre de messages : 418 Date d'inscription : 27/09/2005
| Sujet: TP4 Jeu 10 Nov à 10:35 | |
| - 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 Admin
Nombre de messages : 418 Date d'inscription : 27/09/2005
| Sujet: Re: TP4 Jeu 10 Nov à 11:11 | |
| On recree les TADS suivants : .adresse_type .pays_type .telephone_vry_type .telephone_elt_vry_type du TP3 - 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; /
Dernière édition par le Dim 4 Déc à 21:42, édité 2 fois | |
| | | Admin Admin
Nombre de messages : 418 Date d'inscription : 27/09/2005
| Sujet: Re: TP4 Jeu 10 Nov à 11:17 | |
| dateNai sssance - 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 /nologSQL*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; /
Dernière édition par le Dim 4 Déc à 21:57, édité 22 fois | |
| | | Admin Admin
Nombre de messages : 418 Date d'inscription : 27/09/2005
| Sujet: Re: TP4 Jeu 10 Nov à 13:11 | |
| - 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'; [...]
Dernière édition par le Dim 4 Déc à 22:22, édité 9 fois | |
| | | Admin Admin
Nombre de messages : 418 Date d'inscription : 27/09/2005
| Sujet: Re: TP4 Jeu 10 Nov à 13:18 | |
| - 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;
Dernière édition par le Dim 4 Déc à 22:08, édité 7 fois | |
| | | Admin Admin
Nombre de messages : 418 Date d'inscription : 27/09/2005
| Sujet: Re: TP4 Jeu 10 Nov à 14:02 | |
| 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 Admin
Nombre de messages : 418 Date d'inscription : 27/09/2005
| Sujet: Re: TP4 Dim 4 Déc à 23:30 | |
| --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;
| |
| | | Contenu sponsorisé
| Sujet: Re: TP4 | |
| |
| | | | TP4 | |
|
| Permission de ce forum: | Vous ne pouvez pas répondre aux sujets dans ce forum
| |
| |
| |
|