PLSQL集合数据类型的应用(PL/SQL多级集合、PL/SQL集合类型所特有的集合方法、集合数据类型的赋值、集合数据类型的比较、集合数据类型的批量绑定)

把当下的事做好

已于 2022-11-10 12:57:53 修改

阅读量254

点赞数

分类专栏：笔记文章标签： oracle

于 2022-10-04 22:32:31 首次发布

本文链接：https://blog.csdn.net/weixin_43809550/article/details/127165420

版权

笔记专栏收录该内容

20 篇文章 2 订阅

订阅专栏

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;

把当下的事做好

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
PLSQL集合数据类型的应用(PL/SQL多级集合、PL/SQL集合类型所特有的集合方法、集合数据类型的赋值、集合数据类型的比较、集合数据类型的批量绑定)

当使用嵌套表和VARRAY时，通过执行insert、update、fetch、select，赋值语句，用可以将一个集合的数据赋值给另外一个集合。说明：用于取得批量数据，将批量数据存放到PL/SQL集合变量中，只用于select into、fetch into和dml返回子句中。10g后通过增加indices of子句和values of子句，可以使用不连续的集合元素。PL/SQL复合数据类型包括：PL/SQL记录、PL/SQL集合（PL/SQL表(别名索引表)、嵌套表、VARRAY）。
复制链接

扫一扫