Collection constructors

A collection constructor is a system-defined function with the same name as the collection type. It returns a collection of the specified type.

The syntax for calling a constructor is as follows:

collection_type ( [ value [, value ]... ] )

In this syntax, collection_type is the name of a previously declared nested table or varray type (not an associative array type), and value is a valid value for the elements of the collection_type collection. If collection_type is a varray type, the number of values cannot exceed the maximum value specified for the varray. If collection_type is a nested table type, there is no maximum value.

If the parameter list is empty, the constructor returns an empty collection. Otherwise, the constructor returns a collection containing the specified values.

You can assign the returned collection to a (same-type) collection variable in a variable declaration or in the executable part of a block.

Example: Initialize a collection variable to an empty value.

obclient> DECLARE
  TYPE players IS VARRAY(5) OF VARCHAR2(20);
  team PLAYERS := players();   -- Initialize to an empty value

  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 this example, the constructor is called twice: first to initialize the varray variable team to an empty value, and then in the executable part of the block to assign it a new value. The print_team stored procedure displays the initial and final values of team. The print_team procedure uses the collection method COUNT to determine whether team is empty.

Example: Specify index and element for an associative array using a 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)

The result is as follows:

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, then an associative array type ty_rec is defined, where index is of the PLS_INTEGER type and element is of the rec_type type. v_rec is an associative array variable of type ty_rec. The constructor specifies the index and its corresponding element using the => operator. The associative array is then enumerated to output all elements. Associative arrays also support a constructor that does not specify the index (i.e., a constructor without the => operator), but this is only applicable for associative arrays where the index is of the PLS_INTEGER type. In such cases, 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)

The result is as follows:

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

In this example, an associative array type ty1 is declared, where the index is of the PLS_INTEGER type and the element is of the int type. Then, a variable x of type ty1 is declared. The element values are directly assigned to x within the ty1 constructor. Finally, the index and element of x are traversed and outputted one by one.