Collections

A collection is a composite variable used in a stored procedure and stores multiple elements of the same type in order. A collection is similar to a one-dimensional array. A collection can be passed as a parameter of a subprogram.

The internal components of a collection are referred to as elements. Each element has a unique subscript to describe its position in the collection. The format for accessing an element is: collection name (subscript).

A collection method is a built-in stored procedure, and can return the information about a collection or perform operations on a collection. A collection method is called in the format of collection name.method name. For example, you can use the collection name.COUNT method to return the number of elements in the collection.

Collection types and differences

PL supports three collection types in OceanBase Database:

• Associative arrays (or index-by tables)

• Variable-size arrays (or varrays)

• Nested tables

The following table describes the differences between these three collection types.

• Number of elements

  If the number of elements is specified for a collection, it is the maximum number of elements in the collection. If the number of elements is not specified for a collection, the maximum number of elements in the collection is subject to the ceiling value of the index type.

• Dense or sparse

  In a dense collection, no gaps exist between elements. In other words, every element between the first and last elements is defined and has a value, which can be NULL unless a NOT NULL constraint is specified. In a sparse collection, gaps exist between elements.

• Status before initialization

  An empty collection without any elements exists. You can call the EXTEND method to add elements to an empty collection. Null collections, also known as atomic null collections, do not exist. To make a null collection valid, you must initialize it to empty, namely, set it to empty or assign a non-NULL value to it. A null collection cannot be initialized by using the EXTEND method.

• Definition method

  A collection type defined in a PL block is a local type. It can be used only in the block and is stored in the database only when the block is located in an independent subprogram or in a subprogram of a program package.

  A collection type defined in a package specification is a public type. You can use package_name.type_name to limit the package name and reference the type from outside of the package. The collection will always be stored in the database until the package is dropped.

  A collection type defined at schema level is an independent type. You can use the CREATE TYPE statement to create a collection and use DROP TYPE to drop it.

Assign a value to a collection variable

You can assign a value to a collection variable by using any of the following methods:

• Call the constructor function to create a collection and assign it to the collection variable.

• Use the assignment statement to assign the value of an existing collection variable to it.

• Pass the collection variable as an OUT or IN OUT parameter to a subprogram, and assign a value to it in the subprogram.

To assign a value to a scalar element in a collection variable, use collection_variable_name(index) to reference the element and assign a value to it.

When you assign values to collection variables, note that:

• You can assign the value of a collection variable to another only when the two collection variables have the same element type.

• You can assign a NULL value or an empty collection of the same data type to an array or nested table variable. Any assignment method can be used to make the variable empty.

• You can assign the result of an SQL MULTISET operation or the result of an SQL SET function call to a nested table variable.

Multi-dimensional collection

You can define a multi-dimensional collection based on collections. In other words, each element of a multi-dimensional collection is a collection. For example, a multi-dimensional collection can be a two-dimensional array, that is, an array of arrays.

The following example defines a multi-dimensional array based on varrays.

obclient> DECLARE
      -- Define a varray type named type_var1, which has a maximum capacity of 3 and an element type of INT.
   TYPE type_var1 IS VARRAY(3) OF INT;

      -- Define a varray type named type_var2, which has a maximum capacity of 5 and an element type of type_var1.
   TYPE type_var2 IS VARRAY(5) OF type_var1;

      -- Define a varray variable named var, which has a variable type of type_var2.
   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

Compare collection variables

To check whether a collection variable is smaller than another, you must first define the meaning of “smaller than” in this context, and compile a function that returns TRUE or FALSE.

You cannot compare an associative array variable with a NULL value, compare two associative array variables, or use a relational operator to compare two collection variables. These restrictions also apply to implicit comparisons. For example, a collection variable is not allowed in the DISTINCT, GROUP BY, or ORDER BY clause.

Collection variable comparisons include the following three typical scenarios:

• Compare a varray or nested table variable with a NULL value

  To compare with a NULL value, use the IS [NOT] NULL operator. Do not use a relational operator that means “equal to” (=) or “unequal to” (<>, !=, ~=, or ^=).

  Here is an example:

  obclient> DECLARE
    TYPE players IS VARRAY(5) OF VARCHAR2(20);   -- Define a varray type.
    names PLAYERS := players('Charles', 'Carl', 'James');        -- Define a varray variable.
    TYPE register IS TABLE OF VARCHAR2(20);        -- Define a nested table type.
    team REGISTER;    -- Define a nested table variable.
  
  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
  

• Compare whether two nested table variables are equal

  Two nested table variables are equal only when they have the same set of elements, regardless of the order of the elements. If two nested table variables have the same nested table type and this type does not have elements of the RECORD type, you can use a relational operator that means “equal to” (=) or “unequal to” (<>, !=, ~=, or ^=) to compare the two nested table variables.

  Here is an example:

  obclient> DECLARE
    TYPE players IS TABLE OF VARCHAR2(30);   -- The element type is not the collection type.
    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
  

• Compare nested tables by using an SQL collection expression

  You can compare nested table variables by using an SQL collection expression and test some attributes. Here is an example:

  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)));        -- A condition
    obtest(result => (t4 SUBMULTISET OF t3));      -- A condition
    obtest(result => (t4 NOT SUBMULTISET OF t1));  -- A condition
    obtest(result => (4 MEMBER OF t4));             -- A condition
    obtest(result => (t1 IS A SET));                -- A condition
    obtest(result => (t1 IS NOT A SET));            -- A condition
    obtest(result => (t5 IS EMPTY));                -- A condition
    obtest(quantity => (CARDINALITY(t5)));         -- A function
    obtest(quantity => (CARDINALITY(SET(t5))));    -- Two functions
  END;
  /
  Query OK, 0 rows affected
  
  True
  True
  True
  False
  True
  False
  False
  5
  3