Collections

A collection is a composite variable that stores multiple elements of the same type in sequence, similar to a one-dimensional array. The entire collection can be passed as a parameter to a subprogram.

The elements in a collection are referred to by their unique index. You can access an element by using the index notation: collection name (index).

A collection method is a built-in stored procedure that can return information about a collection or perform operations on a collection. To call a collection method, use the dot notation: collection name.method name. For example, the COUNT method of a collection returns the number of elements in the collection.

Collection types and their differences

OceanBase Database supports the following three collection types in PL/SQL:

• Associative arrays (also known as index tables)

• Varrays

• Nested tables

The following table describes the differences between these collection types.

• Number of elements

  If the number of elements is specified, it indicates the maximum number of elements in the collection. If the number of elements is not specified, the maximum number of elements in the collection is the upper limit of the index type.

• Dense or sparse

  A dense collection has no gaps between elements. That is, every element between the first and last elements is defined and has a value (which can be NULL unless the element has a NOT NULL constraint). A sparse collection has gaps between elements.

• Uninitialized state

  An empty collection exists. To add elements to an empty collection, you can call the EXTEND method. There is no null collection (also known as an atomic null collection). To convert a null collection to a valid collection, you must initialize it to an empty collection or assign a non-NULL value to it. You cannot use the EXTEND method to initialize a null collection.

• Definition method

  A collection type defined in a PL block is a local type. It is available only in the block and is stored in the database only if the block is in a standalone or subprogram of a package.

  A collection type defined in a package header is a public type. You can reference it from outside the package by using the package name (qualified as package_name.type_name). The collection is stored in the database until the package is dropped.

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

Assign values to a collection variable

You can assign values to a collection variable in the following ways:

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

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

• 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 of a collection variable, use collection_variable_name(index) to reference the element and assign a value to it.

When you assign a value to a collection variable, note the following:

• You can assign a value to a collection variable only if 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. In either case, the variable becomes a null value.

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

Multidimensional collections

OceanBase Database supports the creation of multidimensional collections by using collections. A multidimensional collection is a collection whose elements are collections. For example, a multidimensional collection can be a two-dimensional array (that is, an array of arrays).

The following example creates a two-dimensional varray.

obclient> DECLARE
      -- Define a varray type named type_var1. The maximum number of elements in the varray is 3, and the element type is INT.
   TYPE type_var1 IS VARRAY(3) OF INT;

      -- Define a varray type named type_var2. The maximum number of elements in the varray is 5, and the element type is type_var1.
   TYPE type_var2 IS VARRAY(5) OF type_var1;

      -- Define a varray variable named var of type 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

Comparing collections

To determine whether one collection variable is less than another, you must first define what "less than" means in this context and write a function that returns TRUE or FALSE.

You cannot compare associative array variables with NULL values or with each other. You also cannot compare two collection variables with relational operators. This restriction also applies to implicit comparisons. For example, you cannot use collection variables in DISTINCT, GROUP BY, or ORDER BY clauses.

The comparison of collections is mainly divided into the following three typical cases:

• Comparing a variable of a variable-length array or nested table type with a NULL value

  When you compare a variable of a variable-length array or nested table type with a NULL value, you must use the IS [NOT] NULL operator. You cannot use the equal (=) or not equal (<> , !=, ~ =, or ^=) relational operators.

  Here is an example:

  obclient> DECLARE
    TYPE players IS VARRAY(5) OF VARCHAR2(20);   --Define a variable-length array type.
    names PLAYERS := players('Charles', 'Carl', 'James');        --Define a variable-length array 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
  

• Comparing two nested table variables for equality

  Two nested table variables are equal if and only if they have the same elements in any order. If two nested table variables have the same nested table type and the nested table type does not contain elements of a record type, you can use the equal (=) or not equal (<> , !=, ~ =, or ^=) relational operators 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 a 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
  

• Comparing a nested table variable with an SQL collection expression

  You can compare a nested table variable with an SQL collection expression and test some of its properties. 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)));        -- Condition
    obtest(result => (t4 SUBMULTISET OF t3));      -- Condition
    obtest(result => (t4 NOT SUBMULTISET OF t1));  -- Condition
    obtest(result => (4 MEMBER OF t4));             -- Condition
    obtest(result => (t1 IS A SET));                -- Condition
    obtest(result => (t1 IS NOT A SET));            -- Condition
    obtest(result => (t5 IS EMPTY));                -- Condition
    obtest(quantity => (CARDINALITY(t5)));         -- Function
    obtest(quantity => (CARDINALITY(SET(t5))));    -- Two functions
  END;
  /
  Query OK, 0 rows affected
  
  True
  True
  True
  False
  True
  False
  False
  5
  3