Array variables

An array variable is a variable that is defined as a user-defined array type.

An array variable can be defined in one of the following ways:

• An array global variable that is defined using the CREATE VARIABLE statement.
• An SQL parameter that is defined using the CREATE FUNCTION (SQL scalar) or CREATE PROCEDURE (SQL native) statement.
• An SQL variable that is defined using the DECLARE clause of a compound statement.

An array variable (representing an entire array) can be referenced in the following contexts:

• An input argument to the NULL predicate.
• An input argument to the ARRAY_EXISTS predicate.
• An input argument to a built-in array scalar function (ARRAY_DELETE, ARRAY_FIRST, ARRAY_LAST, ARRAY_NEXT, ARRAY_PRIOR, or TRIM_ARRAY).
• An argument to UNNEST specification.
• The outer SELECT list of a fullselect that does not include a set operator, in the definition of a cursor that is not scrollable. In this case a FETCH statement for the cursor must specify an array variable as the target for the corresponding result column of the fullselect for the array variable.
• The outer select list of a SELECT INTO statement, when the target for the corresponding column of the result table of the fullselect is an array variable.
• The outer select list of a scalar fullselect, on the right side of a SET assignment-statement statement or an SQL PL assignment-statement statement, when the corresponding target of the assignment is an array variable.
• The source value for a VALUES INTO statement, when the target for value is an array variable.
• The target of an assignment from a FETCH statement, when the corresponding source data is an array value, and the FETCH statement is issued in an SQL PL context.
• The target of a SELECT INTO statement, when source data for the corresponding column of the result table is an array value.
• The target of an assignment for a SET assignment-statement statement or an SQL PL assignment-statement statement, when the corresponding source value is an array value.
• The target of a VALUES INTO statement, when the source data value is an array value.
• An argument to or from a routine (CALL statement or function invocation).
• The value that is returned in a RETURN statement of an SQL scalar function.
• An ORDER BY or GROUP BY clause of an outer fullselect.
• The source value in a USING clause for an EXECUTE statement or OPEN statement, in an SQL PL context.

An array variable can also be referenced in an array element specification. An element of a user-defined array type can be referenced anywhere that an expression that returns the same data type as an element of that array can be used.