The assignment-statement assigns a value to an SQL parameter or SQL variable.
- Specifies the label for the assignment-statement statement. The label name cannot be the same as the routine name or another label within the same scope. For more information, see References to SQL labels.
- Identifies the SQL parameter that is the assignment target. The SQL parameter must be specified in parameter-declaration in the CREATE PROCEDURE or CREATE FUNCTION statement.
- Identifies the SQL variable that is the assignment target. SQL variables can only be declared in a compound-statement or be a transition variable.
- Identifies the global variable that is the assignment target.
- expression or NULL
- Specifies the expression or value that is the source for the assignment.
- Specifies that the default value for the column associated with the transition variable will be used. This can only be specified for a global variable and in SQL triggers for transition variables. Only one global variable can be assigned in the SET statement when DEFAULT is used.
- Specifies that the indicator associated with this variable is assigned the extended indicator value of DEFAULT. The target of the assignment must be an SQL variable or an SQL parameter. An error is returned if the variable does not allow a null value. Only one variable can be assigned in the SET statement when SQLIND_DEFAULT is used.
- Specifies that the indicator associated with this variable is assigned the extended indicator value of UNASSIGNED. The target of the assignment must be an SQL variable or an SQL parameter. An error is returned if the variable does not allow a null value. Only one variable can be assigned in the SET statement when SQLIND_UNASSIGNED is used.
- A fullselect that returns a single result row. The result column values are assigned to the corresponding SQL variable or parameter. If the result of the fullselect is no rows, then null values are assigned. An error is returned if there is more than one row in the result.
- WITH common-table-expression
- Specifies a common table expression. For an explanation of common table expression, see common-table-expression.
- A fullselect that returns a single result row. The result column values are assigned to each corresponding variable. If the result of the fullselect is no rows, then null values are assigned. An error is returned if there is more than one row in the result.
- Identifies an SQL variable or parameter. The variable or parameter
must be of an array type.
- Numeric expression that specifies which element in the array will be the target of the assignment. The array index must be of an exact numeric type with zero scale; it cannot be null. Its value must be between 1 and the maximum cardinality defined for the array.
- Specifies the value of an array constructor. See ARRAY constructor.
- TRIM_ARRAY function
- Specifies the TRIM_ARRAY scalar function. See TRIM_ARRAY.
- If array A is the null value, set A to the empty array.
- Let C be the cardinality of array A.
- If idx is less than or equal to C, the value in the position identified by idx is replaced by the value of rhs.
- If idx is greater than C, then:
- The value in position i, for i greater than C and less that idx, is set to the null value.
- The value in position idx is set to the value of rhs.
- The cardinality of A is set to idx.
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.
Multiple SQL parameter or SQL variable assignments: If more than one SQL parameter or SQL variable is specified as the target of the assignment-statement, the targets of the assignment-statement are completely evaluated before the assignments are performed. Thus, references to an SQL parameter or SQL variable in a target expression is always the value of the SQL parameter or SQL variable prior to any assignment.
Extended indicator values: For the extended indicator values of SQLIND_DEFAULT and SQLIND_UNASSIGNED to be recognized, the SQL routine or SQL trigger must be created with the SET OPTION EXTIND = *YES. For more information about extended indicator values, see References to host variables.
Arrays: If an assignment is to an array or array element, only one assignment is allowed in the statement.
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).
Considerations for SQLSTATE and SQLCODE variables: Assignment to these variables is not allowed.
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 = p_mynumber SET p_phonenumbers = p_mynumber