Oracle 复合数据类型专题之PLSQL集合数据类型
PL/SQL复合数据类型包括:PL/SQL记录、PL/SQL集合(PL/SQL表(别名索引表)、嵌套表、VARRAY)
这里只对PL/SQL集合数据类型的一系列特有操作与应用做讲述。
PL/SQL多级集合
多级集合是指嵌套了集合类型的集合类型,从而实现多维数组功能,9i后支持。
多级PL/SQL表(索引表)
说明:如果多维数组元素个数没有限制,可以使用多级PL/SQL表和嵌套表。
declare
type al_table_type is table of int index by binary_integer; --定义一维table
type nal_table_type is table of al_table_type index by binary_integer;--定义二维table集合
nv1 nal_table_type;
begin
nv1(1)(1):=10;
nv1(1)(2):=5;
nv1(2)(1):=100;
nv1(2)(2):=50;
dbms_output.put_line('显示二维数组所有元素');
for i in 1..nv1.count loop
for j in 1..nv1(i).count loop
dbms_output.put_line(i||' , '||j||' shi '||nv1(i)(j));
end loop;
end loop;
end;
多级嵌套表
declare
type al_table_type is table of int; --定义一维嵌套表
type nal_table_type is table of al_table_type; --定义二维嵌套表集合
nv1 nal_table_typer := nal_table_typer (al_table_type(2,4), al_table_type(5,73)); --初始化嵌套表
begin
dbms_output.put_line('显示二维数组所有元素');
for i in 1..nv1.count loop
for j in 1..nv1(i).count loop
dbms_output.put_line(i||' , '||j||' shi '||nv1(i)(j));
end loop;
end loop;
end;
多级VARRAY
declare
type al_varray_type is varray(10) of int; --定义一维varray,并限制一维数组长度
type nal_varray_type is varray(10) of al_varray_type; --定义二维varray集合
nvl nal_varray_type:= nal_varray_type(al_varray_type(58,100,102),al_varray_type(55,6,73),al_varray_type(2,4));--初始化varry集合
begin
dbms_output.put_line('显示二维数组所有元素');
for i in 1..nv1.count loop
for j in 1..nv1(i).count loop
dbms_output.put_line(i||' , '||j||' shi '||nv1(i)(j));
end loop;
end loop;
End;
PL/SQL集合类型所特有的集合方法
集合方法是Oracle所提供用于操纵集合变量的内置函数或过程。
方法包含EXISTS、COUNT、LIMIT、FIRST、NEXT、PRIOR和NEXT函数,EXTEND、TRIM和DELETE是过程。
EXISTS方法
说明:确定特定的PL/SQL表元素是否存在,如果集合元素存在,则返回true;如果不存在,则返回false
declare
type ename_table_type is table of emp.ename%type; --定义一个嵌套表类型(或一个集合类型)
ename_table ename_table_type; --声明一个集合变量
begin
if ename_table.exists(1) then
ename_table(1):='scott';
else
dbms_output.put_line('必须初始化集合元素');
end if;
end
COUNT方法
说明:返回当前集合变量中的元素总个数。
declare
type aneme_table_type is table of emp.ename%type index by binary_integer; --定义一个PL/SQL索引表类型(或一个集合类型)
ename_table ename_table_type; --声明集合对象
begin
ename_table(-5):='aa';
ename_table(1):='b';
ename_table(10):='bk';
dbms_output.put_line(ename_table.count);
end;
LIMIT方法
说明:返回集合元素的最大元素个数。因为嵌套表和索引表的元素个数没有限制,所以调用方法会返回NULL;而对于VARRAY会返回最大元素个数。
declare
type aneme_table_type is varray(20) of emp.ename%type;
ename_table ename_table_type:=ename_table_type('mary');
begin
dbms_output.put_line(ename_table_type.limit);
end;
FIRST和LAST方法
说明:返回集合第一个、最后一个元素的下标。
declare
type aneme_table_type is table of emp.ename%type index by binary_integer;
ename_table ename_table_type;
begin
ename_table(-5):='aa';
ename_table(1):='b';
ename_table(10):='bk';
dbms_output.put_line(ename_table.first); -- -5
dbms_output.put_line(ename_table.last); -- 10
end;
PRIOR和NEXT方法
说明:返回当前集合元素的前一个元素和下一个元素
declare
type ename_table_type is table of emp.ename%type index by benary_integer; --PL/SQL表
ett ename_table_type;
begin
et(-5):='scott';
et(1):='smith';
st(5):='mary';
dbms_output.put_line('元素1的前一个元素值:' || et(et.prior(1))); --结果是scott
dbms_output.put_line('元素1的后一个元素值:' || et(et.next(1))); --结果是mary
end;
EXTEND方法
说明:为集合变量增加元素,只适用于嵌套表和VARRAY。
格式:extend 添加一个null元素
extend(n) 添加n个null元素
extend(n,i) 添加n个与第i个元素相同的值
declare
type id_table_type is table of number(6); --嵌套表
idt id_table_type:=id_table_type(1,2,5); --声明并初始化3个元素
begin
if idt.exists(4) then --判读第四个元素是否存在
dbms_output.put_line('元素值:' || idt(4));
else
dbms_output.put_line('元素未初始化'); --结果
end if;
idt.extend(999); --添加999个空元素
dbms_output.put_line('第一个元素是:' || idt(idt.first)); --结果为1
dbms_output.put_line('最后一个元素是:' || nvl(to_char(idt(idt.first)),'null')); --结果为null
dbms_output.put_line('元素总数为:' || idt.count); --结果为1002
end;
TRIM方法
说明:从集合尾部删除元素,只适用于嵌套表和VARRAY。
格式:trim 删除一个元素
trim(n) 删除n个
declare
type id_array_type is varray(30) of number(6); --可变长数组
iat id_array_type:=id_array_type('A','B','C','D','E','F','G','H','I','J','K','L');
begin
dbms_output.put_line('元素总数为:' || iat.count); --结果为12
dbms_output.put_line('最大元素个数:' || iat.limit); --结果是30
iat.trim(5);
dbms_output.put_line('最后一个元素:' || iat(iat.last)); --结果是G
end;
DELETE方法
说明:删除集合元素,只适用于嵌套表和索引表。
格式:delete 删除全部元素
delete(n) 删除第N个元素
delete(m,n) 删除除集合变量中m~n之间的所有元素
declare
type id_table_type is table of number(6) index by binary_integer; --PL/SQL表
itt id_table_type;
begin
for i in 1..10000 loop
itt(i):=i;
end loop;
for j in 1..itt.count loop
if mod(j,2)=0 then
id.delete(j); --余数为0则删除该元素
end if;
end loop;
end;
集合数据类型的赋值(适用于嵌套表和VARRAY)
当使用嵌套表和VARRAY时,通过执行insert、update、fetch、select into赋值语句,可以将一个集合的数据赋值给另外一个集合。
从Oracle10g开始,给嵌套表赋值时,还可以用set、multiset union、multiset intersect、multiset except等集合操作符。其中set用于取消嵌套表中的重复值,multiset union用于取得2个嵌套表的并集(带有distinct可以取消重复结果)。multiset intersect取得2个嵌套表的交集,multiset except取得2个嵌套表的差集。
将一个集合的数据赋值给另外一个集合
说明:当使用赋值语句(:=) 或 sql语句将源集合中的数据赋值给目标集合时,会自动清除模板集合原有数据再赋值。
注意:当进行集合赋值时,源集合和目标集合的数据类型必须一致。
declare
type name_varray_type is varray(4) of varchar2(10); --定义一个嵌套表类型(定义一个集合类型)
name_array1 name_varray_type;
name_array2 name_varray_type;
begin
name_array1:=name_varray_type('scott','smith');
name_array2:=name_varray_type('a','a','a','a','a');
dbms_output.put('2的原数据:');
for i in 1..name_array2.count loop
dbms_output.put(name_array2(i));
end loop;
dbms_output.new_line;
name_array2:=name_array1;
dbms_output.put('2的新数据:');
for i in 1..name_array2.count loop
dbms_output.put(name_array2(i));
end loop;
dbms_output.new_line;
end;
给集合赋NULL值
说明:在清空集合变量的所有数据时,既可以使用delete和trim方法,也可以将一个null集合变量赋给目标集合变量。
declare
type name_varray_type is varray(4) of varchar2(10);
name_array1 name_varray_type;
name_array2 name_varray_type;
begin
name_array1:=name_varray_type('scott','smith');
dbms_output.put('2的原数据个数'||name_array1.count);
name_array1:=name_array2;
--dbms_output.put('2的新数据个数'||name_array1.count);
end;
使用集合操作符给嵌套表赋值
说明:10g开始,允许将多个嵌套表的结果组合到某个嵌套表中,通过使用ANSI集合操作符(set,multiset union,multiset intersect,multiset except)实现。
1)set操作符
说明:用于去掉嵌套表中的重复值。
declare
type nt_table_type is table of number;
nt_table nt_table_type := nt_table_type(2,4,3,1,2);
result nt_table_type;
begin
result := set(nt_table);
dbms_output.put('result:');
for i in 1..result.count loop
dbms_output.put(' '||result(1));
end loop;
dbms_output.new_line;
end;
2)multiset union和multiset union distinct操作符
说明:multiset union取得2个嵌套表的并集,保留重复项;multiset union distinct去掉重复。
declare
type nt_table_type is table of numberr;
nt1 nt_table_type := nt_table_type(1,2,3);
nt2 nt_table_type := nt_table_type(3,4,5);
result nt_table_type;
begin
result := nt1 multiset union nt2;
dbms_output.put('resilt:');
for i in 1..result.count loop
dbms_output.put(' '||result(i));
end loop;
dbms_output.new_line;
end;
3)multiset intersect操作符
说明:取得2个嵌套表的交集。
declare
type nt_table_type is table of numberr;
nt1 nt_table_type := nt_table_type(1,2,3);
nt2 nt_table_type := nt_table_type(3,4,5);
result nt_table_type;
begin
result := nt1 multiset intersect nt2;
dbms_output.put('resilt:');
for i in 1..result.count loop
dbms_output.put(' '||result(i));
end loop;
dbms_output.new_line;
end;
4)multiset except操作符
说明:取两个嵌套表变量的差集。
declare
type nt_table_type is table of numberr;
nt1 nt_table_type := nt_table_type(1,2,3);
nt2 nt_table_type := nt_table_type(3,4,5);
result nt_table_type;
begin
result := nt1 multiset except nt2;
dbms_output.put('resilt:');
for i in 1..result.count loop
dbms_output.put(' '||result(i));
end loop;
dbms_output.new_line;
end;
集合数据类型的比较(适用于嵌套表)
说明:用于比较两个集合变量是否相同。10g后新增集合比较操作符包括:
CARDINALITY(返回嵌套表变量的元素个数)
SUBMULTISET OF(确定一个嵌套表是否为领一个嵌套表的子集)
MEMBER OF(检测特定数据是否为嵌套表元素)
IS A SET(检测嵌套表是否包含重复的元素)
IS EMPTY(检测嵌套表是否为null)
检测集合是否为null
说明:is null使用于嵌套表或VARRAY变量;is empty只使用于嵌套表。
declare
type name_array_type is varray(3) of varchar2(10);
name_array nane_array_type;
begin
--if name_array is empty then
if name_array is null then
dbms_output.put_line('未初始化');
end if;
end;
比较嵌套表是否相同
说明:使用=和!=比较嵌套表是否相同,只能用于嵌套表。
declare
type name_table_type is table of varchar2(10);
name_table1 name_table_type;
name_table2 name_table_type;
begin
name_table1:=name_table_type('scott');
name_table2:=name_table_tyoe('smith');
if name_table1=name_table2 then
dbms_output.put_line('相同');
else
dbms_output.put_line('不同');
end if;
end;
在嵌套表上使用ANSI集合操作符
说明:这些操作符只适用于嵌套表。
1)CARDINALITY
说明:也可以称为函数,返回嵌套表变量的元素个数。
declare
type nt_table_type is table of number;
nt1 nt_table_tyoe:=nt_table_type(1,2,3,1);
begin
dbms_output.put_line('元素个数'||CARDINALITY(nt1));
end;
2)SUBMULTISET OF
说明:用于确定一个嵌套表是否为另一个嵌套表的子集。
declare
type nt_table_type is table of number;
nt1 nt_table_type:=nt_table_type(1,2,3);
nt2 nt_table_type:=nt_table_type(1,2,3);
begin
if nt1 submiltiset of nt2 then
dbms_output.put_line('nt1是nt2的子集');
end if;
end;
3)MEMBER OF
说明:检测特定数据是否为嵌套表的元素。
declare
type nt_table_type is table of number;
nt1 nt_table_tyoe:=nt_table_tyoe(1,2,3,5);
v1 number:=2;
begin
if v1 member of nt1 then
dbms_output.put_line('v1是nt1的元素');
end if;
end;
4)IS A SET
说明:用于检测嵌套表是否包含重复的元素值。
declare
type nt_table_type is table of number;
ot1 nt_table_type:=nt_table_type(1,2,3,5);
begin
if nt1 is a set then
dbms_output.put_line('嵌套表nt1无重复值');
end if;
end;
集合数据类型的批量绑定
说明:9i新增,是指执行单次SQL操作能传递所有集合的数据,当在select、insert、update、delete语句上处理批量数据时,通过批量绑定,可以极大加快数据处理速度。
批量绑定用bult collect子句和forall语句完成。其中bult collect子句用于取得批量数据,该子句只能用于select语句、fetch语句、dml返回子句中;而forall语句值适用于执行批量DML操作。
使用与不使用批量绑定的区别
1)不使用批量绑定
说明:9i之前,为了将多个集合元素插入到数据库表,必须要使用循环完成。
--例子:以索引表为例
declare
type id_table_type id table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..5000 loop
id_table(i):=i;
name_table(i):='Name'||to_char(i);
end loop;
start_time:=dbms_utility.get_time;
for i in 1..id_table.count loop --使用循环完成insert全部插入
insert into demo values(id_table(i),name_table(i));
end loop;
end_time:=dbms_utility.get_time;
dbms_output.put_line('总时间'||to_char((end_time-start_time)/100));
end;
2)使用批量绑定
declare
type id_table_type id table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..5000 loop
id_table(i):=i;
name_table(i):='Name'||to_char(i);
end loop;
start_time:=dbms_utility.get_time;
--批量绑定的使用开始
forall i in 1..id_table.count
insert into demo values(id_table(i),name_table(i));
--批量绑定的使用结束
end_time:=dbms_utility.get_time;
dbms_output.put_line('总时间'||to_char((end_time-start_time)/100));
end;
FORALL语句
说明:当要在PL/SQL中执行批量insert、update、delete操作,可以使用forall语句。
注意:for语句是循环语句,forall不是循环语句。如下面语法,9i 只有语法一,10g 后新增了语法二、三。
9i 前forall语句必须是连续的元素;10g 后通过增加indices of子句和values of子句,可以访问非连续集合。
语法一:forall i in lower_bound..upper_bound sql_statement;
其中,i是隐含定义的整数变量(将作为集合元素下标被引用),lower_bound和upper_bound是集合元素上下界。
语法二:forall i in indices of collection [between lower_bound and upper_bound] sql_statement;
其中,indices of指定只取对应于collection集合元素下标的i值。
语法三:forall i in values of index_collection sql_statement;
其中,values of指定i值从集合变量index_collection中取得。
1)DML语句上使用forall进行批量绑定
declare
type id_table_type id table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
begin
for i in 1..50 loop
id_table(i):=i;
name_table(i):='Name'||to_char(i);
end loop;
forall i in 1..id_table.count --插入全部
insert into demo values(id_table(i),name_table(i));
update demo set name=name_table(i) where id=id_table(i);
delete from demo where id=id_table(i);
commit;
end;
declare
type id_table_type id table of number(6) index by binary_integer;
id_table id_table_type;
begin
for i in 1..50 loop
id_table(i):=i;
end loop;
forall i in 8..10 --插入部分
insert into demo values(id_table(i),name_table(i));
commit;
end;
2)forall indxes in indices of collection
说明:迭代的不是元素值,是元素在集合中的索引值。通过collection(indxes)得到集合的值
declare
type id_table_tyoe is table of number(6);
id_table id_table_type;
begin
id_table:=id_table_type(1,null,3,null,5);
forall i in indicrs of id_table
delete from demo where id=id_table(i);
end;
3)forall values in values of collection
说明:就是从集合中迭代存储的元素值,循环体内直接使用 values就是元素值了,一般用于嵌套集合。
declare
type id_table_type is table of demo.id%type;
type name_table_type is table of demo.name%type;
id_table id_table_type;
name_table name_table_type;
type index_pointer_type is table of pls_integer;
index_pointer index_pointer_type;
begin
select * bulk collect into id_table,name_table from demo;
index_pointer:=index_pointer_type(6,8,10);
forall i in values of index_pointer
insert into new_demo_table2 values(id_table(i),name_table(i));
end;
--结果是向表中插入了6 8 10的数据
5)forall语句上使用 SQL%BULK_ROWCOUNT(i) 属性
说明:SQL%BULK_ROWCOUNT(i)表示每一次的操作所影响的行数,该值是存在一个集合里,forall中的第n条dml语句处理的行数存储在该集合的第n个元素中
declare
type dno_table_type is table of number(3);
dno_table dno_table_type:=dno_table_type(10,20);
begin
forall i in 1..dno_table.count
update emp set sal=sal*1.1 where deptno=dno_table(i);
dbms_output.put_line('第2个元素更新的行数:'||SQL%BULK_ROWCOUNT(2));
end;
BULK COLLECT子句
说明:用于取得批量数据,将批量数据存放到PL/SQL集合变量中,只用于select into、fetch into和dml返回子句中。
语法:。。。BULK COLLECT into collection_name[,collection_name]。。。
其中,collection_name指定存放的集合变量名
1)、在select into语句中使用bulk collect
说明:9i前,select into必须且只能返回1行数据,否则抛异常,9i开始通过在select into使用bulk collect子句可以一次将select语句的多行结果检索到集合变量。
declare
type emp_table_type is table of emp%rowtype index by binary_integer;
emp_table emp_table_type;
begin
select * bulk collect into emp_table from emp where deptno=&no;
for i in 1..emp_table.count loop
dbms_output.put_line(emp_table(i).ename);
end;
2)在DML的返回子句中使用bulk collect子句
说明:为了取得DML操作所改变的数据,可以使用returning子句;为了取得DML所作用的多行数据,需要使用bulk collect子句。
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
delete from emp where deptnp=&no returning ename bulk collect into ename_table;
dbms_output.put('雇员名:');
for i in 1..ename_table.count loop
dbms_output.put(ename_table(i));
end loop;
end;