在oracle 11.2中,oracle总共提供了三种集合类型:varray,嵌套表,联合数组。所谓集合,简单来说就是保存多行数据的数据类型,相当于保存在内存中的小型表,便于暂时保存数据,以及数据的重复使用。下面的表简单描述了其中的区别与联系:
类型
|
|
保存数据类型数量
|
长度
|
创建实例
| |
标量集合
|
varry
|
一种
|
固定长度(长度使用extend方法增加)
|
一维数组(相当于类,有其方法和构造方法【类型名】)
序号连续
对象类型
|
create or replace type type_name as varry(3) of varchar(20)或者在程序的定义区中声明:
type type_name is varry(3) of varchar(20);
初始化:object_name type_name :=type_name();
赋值: object_name(i):=n;
|
标量集合
|
嵌套表
|
一种
|
长度没有限制
|
一维数组(相当于类,有其方法和构造方法【类型名】)
序号连续
对象类型
|
create or replace type type_name as table of varchar(20)或者在程序的定义区中声明:
type type_name is table of varchar(20)
初始化:object_name type_name :=type_name();
赋值: object_name type_name :=type_name();
|
标量集合
|
关联数组
|
一种
|
长度没有限制
|
一维列表
序号不连续,删除会断续
非对象类型,只能用于PL/SQL
|
只能在程序的定义区中声明:
type type_name is table of varchar(20)index by binary_integer
初始化:object_name type_name;
赋值: object_name:=n;
|
对象类型集合
|
SQL嵌套表
|
多种
|
长度没有限制
|
列表(跟table很像)
多维数组
SQL记录集合
|
1、创建对象类型
create or replace type object_type_name is object
(
column_name number,
column_name char
);
2、创建SQL级别的对象类型集合
create or replace type object_type_name_table is table of object_type_name;
3、初始化
object_name object_type_name_table :=object_type_name_table ();
4、赋值
object_name(i):=object_type_name (i,i,i);
|
对象类型集合
|
PL/SQL嵌套表
|
多种
|
长度没有限制
|
列表(跟table很像)
多维数组
SQL记录集合
|
1、3、4同上
2、创建PL/SQL级别的对象类型集合:声明
type object_type_name_table is table of object_type_name
|
对象类型集合
|
记录类型集合
|
多种
|
一行数据
|
相当于数据库表中的一行数据
非对象类型
(只能在PL/SQL中使用)
|
1、声明(在一个包中创建)
type recorde_name is record
(
column_name number,
column_name char
);
type object_recorde_name is table of recorde_name index by PLS_INTEGER;
2、初始化
object_name object_recorde_name;
3、赋值
object_name(i)=i;
|
1、varray固定数组
简单来说,varray就是一维数组,保存一种数据类型的数据,长度固定。声明的时候若为空数组,则表示还没有分配空间,需要手工分配空间extend,否则会报错。另外需要注意的是,该数组的下标是从1开始的。
set serveroutput on;
declare
no int :=1;
type fixed_array is varray(4) of char(2);---varray固定数组类型定义;
Farray fixed_array :=fixed_array();---varray固定数组声明,这里定义的数组为空数组,并没有分配任何空间
begin
for i in (select distinct COUNTRY_ISO_CODE from countries where rownum<4)loop
farray.extend;---分配空间,不能访问空数组,所以空数组的场合,必须进行数组扩展
farray(no):=i.COUNTRY_ISO_CODE;---下标从1开始,不能超过数组所有元素的总和,当下标超出允许范围时,出现异常:ORA-06532: Subscript outside of limit
no := no+1;
end loop;
dbms_output.put_line('分配的空间:'|| farray.count);---count这个API方法是输出所分配的空间;
for i in 1..farray.count loop ----输出结果值;
dbms_output.put('/'||farray(i));
if i=farray.count then
dbms_output.new_line;
end if;
end loop;
end;
anonymous block completed
分配的空间:3
/US/DE/GB
2、可变数组
顾名思义,就是数组的长度没有限制。其中有两种集合类型,一是嵌套表,二是联合数组。跟上面的varray一样,数组的下标从1开始。不过,联合数组的序号有可能是不连续的,删除了其中的某个元素之后就不连续了。
/**嵌套表作为标量集合的时候,跟上面的varray差不多。
**声明是若为空数组,则需要分配空间;
**不同的是,分配的空间没有限制
**/
set serveroutput on;
declare
no int :=1;
type variable_array is table of char(2);--没有空间分配的限定设置
vtable variable_array :=variable_array();
begin
for i in (select distinct COUNTRY_ISO_CODE from countries where rownum<5)loop
vtable.extend; --分配空间
vtable(no):=i.COUNTRY_ISO_CODE;
no := no+1;
end loop;
dbms_output.put_line('分配的空间:'|| vtable.count);---可以看到结果为4
for i in 1..vtable.count loop
dbms_output.put('/'||vtable(i));
if i=vtable.count then
dbms_output.new_line;
end if;
end loop;
end;
anonymous block completed
分配的空间:4
/US/DE/GB/NL
/**联合数组存在这比较多的不同:
**1、声明方式不一样;
**2、不用显式分配空间
**3、删除之后,空间被回收,但是序号依然存在,为此输出值的时候需要注意这些不存在值的序号,否则会报错
**/
set serveroutput on;
declare
no int :=1;
type variable_array is table of char(2) index by binary_integer;--没有空间分配的限定设置
vtable variable_array ;---声明的方式有所不同;
begin
for i in (select distinct COUNTRY_ISO_CODE from countries where rownum<6)loop
----vtable.extend; --分配空间
vtable(no):=i.COUNTRY_ISO_CODE;
no := no+1;
end loop;
dbms_output.put_line('分配的空间:'|| vtable.count);---可以看到结果为5
vtable.delete(2);
dbms_output.put_line('删除后分配的空间:'|| vtable.count);---可以看到结果为4,即已经回收了删除的空间
if vtable.exists(2)=false then
dbms_output.put_line('删除之后的元素不再存在');----可以看到该元素已经不存在
end if;
for i in 3..vtable.count loop ---如果这里包含不存在的元素2的话,会报错。
dbms_output.put('/'||vtable(i));
if i=vtable.count then
dbms_output.new_line;
end if;
end loop;
end;
3、记录
record,保存的是一行数据。与%TYPE,%ROWTYPE用起来比较方便。可以随着表列类型的更新而更新。
/**record只是保存返回的一行记录;
**不过,record比较方便的就是其类型可以随着表列类型的更新而更新;
**/
set serveroutput on;
declare
OneRow countries%rowtype;---该record类型对应的是表的所有列;
type Rrecord is record( ---定义record类型,只是某些表的某些列;
code countries.COUNTRY_ISO_CODE%type,
name countries.COUNTRY_NAME%type
);
country Rrecord;---声明对象;
begin
select * into OneRow from countries where rownum<2;----注意,只能往其中输入一行数据,否则会报错:ORA-01422: 实际返回的行数超出请求的行数;
select country_iso_code,country_name into country from countries where rownum<2;--部分列
dbms_output.put_line('国家ID:'||country.code);
dbms_output.put_line('国家名称:'||country.name);
end;
anonymous block completed
国家ID:US
国家名称:United States of America
4、保存列表数据
上面提到的都是保存一维数组,如果需要保存多维数组的话,可以利用嵌套来实现。
record+varray/嵌套表/联合数组。三种集合类型用法比较相似,为此只是对varray举例
declare
no int :=1;
type Rrecord is record(
code countries.COUNTRY_ISO_CODE%type,
name countries.COUNTRY_NAME%type);----定义record记录类型
type var is varray(3) of Rrecord;-----定义记录集合数组
multirow var := var();----------------声明记录集合数组
begin
multirow.extend(3);---分配空间;
select country_iso_code,country_name BULK COLLECT INTO multirow from countries where rownum<4;---批量插入数据;
----输出结果
for i in 1..multirow.count loop
dbms_output.put_line(i||' code:'||multirow(i).code||' name: '||multirow(i).name);
end loop;
end;
anonymous block completed
1 code:US name: United States of America
2 code:DE name: Germany
3 code:GB name: United Kingdom