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 with 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);
Related posts:
