Initialize a row variable

To perform operations on existing fields in a row-type column, you must first initialize the row variable with the field values.

To perform this initialization, select the existing fields of the row-type column into a row variable with the SELECT statement, as follows:
  • Specify the row-column name in the select list of the SELECT statement.
  • Specify the row host variable in the INTO clause of the SELECT statement.
  • Specify the table or view name, not the collection-derived table clause, in the FROM clause of the SELECT statement.
Suppose you create the tab_unmrow and tab_nmrow tables with the statements in the following figure.
Figure 1: Sample tables with row-type columns
EXEC SQL create table tab_unmrow
(
   area integer,
   rectangle row(
            x integer,
            y integer,
            length integer,
            width integer)
);

EXEC SQL create row type full_name
(
   fname char(15),
   mi char(2),
   lname char(15)
);
EXEC SQL create table tab_nmrow
( 
   emp_num integer,
   emp_name full_name
);
The following code fragment initializes a typed row host variable called a_rect with the contents of the rectangle column in the row whose area column is 1234:
EXEC SQL BEGIN DECLARE SECTION;
   row (
         x integer,
         y integer,
         length integer,
         width integer
      ) a_rect;
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate row :a_rect;
EXEC SQL select rectangle into :a_rect from tab_unmrow
   where area = 1234;

When you use a typed row host variable, the data types of the row-type column (the field types) must be compatible with the corresponding data types of the typed row host variable. The SELECT statement in the preceding code fragment successfully retrieves the rectangle column because the a_rect host variable has the same field types as the rectangle column.

The following SELECT statement fails because the data types of the fields in the emp_name column and the a_rect host variable do not match:
/* This SELECT generates an error */
EXEC SQL select emp_name into :a_rect from tab_nmrow; 
You can select any row into an untyped row host variable. The following code fragment uses an untyped row host variable to access the emp_name and rectangle columns that Sample tables with row-type columns defines:
EXEC SQL BEGIN DECLARE SECTION;
   row an_untyped_row;
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate row :an_untyped_row;
EXEC SQL select rectangle into :an_untyped row 
   from tab_unmrow
   where area = 64;
⋮

EXEC SQL select emp_name into :an_untyped_row 
   from tab_nmrow
   where row{'Tashi'} in (emp_name.fname);

Both SELECT statements in this code fragment can successfully retrieve row-type columns into the an_untyped_row host variable. However, does not perform type checking on an untyped row host variable because its elements do not have a predefined data type.

After you have initialized the row host variable, you can use the collection-derived table clause to select or update existing fields in the row. For more information, see the following sections.