DB2 Version 9.7 for Linux, UNIX, and Windows

Assigning values to a row variable using the SET statement

Assigning values to a row variable can be done using the SET statement. A row value can be assigned to a row variable. A row field value or expression can be assigned to a row field.

Row values can be assigned to row variables using the SET statement if they are both of the same user-defined row data type.

The following is an example of how to assign a row value to a row variable of the same format:

   SET empRow = newHire;

The row value newHire has the same format as the empRow variable - the number and types of the row fields are identical:

   empRow.lastName      /* VARCHAR(128) */
   empRow.firstName     /* VARCHAR(128) */
   empRow.id            /* VARCHAR(10)  */
   empRow.hireDate      /* TIMESTAMP    */
   empRow.dept          /* VARCHAR(3)   */

   newHire.lastName     /* VARCHAR(128) */
   newHire.firstName    /* VARCHAR(128) */
   newHire.id           /* VARCHAR(10)  */
   newHire.hireDate     /* TIMESTAMP    */
   newHire.dept         /* VARCHAR(3)   */

If you attempt to assign a row value to a variable that does not have an identical format an error will be raised.

Row values can be assigned by assigning values to the individual fields in a row. The following is an example of how to assign values to the fields in the row named empRow using the SET statement:

SET empRow.lastName = 'Brown';           // Literal value assignment

SET empRow.firstName = parmFirstName;    // Parameter value of same type assignment

SET empRow.id = var1;                    // Local variable of same type assignment

SET empRow.hiredate = CURRENT_TIMESTAMP; // Special register expression assignment

SET empRow.dept = NULL;                  // NULL value assignment

Any supported field assignment can be used to initialize a row value.