学习PL/SQL之四：集合

使用dbms_output输出数据或者消息时，必须要将sqlplus的环境变量设置为ON。

集合类型包括索引表，嵌套表和变长数组。

1、索引表，下标可为负值，长度没有限制。
   TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type;
   type_name:用于指定用户自定义数据类型名称
   element_type:用于指定索引标的数据类型
   NOT NULL:表示不允许引用NULL元素
   key_type:用于指定索引表元素下标的数据类型(BINARY_INTEGER,PLS_INTEGER OR VARCHAR2)
 exp:
    set serveroutput on
    DECLARE
       TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
       ename_tabme ename_table_type;
   BEGIN
       SELECT ename INTO ename_table(-1) FROM emp WHERE empno=&no;
       dbms_output.put_line(ename_table(-1));
   END;
  ----------------------------------------
   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、嵌套表
   下标从1开始，没有极限值，没有顺序，值的存储可以是稀疏的。
   TYPE table_name IS TABLE OF element_type ;
   在使用时，必须先初始化。
   emp_name table_name:=table_name('a','b');
3、变长数组：
   类似数组。元素下标从1开始，并且长度是有限制的。
   TYPE varray_type_name IS VARRAY(limit);-- OF element_typelimit is not null
   在使用VARRAY 元素时，必须使用其构造方法初始化VARRAY元素。
   ……
     varray_type varray_type_name;
   begin
     varray_type:=varray_type_name('a','a','a');
   ……

PL/SQL变量用于处理单行单列数据，PL/SQL记录用于处理单行多列，PL/SQL集合用于处理多行单列。

为了在PL/SQL中处理多行多列数据，可以使用PL/SQL记录表。如：

DECALARE

      TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;

      emp_table emp_table_type;

BEGIN

      SELECT * INTO emp_table(1)  FROM emp WHERE eno=&no;

      dbms_output_put(emp_table(1).ename);

END;

集合方法：
   EXISTS:确定集合元素是否存在  IF varray_type.EXISTS(1) THEN...
   COUNT:返回当点集合元素的总个数  varray_type.COUNT
   LIMIT:返回集合元素的最大个数 索引表和嵌套表没有最大个数，返回NULL
   FIRST&LAST:返回集合第一个和最后一个元素的下标。非元素。
   PRIOR&NEXT 返回当点集合元素的前一个和后一个的下标。非元素。ename_table.prior(5)
   EXTEND:用于扩展集合变量的尺寸，并为它们增加元素。只适合用于嵌套表和VARRAY.有三种调用格式：EXTEND,EXTEND(n),EXTEND(n,i),其中EXTEND为集合变量添加一个NULL元素，EXTEND(n)为集合变量添加n个null元素。EXTEND(n,i)则为集合变量添加n个元素值和第i个元素相同的元素。
     -- Created on 2008-4-28 by USER
     declare
        type enum_varray_type is varray(10) of varchar2(20);
        enum_varray enum_varray_type;
     begin
        enum_varray:=enum_varray_type('a');
        enum_varray.extend(3,1);
        dbms_output.put_line(enum_varray.count);
     end;
   TRIM:从集合尾部删除元素，trim删除一个，trim(n)删除N个
     enum_varray.trim(2);
     dbms_output.put_line(enum_varray.count);
   DELETE：删除集合元素，只能用与索引表和嵌套表。DELETE 删除所有记录，DELETE(n)删除几个第N个元素，DELETE(m,n)删除集合从m到n个记录。
        declare
           type enum_type_name is table of varchar2(20) index by binary_integer;
           enum_name enum_type_name;
        begin
            enum_name(0):='a';
            enum_name(9):='v';
            enum_name(4):='s';
            enum_name(-1):='g';
            enum_name(8):='f';
            dbms_output.put_line(enum_name.count);
            enum_name.delete(-1,4);
            dbms_output.put_line(enum_name.count);
         end;

集合赋值：
1、将一个集合的数据赋值给另一个集合
   当使用:=或SQL语句将源集合中的数据赋值给目标集合时，会自动清楚目标集合原有的数据，并将源集合中的数据赋值给该目标集合。
   注意：当进行集合赋值时，源集合和目标集合的数据类型必须完全一致。如果集合元素数据类型一致，但集合类型不一致，那也不能进行赋值。
   如下面就是错误的：
   declare
      type name_varray1_type is varray(4) of varchar2(10);
      type name_varray2_type is varray(4) of varchar2(10);
      name_array1 name_varray1_type ;
      name_array2 name_varray2_type ;
   begin
      name_array1 :=name_varray1_type('ss','d');
      name_array2:=name_array1;--这里是错误的
   end;

给嵌套表赋值时可用 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(1,2,3,4,2);
        result nt_table_type;
     begin
        result:=SET(nt_table );
     end;
    result的结果就变成1，2，3，4
2、使用 MULTISET UNION 操作符
   取得两个嵌套表的并集。结果集中包含重复值。
   declare
      type enum_type_name is table of number;
      enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
      enum_name2 enum_type_name:=enum_type_name(4,5,6,7,8);
      enum_name3 enum_type_name;
      i number;
      results varchar2(100);
   begin
      enum_name3:=enum_name1 multiset union enum_name2;
      for i in 1..enum_name3.count loop
         results:=results||','||enum_name3(i);
      end loop;
      dbms_output.put_line(substr(results,2,length(results)));
   end; 
   结果：1,2,3,4,1,4,5,6,7,8
