SET variable statement

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

To execute this statement with a row-fullselect as the right side of the assignment, the privileges held by the authorization ID of the statement must include the privileges necessary to execute the row-fullselect. See the Authorization section in "SQL queries".

To execute this statement with a cursor-value-constructor that uses a select-statement, the privileges held by the authorization ID of the statement must include the privileges necessary to execute the select-statement. See the Authorization section in "SQL queries".

Syntax

target-variable

field-reference

cursor-value-constructor

parameter-declaration

data-type

built-in-type

anchored-parameter-data-type

holdability

target-row-variable

Notes:

• ¹ statement-name cannot be specified if parameter-declaration is specified.
• ² The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
• ³ The data type must be a row type.

Description

target-variable

Identifies the target variable of the assignment. A target-variable representing the same variable must not be specified more than once (SQLSTATE 42701).

global-variable-name

Identifies the global variable that is the assignment target. The global-variable-name must identify a global variable that exists at the current server (SQLSTATE 42704).

host-variable

Identifies the host variable that is the assignment target.

parameter-marker

Identifies the parameter marker that is the assignment target.

SQL-parameter-name

Identifies the parameter that is the assignment target. The parameter must be specified in parameter-declaration in the CREATE PROCEDURE statement.

field-reference

Identifies the field within a row type value that is the assignment target.

row-variable-name

The name of a variable with a data type that is a row type.

field-name

The name of a field within the row type.

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 to be updated in the transition row. A transition-variable-name must identify a column in the subject table of a trigger, optionally qualified by a correlation name that identifies the new value (SQLSTATE 42703).

..attribute-name

Specifies the attribute of a structured type that is set (referred to as an attribute assignment). The SQL-variable-name or transition-variable-name specified must be defined with a user-defined structured type (SQLSTATE 428DP). The ..attribute-name must be an attribute of the structured type (SQLSTATE 42703). An assignment that does not involve the ..attribute-name clause is referred to as a conventional assignment.

expression

Indicates the new value of the target of the assignment. The expression is any expression of the type described in "Expressions". The expression cannot include an aggregate function except when it occurs within a scalar fullselect (SQLSTATE 42903). In the context of a CREATE TRIGGER statement, an expression can contain references to OLD and NEW transition variables. The transition variables must be qualified by the correlation-name (SQLSTATE 42702).

NULL

Specifies the null value. If the target of the assignment is a row variable, each field is assigned the null value. NULL cannot be the value in an attribute assignment unless it was specifically cast to the data type of the attribute (SQLSTATE 429B9).

DEFAULT

Specifies that the default value should be used.

In SQL procedures, the DEFAULT clause can be specified only for static SQL statements. The exception is that the DEFAULT clause can be specified when target-variable is a global variable in a dynamic SQL statement.

If target-variable is a column, the value inserted depends on how the column was defined in the table.

• If the column was defined using the WITH DEFAULT clause, the value is set to the default defined for the column (see default-clause in "ALTER TABLE").
• If the column was defined using the IDENTITY clause, the value is generated by the database manager.
• If the column was defined without specifying the WITH DEFAULT clause, the IDENTITY clause, or the NOT NULL clause, the value is NULL.
• If the column was defined using the NOT NULL clause and:
  • The IDENTITY clause is not used or
  • The WITH DEFAULT clause was not used or
  • DEFAULT NULL was used
  the DEFAULT keyword cannot be specified for that column (SQLSTATE 23502).

If target-variable is an SQL variable, the value inserted is the default, as specified or implied in the variable declaration.

If target-variable is a global variable, the value inserted is the default, as specified in the variable creation.

If target-variable is an SQL variable or an SQL parameter in an SQL procedure, a host variable, or a parameter marker, the DEFAULT keyword cannot be specified (SQLSTATE 42608).

row-fullselect

