集合

集合是存储过程复合变量，可以按顺序存储多个类型相同的元素，类似于一维数组。整个集合可以作为子程序的参数进行传递。

集合的内部组件称为元素，每个元素有唯一的下标描述它在集合中的位置。访问集合的元素，可以使用下标命名方式：集合名(下标)。

集合方法是内建的存储过程，可以返回集合的信息或者对集合进行操作。需要使用" . "表示调用集合，格式为： 集合名 . 方法名。例如，集合名 .COUNT 方法用于返回该集合元素的数量。

集合类型及区别

OceanBase 数据库的 PL 支持如下三种集合类型：

• 关联数组（又叫索引表）

• 可变数组( Varrays )

• 嵌套表

它们的相似点和不同点如下表所示。

• 元素数量

  如果指定了元素数量，则即是集合中元素的最大数量。如果未指定元素数量，则集合中的最大元素数量是索引类型的上限。

• 密集或稀疏

  密集集合是指在元素之间没有空隙，即第一个元素与最后一个元素之间的每个元素都被定义，并具有一个值（该值可以为 NULL，除非该元素具有 NOT NULL 约束）。稀疏集合是指在元素之间存在空隙。

• 未初始化状态

  存在一个没有任何元素的空集合。要将元素添加到空集合中，请调用 EXTEND 方法。不存在 Null 集合（也称为原子 Null 集合）。要将 Null 集合更改为有效集合，必须将其初始化为空，即将其设为空或分配一个非 NULL 值。不支持使用 EXTEND 方法来初始化一个 Null 集合。

• 定义方式

  PL 块中定义的集合类型是本地类型。它仅在块中可用，并且块位于独立或程序包子程序中时才会将集合存储在数据库中。

  包头中定义的集合类型是公共类型。您可以通过限定包的名称（package_name.type_name）来实现外部引用。集合会一直存储在数据库中，直到包被删除。

  在 Schema 级别定义的集合类型是独立类型。可以使用 CREATE TYPE 语句创建集合，也可以使用 DROP TYPE 语句将其删除。

为集合变量赋值

可以通过以下方式为集合变量赋值：

• 调用构造函数以创建一个集合并将其赋值给集合变量。

• 使用赋值语句为其赋值一个现有集合变量的值。

• 将其作为 OUT 或 IN OUT 参数传递给子程序，然后在子程序中赋值。

要将值分配给集合变量的标量元素，使用 collection_variable_name(index) 引用该元素并为其分配值。

为集合变量赋值时需要注意以下事项：

• 仅当两个集合变量同时具有相同的元素类型时，才可以进行赋值。

• 可以为数组或嵌套表变量分配 NULL 值或相同数据类型的空集合。任一种赋值方式都会使变量为空值。

• 可以为嵌套表变量分配 SQL MULTISET 操作或 SQL SET 函数调用的结果。

多维集合

OceanBase 数据库支持使用集合构建多维集合，即多维集合的单个元素是一个集合。例如，多维集合可以为二维数组（即数组的数组）。

如下示例为可变数组的多维数组。

obclient> DECLARE
      -- 定义一个可变数组类型 type_var1，它的最大容量是 3，元素类型是 INT
   TYPE type_var1 IS VARRAY(3) OF INT;
  
      -- 定义一个可变数组类型 type_var2，它的最大容量是 5，元素类型是 type_var1
   TYPE type_var2 IS VARRAY(5) OF type_var1;
  
      -- 定义一个类型为 type_var2 的可变数组变量 var
   var type_var2 := type_var2(type_var1(1,2,3), type_var1(4,5,6), type_var1(7,8,9));
 
 BEGIN 
   FOR i IN 1..3 LOOP
      FOR j IN 1..2 LOOP
        DBMS_OUTPUT.PUT(VAR(i)(j) || '    ');
      END LOOP;
     DBMS_OUTPUT.PUT_LINE('');
   END LOOP;
  
END;/
Query OK, 0 rows affected

集合的比较

如果要确定一个集合变量是否小于另一个变量，首先必须定义"小于"在该上下文中的含义，并编写一个可以返回 TRUE 或 FALSE 的函数。

