ORACLE嵌套表和可变数组

转自：http://blog.163.com/xing_aixin/blog/static/37235505200842211142300/

　一、嵌套表的定义：
　　
　　　　嵌套表是表中之表。一个嵌套表是某些行的集合，它在主表中表示为其中的一列。对主表中的每一条记录，嵌套表可以包含多个行。在某种意义上，它是在 一个表中存储一对多关系的一种方法。考查一个包含部门信息的表，在任何时间内每个部门会有很多项目正在实施。在一个严格的关系模型中，将需要建立两个独立 的表department和project。
　　
　　　　嵌套表允许在department表中存放关于项目的信息。勿需执行联合操作，就可以通过department表直接访问项目表中的记录。这种不 经联合而直接选择数据的能力使得用户对数据访问更加容易。甚至在并没有定义方法来访问嵌套表的情况下，也能够很清楚地把部门和项目中的数据联系在一起。在 严格的关系模型中，department和project两个表的联系需要通过外部关键字（外键）关系才能实现。
　　
　　
　　二、举例说明嵌套表的使用：
　　
　　　　假设有一个关于动物饲养员的表，希望其中具有他们饲养的动物的信息。用一个嵌套表，就可以在同一个表中存储饲养员和其饲养的全部动物的信息。
　　
　　1、创建类型animal_ty：此类型中，对于每个动物都包含有一个记录，记载了其品种、名称和出生日期信息。
　　
　　CREATE TYPE animal_ty AS OBJECT (
　　
　　breed varchar2(25), 
　　
　　name varchar2(25), 
　　
　　birthdate date); 
　　
　　2、创建animals_nt：此类型将用作一个嵌套表的基础类型。
　　
　　CREATE TYPE animals_nt as table of animal_ty;
　　
　　3、创建表breeder：饲养员的信息表
　　
　　create table breeder
　　
　　(breedername varchar2(25),
　　
　　animals animal_nt)
　　
　　nested table animals store as animals_nt_tab;
　　
　　4、向嵌套表中插入记录
　　
　　insert into breeder 
　　
　　values(mary,animal_nt(animal_ty(dog,butch,31-MAR-97),
　　
　　animal_ty(dog,rover,31-MAR-97),
　　
　　animal_ty(dog,julio,31-MAR-97)));
　　
　　insert into breeder 
　　
　　values(jane,animal_nt(animal_ty(cat,an,31-MAR-97),
　　
　　animal_ty(cat,jame,31-MAR-97),
　　
　　animal_ty(cat,killer,31-MAR-97)));
　　
　　commit;
　　
　　5、查询嵌套表
　　
　　select name,birthdate from 
　　
　　table(select animals from breeder);
　　
　　select name,birthdate from 
　　
　　table(select animals from breeder 
　　
　　where breedername=’mary’)
　　
　　where name=’dog’;
　　
　　三、嵌套表的特点：
　　
　　1、对象复用：如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。
　　
　　2、标准支持：如果创建标准的对象，那么它们被重用的机会就会提高。如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。
　　
　　3、定义访问路径：对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性。
　　
　　
　　可变数组
　　
　　一、可变数组的定义：
　　
　　　　可变数组与嵌套表相似，也是一种集合。一个可变数组是对象的一个集合，其中每个对象都具有相同的数据类型。可变数组的大小由创建时决定。在表中建立可变数组后，可变数组在主表中作为一个列对待。从概念上讲，可变数组是一个限制了行集合的嵌套表。
　　
　　　　可变数组，允许用户在表中存储重复的属性。例如：假设用户有一个project表，并在项目中指定了工作人员，一个项目可以有多个工人，而一个工 人也可以为多个项目工作。在严格的关系模型中，用户可以创建一个project表，一个worker表和存储它们之间关系的交叉表 project_worker。
　　
　　　　用户可使用可变数组在project表中存储工人的名字。如果项目限定的工人数不超过10人，可以建立一个以10个数据项为限的可变数组。接下来就可处理此可变数组，从而对于每一个项目，可以选取其中所有工人的名字，而勿需查询表worker。
　　
　　
　　二、举例说明可变数组的使用：
　　
　　1、创建类型comm_info
　　
　　CREATE TYPE comm_info AS OBJECT ( /*此类型为通讯方式的集合
　　
　　no number(3), /*通讯类型号
　　
　　comm_type varchar2(20), /*通讯类型
　　
　　comm_no varchar2(30)); /*号码
　　
　　2、创建可变数组comm_info_list
　　
　　CREATE TYPE comm_info_list AS 
　　
　　VARRAY(50) OF comm_info;
　　
　　3、创建表
　　
　　create table user_info
　　
　　(user_id number(6), /*用户ID号
　　
　　user_name varchar2(20), /*用户名称
　　
　　user_comm comm_info_list); /*与用户联系的通讯方式
　　
　　4、向可变数组插入记录
　　
　　insert into user_info
　　
　　values(1,mary,comm_info_list(comm_info(1,手机,13651401919),
　　
　　comm_info(2,呼机,1281234567)));
　　
　　insert into user_info
　　
　　values(2,carl,comm_info_list(comm_info(1,手机,13901018888),
　　
　　comm_info(2,呼机,1281234567)));
　　
　　commit;
　　
　　5、查询可变数组
　　
　　select user_comm from user_info 
　　
　　where user_id=1;
　　
　　select comm_type,comm_no 
　　
　　from table(select user_comm from user_info 
　　
　　where user_id=1)
　　
　　where no=1;
　　
　　　　与一位用户联系的方式有很多种，比如：手机、呼机、座机等。在一个严格的关系模型中，将需要两个独立的表：用户信息和通讯方式，而在可变数组中， 允许在表user_info中直接访问用户的联系方式，这种不经联合而直接选择数据的能力使得用户对数据的访问更加容易。
　　
　　三、可变数组的特点：
　　
　　1、对象复用：如果编写面向对象的代码，就提高了重用以前编写的代码模块的机会。同样，如果创建面向对象的数据库对象，也就提高了数据库对象能够被重用的机会。
　　
　　2、标准支持：如果创建标准的对象，那么它们被重用的机会就会提高。如果有多个应用或多个表使用同一数据库对象集合，那么它就是既成事实的数据库对象标准。
　　
　　3定义访问路径：对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性。