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.

Start of change If a row-subselect is specified, see Queries for an explanation of the authorization that is required for each subselect. End of change

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

The READ privilege on the variable
Ownership of the variable
DATAACCESS authority
SYSADM authority

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

The WRITE privilege on the variable
Ownership of the variable
DATAACCESS authority
SYSADM authority

Start of change For an assignment to a transition variable, the privilege set must include at least one of the following: End of change

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

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

Syntax for SET assignment-statement

assignment-clause

target-variable

Notes:

¹ 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.
² 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.
³ array-variable-name[array-index] refers to a single element of an array.
⁴ array-variable-name as a target-variable refers to all elements of an array.

Description for SET assignment-statement

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.

Start of change If the statement is issued in a basic trigger, each target variable must be a transition variable. End of change

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.
array-variable-name: Specifies an array variable. array-variable-name as a target-variable refers to all elements of an array.

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,…)

NULL

Specifies the null value and can only be specified for host variables that have an associated indicator variable.

DEFAULT

FL 500Specifies 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.

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.

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.

array-variable-name [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-name

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-name[array-index]=expression

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

Example 1: Set the host variable HVL to the value of the CURRENT PATH special register.

   SET :HVL = CURRENT PATH;

Example 2: Set the host variable PATH to the contents of the SQL PATH special register, the host variable XTIME to the local time at the current server, and the host variable MEM to the current member of the data sharing environment.

   SET :SERVER = CURRENT PATH,
       :XTIME = CURRENT TIME,
       :MEM = CURRENT MEMBER;

Example 3: Set the host variable DETAILS to a portion of a LOB value, using a LOB expression with a LOB locator to refer the extracted portion of the value.

   SET :DETAILS = SUBSTR(:LOCATOR,1,35);

If the LOB data that is specified by the LOB locator LOCATOR is in a different encoding scheme from the value of the ENCODING bind option, and you want to avoid LOB materialization and character conversion, use the following statement instead of the SET statement:

   SELECT SUBSTR(:LOCATOR,1,35)
      INTO :DETAILS
      FROM SYSIBM.SYSDUMMYU;

Example 4: Set host variable HV1 to the results of external function CALC_SALARY and host variable HV2 to the value of special register CURRENT PATH. Use an indicator value with HV1 in case CALC_SALARY returns a null value.

   SET (:HV1:IND1, :HV2) =
       (CALC_SALARY(:HV3, :HF4), CURRENT PATH);

Example 5: Assume that you want to create a before trigger that sets the salary and commission columns to default values for newly inserted rows in the EMPLOYEE table and that you will define the trigger only with NEW in the REFERENCING clause. Assign the default values to the SALARY and COMMISSION columns.

   SET (SALARY, COMMISSION) = (50000, 8000);

Example 6: Assume that you want to create a before trigger that detects any commission increases greater than 10% for updated rows in the EMPLOYEE table and limits the commission increase to 10%. You will define the trigger with both OLD and NEW in the REFERENCING clause. Limit an increase to the COMMISSION column to 10%.

   SET NEWROW.COMMISSION = 1.1 * OLDROW.COMMISSION;

Example 7: Suppose that the associative array variable CANADACAPITALS has array type CAPITALSARRAY. Use SET assignment-statement statements to assign values to CANADACAPITALS.

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.

Example 8: Suppose that the associative array variables CANADACAPITALSA and CANADACAPITALSB have array type CAPITALSARRAY. The following SET assignment-statement statements have been used to assign values to CANADACAPITALSA.

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;

Example 9: Suppose that P_PHONENUMBERS SQL array variable is defined as an ordinary array. Set P_PHONENUMBERS to an array of fixed numbers.

SET P_PHONENUMBERS = ARRAY[9055553907, 4165554213, 4085553678];

Example 10: Set the SQL array variable P_PHONENUMBERS to an array of numbers that are retrieved from the PHONENUMBER table.

SET P_PHONENUMBERS =
 ARRAY [SELECT NUMBER
 FROM PHONENUMBERS
 WHERE EMPID = 624];

Example 11: Suppose that no values have been assigned to SQL array variable P_PHONENUMBERS. Assign the value of SQL variable P_MYNUMBER to the first and tenth elements of 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;