ORACLE对象表和嵌套表

共有特性

对象表和嵌套表在plsql工具中均可以在Tables里面查到对象表和嵌套表在user_tables视图中均无法查到，在user_objects视图中可以查到，查询sql为select * from user_objects where object_type = 'TABLE'

对象表的一些特性

创建一个对象表：

创建一个type类型CREATE TYPE TYPE_OBJECT AS OBJECT ( test_num NUMBER(4), test_var VARCHAR2(20), test_date DATE ) ; 依据这个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);

嵌套表的一些特性

创建一个嵌套表

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

创建一个type

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

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

CREATE OR REPLACE TYPE STRUCT_TYPE_MUL AS TABLE OF STRUCT_TYPE

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

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

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

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

对象表

查询所有表的时候，查出了所有普通表和对象表，排除了嵌套表（查询视图为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里面了的在1的基础上查询某个表是不是对象表select * from user_tables where table_name = 'OBJECT_TABLE' 若其在user_tables里面有值则证明其为普通表，否则为对象表查对象表所依赖的对象select referenced_name from user_dependencies where referenced_type = 'TYPE' and name = 'OBJECT_TABLE' 依据以上信息就可以拼出该对象表的建表语句了

嵌套表

直接查一个表是不是嵌套了其它表即可select * from user_nested_tables where parent_table_name = 'NESTED_TABLE' 若该表内有嵌套表，则可以查出相关数据。根据里面的数据可以拼出嵌套表的创建语句，即NESTED TABLE DETAIL_NESTED STORE AS NESTED_TABLE_STORE，其中DETAIL_NESTED为PARENT_TABLE_COLUMN，NESTED_TABLE_STORE为TABLE_NAME