SET assignment-statement statement
The SET assignment-statement statement assigns values to variables and array elements.
Invocation for SET assignment-statement
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared if the targets of all assignments are global variables.
Authorization for SET assignment-statement
The privileges that are held by the privilege set must include those required to execute any of the expressions.
If a row-subselect is specified, see Queries for an explanation of the authorization that is required for each subselect.
If a global variable or an element of an array global variable is referenced, the privilege set must include at least one of the following:
- The READ privilege on the variable
- Ownership of the variable
- DATAACCESS authority
- SYSADM authority
For an assignment to a global variable or an element of an array global variable, the privilege set must include at least one of the following:
- The WRITE privilege on the variable
- Ownership of the variable
- DATAACCESS authority
- SYSADM authority
For an assignment to a transition variable, the privilege set must include at least one of the following:
- The UPDATE privilege on the table or view on which the trigger that contains the assignment statement is defined
- The UPDATE privilege on the column corresponding to the transition variable to be assigned a value
- Ownership of the table or view on which the trigger that contains the assignment statement is defined
- DBADM authority on the database that contains the table on which the trigger that contains the assignment statement is defined
- DATAACCESS authority
- SYSADM authority
Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the package. If the statement is dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke) and is summarized in DYNAMICRULES behaviors and authorization checking. For more information on these behaviors, including a list of the DYNAMICRULES bind option values that determine them, see Authorization IDs and dynamic SQL.
Syntax for SET assignment-statement
- 1 DEFAULT must only be specified when the corresponding target is a global variable or a transition variable. If DEFAULT is specified for a transition variable in an advanced trigger, then all target variables must be transition variables, and all source values must be specified with the DEFAULT keyword.
- 2 The number of source value specifications (expression, NULL, or DEFAULT) on the right side of the equal sign must match the number of target specifications on the left side of the statement.
Description for SET assignment-statement
- array-variable [array-index]
- Specifies an array element that is the target of the assignment.
An array element must not be specified as the target for an assignment if common-table-expression is also specified in the statement.
- array-variable
- Specifies an array variable.
- [array-index]
- An expression that specifies which element in the array is the
target of the assignment.
For an ordinary array, the array index expression must be castable to INTEGER, and must not be the null value. The index value must be between 1 and the maximum cardinality that is defined for the array.
For an associative array, the array index expression must be castable to the index data type of the associative array, and must not be the null value.
array-index must not be:
- An expression that references the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special register
- A nondeterministic function
- A function that is defined with EXTERNAL ACTION
- A function that is defined with MODIFIES SQL DATA
- A sequence expression
The value that is to be assigned to an array element must be specified immediately following the array element. For example:
array-variable[array-index]=expression
- expression
- Specifies the value that is to be assigned
to the corresponding assignment target. The expression is any expression
of the type described in Expressions. All expressions are evaluated before
any result is assigned to a target. If an expression refers to a variable
or array element that is used in the list of assignment targets, the
value of the variable or array element in the expression is the value
of the variable or array element prior to any assignments.Each assignment to a target is made according to the assignment rules described in Assignment and comparison. When the target variables and expressions are in the following form, the first value is assigned to the first target variable in the list, the second value is assigned to the second target variable in the list, and so on.
(target-variable,target-variable,…)=(expression,expression,…)
- DEFAULT
- Specifies that the new value for the variable is the initial default value for a global variable
or the default value of a column that corresponds to a transition variable. DEFAULT can only be
assigned to a global variable, or to a transition variable. If DEFAULT is specified for a transition
variable in an advanced trigger, then all target variables must be transition variables, and all
source values must be specified with the DEFAULT keyword.
A ROWID column must not be set to the DEFAULT keyword.
- NULL
- Specifies the null value and can only be specified for host variables that have an associated indicator variable.
- VALUES
- Specifies the values that are to be assigned to the corresponding
assignment targets. When more than one value is specified, the values
must be enclosed in parentheses. Each value can be an expression or
NULL, as previously described. The following syntaxes are equivalent:
- (target-variable, target-variable) = (VALUES(expression, NULL))
- (target-variable, target-variable) = (expression, NULL)
A parameter marker must not be specified.
- row-subselect
- A subselect that returns a single row. The number of columns corresponds to the number of target variables that are specified for assignment. Each result column value is assigned to the corresponding variable. If the result of the row subselect is no rows, then null values are assigned. An error is returned if there is more than one row in the result. row-subselect can be specified only in the outermost subselect within SQL PL. row-subselect must not be specified in a basic trigger.
- target-variable
- Identifies one or more targets for the assignment of values. The number of targets must equal
the number of values that are to be assigned.
If the statement is issued in a basic trigger, each target variable must be a transition variable.
The value that is to be assigned to each target variable can be specified immediately following the variable. For example:
variable=expression, variable=expression
Alternatively, sets of parentheses can be used to specify all of the target variables, and then all of the values. For example:
(variable,variable)=(expression,expression)
The data type of each variable in the variable list must be compatible with its corresponding result column. Each assignment to a target-variable is made in sequence through the list, according to the rules described in Assignment and comparison.
The value 'W' is assigned to the SQLWARN3 field of the SQLCA if the number of targets is less than the number of result column values.
If an error occurs on any assignment, the value is not assigned to the target, and no more values are assigned to the specified targets. Any values that have already been assigned remain assigned.
- global-variable-name
- Identifies the global variable that is the assignment target.
- host-variable-name
- Identifies the host variable that is the assignment target. For LOB output values, the target can be a regular host variable (if it is large enough), a LOB locator variable, or a LOB file reference variable.
- SQL-parameter-name
- Identifies the parameter that is the assignment target.
- SQL-variable-name
- Identifies the SQL variable that is the assignment target. SQL variables must be declared before they are used.
- transition-variable-name
- Identifies the column that is to be updated in the transition row. A transition variable name
must identify a column in the subject table of a trigger, and is optionally qualified by a
correlation name that identifies the new value.
transition-variable-name must not correspond to a begin column or end column of a BUSINESS_TIME period, and must not be specified if the statement contains a period-clause.
Notes for SET assignment-statement
- Multiple assignments:
- If more than one assignment is included in the same SET statement, all expressions and row-subselects are completely evaluated before the assignments are performed. Thus, references to a target variable in an expression or row-subselect are always the value of the target variable prior to any assignment in the SET statement.
- LOBs assignments:
- Normally, you use LOB locators to assign and retrieve data from LOB columns. However, because of compatibility rules, you can also use LOB locators to assign data to targets with other data types. For more information on using locators, see Saving storage when manipulating LOBs by using LOB locators.
- Default encoding scheme:
- The default encoding scheme for the data is the value in the bind option ENCODING, which is the option for application encoding. If this statement is used with functions such as LENGTH or SUBSTRING that are operating on LOB locators, and the LOB data that is specifies by the locator is in a different encoding scheme from the ENCODING bind option, LOB materialization and character conversion occur. To avoid LOB materialization and character conversion, select the LOB data from the SYSIBM.SYSDUMMYA, SYSIBM.SYSDUMMYE, or SYSIBM.SYSDUMMYU sample table.
Examples for SET assignment-statement
SET :HVL = CURRENT PATH;
SET :SERVER = CURRENT PATH,
:XTIME = CURRENT TIME,
:MEM = CURRENT MEMBER;
SET :DETAILS = SUBSTR(:LOCATOR,1,35);
SELECT SUBSTR(:LOCATOR,1,35)
INTO :DETAILS
FROM SYSIBM.SYSDUMMYU;
SET (:HV1:IND1, :HV2) =
(CALC_SALARY(:HV3, :HF4), CURRENT PATH);
SET (SALARY, COMMISSION) = (50000, 8000);
SET NEWROW.COMMISSION = 1.1 * OLDROW.COMMISSION;
SET CANADACAPITALS['British Columbia'] = 'Victoria';
SET CANADACAPITALS['Alberta'] = 'Edmonton';
SET CANADACAPITALS['Manitoba'] = 'Winnipeg';
SET CANADACAPITALS['Ontario'] = 'Toronto';
SET CANADACAPITALS['Nova Scotia'] = 'Halifax';
In the CANADACAPITALS array, the array index values are province names, and the associated array element values are the names of the corresponding capital cities. The order in which values are assigned to associative array elements does not matter. The elements of an associative array are stored in the array in ascending order of the associated array index values.
SET CANADACAPITALSA['British Columbia'] = 'Victoria';
SET CANADACAPITALSA['Alberta'] = 'Edmonton';
SET CANADACAPITALSA['Manitoba'] = 'Winnipeg';
SET CANADACAPITALSA['Ontario'] = 'Toronto';
SET CANADACAPITALSA['Nova Scotia'] = 'Halifax';
Use a single SET assignment-statement statement to assign all of the values that are in CANADACAPITALSA to CANADACAPITALSB.
SET CANADACAPITALSB = CANADACAPITALSA;
SET P_PHONENUMBERS = ARRAY[9055553907, 4165554213, 4085553678];
SET P_PHONENUMBERS =
ARRAY [SELECT NUMBER
FROM PHONENUMBERS
WHERE EMPID = 624];
SET P_PHONENUMBERS[1] = P_MYNUMBER;
SET P_PHONENUMBERS[10] = P_MYNUMBER;