Assignment statement Format 1 (Assign a value to an SQL variable or SQL parameter)
Function
Assigns a value to an SQL variable or SQL parameter.
Privileges
Format: Assigning a value to an SQL variable or an SQL parameter
SET assign-to = assignment-value
assign-to:: = {[statement-label.]SQL-variable-name
| [[authorization-identifier.]routine-identifier.]SQL-parameter-name
| [statement-label.]SQL-variable-name..attribute-name [..attribute-name]...
| [[authorization-identifier.]routine-identifier.]SQL-parameter-name..attribute-name [..attribute-name]...}
assignment-value:: = {value-expression|NULL|DEFAULT} |
Operands
- assign-to:: = { [statement-label.] SQL-variable-name
| [ [authorization-identifier.] routine-identifier.] SQL-parameter-name
| [statement-label.] SQL-variable-name.. attribute-name [.. attribute-name]...
| [ [authorization-identifier.] routine-identifier.] SQL-parameter-name .. attribute-name [.. attribute-name]...}
Specifies the SQL variable or SQL parameter into which a value is assigned, or the attribute name of an SQL variable or the attribute name of an SQL parameter.
If authorization-identifier is specified in an SQL procedure statement in a public procedure definition or a public function definition, specify upper-case PUBLIC enclosed in double quotation marks (") as the authorization identifier.
- assignment-value:: = {value-expression | NULL | DEFAULT}
Specifies the value to be assigned.
Common rules
- In assign-to, an SQL parameter mode for which the input/output mode (parameter mode) specified in SQL-routine is IN cannot be specified. The SQL parameter name of a function cannot be specified.
- The data type of assign-to must be compatible with the data type of assignment-value.
- If the data type of assign-to is different from that of assignment-value, a type conversion is performed. If the two data types are the same, the assignment value is directly assigned to assign-to.
- If assignment-value is character data, assign-to and assignment-value must use the same character set. However, if assignment-value is a string constant and if assign-to and assignment-value use different character sets, assignment-value will automatically be converted to the character set of assign-to.
- A subquery cannot be specified in a value expression specified in assignment-value.
- IF DEFAULT is specified in assignment-value, the default for the SQL value to be assigned is assigned. For details about declaring the default for an SQL variable, see Compound statement (Execute multiple statements) in Chapter 7. If an SQL parameter is specified in assign-to and DEFAULT is specified in assignment-value, the null value is assigned to the SQL parameter for the target of assignment. If a column name qualified with an old or new value correlation name is specified in the assignment target for the assignment statement (format 1) specified in a trigger SQL statement, and DEFAULT is specified in assignment-value, the default for the column in the assignment target is assigned. However, if the default for the column qualified with the old and new value correlation name of the assignment target is CURRENT_TIMESTAMP for which USING BES is specified, DEFAULT cannot be specified in assignment-value. For details about the trigger SQL statement, see CREATE TRIGGER (Define a trigger) in Chapter 3.
Notes
- Format 1 of the assignment statement can only be specified in an SQL routine. For specifying an assignment statement in a routine other than an SQL routine, specify Format 2 of the assignment statement.