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