Oracle复杂嵌套表创建与操作

一 复杂嵌套表概述

   复杂嵌套表字段数据类型包括常用类型，对象类型，变长数组类型，嵌套表类型。本篇文章详细介绍了各个类型的典型使用及嵌套表的DDL和DML操作。

二 复杂嵌套表实例

1）创建集合类型和对象类型

创建数据类型	创建语句
嵌套表1	create type scott.tab1_type as table of varchar2(50);
嵌套表2	create or replace type scott.tab2_type as table of varchar2(50);
对象类型	create or replace type scott.obj_nest_t as object(id number,name varchar2(50),salary float);
变长数组	create or replace type scott.val_type as varray(10) of number;

2）创建嵌套表

创建嵌套表中有两个字段为嵌套类型：

create table scott.test_nested_tab(
person scott.obj_nest_t,sex nchar(3),
score scott.val_type,birthday date,
hobby scott.tab1_type,
character scott.tab2_type) 
nested table hobby  store as test_person;  

   执行后发现报错：ORA-22913: must specify table name for nested table column or attribute

说明：该创建嵌套表SQl语句不符合规定，嵌套表中只能有一个嵌套表类型字段
创建嵌套表中有一个字段为嵌套表类型：

create table scott.test_nested_tab(
person scott.obj_nest_t,sex nchar(3),
score scott.val_type,birthday date,
hobby scott.tab1_type) 
nested table hobby store as test_hob;

创建嵌套表在oracle日志表现为：
    76  create table "SCOTT"."TEST_HOB" OF "SCOTT"."TAB1_TYPE"  TABLESPACE "USERS";
   169 create table scott.test_nested_tab(person scott.obj_nest_t,sex nchar(3),score                              
          scott.val_type,birthday date,hobby scott.tab1_type) nested table hobby store as test_hob;

备注：在oracle日志中创建嵌套表语句的用户名为SCOTT，表名为TEST_HOB。

3）显示嵌套表结构

   desc scott.test_nested_tab;
    Name                       Null?    Type
    ----------------------------------------- -------- ----------------------------
    PERSON                         SCOTT.OBJ_NEST_T
    SEX                            NCHAR(3)
    SCORE                            SCOTT.VAL_TYPE
    BIRTHDAY                        DATE
    HOBBY                            SCOTT.TAB1_TYPE

4） 添加表数据

   insert into scott.test_nested_tab values(SCOTT.OBJ_NEST_T(1,'鹏飞',3005.05),'男 ',SCOTT.VAL_TYPE(1,2,3,4,5),to_date('1986-9-13','yyyy-mm- dd'),SCOTT.TAB1_TYPE('听歌','看书','旅游','玩游戏'));
   添加嵌套表数据在oracle日志表现为：
          66  insert into "SCOTT"."TEST_HOB"("COLUMN_VALUE") values ('听歌');
          66  insert into "SCOTT"."TEST_HOB"("COLUMN_VALUE") values ('看书');
          66  insert into "SCOTT"."TEST_HOB"("COLUMN_VALUE") values ('旅游');
          69  insert into "SCOTT"."TEST_HOB"("COLUMN_VALUE") values ('玩游戏');
   备注：以上四条日志添加语句操作表TEST_HOB。
          179 insert into "SCOTT"."TEST_NESTED_TAB"("PERSON","SEX","SCORE","BIRTHDAY") values (Unsupported Type,'男  ',Unsupported Type,TO_DATE('1986-09-13 12:00:00', 'YYYY-MM-DD HH:MI:SS'));
   备注：上一句日志添加语句操作表TEST_NESTED_TAB。

