The assignment-statement assigns a value to an SQL parameter or SQL variable.
>>-+--------+--SET--assignment-clause-------------------------->< '-label:-'
assignment-clause:
.-,-----------------------------------------------------------------------. V | >>-+---+-+-SQL-parameter-name-+-- = --+-expression-+-------------------------+-+-+->< | | '-SQL-variable-name--' +-NULL-------+ | | | | '-DEFAULT----' | | | | .-,----------------------. .-,--------------. | | | | V | V | | | | '-(----+-SQL-parameter-name-+-+--)-- = --+-(----+-expression-+-+--)-+-' | | '-SQL-variable-name--' | +-NULL-------+ | | | | '-DEFAULT----' | | | '-row-fullselect-----------' | +-array-variable-name--[--subindex--]-- = --+-expression-+--------------------+ | '-NULL-------' | '-array-variable-name-- = -+-array-constructor---+----------------------------' '-TRIM_ARRAY function-' row-fullselect |--+--------------------------------------------------+--fullselect--| | .-,-----------------------. | | V | | '-WITH--+-----------+----common-table-expression-+-' '-RECURSIVE-'
array-variable-name
Identifies an SQL variable or parameter. The variable or parameter
must be of an array type.
Assignment rules: Assignments in the assignment statement must conform to the SQL retrieval assignment rules as described in Assignments and comparisons.1
If the
assignment is of the form SET A[idx] = rhs, where A is
an array variable name, idx is an expression used as the subindex,
and rhs is an expression of a compatible type as the array
element, then:
Assignments involving SQL parameters: An IN parameter can appear on the left or right side of an assignment-statement. When control returns to the caller, the original value of the IN parameter is retained. An OUT parameter can also appear on the left or right side of an assignment-statement. If used without first being assigned a value, the value is null. When control returns to the caller, the last value that is assigned to an OUT parameter is returned to the caller. For an INOUT parameter, the first value of the parameter is determined by the caller, and the last value that is assigned to the parameter is returned to the caller.
Special Registers: If a variable has been declared with an identifier that matches the name of a special register (such as PATH), then the variable must be delimited to distinguish it from assignment to the special register (for example, SET "PATH" = 1; for a variable called PATH declared as an integer).
Example 1: Increase the SQL variable p_salary by 10 percent.
SET p_salary = p_salary + (p_salary * .10)
Example 2: Set SQL variable p_salary to the null value
SET p_salary = NULL

Example 3: Set the SQL array variable p_phonenumbers to an array of fixed numbers.

SET p_phonenumbers = ARRAY[9055553907, 4165554213, 4085553678]

Example 4: Set the SQL array variable p_phonenumbers to an array of numbers retrieved from the PHONENUMBER table.

SET p_phonenumbers = ARRAY [SELECT NUMBER FROM PHONENUMBERS WHERE EMPID = 624]

Example 5:Assign p_mynumber to the first and tenth elements of the SQL array variable p_phonenumbers. After the first assignment, the cardinality of p_phonenumbers is 1. After the second assignment, the cardinality is 10, and elements 2 to 9 have been implicitly assigned the null value.

SET p_phonenumbers[1] = p_mynumber SET p_phonenumbers[10] = p_mynumber