Declare row-type variables

Row-type variables hold data from named or unnamed row types. You can define a named row variable or an unnamed row variable. Suppose you define the named row types that the following figure shows.

Figure 1. Named and unnamed row variables.

CREATE ROW TYPE zip_t
(
   z_code      CHAR(5),
   z_suffix    CHAR(4)
);

CREATE ROW TYPE address_t
(
   street      VARCHAR(20),
   city        VARCHAR(20),
   state       CHAR(2),
   zip         zip_t
);

CREATE ROW TYPE employee_t 
(
   name        VARCHAR(30),
   address     address_t
   salary      INTEGER
);

CREATE TABLE employee OF TYPE employee_t;

If you define a variable with the name of a named row type, the variable can only hold data of that row type. In the following figure, the person variable can only hold data of employee_t type.

Figure 2. Defining the person variable.

DEFINE person employee_t;

To define a variable that holds data stored in an unnamed row type, use the ROW keyword followed by the fields of the row type, as the following figure shows.

Figure 3. Use the ROW keyword followed by the fields of the row type.

DEFINE manager ROW (name        VARCHAR(30),
                    department  VARCHAR(30),
                    salary      INTEGER );

Because unnamed row types are type-checked for structural equivalence only, a variable defined with an unnamed row type can hold data from any unnamed row type that has the same number of fields and the same type definitions. Therefore, the variable manager can hold data from any of the row types in the following figure.

Figure 4. Unnamed row types.

ROW ( name         VARCHAR(30),
      department   VARCHAR(30),
      salary       INTEGER );

ROW ( french      VARCHAR(30),
      spanish     VARCHAR(30),
      number      INTEGER );

ROW ( title       VARCHAR(30),
      musician    VARCHAR(30),
      price       INTEGER );

Important: Before you can use a row type variable, you must initialize the row variable with a LET statement or SELECTINTO statement.