Correction de l’atelier 3

Exercice 1:

Schéma 1

1. create type t_stage as object ( Nstage number,

                                                          theme varchar2(10));

        create type etudiant as object (Netu number,

                                                        nom varchar2(10),

                                                          stg ref t_stage);

Finalement, instancier les tables stage et étudiant.

        Create  table stage of t_stage ;

       Create table etudiant of t_etudiant;

Schéma 2:

      Create type C1 as object (a varchar2(10));

     Create type A as object (c varchar2(10), leC1  ref C1) ;

    Create type tabA as table of A;

    Create type C2 as object (b varchar2(10), lesA  tabA);

Schéma 3:

• Create type C;
• Create type list_C  as object (list ref c) ;
• Create type tab_ C  as table of list_ C;
• Create or replace type C as object(a varchar2(10), leC ref  C, lesC  tab_C);

Exercice 2:

1. Create type tab_email as varray(4) of varchar2(20);

Create type t_personne as object( code number,

                                                           nom varchar2(10),

                                                          gsm varchar2(10),

                                                          email tab_email);

2. create table personne of t_personne (

                           Constraint pk_per primary key(code),

                           Constraint nn_per nom not null,

                          Constraint uq_per unique(gsm)) ;

3. desc t_personne

     Desc personne

4. desc user_constraints

5. insert into personne

                     values( 10,’ali’,’066778899’,tab_email(‘ali@gl.com’,’ali@mea.ma’)) ;

   insert into personne

                     values ( 20,’amal’,’06554433’,tab_email(‘al@gil.com’,’al@nara.ma’)) ;

6. Select nom, gsm from personne ;

7.  select p.code, count(*)

     from personne p, table(p.email)

    group by p.code;

ou

select count(*)

 from table(    select p.email

           from personne p

           where p.code=10 );

Exercice 3:

create or replace type t_personne as object  (id number,

nom varchar(20),

prenom varchar(20),

age number,

member function afficher return varchar);

create or replace type body t_personne as member function afficher return varchar is

nom varchar(20);

begin

      return concat(self.nom,self.prenom);

end afficher;

end;

create or replace type t_travailleur under t_personne (salaire number);

create or replace type t_etudiant under t_personne( niveau varchar(20));

desc t_etudiant;

create table etudiant of t_etudiant

                                     ( constraint  pk_id primary key(id));

create table travailleur of t_travailleur

                                     (  constraint   pk_id_tr primary key(id));

• insert into travailleur values(10,'salim','ali',25,7500) ;
• insert into travailleur values(20,'jamil','amal',27,8000) ;
• insert into travailleur values(30,'karam','nour',23,5500) ;

7. select * from travailleur;

   select t.afficher() from travailleur t;