8.pl/sql集合

  处理单行单列数据,可以使用标量变量,处理单行多列的数据，可以使用PL/SQL记录

  处理单列多行数据,可以使用PL/SQL集合

  PL/SQL集合类型类似于高级语言数组的一种复合数据类型

  包括:索引表(PL/SQL表),嵌套表(NESTED TABLE),变长数组(VARRAY)三种

  8.1 索引表:PL/SQL表 元素个数没有限制,并且下标可以是负值

  定义索引表:

  1. type type_name is table of element_type [not null] index by key_type; 
  2.  
  3. identifier type_name; 

  在索引表中使用BINARY_INTEGER和PLS_INTEGER

 

  1. set serveroutput on 
  2.  
  3. declare 
  4.  
  5.   type ename_table_type is table of emp.ename%type index by binary_integer; 
  6.  
  7.   ename_table ename_table_type; 
  8.  
  9. begin 
  10.  
  11.   select ename into ename_table(-1) from emp where empno=&no; 
  12.  
  13.   dbms_output.put_line('雇员名:'||ename_table(-1)); 
  14.  
  15. end;  

 

  在索引表中使用VARCHAR

  1. set serveroutput on 
  2.  
  3.  declare 
  4.  
  5.    type area_table_type is table of number index by varchar2(10); 
  6.  
  7.    area_table area_table_type; 
  8.  
  9.  begin 
  10.  
  11.    area_table('北京'):=1; 
  12.  
  13.    area_table('上海'):=2; 
  14.  
  15.    area_table('广州'):=3; 
  16.  
  17.    dbms_output.put_line('第一个元素:'||area_table.first); 
  18.  
  19.    dbms_output.put_line('最后一个元素:'||area_table.last); 
  20.  
  21.  end; 

  8.2 嵌套表:元素个数从1开始,并且元素个数没有限制

  定义嵌套表:

  1. type type_name is table of element_type; 
  2.  
  3. identifier type_name; 
  4.  
  5.    
  6.  
  7.       declare 
  8.  
  9.         type ename_table_type is table of emp.ename%type; 
  10.  
  11.         ename_table ename_table_typeename_table_type:=ename_table_type('A','A'); 

  在PL/SQL块中使用嵌套表:使用嵌套表变量时,必须首先使用构造方法初始化嵌套表变量,然后才能在块内引用嵌套表元素

  1. declare 
  2.  
  3.     type ename_table_type is table of emp.ename%type; 
  4.  
  5.     ename_table ename_table_type; 
  6.  
  7.   begin 
  8.  
  9. ename_table:=ename_table_type('mary','mary','mary'); 
  10.  
  11. dbms_output.put_line('雇员名:'|| ename_table(2)); 
  12.  
  13.     select ename into ename_table(2) from emp where empno=&no; 
  14.  
  15.     dbms_output.put_line('雇员名:'||ename_table(2)); 
  16.  
  17.   end; 

  在表列中使用嵌套表:

  在表列中使用嵌套表类型,必须首先使用CREATE TYPE命令建立嵌套表类型.

  当使用嵌套表类型作为表列的数据类型时,必须要为嵌套表列指定专门的存储表

  1. create type phone_type is table of varchar2(20); 
  2.  
  3.  /
  1. create table employee( 
  2.  
  3.     id number(4),name varchar2(10),sal number(6,2), 
  4.  
  5.     phone phone_type 
  6.  
  7.  )nested table phone store as phone_table; 

  8.3 在PL/SQL块中为嵌套表列插入数据

  当定义嵌套表类型时,ORACLE自动为该类型生成相应的构造方法.当为嵌套表列插入数据时,需要使用嵌套表的构造方法

 

  1. begin 
  2.  
  3.   insert into employee values(1,'scott',800,phone_type('0471-3456788','13804711111')); 
  4.  
  5. end;  

 

  在PL/SQL块中检索嵌套表列的数据

  当在PL/SQL块中检索嵌套表列的数据时,需要定义嵌套表类型的变量接受其数据.

  1. set serveroutput on 
  2.  
  3.  declare 
  4.  
  5.    phone_table phone_type; 
  6.  
  7.  begin 
  8.  
  9.    select ename into phone_table from emp where empno=&no; 
  10.  
  11.    for i in 1..phone_table.count loop 
  12.  
  13.    dbms_output.put_line('电话:'||phone_table(i)); 
  14.  
  15.    end loop; 
  16.  
  17.  end; 

  8.4 在pl/sql块中更新嵌套表列的数据

  更新嵌套表列的数据时,首先需要定义嵌套表变量,并使用构造方法初始化变量,然后才可在执行部分使用UPDATE语句更新数据

  1. declare 
  2.  
  3.     phone_table phone_typephone_type:=phone_type('0471-3456788','13804711111','0471-2233066','13056278568'); 
  4.  
  5.   begin 
  6.  
  7.     update employee set phone=phone_talbe where id=1; 
  8.  
  9.   end; 

  8.5变长数组(varray)

  VARRAY也是一种用于处理PL/SQL数组的数据类型, 它也可以做为表列的数据类型使用.

  元素下标以1开始,并且元素的最大个数是有限制的

  定义VARRAY的语法:  

  1. type type_name is varray(size_limite) of element_type [not mull]; 
  2.  
  3. identifier type_name; 

  当使用VARRAY元素时,必须要使用其构造方法初始化VARRAY元素.

  1. declare 
  2.  
  3.     type ename_table_type is varray(20) of emp.ename%type; 
  4.  
  5.     ename_talbe ename_table_typeename_table_type:=ename_table_type('A','A'); 

  8.6 在PL/SQL块中使用VARRAY

  必须首先使用其构造方法来初始化VARRAY变量,然后才能在PL/SQL块内引用VARRAY元素

 

  1. declare 
  2.  
  3.   type ename_table_type is varray(20) of emp.ename%type; 
  4.  
  5.   ename_table ename_table_typeename_table_type:=ename_table_type('mary'); 
  6.  
  7. begin 
  8.  
  9.   select ename into ename_table1) from emp where empno=&no; 
  10.  
  11.   dbms_output.put_line('雇员名:'||ename_table(1)); 
  12.  
  13. end;  

 

  在表列中使用varray

  要在表列中引用该数据类型,则必须使用CREATE TYPE命令建立VARRAY类型

  1. create type phone_type is varray(20) of varchar2(20); 
  2.  
  3.   / 

 

  1. create table employee( 
  2.  
  3.     id number(4),name varchar2(10), 
  4.  
  5.     sal number(6,2),phone phone_type); 

  在PL/SQL块中操纵VARRAY列的方法与操纵嵌套表列的方法完全相同.嵌套表列元素个数没有限制,而VARRAY列的元素个数是有限制的.

  PL/SQL记录表

  PL/SQL变量处理单行单列数据

  PL/SQL记录处理单行多列数据

  PL/SQL集合处理多行单列数据

  PL/SQL记录表处理多行多列数据

  8.7 PL/SQL记录表结合了PL/SQL记录和PL/SQL集合的优点

  1. declare 
  2.  
  3.     type emp_table_type is table of emp%rowtype index by binary_integer; 
  4.  
  5.     emp_table emp_table_type; 
  6.  
  7.   begin 
  8.  
  9.     select * into emp_table(1) from emp where empno=&no; 
  10.  
  11.     dbms_output.put_line('雇员姓名:'||emp_table(1).ename); 
  12.  
  13.     dbms_output.put_line('雇员姓名:'||emp_table(1).sal); 
  14.  
  15.   end; 

  8.8 多级集合

  多级集合是指嵌套了集合类型的集合类型

 

  在PL/SQL块中使用多级VARRAY:实现多维数组功能

  定义二维VARRAY(10,10):

 

  1.   declare 
  2.  
  3.     type a1_varray_type is varray(10) of int;--定义一维VARRAY 
  4.  
  5.     type na1_varray_type is varray(10) of a1_varray_type;--定义二维VARRAY集合 
  6.  
  7.     --初始化二维集合变量 
  8.  
  9.     nv1 nal_varray_typenal_varray_type:=nal_varray_type( 
  10.  
  11.         a1_varray_type(58,100,102), 
  12.  
  13.         a1_varray_type(55,6,73), 
  14.  
  15.         a1_varray_type(2,4); 
  16.  
  17. ） 
  18.  
  19.     begin 
  20.  
  21.       dbms_output.put_line('显示二维数组所有元素'); 
  22.  
  23.       for i in 1..nv1.count loop 
  24.  
  25.           for j in 1..nv1(i).count loop 
  26.  
  27.               dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j)); 
  28.  
  29.           end loop; 
  30.  
  31.       end loop; 
  32.  
  33.    end;  

 

  在PL/SQL块中使用多级嵌套表

  如果多维数组的元素个数没有限制,那么可以在嵌套表类型中嵌套另一个嵌套表类型

  8.9 二维嵌套表

  1. declare 
  2.  
  3.     type a1_table_type is table of int;--定义一维嵌套表 
  4.  
  5.     type nal_table_type is table of a1_table_type;--定义二维嵌套表集合 
  6.  
  7.     --初始化二维集合变量 
  8.  
  9.     nvl nal_table_typenal_table_type:=nal_table_type( 
  10.  
  11.         a1_table_type(2,4), 
  12.  
  13.         a1_table_type(5,73)); 
  14.  
  15.   begin 
  16.  
  17.     dbms_output.put_line('显示二维数组所有元素'); 
  18.  
  19.     for i in 1..nvl.count loop 
  20.  
  21.        for j in 1..nvl(i).count loop 
  22.  
  23.            dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j)); 
  24.  
  25.        end loop; 
  26.  
  27.     end loop; 
  28.  
  29.   end; 

  在PL/SQL块中使用多级索引表

  二维索引表:

  1. declare 
  2.  
  3.    type a1_table_type is table of int index by binary_integer; 
  4.  
  5.    type nal_table_type is table of al_table_type index by binary_integer; 
  6.  
  7.    nvl nal_table_type; 
  8.  
  9.  begin 
  10.  
  11.    nvl(1)(1):=10; 
  12.  
  13.    nvl(1)(2):=5; 
  14.  
  15.    nvl(2)(1):=100; 
  16.  
  17.    nvl(2)(2):=50; 
  18.  
  19.    dbms_output.put_line('显示二维数组所有元素'); 
  20.  
  21.    for i in 1..nvl.count loop 
  22.  
  23.       for j in 1..nvl(i).count loop 
  24.  
  25.          dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j)); 
  26.  
  27.       end loop; 
  28.  
  29.    end loop; 
  30.  
  31.  end; 

  8.10集合方法:ORACLE提供的用于操纵集合变量的内置函数或过程,其中EXISTS,COUNT,LIMIT,FIRST,NEXT,FRIOR和NEXT是函数

  而EXTEND,TRIM和DELETE则是过程

  集合方法只能在PL/SQL语句中使用,不能在SQL语句中使用.

  集合方法EXTEND和TRIM只适用于嵌套表和VARRAY,而不适合于索引表

  1.EXISTS:用于确定集合元素是否存在

  1. declare 
  2.  
  3.     type ename_table_type is table of emp.ename%type; 
  4.  
  5.     ename_table ename_table_typeename_table_type:=ename_table_type('A','A'); 
  6.  
  7.   begin 
  8.  
  9.     if ename_table.exists(1) then 
  10.  
  11.        ename_table(1):='SCOTT'; 
  12.  
  13.        dbms_output.put_line(ename_table(1) ); 
  14.  
  15.     else 
  16.  
  17.        dbms_output.put_line('必须初始化集合元素'); 
  18.  
  19.     end if; 
  20.  
  21.   end; 

  2.COUNT:用于返回当前集合变量中的元素总个数.

  1. declare 
  2.  
  3.    type ename_table_type is table of emp.ename%type index by binary_integer; 
  4.  
  5.    ename_table ename_table_type; 
  6.  
  7.  begin 
  8.  
  9.    ename_table(-5):='scott'; 
  10.  
  11.    ename_table(1):='smith'; 
  12.  
  13.    ename_table(5):='mary'; 
  14.  
  15.    ename_table(10):='blake'; 
  16.  
  17.    dbms_output.put_line('集合元素总个数:'||ename_table.count); 
  18.  
  19.  end; 

  3.LIMIT:用于返回集合元素的最大个数.因为嵌套表和索引表的余数个数没有限制,返回NULL

  对于VARRAY来说，该方法返回VARRAY所允许的最大元素个数

  1. declare 
  2.  
  3.     type ename_table_type is varray(20) of emp.ename%type; 
  4.  
  5.     ename_table ename_table_typeename_table_type:=ename_table_type('mary'); 
  6.  
  7.   begin 
  8.  
  9.     dbms_output.put_line('集合元素的最大个数:'||ename_table.limit); 
  10.  
  11.   end; 

 

  4.FIRST和LAST:FIRST用于返回集合变量第一个元素的下标,而LAST方法则用于返回集合变量最后一个元素的下

  1. declare 
  2.  
  3.     type ename_table_type is table of emp.ename%type index by binary_integer; 
  4.  
  5.     ename_table ename_table_type; 
  6.  
  7.   begin 
  8.  
  9.     ename_table(-5):='scott'; 
  10.  
  11.     ename_table(1):='smith'; 
  12.  
  13.     ename_table(5):='mary'; 
  14.  
  15.     ename_table(10):='blake'; 
  16.  
  17. dbms_output.put_line('第一个元素:'||ename_table.first); 
  18.  
  19. dbms_output.put_line('第一个元素:'||ename_table(-5)); 
  20.  
  21.     dbms_output.put_line('最后一个元素:'||ename_table.last); 
  22.  
  23.   end; 

  5.FRIOR和NEXT:PRIOR返回当前集合元素的前一个元素的下标,而NEXT方法则用于返回当前集合元素的后一个元素的下标

  1. declare 
  2.  
  3.    type ename_table_type is table of emp.ename%type index by binary_integer; 
  4.  
  5.    ename_table ename_table_type; 
  6.  
  7.  begin 
  8.  
  9.    ename_table(-5):='scott'; 
  10.  
  11.    ename_table(1):='smith'; 
  12.  
  13.    ename_table(5):='mary'; 
  14.  
  15.    ename_table(10):='blake'; 
  16.  
  17.    dbms_output.put_line('元素5的前一个元素:'||ename_table.prior(5)); 
  18.  
  19.    dbms_output.put_line('元素5的后一个元素:'||ename_table.next(5)); 
  20.  
  21.  end; 

  6.EXTEND:用于扩展集合变量的尺寸,并为它们增加元素.只适用于嵌套表和VARRAY.

         三种调用格式:EXTEND,EXTEND(N),EXTEND(N,I):添加N个元素,值与第I个元素相同

  1. declare 
  2.  
  3.    type ename_table_type is varray(20) of varchar2(10); 
  4.  
  5.    ename_table ename_table_type; 
  6.  
  7.  begin 
  8.  
  9.    ename_table:=ename_table_type('mary'); 
  10.  
  11.    ename_table.extend(5,1); 
  12.  
  13.    dbms_output.put_line('元素个数:'||ename_table.count); 
  14.  
  15.  end; 

  7.TRIM:用于从集合尾部删除元素,有TRIM和TRIM(N)两种调用格式.

    只适用于嵌套表和VARRAY

  1. declare 
  2.  
  3.     type ename_table_type is table of varchar2(10); 
  4.  
  5.     ename_table ename_table_type; 
  6.  
  7.   begin 
  8.  
  9.     ename_table:=ename_table_type('a','a','a','a','a'); 
  10.  
  11.     ename_table.trim(5); 
  12.  
  13.     dbms_output.put_line('元素个数:'||ename_table.count); 
  14.  
  15.   end; 

  8.DELETE:删除结合元素,但该方法只适用于嵌套表和索引表,不适用于VARRAY.

         有DELETE,DELETE(N),DELETE(M,N)三种调用方式.

         DETELE(M,N)删除集合变量从M到N之间的所有元素

 

  1.   declare 
  2.  
  3.     type ename_table_type is table of emp.ename%type index by binary_integer; 
  4.  
  5.     ename_table ename_table_type; 
  6.  
  7.   begin 
  8.  
  9.     ename_table(-5):='scott'; 
  10.  
  11.     ename_table(1):='smith'; 
  12.  
  13.     ename_table(5):='mary'; 
  14.  
  15.     ename_table(10):='blake'; 
  16.  
  17. ename_table.delete(1); 
  18.  
  19. if ename_table. exists(5) then  dbms_output.put_line('success'); 
  20.  
  21. else 
  22.  
  23. dbms_output.put_line('元素总个数:'||ename_table.count); 
  24.  
  25. end if; 
  26.  
  27.   end; 

 

 

  8.11集合赋值

  使用嵌套表和VARRAY时,通过执行INSERT,UPDATE,FETCH,SELECT赋值语句,可以将一个集合的数据赋值给另一个集合.

  当给嵌套表赋值时,还可以使用SET,MULTISET UNION,MULTISET INTERSECT,MULTISET EXCEPT等集合操作符

  SET:用于取消嵌套表中的重复值.

  MULTISET UNION:取得两个嵌套表的并集(DISTINCT)

  MULTISET INTERSECT:用于取得两个嵌套表的交集.

  NULTISET EXCEPT:用于取得两个嵌套表的差集

  1.将一个集合的数据赋值个另一个集合

    源集合和目标集合的数据类型必须完全一致.

  1. declare 
  2.  
  3.    type name_array_type is varray(4) of varchar2(10); 
  4.  
  5.    name_array1 name_array_type; 
  6.  
  7.    name_array2 name_array_type; 
  8.  
  9.  begin 
  10.  
  11.    name_array1:=name_array_type('scott','smith'); 
  12.  
  13.    name_array2:=name_array_type('a','a','a','a'); 
  14.  
  15.    dbms_output.put_line('name_array2的原数据:'); 
  16.  
  17.    for i in 1..name_array2.count loop 
  18.  
  19.       dbms_output.put_line(' '||name_array2(i)); 
  20.  
  21.    end loop; 
  22.  
  23.    dbms_output.new_line; 
  24.  
  25.    name_array2:=name_array1; 
  26.  
  27.    dbms_output.put('name_array2的新数据:'); 
  28.  
  29.    for i in 1..name_array2.count loop 
  30.  
  31.       dbms_output.put(' '||name_array2(i)); 
  32.  
  33.    end loop; 
  34.  
  35.    dbms_output.new_line; 
  36.  
  37.  end; 

 

  2.给集合赋NULL值:清空集合变量的所有数据(集合方法DETELE,TRIM也可以)

  1. declare 
  2.  
  3.    type name_varray_type is varray(4) of varchar2(10); 
  4.  
  5.    name_array name_varray_type; 
  6.  
  7.    name_empty name_varray_type; 
  8.  
  9.  begin 
  10.  
  11.    name_array:=name_varray_type('scott','smith'); 
  12.  
  13.    dbms_output.put_line('name_array的原有元素个数:'||name_array.count); 
  14.  
  15.    name_array:=name_empty; 
  16.  
  17.    if name_array is null then      
  18.  
  19.       dbms_output.put_line('name_array的现有元素个数:0'); 
  20.  
  21.    end if; 
  22.  
  23.  end; 

 

  3.使用集合操作符给嵌套表赋值

    1.使用SET操作符:用于取消特定嵌套表中的重复值.

  1. declare 
  2.  
  3.    type nt_table_type is table of number; 
  4.  
  5.    nt_table nt_table_typent_table_type:=nt_table_type(2,4,3,1,2); 
  6.  
  7.    result nt_table_type; 
  8.  
  9.  begin 
  10.  
  11.    result:=set(nt_table); 
  12.  
  13.    dbms_output.put('result:'); 
  14.  
  15.    for i in 1..result.count loop 
  16.  
  17.      dbms_output.put(' '||result(i)); 
  18.  
  19.    end loop; 
  20.  
  21.    dbms_output.new_line; 
  22.  
  23.  end; 

 

    2.使用MULTISET UNION操作符:取得两个嵌套表的并集.结果集中会包含重复值

 

  1. declare 
  2.  
  3.   type nt_table_type is table of number; 
  4.  
  5.   nt1 nt_table_typent_table_type:=nt_table_type(1,2,3); 
  6.  
  7.   nt2 nt_table_typent_table_type:=nt_table_type(3,4,5); 
  8.  
  9.   result nt_table_type; 
  10.  
  11. begin 
  12.  
  13.   result:=nt1 multiset union nt2; 
  14.  
  15.   dbms_output.put('result:'); 
  16.  
  17.   for i in 1..result.count loop 
  18.  
  19.     dbms_output.put(' '||result(i)); 
  20.  
  21.   end loop; 
  22.  
  23.   dbms_output.new_line; 
  24.  
  25. end; 

 

  3.使用MULTISET UNION DISTINCT操作符:用于取得两个嵌套表的并集,并取消重复结果.

  1. declare 
  2.  
  3.     type nt_table_type is table of number; 
  4.  
  5.     nt1 nt_table_typent_table_type:=nt_table_type(1,2,3); 
  6.  
  7.     nt2 nt_table_typent_table_type:=nt_table_type(3,4,5); 
  8.  
  9.     result nt_table_type; 
  10.  
  11.   begin 
  12.  
  13.     result:=nt1 multiset union distinct nt2; 
  14.  
  15.     dbms_output.put('result:'); 
  16.  
  17.     for i in 1..result.count loop 
  18.  
  19.       dbms_output.put(' '||result(i)); 
  20.  
  21.     end loop; 
  22.  
  23.     dbms_output.new_line; 
  24.  
  25.   end;

  4.使用MULTISET INTERSECT操作符:用于取得两个嵌套表的交集

  1. declare 
  2.  
  3.     type nt_table_type is table of number; 
  4.  
  5.     nt1 nt_table_typent_table_type:=nt_table_type(1,2,3); 
  6.  
  7.     nt2 nt_table_typent_table_type:=nt_table_type(3,4,5); 
  8.  
  9.     result nt_table_type; 
  10.  
  11.   begin 
  12.  
  13.     result:=nt1 multiset intersect nt2; 
  14.  
  15.     dbms_output.put('result:'); 
  16.  
  17.     for i in 1..result.count loop 
  18.  
  19.       dbms_output.put(' '||result(i)); 
  20.  
  21.     end loop; 
  22.  
  23.     dbms_output.new_line; 
  24.  
  25.   end;

  5.使用MULTISET EXCEPT操作符:取得两个嵌套表的差集.在NT1中存在,但在NT2中不存在

  1. declare 
  2.  
  3.    type nt_table_type is table of number; 
  4.  
  5.    nt1 nt_table_typent_table_type:=nt_table_type(1,2,3); 
  6.  
  7.    nt2 nt_table_typent_table_type:=nt_table_type(3,4,5); 
  8.  
  9.    result nt_table_type; 
  10.  
  11.  begin 
  12.  
  13.    result:=nt1 multiset except nt2; 
  14.  
  15.    dbms_output.put('result:'); 
  16.  
  17.    for i in 1..result.count loop 
  18.  
  19.       dbms_output.put(' '||result(i)); 
  20.  
  21.    end loop; 
  22.  
  23.    dbms_output.new_line; 
  24.  
  25.  end;