The collection constructor function is a system-defined function with the same name as the collection type. This function returns a collection of this type.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Note
The constructor function applies only to variable-size arrays (or varrays) and nested tables. Associative arrays do not support the constructor function. In this topic, a collection is a varray or nested table.
The syntax for calling the constructor function is as follows:
collection_type ( [ value [, value ]... ] )
collection_type indicates the name of the nested table type or varray type that is previously declared, and value indicates a valid value of collection_type. If collection_type indicates a varray, the varray has a maximum number of elements, and the number of elements in it cannot exceed the maximum number. If collection_type indicates a nested table, it does not have a maximum value.
If the parameter list is empty, the constructor function returns an empty collection. Otherwise, the constructor function returns a collection that contains specified values.
You can assign the returned collection to a collection variable of the same type in the variable declaration and in the executable part of a block.
Example: Initialize a collection variable to empty
obclient> DECLARE
TYPE players IS VARRAY(5) OF VARCHAR2(20);
team PLAYERS := players(); -- Initialize to empty.
PROCEDURE print_team (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
IF team.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('Empty');
ELSE
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_team('Team Members:');
team := players('Charles', 'Carl', 'James', 'Gary','Brian');
print_team('Team Members:');
END;
/
Query OK, 0 rows affected
Team Members:
Empty
---
Team Members:
1.Charles
2.Carl
3.James
4.Gary
5.Brian
---
In the preceding example, the constructor function is called twice to initialize the varray variable team to empty and then assign a new value to the variable in the executable part of the block. The print_team stored procedure displays the initial and final values of team. print_team uses the COUNT collection method to check whether team is empty.