PL/SQL 集合类型

PL/SQL支持集合的概念，在PL/SQL中集合就是一维数组，其可存储一个或多个元素，或者直接为空，通过索引可以访问这些元素，集合里的元素必须是同样的类型。集合类型属于复合类型，除了作为数据结构本身的需要，集合类型的使用有益于提高程序的效率。

#集合类型的类别
PL/SQL共有三类集合类型：关联数组， 嵌套表， 可变长数组。

##关联数组
关联数组是PL/SQL最早提供的集合类型，索引可以数值也可以是字符串，只能用于PL/SQL中，不能用于SQL及表的声明中，关联数字可以是厚密的，也可以是稀疏的。

声明关联数组类型的方式为：

--以字符串为索引
TYPE associative_array_type1 IS TABLE OF NUMBER 
   INDEX BY VARCHAR2(100);
--以数值为索引
TYPE associative_array_type2 IS TABLE OF NUMBER 
   INDEX BY PLS_INTEGER;

除了自己声明集合类型外，标准包里面也提供了一些预定义的集合类型，比如DBMS_UTILITY.maxname_array，其是一个字符串类型的关联数组，字符串最大长为32767。

##嵌套表
嵌套表的索引只可以是数值，可以用于PL/SQL中，SQL及表的声明中，嵌套表可以是厚密的，也可以是稀疏的，但一般都是稠密的。

声明嵌套表类型的方式为：

TYPE nested_table_type IS TABLE OF NUMBER;

嵌套表支持用MULTISET操作符执行集合操作以及相等比较。

比如求collection1与collection2的差集：

collection3 := collection1 MULTISET EXCEPT collection2;

##可变长数组
同嵌套表一样，可变长数组的索引只可以是数值，可以用于PL/SQL中，SQL及表的声明中，但可变长数组只能是稠密的，在定义可变长数组的类型时，需要指定其允许存储的最大元素个数。

声明可变长数组类型的方式为：

TYPE varray_type IS VARRAY(10)OF NUMBER ;

#集合的初始化
一旦有了集合类型的声明，就可以声明集合变量，方式和其他类型变量一致。但是嵌套表和可变长数组类型的集合变量，使用前需要先初始化。

DECLARE
   TYPE nested_table_type IS TABLE OF NUMBER;
   nested_table1 nested_table_type;
   nested_table2 nested_table_type;
BEGIN
   nested_table1 := nested_table_type();
   nested_table2 := nested_table_type(1, 2, 3);
END;

初始化通过构造函数完成，构造函数与类型同名，可以不带参数的执行构造一个空的集合，也可以带若干个对应元素类型的参数，这些参数将会直接被写入到集合中。

嵌套表和可变长数组类型的集合如果使用前未初始化，将会有异常抛出，但是关联数组没这个问题，可以声明后直接使用。

#基本的集合操作

##赋值
集合支持通过索引在单个元素上赋值，也支持整个集合之间赋值。

DECLARE
   TYPE nested_table_type IS TABLE OF NUMBER;
   TYPE associative_array_type IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER; 
   nested_table nested_table_type;
   associative_array1 associative_array_type;
   associative_array2 associative_array_type;
BEGIN
   nested_table := nested_table_type();
   nested_table.extend();
   nested_table(1) = 100;
   associative_array1(2) := 100;
   associative_array2 := associative_array1;
END;

关联数组类型的集合索引不需要从1开始，也不需要执行EXTEND扩充集合。嵌套表和可变长数组需要在对单个元素赋值前调用EXTEND。

同类型的集合支持互相赋值。

##删除元素
删除所有元素，支持所有集合类型

collection.delete;

通过索引删除单个元素，仅支持关联数组和嵌套表

collection.delete(index);

通过索引删除范围内的元素，仅支持关联数组和嵌套表

collection.delete(begin_index, end_index);

删除集合末端一个或多个元素，仅支持嵌套表和可变长数组

collection.trim(number_of_element);