3、使用MULTISET UNION DISTINCT操作符
   和使用MULTISET UNION唯一不同是去掉了重复值
4、使用 MULTISET INTERSECT 操作符
   取得两个嵌套表的交集
5、使用MULTISET EXCEPT 操作符
   用于取得两个嵌套表的差集。下面的例子是取得  enum_name1中存在但是enum_name2中不存在的元素：
   declare
     type enum_type_name is table of number;
     enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
     enum_name2 enum_type_name:=enum_type_name(4,5,6,7,8);
     enum_name3 enum_type_name;
     i number;
     results varchar2(100);
   begin
     enum_name3:=enum_name1 multiset except distinct enum_name2;
     for i in 1..enum_name3.count loop
       results:=results||','||enum_name3(i);
     end loop;
      dbms_output.put_line(substr(results,2,length(results)));
   end; 
   结果：1，2，3

比较集合：
   1、检测集合是否为NULL (嵌套表和VARRAY)
      IS NULL 
        declare
           type enum_type_name is table of number;
           enum_name3 enum_type_name;
           i number;
           results varchar2(100);
        begin
           if enum_name3 is null then
             dbms_output.put_line('必须初始化');
           else
             null;
           end if; 
        end;  
      IS EMPTY --只适用于嵌套表
   2、比较嵌套表是否相同 在oracle10后用！=或者=
      declare
     type enum_type_name is table of number;
     enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
     enum_name2 enum_type_name:=enum_type_name(4,5,6,7,8);
   begin
     if enum_name1=enum_name2 then
       dbms_output.put_line('嵌套表相通');
     else
       dbms_output.put_line('嵌套表不同');
     end if; 
   end;
   结果：嵌套表不同
  3、在嵌套表中使用集合操作符
    （1）、使用函数CARDINALITY
          此函数用于返回嵌套表变量的元素个数。和count应该差不多。不同的就是这个函数只能用在嵌套表中。
    （2）、使用操作符SUBMULTISET OF
          用于确定有一个嵌套表是否为另一个嵌套表的子集。
           declare
        type enum_type_name is table of number;
        enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
        enum_name2 enum_type_name:=enum_type_name(1,2);
      begin
        if enum_name2 submultiset of enum_name1 then
          dbms_output.put_line('enum_name2 是 enum_name1 的子集');
        else
          dbms_output.put_line('enum_name2 不是 enum_name1 的子集');
        end if; 
      end; 
          结果：enum_name2 是 enum_name1 的子集
      (3)、使用 MEMBER OF 操作符
            检测特定数据是否为嵌套表元素。
            declare
        type enum_type_name is table of number;
        enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
        i number:=&vl;
      begin
        if i member of enum_name1 then
          dbms_output.put_line(i||' 是 enum_name1 的元素');
        else
          dbms_output.put_line(i||' 不是 enum_name1 的元素');
        end if; 
      end;
      输入：1
      结果：1 是 enum_name1 的元素
   （4）、IS A SET 操作符
          用于检测嵌套表是不是没有重复元素
          declare
         type enum_type_name is table of number;
         enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
       begin
         if  enum_name1 is a set then
           dbms_output.put_line('enum_name1 没有重复元素');
         else
           dbms_output.put_line('enum_name1 有重复元素');
         end if; 
       end; 
             结果：enum_name1 有重复元素
       (5) for all

           FOR ALL  i  INDICES OF 1..id_table.COUNT

                     insert into demo value(id_table(i));

        ............................

              INDICES OF 可以跳过null元素

    DECLARE

             TYPE  id_table_type IS TABLE OF number(6);

              id_table id_table_type;

    BEGIN

             id_table:=id_table_type('1',null,'3',null,'5');

             FOR ALL  i  INDICES OF id_table

                      delete from demo where id=id_table(i);

END;            

 

BULK COLLECT:不要忘记我          

适用于SELECT INTO 和 FECTH INTO 和DML 的返回子句。

 

在9i之前，当编写SELECT INTO 语句时，必须返回一行语句，并且最多只能返回一行语句。

从9i开始适用 BULK COLLECT 可以将SELECT的多行结果检索到集合当中。

DECLARE

            TYPE emp_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;

            emp_table emp_table_type;

BEGIN

            SELECT ename BULK COLLECT INTO emp_table  FROM emp WHERE deptno:=&depno;

            FOR i IN 1..emp_table.COUNT LOOP

                       dbms_out_put.put_line('雇员名:'||emp_table(i));

           END LOOP;

END;

在DML返回语句中使用：

DECLARE

            TYPE emp_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;

            emp_table emp_table_type;

BEGIN

            DELETE FROM emp WHERE deptno:=&depno RETURN  ename BULK COLLECT INTO emp_table ;

            FOR i IN 1..emp_table.COUNT LOOP

                       dbms_out_put.put_line('雇员名:'||emp_table(i));

           END LOOP;

END;