Oracle复合类型：嵌套表、可变数组与索引表

一、 在pl/sql中使用嵌套表和可变数组

1、在pl/sql中使用嵌套表

在PL/SQL中使用嵌套表，此时嵌套表就相当于其他语言中的数组。

①、示例：

set SERVEROUTPUT ONDECLARE      --下面这句是嵌套表声明的TYPE ename_array_type is table of emp.ename%TYPE ;--定义一个游标CURSOR ename_cursor is select ename from emp where job='CLERK';--初始化嵌套表，此时嵌套表为空ename_arrayename_array_type:=ename_array_type();i number:=0;BEGINforename in ename_cursorloopi:=i+1;--执行extend函数，为数组增加一个null元素ename_array.EXTEND;ename_array(i):=ename.ename;    DBMS_OUTPUT.PUT_LINE(ename.ename);    end loop;--嵌套表下标是从1开始的，而不是0DBMS_OUTPUT.PUT_LINE('嵌套表：'||ename_array(1)||'  '|| ename_array(2));END;/

②、嵌套表声明：

TYPE type_name is table of 表元素类型。例如：

TYPE ename_array_type is table of emp.ename%TYPE ;

③、嵌套表初始化：

嵌套表必须初始化后才能使用，初始化方式有两种，
一种是初始化为空，例如上面例子中

ename_arrayename_array_type:=ename_array_type();

由于ename_array被初始化为空，所以如果需要往里面放数据，就
需要为其增加一个元素，否则将会报错。EXTEND就是用来为数组增加元素的，例如上面例子中：
ename_array.EXTEND;

另一种是初始化时直接为数组分配元素，
如：ename_array:=ename_array_type(‘草泥马’,’草泥马’);
此时ename_array(1)和ename_array(2)的值为草泥马。这时候也可以直接为ename_array(1)和ename_array(2)赋值，如

ename_array(1):='羊驼';ename_array(2):='红色羊驼';

但是如果想为第三个元素赋值，需要先执行：ename_array.EXTEND。
因为在初始化的时候，只为数组分配了两个元素，所以想给第三
个元素赋值就必须先为数组分配一个元素。

2、在pl/sql中使用可变数组

①、示例：

set SERVEROUTPUT ONDECLARE--这个例子跟上面基本一样，只是下面这句原本is table变成is varray(5)TYPE ename_array_type is VARRAY(4) of emp.ename%TYPE ;CURSOR ename_cursor is select ename from emp where job='CLERK';ename_arrayename_array_type:=ename_array_type();i number:=0;BEGINforename in ename_cursorloopi:=i+1;ename_array.EXTEND;ename_array(i):=ename.ename;DBMS_OUTPUT.PUT_LINE(ename.ename);    end loop;--下标也是从1开始的，而不是0DBMS_OUTPUT.PUT_LINE('嵌套表：'||ename_array(1)||'  '|| ename_array(2));END;/

②、可变数组声明

TYPE type_name is varray(数组元素个数) of 表元素类型。例如：

TYPE ename_array_type is VARRAY(4) of emp.ename%TYPE ;

由于ename_array_type被声明为VARRAY(4)，这说明ename_array_type类
型的数组，最多只能放4个元素。如果想为第5个元素赋值，将会报错，即
ename_array(5):=’hello’，此时将会报错，即使在赋值前调用EXTEND：
ename_array.EXTEND也依然会报错。

3、在pl/sql中使用嵌套表和可用数组的区别

在PL/SQL中使用嵌套表和可变数组，基本是一样的，唯一的不同是，可变数
组的元素个数是有限制的，而嵌套表没有元素个数的限制

二、 在表列中使用嵌套表和可变数组（面向对象的SQL）

1、在表列中使用嵌套表

示例：

①、创建对象

create or replace TYPE comm_info AS OBJECT ( /*此类型为通讯方式的集合*/no number(3), /*通讯类型号*/comm_type varchar2(20), /*通讯类型*/comm_no varchar2(30)/*号码*/);

②、创建嵌套表

create or replace TYPE comm_info_list AS table OF comm_info;

③、创建表

create table user_info(user_id number(6), /*用户ID号*/user_name varchar2(20), /*用户名称*/user_comm  comm_info_list /*与用户联系的通讯方式*/)--下面这句的意思是把user_comm的数据放到user_comm_table表中存储nested table user_comm store as user_comm_table;

执行命令后，你会发现你创建了两张表，一张是user_info，一张是嵌套表
user_comm_table

④、插入数据

insert into user_infovalues(1,'mary',comm_info_list(    comm_info(1,'手机','13651401919'),    comm_info(2,'呼机','1281234567')  ));insert into user_infovalues(2,'carl',comm_info_list(    comm_info(1,'手机','13901018888'),    comm_info(2,'呼机','1281234567')  ));

⑤、查询嵌套表

通过user_info查询其嵌套表信息，此时注意括号内的查询，只能查询一列，即可以select user_comm而不能select user_comm,user_name，并且只能返回一条记录，否则会报错

selectcomm_type,comm_nofrom table(select user_comm from user_info where user_id=1);执行结果如下：COMM_TYPE            COMM_NO                      -------------------- ------------------------------手机                 13651401919                   呼机                 1281234567   

如果不查询具体的嵌套表信息，则没有只能查询一列并且只能返回一条记录的限制，但是好像获取的信息不那么有用：

