CREATE OR REPLACE TYPE excep_type
AS
OBJECT (overridden_attribute VARCHAR2 (30),exception_id NUMBER);
CREATE TABLE obj_test (id NUMBER,obj_col excep_type);
INSERT INTO obj_test
VALUES (1,excep_type ('x',1));
SELECT * FROM obj_test;
ID OBJ_COL
--------------
1 (X,1)
这对我来说很好,但是假设我想在这个对象类型列obj_col中添加更多记录,那么需要做什么.
假设还有一个记录Y,需要将2插入到obj_col列中以获取ID 1
然后需要执行什么声明.
预期输出
ID OBJ_COL
--------------
1 (X,1)
(Y,2)
如果我想更新现有的一个LIKE X,1从Z更新,3,那么需要做什么.预期输出
ID OBJ_COL -------------- 1 (Z,3)
请在这件事上给予我帮助
解决方法
要实现这一点,您需要使用嵌套表.这是一个例子：
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 <Ob X 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 <Ob X 1 1 <Ob Y 2 -- 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 <Ob Z 3 1 <Ob Y 2
