Infolinks

Saturday 12 May 2012

SQL-REF DEREF VALUE


REF DEREF VALUE

REF

Ø  The ref function allows referencing of existing row objects.
Ø  Each of the row objects has an object id value assigned to it.
Ø  The object id assigned can be seen by using ref function.

DEREF

Ø  The deref function performs opposite action.
Ø  It takes a reference value of object id and returns the value of the row objects.

VALUE

Ø  Even though the primary table is object table, still it displays the rows in general format.
Ø  To display the entire structure of the object, this will be used.

Ex:
    1) create vendot_adt type
           SQL> Create type vendor_adt as object (vendor_code number(2), vendor_name
                 varchar(2),  vendor_address varchar(10));/
    2) create object tables vendors and vendors1
           SQL> Create table vendors of vendor_adt;
           SQL> Create table vendors1 of vendor_adt;
    3) insert the data into object tables
         SQL> insert into vendors values(1, ‘a’, ‘hyd’);
           SQL> insert into vendors values(2, ‘b’, ‘bang’);
           SQL> insert into vendors1 values(3, ‘c’, ‘delhi’);
           SQL> insert into vendors1 values(4, ‘d’, ‘chennai’);
    4) create another table orders which holds the vendor_adt type also.
         SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt);
                                                     Or

         SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt with
                 rowid);
    5) insert the data into orders table
         The vendor_info column in the following syntaxes will store object id of any table
         which is referenced by vendor_adt object ( both vendors and vendors1).
         SQL> insert into orders values(11,(select ref(v) from vendors v where vendor_code
                 = 1));
          SQL> insert into orders values(12,(select ref(v) from vendors v where vendor_code
                 = 2));
          SQL> insert into orders values(13,(select ref(v1) from vendors1 v1 where
                  vendor_code = 1));
          SQL> insert into orders values(14,(select ref(v1) from vendors1 v1 where
                  vendor_code = 1));
     6) To see the object ids of vendor table
            SQL> Select ref(V) from vendors v;
     7) If you see the vendor_info of orders it will show only the object ids not the values,
          to see the values
            SQL> Select deref(o.vendor_info) from orders o;
     8) Even though the vendors table is object table it will not show the adt along with
          data, to see the data along with the adt
            SQL>Select * from vendors;
          This will give the data without adt.
            SQL>Select value(v) from vendors v;
          This will give the columns data along wih the type.

REF CONSTRAINTS

Ø  Ref can also acts as constraint.
Ø  Even though vendors1 also holding vendor_adt, the orders table will store the object ids of vendors only because it is constrained to that table only.
Ø  The vendor_info column in the following syntaxes will store object ids of vendors only.




SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt scope is
         vendors);
                                                     Or
SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt constraint fk
         references vendors);

No comments:

Post a Comment