assignment-statement

The assignment-statement assigns a value to an SQL parameter, an SQL variable, Start of change or a transition-variable End of change .

Syntax

row-fullselect

Description

label

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.

SQL-parameter-name

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.

SQL-variable-name

Identifies the SQL variable that is the assignment target. SQL variables can only be declared in a compound-statement and must be declared before they are used.

transition-variable

Identifies the column to be updated in the new row. A transition-variable must identify a column in the subject table of a trigger, optionally qualified by a correlation name that identifies the new value. An OLD transition-variable must not be identified. A transition-variable can only be modified in a BEFORE trigger.

A transition-variable must not be identified more than once in the same assignment-statement.

Values are assigned to transition-variables according to the storage assignment rules. For more information see Assignments and comparisons.

global-variable

Identifies the global variable that is the assignment target.

expression

Specifies the expression or value that is the source for the assignment.

An expression may contain references to OLD and NEW transition-variables. If the CREATE TRIGGER statement contains both OLD and NEW clauses, references to transition-variables must be qualified by the correlation-name.

NULL

Specifies the null value. For transition-variables, NULL can only be specified for nullable columns.

DEFAULT

Specifies that the default value will be used. DEFAULT can only be specified for a global variable and in SQL triggers for transition variables. DEFAULT is not allowed for a transition-variable associated with a GENERATED column. Only one global variable can be assigned in the SET statement when DEFAULT is used.

SQLIND_DEFAULT

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. End of change

SQLIND_UNASSIGNED

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. End of change

row-fullselect

A fullselect that returns a single result row. The result column values are assigned to the corresponding 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.

fullselect

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.

array-variable-name

Identifies an SQL variable or parameter. The variable or parameter must be of an array type.

[array-index]: 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.

array-constructor

Specifies the value of an array constructor. See ARRAY constructor.

TRIM_ARRAY function

Specifies the TRIM_ARRAY scalar function. See TRIM_ARRAY.

Notes

Assignment rules: Assignments in the assignment statement must conform to the SQL retrieval assignment rules as described in Assignments and comparisons.¹

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 array index, and rhs is an expression of a compatible type as the array element, then:

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:
1. The value in position i, for i greater than C and less that idx, is set to the null value.
2. The value in position idx is set to the value of rhs.
3. 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 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, a reference to a variable in a target expression is always the value of the variable prior to any assignment.

Start of change 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. End of change

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 prohibited; however, it is not recommended as assignment does not affect the diagnostics area or result in the activation of condition handlers. The SQLCODE and SQLSTATE will be reset and the diagnostics area or SQLCA initialized for each assignment-statement other than assignment-statements that:

assign the SQLSTATE or SQLCODE variable to another variable or
set a constant value into the SQLSTATE or SQLCODE variable.

Examples

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

Example 6: Ensure that the salary column is never greater than 50000. If the new value is greater than 50000, set it to 50000.

CREATE TRIGGER LIMIT_SALARY
  BEFORE INSERT ON EMPLOYEE
  REFERENCING NEW AS NEW_VAR
  FOR EACH ROW MODE DB2SQL
  WHEN (NEW_VAR.SALARY > 50000)
    BEGIN ATOMIC
    SET NEW_VAR.SALARY = 50000;
  END

Example 7: When the job title is updated, increase the salary based on the new job title. Assign the years in the position to 0.

CREATE TRIGGER SET_SALARY
  BEFORE UPDATE OF JOB ON STAFF
  REFERENCING OLD AS OLD_VAR
              NEW AS NEW_VAR
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
    SET (NEW_VAR.SALARY, NEW_VAR.YEARS) =
        (OLD_VAR.SALARY * CASE NEW_VAR.JOB
            WHEN 'Sales' THEN 1.1
            WHEN 'Mgr'   THEN 1.05
            ELSE 1 END ,0);
  END

¹ If assigning to an SQL-variable or SQL-parameter and the standards option is specified, storage assignment rules apply. For information about the standards option, see Standards compliance.