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 index. You can access the elements of a collection by using the index naming method: 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 format: 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 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 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, meaning that 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 with no elements. 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 into a valid collection, you must initialize it by setting it to empty or assigning 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 only available within the block and is stored in the database only when the block is in a standalone or subprogram of a package.

A collection type defined in the package header is a public type. You can reference it externally by qualifying the package name with the type 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 using the CREATE TYPE statement and drop it using the DROP TYPE statement.

Assign values to collection variables

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

• Call the 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 assigning values to a collection variable, note the following:

• You can assign a value to a collection variable only if both 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. Either assignment method will result in the variable being NULL.

• 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, where each element of the collection is itself a collection. For example, a multidimensional collection can be a two-dimensional array, which is an array of arrays.

The following example demonstrates a multidimensional array using varrays.

obclient> DECLARE

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

TYPE type_var1 IS VARRAY(3) OF INT;
-- Define a varray type 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 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 what "less than" means in this context and write a function that can return TRUE or FALSE. You cannot compare associative array variables to NULL values or to each other. You cannot also use relational operators to compare two set variables. This restriction also applies to implicit comparisons. For example, set variables cannot appear in DISTINCT, GROUP BY, or ORDER BY clauses.

The comparison of sets mainly includes the following three typical cases:

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

When comparing a NULL value, use the IS [NOT] NULL operator. You cannot use the relational operators = or !=, <>, ~ =, 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

• Comparing nested tables for equality

Two nested tables are equal if they contain the same elements in any order. If two nested tables have the same nested table type and the elements of the nested table type are not record types, you can use the relational operators = and !=, <>, ~ =, or ^= to compare the two nested tables. 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 with an SQL collection expression

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