Initialize a row variable

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.

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;

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);