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 the 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.