要实现这一点,您需要使用嵌套表.这是一个例子:
create or replace type excep_type as object
(
overridden_attribute varchar2 (30),exception_id number
)
/
create or replace type t_excep_type as table of excep_type
/
create table nst_table
(
id number,exp t_excep_type
) nested table exp store as nst_exp -- name of the nested table
/
-- inserting of record in the base table
sql> insert into nst_table(id,exp)
2 values(1,t_excep_type(excep_type('X',1)));
1 row inserted
sql> commit;
Commit complete
sql> select * from nst_table t,table(t.exp);
ID EXP OVERRIDDEN_ATTRIBUTE EXCEPTION_ID
--------------------------------------------- ------------
1
-- inserting of record in the nested table
sql> insert into table(select exp from nst_table where id = 1)
2 values (excep_type('Y','2'))
3 ;
1 row inserted
sql> commit;
Commit complete
-- unnesting.
sql> select * from nst_table t,table(t.exp);
ID EXP OVERRIDDEN_ATTRIBUTE EXCEPTION_ID
---------- --- ------------------------------ ------------
1
1
-- updating of data in the nested table
sql> update table(select exp from nst_table where id = 1)
2 set overridden_attribute = 'Z',exception_id = 3
3 where exception_id = 1 and overridden_attribute = 'X';
1 row updated
sql> select * from nst_table t,table(t.exp);
ID EXP OVERRIDDEN_ATTRIBUTE EXCEPTION_ID
---------- --- -------------------------------------------
1
1
但是,存储数据以实现主 – 细节关系的方法并不是最好的方法.