关联数组变量不能与 NULL 值进行比较，关联数组变量之间也不能进行比较；两个集合变量不能使用关系运算符进行比较。此限制也适用于隐式比较。例如，集合变量不能出现在 DISTINCT、GROUP BY 或 ORDER BY 子句中。

关于集合的比较主要分为以下三种典型情况：

• 可变数组和嵌套表变量与 NULL 值比较

  与 NULL 值进行比较时，请使用 IS [NOT] NULL 运算符。不能使用关系运算符等于（=）和不等于（<>，!=，〜= 或 ^=）。

  示例如下：

  obclient> DECLARE
    TYPE players IS VARRAY(5) OF VARCHAR2(20);   --定义可变数组类型
    names PLAYERS := players('Charles', 'Carl', 'James');        --定义可变数组变量
    TYPE register IS TABLE OF VARCHAR2(20);        -- 定义嵌套表类型
    team REGISTER;    -- 定义嵌套表变量
  
  BEGIN
    IF names IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Names IS NULL');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Names IS NOT NULL');
    END IF;
  
    IF team IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE('Team IS NOT NULL');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Team IS NULL');
    END IF;
  END;
  /
  Query OK, 0 rows affected
  
  Names IS NOT NULL
  Team IS NULL
  

• 比较嵌套表是否相等

  当且仅当两个嵌套表变量具有相同的元素集（以任何顺序）时才相等。如果两个嵌套表变量具有相同的嵌套表类型，并且该嵌套表类型不具有记录类型的元素，则可以使用关系运算符等于（=）和不等于（< >，!=，〜= 或 ^=）来比较两个嵌套表变量。

  示例如下：

  obclient> DECLARE
    TYPE players IS TABLE OF VARCHAR2(30);   --元素类型非集合类型
    name_list1 PLAYERS := players('Andrew', 'Barton', 'Conrad', 'Dick','Edward');
    name_list2 PLAYERS := players('Dick', 'Edward', 'Andrew', 'Conrad','Barton');
    name_list3 PLAYERS := players('John', 'Mary', 'Alberto', 'Juanita');
  
  BEGIN
    IF name_list1 = name_list2 THEN
      DBMS_OUTPUT.PUT_LINE('name_list1 = name_list2');
    END IF;
  
    IF name_list2 != name_list3 THEN
      DBMS_OUTPUT.PUT_LINE('name_list2 != name_list3');
    END IF;
  END;
  /
  Query OK, 0 rows affected
  
  name_list1 = name_list2
  name_list2 != name_list3
  

• 比较嵌套表与 SQL 集合表达式

  可以使用 SQL 集合表达式比较嵌套表变量，并测试某些属性。示例如下：

  obclient> DECLARE
    TYPE nested_ty IS TABLE OF NUMBER;
    t1 nested_ty := nested_ty(6,7,9);
    t2 nested_ty := nested_ty(8,7,6);
    t3 nested_ty := nested_ty(7,8,6,8);
    t4 nested_ty := nested_ty(6,7,8);
    t5 nested_ty := nested_ty(6,7,8,6,7);
  
    PROCEDURE obtest (
      result BOOLEAN := NULL,
      quantity NUMBER := NULL
    ) IS
    BEGIN
      IF result IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE (
          CASE result
             WHEN TRUE THEN 'True'
             WHEN FALSE THEN 'False'
          END
        );
      END IF;
      IF quantity IS NOT NULL THEN
          DBMS_OUTPUT.PUT_LINE(quantity);
      END IF;
    END;
  BEGIN
    obtest(result => (t4 IN (t1,t2,t3,t5)));        -- 条件
    obtest(result => (t4 SUBMULTISET OF t3));      -- 条件
    obtest(result => (t4 NOT SUBMULTISET OF t1));  -- 条件
    obtest(result => (4 MEMBER OF t4));             -- 条件
    obtest(result => (t1 IS A SET));                -- 条件
    obtest(result => (t1 IS NOT A SET));            -- 条件
    obtest(result => (t5 IS EMPTY));                -- 条件
    obtest(quantity => (CARDINALITY(t5)));         -- 函数
    obtest(quantity => (CARDINALITY(SET(t5))));    -- 两个函数
  END;
  /
  Query OK, 0 rows affected
  
  True
  True
  True
  False
  True
  False
  False
  5
  3