如何在oracle中向对象类型列添加更多行

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

但是,存储数据以实现主 – 细节关系的方法并不是最好的方法.

（编辑：91站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!