Collections

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

The elements of a collection are referred to by their unique subscripts. You can access the elements of a collection by using the subscript notation: collection name (subscript).

A collection method is a built-in stored procedure that can return information about a collection or perform operations on it. To call a collection method, use the following syntax: 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-by tables)

• Varrays

• Nested tables

The following table describes the similarities and differences between these collection types.

| Collection type | Number of elements | Index type | Dense or sparse | Uninitialized state | Definition method |

• Number of elements

If the number of elements is specified, it specifies 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. In other words, each 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, 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 empty, that is, set it to empty or assign a non-NULL value. 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. If the block is in a standalone or program unit, the collection is stored in the database.

A collection type defined in the package header is a public type. You can reference it from outside the package by using the fully qualified name (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 collection variables

You can assign values to collection variables by using the following methods:

• Call a constructor to create a collection and assign it 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 values to collection variables, note the following:

• You can assign values to a collection variable only if the collection variable has the same element type as another collection variable.

• 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 call to a nested table variable.

Multidimensional collections

OceanBase Database supports the creation of multidimensional collections by using collections. In a multidimensional collection, each element is a collection. For example, a multidimensional collection can be a two-dimensional array (an array of arrays).

The following example creates a two-dimensional varray.

obclient> DECLARE

-- Define a varray type named type_var1 with a maximum capacity of 3 and element type INT.

TYPE type_var1 IS VARRAY(3) OF INT;
-- Define a varray type named type_var2 with a maximum capacity of 5 and element type 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 sets

To determine whether one set variable is less than another, you must first define the meaning of "less than" 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 use relational operators to compare two set variables. This restriction also applies to implicit comparisons. For example, you cannot use set variables in the DISTINCT, GROUP BY, or ORDER BY clauses.

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

• Comparing a variable of a varray or nested table type with a NULL value

When you compare a variable of a varray 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 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

• 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 elements of the nested table type are not record types, 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 set 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 set expression

You can use an SQL set expression to compare a nested table variable and test certain 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

False 5 3