##迭代
根据集合是稠密还是稀疏的特点，集合适用于不同的迭代方法。

FOR适合迭代稠密的集合

FOR index IN collection.FIRST .. collection.LAST
LOOP
   DBMS_OUTPUT.put_line (collection (indx));
END LOOP;

如果collection是稀疏的，则由于访问了不存在的索引，抛出异常NO_DATA_FOUND。

WHILE适合迭代稀疏的集合

index := collection.FIRST;
WHILE (index IS NOT NULL)
LOOP
   DBMS_OUTPUT.put_line (collection (index));
   index := collection.NEXT (index);
END LOOP;

如果需要倒序迭代集合，只需要把FIRST和NEXT改为LAST和PRIOR即可。

#集合与性能

合理的使用集合，可以提升程序的数据提取，修改性能。PL/SQL程序在执行过程中，PL/SQL执行引擎和SQL执行引擎之间的过于频繁的上下文切换会显著的降低程序的性能，通过集合的使用，可以降低上下文的切换的次数。

##BUlK COLLECT
BUlK COLLECT可以让SELECT实现在一次数据提取动作中提取多条数据，增加数据提取的速度。

BUlK COLLECT可以使用于所有类型的集合，可以提取数据到多个集合，此时每个select的列对应于一个集合，也可以提取到单个记录集合，生成的集合总是稠密的，索引也总是从1开始，如果没有数据，则集合为空。

提取所有符合条件的数据到集合：

 SELECT *
     BULK COLLECT INTO collection_name
     FROM table_name;

为了防止太多的数据被一次性提取到内存中，可以结合通过光标的方式结合limit使用

 OPEN cursor_name;
 LOOP
     FETCH cursor_name
     BULK COLLECT INTO collection_name
     LIMIT number_of_limit;
     EXIT WHEN collection_name.COUNT = 0;
 END LOOP

#FORALL
FORALL可以通过集合快速的修改多条数据，数据的批量修改仅需要一次上下文的切换。凡是在循环中使用DML语句的情况，基本都可以通过FORALL重写，FORALL并不是循环，而是语句，每个FORALL语句中只能有一条DML语句。

与FOR循环类似，FORALL IN自动声明迭代器变量index，要求集合collection_name本身是稠密的。

FORALL index IN 1 .. collection_name.COUNT
        UPDATE table_name
           SET column_name = value
         WHERE condition_column_name = collection_name(index);

如果集合本身是稀疏的，则需要使用INDICES OF：

FORALL index IN INDICES OF collection_name
   UPDATE table_name
           SET column_name = value
         WHERE condition_column_name = collection_name(index);

类似的语法结构还有VALUES OF，也可以实现对稀疏集合的访问。

##DML错误的处理
如果FORALL执行中，如果DML在执行中途发生了错误，则相应错误的异常会被抛出，之后的DML将不会再被执行，比如修改1000条数据，在修改第500条时发生了错误，剩下的500条修改将不会再被执行。

如果想要在遇到错误时继续，则可使用SAVE EXCEPTIONS子句，此时错误信息将会被存储到SQL%BULK_EXCEPTIONS中，之后的DML将继续得到执行，直到所有的DML都被执行后，ORA-24381异常被抛出。

可以通过在异常处理区域捕获异常的方式，获取错误信息

BEGIN
      FORALL index IN 1 .. collection_name.COUNT SAVE EXCEPTIONS
        UPDATE table_name
           SET column_name = value
         WHERE condition_column_name = collection_name(index);
EXCEPTION
   WHEN OTHERS
   THEN
      IF SQLCODE = -24381
      THEN
         FOR index IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            DBMS_OUTPUT.put_line (
                  SQL%BULK_EXCEPTIONS (index ).ERROR_INDEX
               || ‘: ‘
               || SQL%BULK_EXCEPTIONS (index ).ERROR_CODE);
         END LOOP;
      ELSE
         RAISE;
      END IF;
END;