A fullselect that returns a single row with the number of columns corresponding to the number of target variables or fields in the row variable specified for assignment. The values are assigned to each corresponding target variable or field. If the result of the row fullselect is no rows, null values are assigned to the target variables in the list or, in an assignment to a row variable, a single null is assigned. In the context of a CREATE TRIGGER statement, a row-fullselect can contain references to OLD and NEW transition variables, which must be qualified by their correlation-name to specify which transition variable is to be used (SQLSTATE 42702). An error is returned if there is more than one row in the result (SQLSTATE 21000).

boolean-variable-name

Identifies an SQL variable or parameter or a global variable. The variable or parameter must be of Boolean type (SQLSTATE 428H0). The SET statement must be issued within a compound SQL (compiled) statement (SQLSTATE 428H2).

search-condition

A search condition whose result is true, false, or unknown. A result of unknown is returned as the Boolean value NULL.

TRUE

Specifies the Boolean value TRUE.

FALSE

Specifies the Boolean value FALSE.

NULL

Specifies the Boolean value NULL.

array-variable-name

Identifies an SQL variable, SQL parameter, or global variable of an array type (SQLSTATE 428H0).

[array-index]

An expression that specifies which element in the array will be the target of the assignment. For an ordinary array, the array-index must be assignable to INTEGER (SQLSTATE 22018 or 428H1). Its value must be between 1 and the maximum cardinality defined for the array and cannot be the null value (SQLSTATE 2202E).

For an associative array, the array index expression must be assignable to the index data type of the associative array (SQLSTATE 22018 or 428H1) and cannot be the null value (SQLSTATE 2202E).

target-cursor-variable

Identifies a cursor variable. The data type of target-cursor-variable must be a cursor type (SQLSTATE 42821).

cursor-variable-name

Identifies a cursor variable of the same cursor type as target-cursor-variable.

cursor-value-constructor

A cursor-value-constructor specifies the select-statement that is associated with the target variable. The assignment of a cursor-value-constructor to a cursor variable defines the underlying cursor of that cursor variable.

ASENSITIVE or INSENSITIVE

Specifies whether the cursor is asensitive or insensitive to changes. See "DECLARE CURSOR" for more information. The default is ASENSITIVE.

ASENSITIVE

Specifies that the cursor should be as sensitive as possible to inserts, updates, or deletes made to the rows underlying the result table, depending on how the select-statement is optimized. ASENSITIVE is the default.

INSENSITIVE

Specifies that the cursor does not have sensitivity to inserts, updates, or deletes that are made to the rows underlying the result table. If INSENSITIVE is specified, the cursor is read-only and the result table is materialized when the cursor is opened. As a result, the size of the result table, the order of the rows, and the values for each row do not change after the cursor is opened. The SELECT statement cannot contain a FOR UPDATE clause, and the cursor cannot be used for positioned updates or deletes.

parameter-declaration, )

Specifies the input parameters of the cursor, including the name and the data type of each parameter. Named input parameters can be specified only if select-statement is also specified in cursor-value-constructor (SQLSTATE 428HU).

parameter-name

Names the cursor parameter for use as an SQL variable within select-statement. The name cannot be the same as any other parameter name for the cursor. Names should also be chosen to avoid any column names that could be used in select-statement, since column names are resolved before parameter names.

data-type

Specifies the data type of the cursor parameter used within select-statement. Structured types, and reference types cannot be specified (SQLSTATE 429BB).

built-in-type

Specifies a built-in data type. For a more complete description of each built-in data type, see "CREATE TABLE".

anchored-parameter-data-type

Identifies another object used to determine the data type of the cursor parameter. The data type of the anchor object is bound by the same limitations that apply when specifying the data type directly.

ANCHOR DATA TYPE TO

Indicates an anchored data type is used to specify the data type.

variable-name

Identifies a local SQL variable, an SQL parameter, or a global variable. The data type of the referenced variable is used as the data type for the cursor parameter.

table-name.column-name

Identifies a column name of an existing table or view. The data type of the column is used as the data type for the cursor parameter.

distinct-type-name

Specifies the name of a distinct type. If distinct-type-name is specified without a schema name, the distinct type is resolved by searching the schemas in the SQL path.

holdability

