Declaring local variables of type array

Declaring array data type variables is a task that you perform after creating array data types if you want to be able to temporarily store or pass array data type values.

Before you begin

Before you create a local variable of type row:

About this task

Declaring array data types can be done in supported contexts including within: SQL procedures, SQL functions, and triggers.

Procedure

  1. Define the DECLARE statement.
    1. Specify a name for the array data type variable.
    2. Specify the name of the array data type that you used when you created the array data type.

    If the array data type was declared using the following CREATE TYPE statement:

    CREATE TYPE simpleArray AS INTEGER ARRAY[10];
    You would declare a variable of this data type as follows:
    DECLARE myArray simpleArray;

    If the array data type was declared using the following CREATE TYPE statement:

    CREATE TYPE id_Phone AS VARCHAR(20) ARRAY[100];
    You would create a variable of this data type as follows:
    DECLARE id_Phone_Toronto_List  id_Phone;
    This array can contain up to 100 phone values stored as VARCHAR(20) data type values indexed by integer values ranging from 1 to 100. The variable name indicates that the phone values are Toronto phone numbers.
  2. Include the DECLARE statement within a supported context. This can be within a CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER statement.
  3. Execute the statement which contains the DECLARE statement.

Results

The statement should execute successfully.
If the statement does not execute successfully due to an error with the DECLARE statement:
  • Verify the SQL statement syntax of the DECLARE statement and execute the statement again.
  • Verify that no other variable with the same name has already been declared within the same context.
  • Verify that the array data type was created successfully.

What to do next

After declaring associative array variables, you might want to assign values to them.