ORACLE对象表和嵌套表

共有特性

1. 对象表和嵌套表在plsql工具中均可以在Tables里面查到
2. 对象表和嵌套表在user_tables视图中均无法查到，在user_objects视图中可以查到，查询sql为

   select * from user_objects where object_type = 'TABLE'
   

对象表的一些特性

创建一个对象表：

1. 创建一个type类型

   CREATE TYPE TYPE_OBJECT AS OBJECT (
     test_num   NUMBER(4),
     test_var   VARCHAR2(20),
     test_date  DATE
   ) ;
   

2. 依据这个type创建对象表

   --DROP TABLE OBJECT_TABLE;
   CREATE TABLE OBJECT_TABLE OF TYPE_OBJECT;
   

给对象表插入数据

可以像给普遍表格插入数据一样

INSERT INTO OBJECT_TABLE VALUES (1,'测试对象表1',TO_DATE('20180101','YYYYMMDD'));

也可以先将数据转成type类型再直接插入type数据

INSERT INTO OBJECT_TABLE VALUES (TYPE_OBJECT(2,'测试对象表2',TO_DATE('20180102','YYYYMMDD')));

查询对象表数据

与普通表的查询一样

select * from OBJECT_TABLE

也可以进行分页查询

select * from (select a.*, ROWNUM rn from (select * from OBJECT_TABLE) a where ROWNUM < 3) where rn > 1

查询对象表中列的属性

与普通表一样，对象表的列属性也可以在user_tab_columns视图中查到

select * from user_tab_columns where TABLE_NAME = 'OBJECT_TABLE'

支持约束

create index my_index on OBJECT_TABLE(test_var);
alter table OBJECT_TABLE add primary key (test_num);

嵌套表的一些特性

创建一个嵌套表

嵌套表是嵌套在其他表里面的，因此创建嵌套表时得有个被嵌套的表，在这里我称呼其为母表

1. 创建一个type

   CREATE OR REPLACE TYPE STRUCT_TYPE AS OBJECT(
      ID NUMBER,
      DETAIL VARCHAR2(100)
   )
   

2. 依据这个type再创建一个嵌套表类型（其中type可以为任何类型，包括varray 和 object ，通常object 居多。）

   CREATE OR REPLACE TYPE STRUCT_TYPE_MUL AS TABLE OF STRUCT_TYPE
   

3. 使用该嵌套表类型，作为一个列嵌套到母表中

   CREATE TABLE NESTED_TABLE
   (
     ID            NUMBER not null,
     NAME           VARCHAR2(60),
     DETAIL_NESTED        STRUCT_TYPE_MUL
   )NESTED TABLE DETAIL_NESTED STORE AS NESTED_TABLE_STORE;
   

   可以注意到建表语句尾部多了一句：NESTED TABLE DETAIL_NESTED STORE AS NESTED_TABLE_STORE，这是声明这个嵌套表类型的列为一个嵌套表，被声明的表为：NESTED_TABLE_STORE。该建表语句执行后，会创建两个表，一个为母表NESTED_TABLE，一个就是嵌套表NESTED_TABLE_STORE。

给嵌套表插入数据

嵌套表是无法单独插入数据的，必须通过母表插入数据：

insert into NESTED_TABLE values 
(1, 'test', STRUCT_TYPE_MUL(STRUCT_TYPE(11,'北京'), STRUCT_TYPE(12, '上海')));

其插入数据的方式与变长数组varray类似

查询嵌套表数据

嵌套表是无法单独查询数据的，强行查询会返回错误：ORA-22812:无法参考嵌套表列的存储表；只能通过母表查看数据

select * from NESTED_TABLE

查询嵌套表的列数据

嵌套表的列数据无法在user_tab_columns中查到

嵌套表的其它特性

嵌套表创建后在视图：user_nested_tables中存在记录

select * from user_nested_tables

里面包含了嵌套表的基本属性

数据移植系统对对象表和嵌套表的处理

对象表

1. 查询所有表的时候，查出了所有普通表和对象表，排除了嵌套表（查询视图为user_objects）

   select * from user_objects o left join user_nested_tables nt on o.OBJECT_NAME = nt.TABLE_NAME where nt.TABLE_NAME is null and o.object_type='TABLE'
   

   需注意plsql是把嵌套表显示在Tables里面了的
2. 在1的基础上查询某个表是不是对象表

   select * from user_tables where table_name = 'OBJECT_TABLE'
   

   若其在user_tables里面有值则证明其为普通表，否则为对象表
3. 查对象表所依赖的对象

   select referenced_name from user_dependencies where referenced_type = 'TYPE' and name = 'OBJECT_TABLE'
   

   依据以上信息就可以拼出该对象表的建表语句了

嵌套表

1. 直接查一个表是不是嵌套了其它表即可

   select * from user_nested_tables where parent_table_name = 'NESTED_TABLE'
   

   若该表内有嵌套表，则可以查出相关数据。
2. 根据里面的数据可以拼出嵌套表的创建语句，即NESTED TABLE DETAIL_NESTED STORE AS NESTED_TABLE_STORE，其中DETAIL_NESTED为PARENT_TABLE_COLUMN，NESTED_TABLE_STORE为TABLE_NAME