select user_comm from user_info;执行如下：-------------------------------------------------------------------------------------------------------SCOTT.COMM_INFO_LIST(    SCOTT.COMM_INFO(1,'手机','13651401919'),    SCOTT.COMM_INFO(2,'呼机','1281234567')) SCOTT.COMM_INFO_LIST(    SCOTT.COMM_INFO(1,'手机','13901018888'),    SCOTT.COMM_INFO(2,'呼机','1281234567'))    

2、在表列中使用可变数组

示例：

①、创建对象

create or replace TYPE comm_info AS OBJECT ( /*此类型为通讯方式的集合*/    no number(3), /*通讯类型号*/    comm_type varchar2(20), /*通讯类型*/    comm_no varchar2(30)/*号码*/);

②、创建可变数组

create or replace TYPE comm_info_list AS varray(50) OF comm_info;

③、创建表

create table user_info(    user_id number(6), /*用户ID号*/    user_name varchar2(20), /*用户名称*/    user_comm comm_info_list /*与用户联系的通讯方式*/)

至于数据插入，和查询，就跟嵌套表的是一模一样。

3、在表列中使用嵌套表和可变数组的区别

第一个区别是：创建表的时候不一样，嵌套表创建时需要加这句
nested table user_comm store as user_comm_table，并且执行创建表语句后，
会产生两张表，其中一张是嵌套表，嵌套表类型的信息就放在嵌套表里。而
可变数组不需要加这句话，所以创建表的语句显得比较简单，而且执行创建
后，只会创建一张表。

第二个区别：
可变数组是有元素个数的限制的，比如
create or replace TYPE comm_info_list AS varray(50) OF comm_info;
则comm_info_list类型的数组最多只能放50个元素，即插入的时候，

insert into user_info    values(1,'mary',comm_info_list(    comm_info(1,'手机','13651401919'),    comm_info(2,'呼机','1281234567')  ));

括号内的comm_info最多只能放50个，上面放了两个，分别是
comm_info(1,’手机’,’13651401919’)和 comm_info(2,’呼机’,’1281234567’)
而嵌套表没有这个限制。

三、 索引表（index by）

索引表只能用于PL/SQL中。在前面PL/SQL中使用嵌套表，其声明是：
TYPE ename_array_type is table of emp.ename%TYPE ;
而把这个声明改为index by就成了索引表，即：
TYPE ename_array_type is table of emp.ename%TYPE index by binary_integer ;
binary_integer后面也可以跟其他PL/SQL支持的类型，比如index by varchar2
虽然索引表跟嵌套表很像，但是嵌套表像数组，而索引表更像key-value映射的map
而index by后面的就是key的数据类型。

1、索引表使用示例：

set SERVEROUTPUT ONDECLARE    TYPE ename_array_type is table of emp.ename%TYPE index by BINARY_INTEGER;    CURSOR ename_cursor is select ename from emp where job='CLERK';    ename_array ename_array_type;--这里直接定义就行，初始化都不用了    i BINARY_INTEGER:=0;BEGIN   for ename in ename_cursor    loop       -- ename_array.EXTEND;这句被注释起来了        ename_array(i):=ename.ename;        DBMS_OUTPUT.PUT_LINE(ename.ename);            i:=i+1;    end loop;--下标从0开始    DBMS_OUTPUT.PUT_LINE('嵌套表：'||ename_array(0)||'  '|| ename_array(1));end;/

可以看到使用在PL/SQL中使用索引表会比使用嵌套表和可变数组简单很多。

2、索引表作为map的使用示例

前面说索引表更像是map，为什么这么说，可以看下面的例子：

set SERVEROUTPUT ONdeclare    type map_type is table of number(3) index by varchar2(3);    map_ map_type;      map_key varchar2(3);begin    map_ ('a') := 10;    map_ ('b') := 20;      map_key :=map_ .first;    while(map_key is not null) loop        dbms_output.put_line(map_ (map_key));        map_key:=map_ .next(map_key);    end loop;end;/

四、 嵌套表，可变数组与索引表的区别

嵌套表与可变数组的区别是，嵌套表没有元素个数的限制，而可变数组有元素个数的限制。嵌套表和可变数组与索引表的区别是，嵌套表和可变数组可以用于非PL/SQL环境，而索引表只能用于PL/SQL中。

五、 相关函数

1、COUNT 返回集合中元素的个数

2、删除集合元素DELETE，DELETE(x)，DELETE(x,y)，TRIM，TRIM(x)
DELETE删除集合中所有元素
DELETE(x) 删除元素下标为x的元素，如果x为null,则集合保持不变 对VARRAY
非法
DELETE(x,y) 删除元素下标从X到Y的元素，如果X>Y集合保持不变 对VARRAY
非法
TRIM 从集合末端开始删除一个元素 对index_by不合法
TRIM(x) 从集合末端开始删除x个元素 对index_by不合法

3、判断元素是否存在EXIST(x)
如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE

4、为集合添加元素EXTEND，EXTEND(x)，EXTEND(x,n)
EXTEND 在集合末尾添加一个元素 对Index_by非法
EXTEND(x) 在集合末尾添加x个元素 对Index_by非法
EXTEND(x,n) 在集合末尾添加元素n的x个副本 对Index_by非法

5、返回集合元素下标FIRST、LAST、NEXT(x)，PRIOR(x)
FIRST 返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
LAST 返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT。
NEXT(x) 返回在元素x之后及紧挨着它的元素的下标值，如果该元素是最后一
个元素，则返回null。
PRIOR(x) 返回集合中在元素x之前紧挨着它的元素的下标值，如果该元素是第
一个元素，则返回null。

6、LIMIT 返回VARRY集合的最大的元素个数，对于嵌套表和Index_by集合无用。