----------------- Object in Oracle 8 -----------------

--- Abstract Data Types

create or replace type address_ty as object

(

 street_no number(3), street_name varchar2(20),

 city varchar2(20), state varchar2(20)

);

 

create table vend_mast

(

 vencode varchar2(5), venname varchar2(15),

 venadd address_ty, tel_no number(6)

);

insert into vend_mast values('v001','anand', address_ty(100,'first st','erode','tn'),456789);

select a.venadd.city from vend_mast a;

drop type address_ty force;

--- Varying Arrays

 create type itemcode as varray(5) of varchar2(5);

 create type qty_ord as varray(5) of number(5);

 create type qty_deld as varray(5) of number(5);

  create table order_detail( orderno varchar2(5), item_va itemcode, qty_va qty_ord, qtyd_va qty_deld);

  insert into order_detail values('o100',

              itemcode('i001','i002','i003','i004','i005'),

              qty_ord(100,50,50,100,50),

              qty_deld(100,200,300,200,100);

 select item_va from order_detail;

--- Nested Tables

create type ord_ty as object(itemcode varchar2(5),qty_ord number(5),qty_deld number(5) );

create type ord_nt as table of ord_ty;

 create table order_master(orderno varchar2(5), odate date, vencode varchar2(5),dets ord_nt)

            nested table dets store as ord_nt_tab;

select a.itemcode,a.qty_deld from the ( select dets

              from order_master) a;

insert into the ( select dets from order_master where orderno='o201') values( ord_ty('i001',10,10) );

update the ( select dets from order_master where orderno='o100') set qty_deld=80 where qty_ord=100;

 --inserts based on queries

 insert into order_master values('o202','12-jan-98','v001', cast( multiset( select * from the

     ( select dets from order_master where orderno='o201'))

     as ord_nt ));

--- Object Tables

 create type vend_ty as object (vencode varchar2(5), venname varcahr2(20), venadd  varcahr2(30),

       telno  number(8) );

create table vendor_master of vend_ty;

 insert into vendir_master values(vend_ty('v001','ram','gv st',44567) );

  -- REF operator

 select ref(a) from vendor_master a;          -> it gives OID

 create table test( orderno varchar2(5), vendet ref vend_ty );

 insert into test(  select 'o300',ref(a) from vendor_master a where vencode='v201' );

 select deref(a.vendet) from test a;

 

 --- Object View   

   The ability to overlay the Object Oriented structures such as abstract data types on existing relational tables. This can be done with the help of Object View.

  create or replace type item_ty as object(qty_hand number(5), re_level number(5),

           max_level number(5) );

 create or replace type other_ty as object(p_cat varchar2(20), itemrate number(8,2),

              items item_ty) ;

 create or replace type itemfile_ty as object(itemcode varchar2(5), itemdesc varchar2(20),

        others other_ty );

                      

create or replace view item_ov( itemcode,others )

            as ( select itemcode,other_ty(p_cat,itemrate,

            item_ty(qty_hand,re_level,max_level) )

            from itemfile;

insert into item_ov values( 'i201', other_ty('parts',11.90, item_ty(90,25,125) ) );

insert into itemfile values( 'i201','nuts','spares', 100,50,500,10.50 );

 

© 2004-2005 Mercury Digital Media