PL/SQL集合

时间：2010-09-14 来源：蓝紫

2010年9月14日 11:06:47

PL/SQL集合

为了处理单行的数据，开发人员可以使用标量变量；为了处理单行多列的数据，可以使用PL/SQL记录，而为了处理单列多行数据，可以使用PL/SQL集合。
   PL/SQL集合类似于高级语言数组的一种复合数据类型，集合类型包括索引表（PL/SQL表）、嵌套表（Nested Table）和变长数组（VARRAY）等三种类型。使用这些集合类型时，要注意三种区别，以便选择最合适的数据类型。

1.索引表
   索引表也成为PL/SQL表，它是Oracle早期版本用于处理PL/SQL数组的数据类型。但高级语言数组的元素个数是有限制的，并且下标不能为负值；而索引表的元素个数没有限制，并且下标可以为负值。
   定义索引表的语法如下：
    type type_name is table of element_type
    （not null）index by key_type;
     identifier type_name;
如上所示，type_name用于指定用户自定义数据类型的名称（IS TABLE..INDEX表示索引表）：element_type用于指定索引表元素的数据类型；not null用于指定不允许引用null元素，key_type用于指定索引表下标的数据类型（binary_integer、pls_integer和varchar2）；identifier用于定义索引表变量。
   注：从Oracle9i开始索引表下标才允许使用varchar2数据类型。

--在索引表中使用binary_integer和pls_integer
declare
type name_table_type is table of temp_mycursor.name%type
    index by binary_integer;
name_table name_table_type;
begin
select name into name_table(-1) from temp_mycursor
    where id=&no;
dbms_output.put_line('名称:'||name_table(-1));
end;

--在索引表中使用varchar2
declare
   type area_table_type is table of number
      index by varchar2(10);
    area_table area_table_type;
begin
    area_table('北京'):=1;
    area_table('上海'):=2;
    area_table('广州'):=3;
    dbms_output.put_line('第一个元素:'||area_table.first);
    dbms_output.put_line('最后一个元素:'||area_table.last);
end;

2.集合方法
   集合方法是oracle所提供的用于操纵集合变量的内置函数或过程，其中exists，count，limit，first，next，prior，next是函数，而extend，trim，delete则是过程。集合调用语法如下：
        collection_name.method_name[(parameters)]
   注意，集合方法只能在PL/SQL语句中使用，而不能在SQL语句中使用。另外集合方法extend和trim只适用于嵌套表和varray，而不适用于索引表。

--exists 用于确定集合元素是否存在
declare
   type area_table_type is table of temp_mycursor.name%type;
    area_table area_table_type;
begin
    if area_table.exists(1) then
       area_table(1):='tt';
    else
      dbms_output.put_line('必须初始化元素');
    end if;
end;

--count的使用（统计元素总数）
declare
   type area_table_type is table of temp_mycursor.name%type
      index by binary_integer;
    area_table area_table_type;
begin
    area_table(-1):='tt';
    area_table(3):='cc';
    area_table(4):='gg';
    dbms_output.put_line('元素3的前一个元素:'||area_table.count);
end;

--limit的使用（返回集合元素的最大个数）
declare
   type area_table_type is varray(30) of temp_mycursor.name%type;
    area_table area_table_type:=area_table_type('ff');
begin
    dbms_output.put_line('集合元素的最大个数:'||area_table.limit);
end;

--first和last的使用
declare
   type area_table_type is table of temp_mycursor.name%type
      index by binary_integer;
    area_table area_table_type;
begin
    area_table(-1):='tt';
    area_table(3):='cc';
    area_table(4):='gg';
    dbms_output.put_line('第一个元素:'||area_table.first);
    dbms_output.put_line('最后一个元素:'||area_table.last);
end;

--prior和next的使用
declare
   type area_table_type is table of temp_mycursor.name%type
      index by binary_integer;
    area_table area_table_type;
begin
    area_table(-1):='tt';
    area_table(3):='cc';
    area_table(4):='gg';
    dbms_output.put_line('元素3的前一个元素:'||area_table.prior(3));
    dbms_output.put_line('元素3的下一个元素:'||area_table.next(3));
end;

--extend 用于扩展集合变量的尺寸，并为它们增加元素。该方法有extend，extend(n),extend(n,i)等三种调用格式，
         其中extend用于为集合变量添加一个null元素，extend(n)用于为集合变量添加n个null元素，而extend(n,i)用           于为集合变量添加n个元素（元素值与第i个元素相同）
   declare
   type area_table_type is varray(20) of varchar2(20);
    area_table area_table_type;
begin
    area_table:=area_table_type('tt');
    area_table.extend(5,1);
    dbms_output.put_line('元素总个数:'||area_table.count);
end;

--trim 裁剪元素
    declare
   type area_table_type is table of varchar2(10);
    area_table area_table_type;
begin
    area_table:=area_table_type('a','a','a','a');
    area_table.trim(2); --裁掉2个元素
    dbms_output.put_line('元素总个数:'||area_table.count); --总个数2个
end;

--delete 删掉某元素
declare
   type area_table_type is table of temp_mycursor.name%type
      index by binary_integer;
    area_table area_table_type;
