Oracle嵌套表及可变数组概述

q675922735

　　一、嵌套表
　　所谓嵌套表，简单地说就是把一个表作为另一个表的字段。
　　而众所周知，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;