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 individual components of a collection are called elements. Each element has a unique index that describes its position in the collection. To access an element, you can use the index notation: collection name (index).

Collection methods are built-in procedures that can return information about a collection or perform operations on it. 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:

• Associative arrays (also known as index tables)

• Varrays

• Nested tables

The following table describes the similarities and 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 is one in which there are 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 is one in which there are 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, 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 within 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 externally by qualifying the package name with the collection 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 by executing the CREATE TYPE statement and drop it by executing the DROP TYPE statement.

Assign values to collection variables

You can assign values to collection variables in the following ways:

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

• Use an assignment statement to assign the value of an existing collection variable to another 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 and the source collection variable 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 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 shows a multidimensional 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 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 use relational operators to compare two collection variables. This restriction also applies to implicit comparisons. For example, collection variables cannot appear in DISTINCT, GROUP BY, or ORDER BY clauses.

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

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

  When comparing a varray or nested table variable with a NULL value, use the IS [NOT] NULL operator. You cannot use the equality (=) or inequality (<> , !=, ~ =, or ^=) 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 nested tables for equality

  Two nested tables are equal if they have 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 equality (=) or inequality (<> , !=, ~ =, or ^=) operators 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 compare a nested table variable with an SQL collection expression and test certain 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)));        -- 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