oracle可变数组（VARRAY）

一、可变数组的定义：

　　可变数组与嵌套表相似，也是一种集合。一个可变数组是对象的一个集合，其中每个对象都具有相同的数据类型。可变数组的大小由创建时决定。在表中建立可变数组后，可变数组在主表中作为一个列对待。从概念上讲，可变数组是一个限制了行集合的嵌套表。

　　可变数组，允许用户在表中存储重复的属性。例如：假设用户有一个project表，并在项目中指定了工作人员，一个项目可以有多个工人，而一个工人也可以为多个项目工作。在严格的关系模型中，用户可以创建一个project表，一个worker表和存储它们之间关系的交叉表project_worker。

　　用户可使用可变数组在project表中存储工人的名字。如果项目限定的工人数不超过10人，可以建立一个以10个数据项为限的可变数组。接下来就可处理此可变数组，从而对于每一个项目，可以选取其中所有工人的名字，而勿需查询表worker。​

二、变长数组的语法

创建变长数组类型

CREATE TYPE varray_type AS VARRAY(2) OF VARCHAR2(50);

这个变长数组最多可以容纳两个数据，数据的类型为 varchar2(50) ​

更改元素类型的大小或精度

可以更改变长数组类型和嵌套表类型 元素的大小。

ALTER TYPE varray_type MODIFY ELEMENT TYPE varchar2(100) CASCADE;​

CASCADE选项把更改传播到数据库中的依赖对象。也可以用 INVALIDATE 选项使依赖对象无效

增加变长数组的元素数目​​

ALTER TYPE vrray_name MODIFY LIMIT 5 CASCADE;​

使用变长数组

CREATE TABLE table_name( column_name type, var_col_name varray_type );

获得变长数组的信息

DESC[RIBE] varray_type;

SELECT * FROM user_varraysWHERE type_name = varray_name;

DESC 获得的是 varray_type AS VARRAY(2) OF VARCHAR2(50)​​

填充变长数组元素

INSERT INTO table_name VALUES( value, varray_type( 'xxxx', 'xx', 'x') );

可以一次向变长数组添加多个数据。​

查找变长数组元素

SELECT *FROM table_Name;​

如果变长数组中的元素有多个，会一起输出，输出的数据列是一个长列，跟包含对象的表一样。​

更改变长数组元素

 要想更改变长数组的一个元素，需要把其他元素一起更改，整个变长数组作为一个整体来的。​

UPDATE table_name SET var_col_name = varray_type('xxx','xxxxxx') WHERE expr1;

三、举例说明可变数组的使用：

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定义访问路径：对于每一个对象，用户可定义在其上运行的过程和函数，从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径，就可以标准化数据访问的方法并提高对象的可复用性。​

五、可变数组的使用：​

1、创建带有可变数组的表

（1）创建可变数组的基类型

CREATE OR REPLACE TYPE MingXiType AS OBJECT

(

GoodID varchar2(20),

InCount int,

ProviderID varchar(20)

);

（2）创建嵌套项类型的可变数组​

CREATE OR REPLACE TYPE arrMingXiType AS VARRAY(100) OF MingXiType;

（3）创建一个主表

CREATE TABLE InStockOrder

(

OrderID varchar(15) Not Null Primary Key,

InDate date,

OperatorID varchar(15),

MingXi arrMingXiType

);

2、操作可变数组​

INSERT INTO InStockOrder

VALUES(''200710110001'',TO_DATE(''2007-10-11'',''YYYY-MM-DD''),''007'',

arrMingXiType(MingXiType(''G001'',100,''1001''),

MingXiType(''G002'',888,''1002''))

);

​(2)查询数据

SELECT* FROM InStockOrder;

(3)使用Table()函数

SELECT* FROMTable(SELECTt.MingXi FROMInStockOrder t

WHERE t.OrderID=''200710110001'');

（4）修改数据

UPDATE InStockOrder

SET MingXi=arrMingXiType(MingXiType(''G001'',200,''1001''),

MingXiType(''G002'',8888,''1002''))

WHERE OrderID=''200710110001''

​注意：不能更新VARRAY中的单个元素，必须更新整个VARRAY

转自：http://blog.sina.com.cn/s/blog_14deb4ba00102vh2k.html   新浪博客   id：伤心云雨