REF TYPE 사용 예제
=================

. 오브젝트 reference 할 수 있는 값을 가지는 데이터 타입

. 오브젝트와 오브젝트의 관계 표현에 유용

< sample >

-- object table 생성, data insert

SQL> insert into address_tbl values ('55 Madison Ave', 'Madison',
'WI', '53715') ;
SQL> insert into address_tbl values (address('2 Avocet Drive',
'Redwood Shores', 'CA', '95054' ));

SQL> select value(p) from address_tbl p;
ADDRESS('55 Madison Ave', 'Madison', 'WI', '53715')
ADDRESS('2 Avocet Drive', 'Redwood Shores', 'CA', '95054')

-- ref의 값을 특정 table로 제한
SQL> create table address_ref_tbl
( empno number,
address_ref ref address scope is address_tbl);

-- Insert
SQL> insert into address_ref_tbl
select 10, ref(p) from address_tbl p where state = 'WI';
SQL> insert into address_ref_tbl values (20, null);
SQL> update address_ref_tbl
set address_ref = (select ref(p) from address_tbl p
where state = 'CA')
where empno = 20 ;

-- Select
SQL> select * from address_ref_tbl;
10 0000220208910A3F700F3811D3A2300010A4F93C83910A3F6F0F381 ....
20 000220208910A3F710F3811D3A2300010A4F93C83910A3F6F0F3811 ...

-- ref가 가리키는 object를 select

SQL> select empno, deref(address_ref) from address_ref_tbl;
10 ADDRESS('55 Madison Ave', 'Madison', 'WI', '53715')
20 ADDRESS('2 Avocet Drive', 'Redwood Shores', 'CA', '95055')

-- ref가 가리키는 object의 attribute를 select

SQL> select p.address_ref.zip from address_ref_tbl p;
53715
95054

-- reference하는 오브젝트의 값이 변한 경우 -> select 결과도 따라 변한다.
SQL> update address_tbl set zip = '95055' where zip= 95054;
SQL> select empno, deref(address_ref) from address_ref_tbl;
10 ADDRESS('55 Madison Ave', 'Madison', 'WI', '53715')
20 ADDRESS('2 Avocet Drive', 'Redwood Shores', 'CA', '95055')

-- update
SQL> update address_ref_tbl set address_ref =
(select ref(p) from address_tbl p where zip ='95055')