begin
    area_table(-1):='tt';
    area_table(3):='cc';
    area_table(4):='gg';
    area_table.delete(3); --删掉元素3
    dbms_output.put_line('元素总个数:'||area_table.count); --总个数2
end;

3.集合赋值
--将一个集合的数据赋值给另一个集合
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');
dbms_output.put('name_array2的原数据:');
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('name_array2的新数据:');
for i IN 1..name_array2.count LOOP
    dbms_output.put(' '||name_array2(i));
END LOOP;
dbms_output.new_line;
END;
输出结果 name_array2的原数据:'a','a','a','a'；
         name_array2的新数据:'SCOTT','SMITH'
备注：当进行集合赋值时，源集合和目标集合的数据类型必须完全一致。如果集合元素数据类型一致，但集合类型不一致，也不能进行赋值。

--给集合赋NULL值
DECLARE
TYPE name_varray_type IS VARRAY(4) OF VARCHAR2(10);
name_array name_varray_type;
name_empty name_varray_type;
BEGIN
name_array:=name_varray_type('SCOTT','SMITH');
dbms_output.put_line('name_array的原有元素个数:'||name_array.count);
name_array:=name_empty;
IF name_array IS NULL THEN
dbms_output.put_line('name_array的现有元素个数:0');
END IF;
END;
输出结果 name_array的原有元素个数:2
         name_array的现有元素个数:0

--使用集合操作符给嵌套表赋值
--使用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_line(' '||result(i));
END LOOP;
dbms_output.new_line;
END;
result: 2
4
3
1

--multiset union操作符的使用
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(3,4,5);
result nt_table_type;
BEGIN
result:=nt1 multiset union nt2;
dbms_output.put('result:');
FOR i in 1..result.count LOOP
    dbms_output.put_line(' '||result(i));
END LOOP;
dbms_output.new_line;
END;
result: 1
2
3
3
4
5

--multiset union distinct操作符的使用
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(3,4,5);
result nt_table_type;
BEGIN
result:=nt1 multiset union distinct nt2;
dbms_output.put('result:');
FOR i in 1..result.count LOOP
    dbms_output.put_line(' '||result(i));
END LOOP;
dbms_output.new_line;
END;

result: 1
2
3
4
5

--multiset intersect操作符的使用
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(3,4,5);
result nt_table_type;
BEGIN
result:=nt1 multiset intersect nt2;
dbms_output.put('result:');
FOR i in 1..result.count LOOP
    dbms_output.put_line(' '||result(i));
END LOOP;
dbms_output.new_line;
END;
result: 3

--multiset except操作符的使用
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(3,4,5);
result nt_table_type;
BEGIN
result:=nt1 multiset except nt2;
dbms_output.put('result:');
FOR i in 1..result.count LOOP
    dbms_output.put_line(' '||result(i));
END LOOP;
dbms_output.new_line;
END;
result: 1 2（nt1表在nt2表中不存在的）

4.比较集合
--检测null
DECLARE
TYPE name_array_type IS varray(3) OF varchar2(10);
name_array name_array_type;
BEGIN
IF name_array IS NULL THEN
    dbms_output.put_line('name_array未初始化');
END IF;
END;
输出：name_array未初始化
注：也可以使用 is empty进行判断

--比较嵌套表是否相同
DECLARE
TYPE nt_table_type IS table OF varchar2(10);
nt1 nt_table_type;
nt2 nt_table_type;
BEGIN
nt1:=nt_table_type('scott');
nt2:=nt_table_type('smith');
IF nt1=nt2 THEN
    dbms_output.put_line('两个嵌套表完全相同');
else
    dbms_output.put_line('两个嵌套表值不同');
END IF;
END;
输出结果：两个嵌套表值不同
注：oracle10g开始才允许使用比较操作符=和！=检测两个嵌套表变量是否相同。使用这两个比较符只能比较嵌套表，不能比较varray和索引表。

--在嵌套表上使用集合操作符
从oracle10g开始，开发人员可以在嵌套表上使用ANSI集合操作符CARDINALITY,MEMBEROF,IS A SET。注意，这些操作符只适用于嵌套表，不适用于varray和索引表。

--使用cardinality
DECLARE
TYPE nt_table_type IS table OF number;
nt1 nt_table_type:=nt_table_type(1,2,3,1);
BEGIN
    dbms_output.put_line('元素个数:'||cardinality(nt1));
END;
元素个数:4

--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,4);
BEGIN
IF nt1 SUBMULTISET OF nt2 THEN
    dbms_output.put_line('nt1是nt2的子集');
END IF;
END;
nt1是nt2的子集

--MEMBER OF 检测特定数据是否是嵌套表的元素
DECLARE
TYPE nt_table_type IS table OF number;
nt1 nt_table_type:=nt_table_type(1,2,3);
v1 NUMBER:=&V1;
BEGIN
IF v1 MEMBER OF nt1 THEN
    dbms_output.put_line('v1是nt1的元素');
END IF;
END;
输入的值：1
v1是nt1的元素

--IS A SET 检测嵌套表是否包含重复的元素值
DECLARE
TYPE nt_table_type IS table OF number;
nt1 nt_table_type:=nt_table_type(1,2,3);
BEGIN
IF nt1 IS A SET THEN
    dbms_output.put_line('嵌套表nt1无重复值');
END IF;
END;
嵌套表nt1无重复值