assignment-statement
The assignment statement assigns a value to a SQL parameter or to an SQL variable.
Syntax
>>-+--------+--SET--| assignment-clause |---------------------->< '-label:-'
assignment-clause:
.-,-----------------------------------------. V | >>---+---+-SQL-parameter-name-+--=--+-expression-+-+---------------------------------------+--->< | '-SQL-variable-name--' '-NULL-------' | | .-,----------------------. .-,--------------. | | V | V | | '-(----+-SQL-parameter-name-+-+--)--=--(--+---+-expression-+-+-------------------+--)-' '-SQL-variable-name--' | '-NULL-------' | '-VALUES--+-+-expression-+-----------+-' | '-NULL-------' | | .-,--------------. | | V | | '-(----+-expression-+-+--)-' '-NULL-------'
Description
- label
- Specifies the label for assignment-statement. The label name cannot be the same as the name of the SQL routine, or another label within the same scope. For additional information, see References to labels.
- SQL-parameter-name
- Identifies the parameter that is the assignment target. The parameter must be specified in parameter-declaration in the CREATE 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. For information on declaring SQL variables, see compound-statement.
- expression or NULL
- Specifies the expression or value that is the source for the assignment.
Notes
Assignment rules: Assignment statements in SQL routines must conform to the SQL assignment rules. For example, the data type of the target and source must be compatible. See Assignment and comparison for assignment rules.
When a string is assigned to a fixed-length variable and the length of the string is less than the length attribute of the target, the string is padded on the right with the necessary number of single-byte or double-byte blanks. When a string is assigned to a variable and the string is longer than the length attribute of the variable, the value is truncated and a warning is returned.
If truncation of the whole part of a number occurs on assignment to a numeric variable, the value is truncated and a warning is returned.
Assignments involving SQL parameters for SQL procedures: An IN parameter can appear on the left or right side in 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 in an assignment statement. If used without first being assigned a value, the value is undefined. 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
assignments: If more than one assignment is included in the
same SET statement, all expressions are completely evaluated before
the assignments are performed. Thus, references to a target variable
in an expression are always the value of the target variable prior
to any assignment in the SET statement.
Considerations for SQLSTATE and SQLCODE SQL variables: Assignment to these variables is not prohibited. However, it is not recommended as assignment does not affect the diagnostic area or result in the activation of condition handlers. Furthermore, processing an assignment to these SQL variables causes the specified values for the assignment to be overlayed with the SQL return codes returned from executing the statement that does the assignment.
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 = NULLExample 3: Set SQL variable midinit to the first character of SQL variable midname.
SET midinit = SUBSTR(midname,1,1)