PolarDB:Assignment of a collection variable

Overview

Use one the following methods to assign values to collection variables:

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

• Use an assignment statement to assign the values of an existing collection variable to another collection variable.

• Pass the collection variable as an parameter in the OUT or IN OUT mode to the subprogram, and then assign values to the collection variable within the subprogram. Only variable-length arrays and nested tables support this method. Associative arrays do not support this method.

• Assign values to the elements of a collection variable by using the collection(index) = value syntax.

Examples

See the following example of a successful assignment:

DECLARE
  TYPE char_col1 IS VARRAY(3) OF CHAR(5);
  TYPE char_col2 IS VARRAY(3) OF CHAR(5);
  TYPE int_col IS VARRAY(3) OF INT;
 
  group1 char_col1 := char_col1('1', '2', '3');
  group2 char_col2;
  group3 int_col;
BEGIN

  group2 := group1; -- Collection variables of different types that contain elements of the same type.
  RAISE NOTICE 'group2:';
  FOR i IN 1..3 LOOP
    RAISE NOTICE '%', group2(i);
  END LOOP;

  group3 := group1; -- Collection variables of different types that contain elements whose types can be implicitly converted.
  RAISE NOTICE 'group3:';
  FOR i IN 1..3 LOOP
    RAISE NOTICE '%', group3(i);
  END LOOP;
END;

Sample result:

NOTICE:  group2:
NOTICE:  1    
NOTICE:  2    
NOTICE:  3    
NOTICE:  group3:
NOTICE:  1
NOTICE:  2
NOTICE:  3
DO

If the implicit conversion fails, an error is returned when a value is assigned to the corresponding element. See the following example of a failed assignment:

DECLARE
  TYPE char_col IS VARRAY(3) OF CHAR(5);
  TYPE int_col IS VARRAY(3) OF INT;
 
  group1 char_col := char_col('1', 'a'); -- The a type cannot be converted to the int type.
  group2 int_col;
BEGIN
  group2 := group1;
END;

Sample result:

ERROR:  invalid input syntax for type integer: "a    "
CONTEXT:  PL/SQL function inline_code_block line 8 at assignment

You can use NULL to assign null to variable-length arrays or nested tables that are initialized.

DECLARE
  TYPE nt_type IS TABLE OF INT;
  nt nt_type; -- The initial value is null.

  PROCEDURE print_status IS
  BEGIN
    IF nt IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('nt is null.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('nt is not null.');
    END IF;
  END;
BEGIN
  print_status;
  nt=nt_type(); -- The initial value is an empty value, not null.
  print_status;
  nt = NULL; -- Assign null to the collection variable.
  print_status;
END;

Sample result:

nt is null.
nt is not null.
nt is null.
DO

An associative array variable cannot be null. An error is returned if you assign null to an associative array variable.

DECLARE
  TYPE aarray_type IS TABLE OF INT INDEX BY VARCHAR(10);
  aarray aarray_type := NULL; -- Assign null to the associative array variable.
BEGIN
END;

Sample result:

ERROR:  can not assign NULL to associative array
CONTEXT:  PL/SQL function inline_code_block line 4 during statement block local variable initialization