Assignment statement Format 2 (Assign a value to an embedded variable or a ? parameter)

Function

Assigns a value to an embedded variable or a ? parameter.

Privileges

None.

Format: Embedded-variable, or assigning to a ? parameter

SET  assign-to = assignment-value
assign-to::= {:embedded-variable [:indicator-variable] | ?-parameter}
assignment-value::= {:embedded-variable [:indicator-variable] AS data-type
       | ?-parameter AS data-type
       | LENGTH (value-expression)
       | SUBSTR (value-expression-1, value-expression-2[, value-expression-3])
       |POSITION(value-expression-1 IN value-expression-2 [FROM value-expression-3])}

Operands

Specifies the embedded variable or the ? parameter into which a value is to be assigned.

Specifies the value to be assigned.

:embedded-variable [:indicator-variable] AS data-type
The only data types that can be specified are the BLOB and the BINARY types. In the AS clause, specify the data type of the embedded variable. An error can occur if, in this operation, the actual length of the data (for a locator, the actual length of the data allocated to the locator) given by embedded-variable is greater than the maximum length of the data type specified in the AS clause.
?-parameter AS data-type
The only data types that can be specified are the BLOB and the BINARY types. In the AS clause, specify the data type of ?-parameter. An error can occur if, in this operation, the actual length of the data (for a locator, the actual length of the data allocated to the locator) given by ?-parameter is greater than the maximum length of the data type specified in the AS clause.
LENGTH (value-expression)
SUBSTR (value-expression-1, value-expression-2, [value-expression-3])
POSITION (value-expression-1 IN value-expression-2 [FROM value-expression-3])
The only data types that can be specified in value-expression for the scalar function LENGTH, in value-expression-1 for the scalar function SUBSTR, or in value-expression-2 for the scalar function POSITION are the BLOB and the BINARY types. The only items that can be specified are embedded-variable and ?-parameter For other specification methods, see the rules on the individual scalar functions.

Common rules

  1. The data type of the assignment target must be compatible with the data type of the value being assigned.
  2. If the data type of the assignment target is different from that of the value being assigned, a type conversion is performed. If they are of the same data type, the assigned value is directly assigned to the assignment target.
  3. The following items cannot be specified in a value expression specified as an assignment value:
    • Column specification
    • Component specification
    • Scalar subquery

Notes

  1. Format 2 of the assignment statement cannot be specified in an SQL routine. For specifying an assignment statement in an SQL routine, Format 1 of the assignment statement should be used.

Examples

Assign a part of the BLOB data allocated to the embedded variable (XLOC) of the BLOB locator to an embedded variable (XDATA) of the BLOB type.

SET :XDATA = SUBSTR(:XLOC AS BLOB(1M), 100, 1024)