Oracle PLSQL的集合类型

一、Types of Collections

    1、Associative arrays 数组
      它是同种类型的一维、无边界的稀疏集合，只能用于 PL/SQL。
      DECLARE TYPE t_name IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER; --创建 Collection
              i_name t_name;      --创建 instance
              l_row PLS_INTEGER;
      BEGIN
              i_name(202020):='aaa';
              i_name(-125):='bbb';
              i_name(88):='ccc';              --赋值，row number 可以为任何整数，且可以跳跃（稀疏的），也不用按照顺序赋值，其内部最终按照 row number 排序
              l_row := i_name.FIRST;          --返回第一个 row number
              l_row := i_name.NEXT (l_row);   --返回 l_row 之后的一个 row number，会自动跳过为空的行
              DBMS_OUTPUT.put_line (i_name (l_row)); --返回指定行的值
      END;
   
    2、Nested tables 嵌套表
      也是同种类型的一维无边界集合。起初它是密集的，经过删除操作会变成稀疏。它可以在 PL/SQL 和 Database（某一列为一个嵌套表）中被定义。它是 multisets 的，也就是说 nested table 中的元素没有内在的顺序
      DECLARE TYPE t_name IS TABLE OF varchar2(10); --创建 Collection，和 Associative arrays 相差一个 INDEX BY
              i_n1 t_name := t_name(); --创建 instance，需要使用构造函数
              i_n2 t_name := t_name();
              i_n3 t_name := t_name();
      BEGIN
              i_n1.EXTEND(2); --赋值前要分配空间
              i_n1(1):='aaa'; --赋值，row number 最小为 1，最大为 EXTEND 分配的空间数，可以跳过某个行不分配则为 null
              i_n1(2):='bbb';
              i_n2.EXTEND;     --分配一个空间
              i_n2(1):='bbb';
              i_n3 := i_n1 MULTISET EXCEPT i_n2; --10g 开始提供的功能，将 i_n1 中除去 i_n2 的值，赋值给 i_n3
              FOR l_row IN i_n1.FIRST .. i_n1.LAST --从行首到行尾，依次输出
              LOOP
                 DBMS_OUTPUT.put_line (i_n1(l_row));
              END LOOP;
      END;

    3、VARRAYs 变量数组
      VARRAYs (variable-sized arrays)，也是同种类型的一维集合。但它是有界且不稀疏的。在定义 VARRAYs 时要指定它的最大范围。VARRAYs 也可以在 PL/SQL 和 Database 中被定义，但它其中的元素是有顺序的。
      DECLARE TYPE t_name IS VARRAY (2) OF VARCHAR2 (10); --创建 Collection，需要指定最大范围
              i_n1 t_name := t_name(); --创建 instance，需要使用构造函数
      BEGIN
              i_n1.EXTEND(2); --赋值前要分配空间，不能超过最大范围
              i_n1(1):='aaa'; --赋值，row number 最小为 1，最大为 EXTEND 分配的空间数
              i_n1(2):='bbb';
      END;
    
二、Where You Can Use Collections

    1、作为 record 的成分
    2、作为程序的参数
      由于 Oracle 没有预定的集合类型，当作为参数使用前，需要定义集合类型：
        1.用 CREATE TYPE 定义 schema-level 类型
        2.在 package specification 中声明
        3.在外层作用域定义
    3、作为函数的返回值
      1.返回集合直接赋值给 collection variable，这时这个 collection variable 不需要初始化
      2.将返回集合中的一个元素赋值给一个类型兼容的变量
          variable_of_element_type := function() (subscript);
          如果函数返回空值，在赋值时会产生 COLLECTION_IS_NULL 异常，应该捕获并适当处理该异常
    4、作为数据库表的列
      当使用 nested table datatype 作为列时，必须指定 store table 的名字：
        CREATE TABLE personality_inventory (
           person_id NUMBER,
           favorite_colors Color_tab_t,
           date_tested DATE,
           test_results BLOB)
        NESTED TABLE favorite_colors STORE AS favorite_colors_st;
      不能对 store table 进行维护或者试图直接查询、存储数据，只能通过 outer table 来获取它的属性。也不能指定它的 storage parameters，它继承与 outermost table。
      nested tables 和 VARRAYs 的区别在于，VARRAY 和其它数据一起存于表内，而 nested table 存于表外，VARRAY 适合 "small" arrays，nested table 适合 "large" arrays。
    5、作为 object type 的属性
  
三、Choosing a Collection Type

    1.如果要用稀疏 array，那么只能使用 associative array，虽然可以先分配 nested table 在删除其中的项目，但效率很低
    2.如果在 PL/SQL 中要用负数的下标，只能用 associative array
    3.如果使用 10g，希望使用 set 层面的操作，那么选择 nested tables + MULTISET EXCEPT 的方法
    4.如果要限制存储的行数，使用 VARRAYs
    5.如果要在 column 中存储大数据量集合，那么使用 nested table，Oracle可以使用单独的表来存储它
    6.如果你想将存在 collection column 中的数据保持原有顺序，并且数据量很小，可以使用 VARRAY，小的概念可以按照 BLOCK 的大小来判断，如果数据量超过一个 BLOCK，将会产生行连接
    7.还有些情况适合使用 VARRAY：you don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.

 

 

四，集合内建函数
集合还有很多内建函数，这些函数称为方法，调用方法的语法如下：
collection.method
下表中列出oracle 中集合的方法
方法 描述 使用限制
COUNT 返回集合中元素的个数
DELETE 删除集合中所有元素
DELETE(x) 删除元素下标为x的元素，如果x为null,则集合保持不变 对VARRAY非法
DELETE(x,y) 删除元素下标从X到Y的元素，如果X>Y集合保持不变 对VARRAY非法
EXIST(x) 如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE
EXTEND 在集合末尾添加一个元素 对Index_by非法
EXTEND(x) 在集合末尾添加x个元素 对Index_by非法
EXTEND(x,n) 在集合末尾添加元素n的x个副本 对Index_by非法
FIRST 返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
LAST 返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT。
LIMIT 返回VARRY集合的最大的元素个数，对于嵌套表和Index_by集合无用。
NEXT(x) 返回在元素x之后及紧挨着它的元素的值，如果该元素是最后一个元素，则返回null。
PRIOR(x) 返回集合中在元素x之前紧挨着它的元素的值，如果该元素是第一个元素，则返回null。
TRIM 从集合末端开始删除一个元素 对index_by不合法
TRIM(x) 从集合末端开始删除x个元素 对index_by不合法