Oracle嵌套表及可变数组概述

　　一、嵌套表

　　所谓嵌套表，简单地说就是把一个表作为另一个表的字段。

　　而众所周知，Oracle在创建表时需要指定每个字段的类型，故要使用嵌套表，首先要把被嵌套的表定义为一种类型。

　　而需要被定义的类型有两种，简单来看，一种是被嵌套的表的每一行记录的类型，另一种是被嵌套的表本身。

　　下面举例说明，以下是一个年级－班级的例子，一个年级下有多个班级。

　　定义记录类型的方式如下：

　　

 

create type class_type as object(
       id number,
       class_name varchar2(32)
       );

  　　定义表类型的方式如下：

 

 

create type class_name as table of class_type;

  　　创建嵌套表：

 

 

create table grade (
       id number,
       grade_name varchar2(32),
       classes class_name
) nested table classes store as ts_classes;

 

 　　注意以上SQL的结尾部分，与普通表创建不同的是，需要指定“嵌套表的列的存储表”，上例为ts_classes，即将被嵌套的class_name类型的“列表”存储在了名为ts_classes的表中。该SQL执行后，除了创建grade表外，还会创建一个名为ts_classes的表——它是一个存储表——与普通表不同，这种表不能直接select查询，否则会抛出异常"ORA-22812: 无法参考嵌套表列的存储表"。

　　并且，创建嵌套表的同时，会自动在嵌套的列上加上一个unique index。如classes列上会有一个唯一索引。

 

　　对嵌套表进行各种基本操作的方式如下：

 

insert into grade values(1, 
                         '一年级', 
                         class_name(
                             class_type(1, '1班'),
                             class_type(2, '2班'),
                             class_type(3, '3班')
                         )
                         );

update table (select classes from grade where id = 1) t
       set value(t) = class_type(3, '4班') where t.id = 3;
       
delete from table (select classes from grade where id = 1) t
       where t.id = 3;

 　　二、可变数组

　　可变数组的使用跟嵌套表很相似，也需要类型定义。

　　下面以工厂－工人为示例。

create type worker_type as object (
       id number,
       name varchar2(32)
);

create type worker_list as varray(20) of worker_type;

create table factory (
       id number,
       name varchar2(32),
       workers worker_list
);

insert into factory values(1, 
                         '工厂1 ', 
                         worker_list(
                             worker_type(1, '张三'),
                             worker_type(2, '李四'),
                             worker_type(3, '王五')
                         )
                         );

　　可见，与嵌套表不同的有如下几点：

　　1. 把定义表类型的语句替换成了定义数组的语句；

　　2. 不需要指定数组列的存储位置；

　　3. 数组列不会自动添加唯一索引。

 

　　附：

　　嵌套表和可变数组的表在select查询时，在ＰＬ／ＳＱＬ中，嵌套表列或数组列会以Collection列的形式显示并不展示其内容，可点击查看内容；不能以编辑形式查询（笔者用的PL/SQL9.0）。

　　但可以如下查询将其内容一并展示：

select t.id, t.grade_name, c.* from grade t , table(t.classes) c;