Specifies whether the cursor is prevented from being closed as a consequence of a commit operation. See "DECLARE CURSOR" for more information. The default is WITHOUT HOLD.

WITHOUT HOLD

Does not prevent the cursor from being closed as a consequence of a commit operation.

WITH HOLD

Maintains resources across multiple units of work. Prevents the cursor from being closed as a consequence of a commit operation.

select-statement

Specifies the SELECT statement of the cursor. See "select-statement" for more information. If parameter-declaration is included in cursor-value-constructor, then select-statement must not include any local SQL variables or routine SQL parameters (SQLSTATE 42704).

statement-name

Specifies the prepared select-statement of the cursor. See "PREPARE" for an explanation of prepared statements. The target cursor variable must not have a data type that is a strongly-typed user-defined cursor type (SQLSTATE 428HU). Named input parameters must not be specified in cursor-value-constructor if statement-name is specified (SQLSTATE 428HU).

target-row-variable

Identifies the target row variable of the assignment. The data type must be of a row type.

row-expression

Specifies the new row value for the target of the assignment. It can be any row expression of the type described in "Row expression". The number of fields in the row must match the target of the assignment and each field in the row must be assignable to the corresponding field in the target of the assignment. If the source and the target values are a user-defined row type, the type names must be the same (SQLSTATE 42821).

Rules

• The number of values to be assigned from expressions, NULLs, DEFAULTs, or the row-fullselect must match the number of target-variables specified for assignment (SQLSTATE 42802).
• A SET variable statement cannot assign an SQL variable and a transition variable in one statement (SQLSTATE 42997).
• Global variables cannot be assigned inside triggers that are not defined using a compound SQL (compiled) statement, functions that are not defined using a compound SQL (compiled) statement, methods, or compound SQL (inlined) statements (SQLSTATE 428GX).
• If the value being assigned is an array resulting from an array constructor or from ARRAY_AGG, the base types of the array and of the target variable must be identical (SQLSTATE 42821).
• Use of anchored data types: An anchored data type cannot refer to the following objects (SQLSTATE 428HS): a nickname, typed table, typed view, statistical view that is associated with an expression-based index, declared temporary table, row definition that is associated with a weakly typed cursor, object with a code page or collation that is different from the database code page or database collation.
• Assignments involving cursor variables: Assignments that reference a cursor variable that set it to the value of a cursor value constructor can only be used in compound SQL (compiled) statements. Any OPEN statement using a cursor variable must occur within the same scope as the assignment (SQLSTATE 51044).

Notes

• Values are assigned to target variables according to specific assignment rules.
• Assignment statement in SQL procedures: Assignment statements in SQL procedures must conform to the SQL assignment rules. String assignments use retrieval assignment rules.
• Assignments of array elements: If the assignment is of the form SET A[idx] = rhs, where A is an array variable name, idx is an expression used as the array-index, and rhs is an expression of the same type as the array element, then:
  1. If array A is the null value, set A to the empty array.
  2. Let C be the cardinality of array A.
  3. If A is an ordinary array:
     • If idx is less than or equal to C, the value in the position identified by idx is replaced by the value of rhs.
     • If idx is greater than C, then:
       • The value in position i, for i greater than C and less than idx, is set to the null value.
       • The value in position idx is set to the value of rhs.
       • The cardinality of A is set to idx.
  4. If A is an associative array:
     • If idx matches an existing array index value, the element value with array index idx is replaced by the value of rhs.
     • If idx does not match any existing array index value, then:
       • The cardinality of A is incremented by 1
       • The new element value is set to rhs with associated array index value idx.
  5. If idx is less than or equal to C, the value in the position identified by idx is replaced by the value of rhs.
  6. If idx is greater than C, then:
     1. The value in position i, for i greater than C and less than idx, is set to the null value.
     2. The value in position idx is set to the value of rhs.
     3. The cardinality of A is set to idx.
