前篇嵌套表文章，嵌套表类型太少了，我觉得案例不够典型，今天就重新编写案例改编嵌套表。
一 复杂嵌套表概述

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

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

       创建数据类型，创建类型语句及其在oracle日志的表现如下表：
 创建数据类型 创建语句 创建语句在oracler日志显示为
 嵌套表1 create type scott.tab1_type as table of varchar2(50); 55  create type scott.tab1_type as table of varchar2(50);;
 嵌套表2  create or replace type scott.tab2_type as table of varchar2(50); 66  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); 94  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;64  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表中。