mon master2 ISIFAR

ISIFAR
 
AccueilFAQRechercherS'enregistrerMembresGroupesConnexion
Partagez | 
 

 TP4

Voir le sujet précédent Voir le sujet suivant Aller en bas 
AuteurMessage
Admin
Admin


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

MessageSujet: TP4   Jeu 10 Nov à 8: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;

Revenir en haut Aller en bas
Voir le profil de l'utilisateur http://mastertwo.jeun.fr
Admin
Admin


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

MessageSujet: Re: TP4   Jeu 10 Nov à 9: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 à 19:42, édité 2 fois
Revenir en haut Aller en bas
Voir le profil de l'utilisateur http://mastertwo.jeun.fr
Admin
Admin


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

MessageSujet: Re: TP4   Jeu 10 Nov à 9:17

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


Dernière édition par le Dim 4 Déc à 19:57, édité 22 fois
Revenir en haut Aller en bas
Voir le profil de l'utilisateur http://mastertwo.jeun.fr
Admin
Admin


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

MessageSujet: Re: TP4   Jeu 10 Nov à 11: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 à 20:22, édité 9 fois
Revenir en haut Aller en bas
Voir le profil de l'utilisateur http://mastertwo.jeun.fr
Admin
Admin


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

MessageSujet: Re: TP4   Jeu 10 Nov à 11: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 à 20:08, édité 7 fois
Revenir en haut Aller en bas
Voir le profil de l'utilisateur http://mastertwo.jeun.fr
Admin
Admin


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

MessageSujet: Re: TP4   Jeu 10 Nov à 12: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');



Revenir en haut Aller en bas
Voir le profil de l'utilisateur http://mastertwo.jeun.fr
Admin
Admin


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

MessageSujet: Re: TP4   Dim 4 Déc à 21: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;


Revenir en haut Aller en bas
Voir le profil de l'utilisateur http://mastertwo.jeun.fr
 

TP4

Voir le sujet précédent Voir le sujet suivant Revenir en haut 
Page 1 sur 1

Permission de ce forum:Vous ne pouvez pas répondre aux sujets dans ce forum
mon master2 ISIFAR ::  :: -