• If a variable has been declared with an identifier that matches the name of a special register (such as PATH), the variable must be delimited to prevent unintentional assignment to the special register (for example, SET "PATH" = 1; for a variable called PATH that has been declared as an integer).
• If more than one assignment is included, each expression and row-fullselect is evaluated before the assignments are performed. Thus, references to target variables in an expression or row fullselect are always the value of the target variable before any assignment in the single SET statement.
• When an identity column defined as a distinct type is updated, the entire computation is done in the source type, and the result is cast to the distinct type before the value is actually assigned to the column. (There is no casting of the previous value to the source type before the computation.)
• To have the database manager generate a value on a SET statement for an identity column, use the DEFAULT keyword:

     SET NEW.EMPNO = DEFAULT

  In this example, NEW.EMPNO is defined as an identity column, and the value used to update this column is generated by the database manager.
• For more information about consuming values of a generated sequence for an identity column, and for information about exceeding the maximum value for an identity column, see "INSERT".

Examples

• Example 1: Set the salary column of the row for which the trigger action is currently executing to 50000.

     SET NEW_VAR.SALARY = 50000;

  Or:

     SET (NEW_VAR.SALARY) = (50000);

• Example 2: Set the salary and the commission column of the row for which the trigger action is currently executing to 50000 and 8000, respectively.

     SET NEW_VAR.SALARY = 50000, NEW_VAR.COMM = 8000;

  Or:

     SET (NEW_VAR.SALARY, NEW_VAR.COMM) = (50000, 8000);

• Example 3: Set the salary and the commission column of the row for which the trigger action is currently executing to the average salary and commission of employees in the department that is associated with the updated row.

     SET (NEW_VAR.SALARY, NEW_VAR.COMM)
        = (SELECT AVG(SALARY), AVG(COMM)
             FROM EMPLOYEE E
             WHERE E.WORKDEPT = NEW_VAR.WORKDEPT);

• Example 4: Set the salary and the commission column of the row for which the trigger action is currently executing to 10000 and the original value of salary (that is, before the SET statement was executed), respectively.

     SET NEW_VAR.SALARY = 10000, NEW_VAR.COMM = NEW_VAR.SALARY;

  Or:

     SET (NEW_VAR.SALARY, NEW_VAR.COMM) = (10000, NEW_VAR.SALARY);

• Example 5: Increase the SQL variable P_SALARY by 10 percent.

     SET P_SALARY = P_SALARY + (P_SALARY * .10)

• Example 6: Set the SQL variable P_SALARY to the null value.

     SET P_SALARY = NULL

• Example 7: Assign numbers 2.71828183 and 3.1415926 to the first and tenth elements of the array variable SPECIALNUMBERS. 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 SPECIALNUMBERS[1] = 2.71828183;
  
     SET SPECIALNUMBERS[10] = 3.14159265;

• Example 8: Given a table named SECURITY.USERS, which has a row for every user that could connect to the database, assign the current time and the authorization level to the global variables USERINFO.GV_CONNECT_TIME and USERINFO.GV_AUTH_LEVEL, respectively.

     SET USERINFO.GV_CONNECT_TIME = CURRENT TIMESTAMP,
       USERINFO.GV_AUTH_LEVEL = (
         SELECT AUTHLEVEL FROM SECURITY.USERS
           WHERE USERID = CURRENT USER)

• Example 9: Assign values to associative array variable, CAPITALS, which has been declared as the array type CAPITALSARRAY.

     SET CAPITALS['British Columbia'] = 'Victoria';
     SET CAPITALS['Alberta'] = 'Edmonton';
     SET CAPITALS['Manitoba'] = 'Winnipeg';
     SET CAPITALS['Canada'] = 'Ottawa';

  When populating the CAPITALS array, the array indexes are province, territory, and country names specified by strings and the associated array elements are capital cities, also specified by strings.
• Example 10: Assign easy to remember names as indexes for personal phone numbers stored in the array variable PHONELIST of array type PERSONAL_PHONENUMBERS.

     SET PHONELIST['Home'] = '4163053745';
     SET PHONELIST['Work'] = '4163053746';
     SET PHONELIST['Mom']  = '4164789683';