Oracle 复合数据类型专题之PLSQL集合数据类型

Oracle PL/SQL集合数据类型专题:


一、什么是PL/SQL集合数据类型
PL/SQL复合数据类型包括:PL/SQL记录、PL/SQL集合(分为PL/SQL表(别名索引表)、嵌套表、VARRAY)。
其中PL/SQL集合数据类型是指符合数据类型中的PL/SQL集合。


复合数据类型的说明在本博客的《PLSQL数据类型》中有详述,这里只对PL/SQL集合数据类型的一系列特有操作与应用做讲述。




二、PL/SQL集合的多级集合调用应用
说明:多级集合是指嵌套了集合类型的集合类型,从而实现多维数组功能,9i后支持。


1)多级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..nu1(i).count loop
    Dbms_output.put_line(i||’,’||j||’shi’||nv1(i)(j));
  End loop;
End loop;
End;


2)多级嵌套表
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;


3)多级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);
begin
dbms_output.put_line(‘显示二维数组所有元素’);
for i in 1..nv1.count loop
  for j in 1..nv1.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是过程。


1、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.exsts(1) then
ename_table(1):='scott';
else
dbms_output.put_line('必须初始化集合元素');


2、count方法
说明:返回当前集合变量中的元素总个数。
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.count);
end;


3、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;


4、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);
dbms_output.put_line(ename_table.last);
end;


5、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;


6、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(no));
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;


7、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;


8、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时,通过执行insert、update、fetch、select,赋值语句,用可以将一个集合的数据赋值给另外一个集合。
从Oracle10g开始,给嵌套表赋值时,还可以用set、multiset union、multiset intersect、multiset except等集合操作符。
其中set用于取消嵌套表中的重复值,multiset union用于取得2个嵌套表的并集(带有distinct可以取消重复结果)
multiset intersect取得2个嵌套表的交集,multiset except取得2个嵌套表的差集。


1、将一个集合的数据赋值给另外一个集合
说明:当使用赋值语句(:=)或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;


2、给集合符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;


3、使用集合操作符给嵌套表赋值
说明: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)等。


1、检测集合是否为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;


2、比较嵌套表是否相同
说明:使用=和!=比较嵌套表是否相同,只能用于嵌套表。
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;


3、在嵌套表上使用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、使用与不使用批量绑定的区别


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 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;


2、FORALL语句
说明:当要在PL/SQL中执行批量insert、update、delete操作,可以使用forall语句。
9i前forall语句必须是连续的元素;10g后通过增加indices of子句和values of子句,可以使用不连续的集合元素。
注意:for语句时循环语句,forall不是循环语句。如下面语法,9i只有语法一,10g后新增了语法二、三。


语法一: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;


2)、FORALL语句中使用部分集合连续的元素
说明:上面说到的都是集合的所有元素,forall可以使用部分元素。
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;


3)、forall语句上使用indices of子句
说明:indices of子句是10g新增,用于跳过集合中的null元素。
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;


4)、forall语句上使用values of子句
说明:values of子句是10g新增,用于从其他集合中取得集合下标的值。
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属性
说明:专门为forall语句提供的属性,用于取得执行批量绑定是第i个元素所作用的行数。
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;


3、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开始通过在elect 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
dnms_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;




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值