Declaring associative array variables

Declaring associative array variables is a task that you perform after creating associative array data types to be able to temporarily store or pass associative array data type values. Local variables are declared using the DECLARE statement. Global variables are created using the CREATE VARIABLE statement.

Before you begin

  • Read: Associative array data types
  • Read: Restrictions on associative array data types
  • Read: Creating the associative array data type
  • For global variables, you require the privilege to execute the CREATE VARIABLE statement. For local variables, no privileges required to execute the DECLARE statement

About this task

Associative array variables can be declared and used in supported contexts to store sets of row data.

Procedure

  1. Define the DECLARE statement for a local variable or the CREATE TYPE statement for a global variable:
    1. Specify a name for the associative array data type.
    2. Specify the name of the associative array data type that you used when you created the associative array data type.
  2. Execute the CREATE TYPE statement from a supported interface.

Example

Example 1:
Consider an associative array data type defined as:
CREATE TYPE Representative_Location AS VARCHAR(20) ARRAY[VARCHAR(30)];
To declare a variable of this data type you would use the DECLARE statement as follows:
DECLARE RepsByCity Representative_Location;

This array can contain up to the maximum number of associative array element values stored as VARCHAR(20) data type values indexed by unique variable character data type values. The variable name indicates that a set of names of sales representatives is indexed by the name of the city that they represent. In this array, no two sales representative names can be represented by the same city which is the array index value.

Example 2:
Consider an associative array data type defined to store as element values, the names of capital cities, where the indices are province names: 
CREATE TYPE capitalsArray AS VARCHAR(12) ARRAY[VARCHAR(16)];
To create a variable of this data type you would use the CREATE VARIABLE statement as follows:
CREATE VARIABLE capitals capitalsArray;
This array can contain up to the maximum number of associative array element values stored as VARCHAR(20) data type values indexed by unique variable character data type values. The variable name indicates that a set of names of sales representatives is indexed by the name of the city that they represent. In this array, no two sales representative names can be represented by the same city which is the array index value.

What to do next

If the DECLARE statement or CREATE VARIABLE statement executes successfully, the array data type will have been defined successfully and can be referenced. To validate that the associative array variables was created you can assign values to the array or attempt to reference values in the array.

If the DECLARE statement or CREATE VARIABLE statement failed to execute successfully, verify the SQL statement syntax of the DECLARE statement and execute the statement again. See the DECLARE statement.