5）更改表数据

  update scott.test_nested_tab p set p.person.id=2,p.person.name='佳诺',p.person.salary=400,sex='女 ',score=SCOTT.VAL_TYPE(2,4,6,8,9),birthday=to_date('1990-2-2','yyyy-mm- dd'),HOBBY=SCOTT.TAB1_TYPE('看书','旅游','听歌','聊天') where p.person.id=1;
   更改嵌套表数据在oracle日志表现为：
          388 update "SCOTT"."TEST_NESTED_TAB" a set a."PERSON" = Unsupported Type, a."SEX" = '女  ', a."SCORE" = Unsupported Type, a."BIRTHDAY" = TO_DATE('1990-02-02 12:00:00', 'YYYY-MM-DD HH:MI:SS') where a."PERSON" = Unsupported Type and a."SEX" = '男  ' and a."SCORE" = Unsupported Type and a."BIRTHDAY" = TO_DATE('1986-09-13 12:00:00', 'YYYY-MM-DD HH:MI:SS') and a.ROWID = 'AABropAAJAAAlR0AAA';
   备注：上一句日志更改语句操作表TEST_NESTED_TAB。
          66  insert into "SCOTT"."TEST_HOB"("COLUMN_VALUE") values ('看书');
          66  insert into "SCOTT"."TEST_HOB"("COLUMN_VALUE") values ('旅游');
          66  insert into "SCOTT"."TEST_HOB"("COLUMN_VALUE") values ('听歌');
          66  insert into "SCOTT"."TEST_HOB"("COLUMN_VALUE") values ('聊天');
          97  delete from "SCOTT"."TEST_HOB" where "COLUMN_VALUE" = '听歌' and ROWID = 'AABroqAAJAAAlRsAAA';
          97  delete from "SCOTT"."TEST_HOB" where "COLUMN_VALUE" = '看书' and ROWID = 'AABroqAAJAAAlRsAAB';
          97  delete from "SCOTT"."TEST_HOB" where "COLUMN_VALUE" = '旅游' and ROWID = 'AABroqAAJAAAlRsAAC';
          100 delete from "SCOTT"."TEST_HOB" where "COLUMN_VALUE" = '玩游戏' and ROWID = 'AABroqAAJAAAlRsAAD';
   备注：嵌套表集合有数据，oracle处理更改嵌套表集合时先添加你所更改后的嵌套集合的数据 ，然后删除更改前的嵌套集合的数据。

6）–查看表数据

   select * from scott.test_nested_tab;

   PERSON(ID, NAME, SALARY)    SEX       SCORE                BIRTHDAY          HOBBY
   --------------------------------------------------------------------------------
   OBJ_NEST_T(2, '佳诺', 400)   女     VAL_TYPE(2, 4, 6, 8, 9)  02-FEB-90  TAB1_TYPE('看书', '旅游', '听歌', '聊天')

7）删除表数据

   delete from scott.test_nested_tab p where p.person.id=2;
   删除嵌套表数据在oracle日志表现为：
          240 delete from "SCOTT"."TEST_NESTED_TAB" a where a."PERSON" = Unsupported Type and a."SEX" = '女  ' and a."SCORE" = Unsupported Type and a."BIRTHDAY" = TO_DATE('1990-02-02 12:00:00', 'YYYY-MM-DD HH:MI:SS') and a.ROWID = 'AABropAAJAAAlR0AAA';
          97  delete from "SCOTT"."TEST_HOB" where "COLUMN_VALUE" = '看书' and ROWID = 'AABroqAAJAAAlRsAAE';
          97  delete from "SCOTT"."TEST_HOB" where "COLUMN_VALUE" = '旅游' and ROWID = 'AABroqAAJAAAlRsAAF';
          97  delete from "SCOTT"."TEST_HOB" where "COLUMN_VALUE" = '听歌' and ROWID = 'AABroqAAJAAAlRsAAG';
          97  delete from "SCOTT"."TEST_HOB" where "COLUMN_VALUE" = '聊天' and ROWID = 'AABroqAAJAAAlRsAAH';

8）删除表

   drop table scott.test_nested_tab;
   删除嵌套表在oracle日志表现为：
         51  drop table "SCOTT"."TEST_HOB" cascade constraints;
         34  drop table scott.test_nested_tab;
   备注删除嵌套表在oracle日志表现在操作TEST_HOB表中。

转载于复杂嵌套表