Collection constructor function

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.

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.

Example: Associative array with index and element specified by the constructor

obclient> declare
    ->     type rec_type is record (
    ->         id number,
    ->         name varchar2(50),
    ->         salary number
    ->     );
    ->     type ty_rec is table of rec_type index by PLS_INTEGER;
    ->     v_rec ty_rec;
    ->     idx PLS_INTEGER;
    -> begin
    ->     v_rec := ty_rec(1 => rec_type(1, 'Alice', 5000),2 => rec_type(2, 'Bob', 6000),3 => rec_type(3, 'Charlie', 7000));
    ->     idx := v_rec.FIRST();
    ->     while idx is not null loop
    ->         dbms_output.put_line('ID: ' || v_rec(idx).id || ', Name: ' || v_rec(idx).name || ', Salary: ' || v_rec(idx).salary);
    ->         idx := v_rec.NEXT(idx);
    ->     end loop;
    -> end;
    -> /
Query OK, 0 rows affected (0.146 sec)

ID: 1, Name: Alice, Salary: 5000
ID: 2, Name: Bob, Salary: 6000
ID: 3, Name: Charlie, Salary: 7000

In this example, a record type is first defined, followed by an associative array type ty_rec, where the index is of type PLS_INTEGER and the element is of type rec_type. The variable v_rec is an associative array of type ty_rec. The constructor function, which uses the => operator, is used to specify the index and its corresponding element. The associative array is then enumerated to output all its elements.

Additionally, the associative array supports a constructor function that does not specify the index (i.e., does not use the => operator), which is applicable only for associative arrays with an index of type PLS_INTEGER. In this case, the index starts from 1 and increments by 1 by default. Here is an example:

obclient> declare
    ->     type ty1 is table of int index by PLS_INTEGER;
    ->     x ty1;
    ->     idx PLS_INTEGER;
    -> begin
    ->     x := ty1(10, 20, 30);
    ->     idx := x.FIRST();
    ->     while idx is not null loop
    ->       dbms_output.put_line('index: ' || idx || ', Element: ' || x(idx));
    ->       idx := x.NEXT(idx);
    ->     end loop;
    -> end;
    -> /
Query OK, 0 rows affected (0.122 sec)

index: 1, Element: 10
index: 2, Element: 20
index: 3, Element: 30

In this example, an associative array type ty1 is first declared, where the index is of type PLS_INTEGER and the element is of type int. The variable x is then declared as an associative array of type ty1. The constructor function for ty1 is used to directly assign the element value to x. Finally, the index and element of x are traversed and outputted sequentially.