Select row-type data

This section describes how to query data that is defined as row-type data. A ROW type is a complex type that combines one or more related data fields.

The two kinds of ROW types are as follows:

Named ROW type: A named ROW type can define tables, columns, fields of another row-type column, program variables, statement local variables, and routine return values.
Unnamed ROW type: An unnamed ROW type can define columns, fields of another row-type column, program variables, statement local variables, routine return values, and constants.

The examples used throughout this section use the named ROW types zip_t, address_t, and employee_t, which define the employee table. The following figure shows the SQL syntax that creates the ROW types and table.

Figure 1. SQL syntax that creates the ROW types and table.

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

The named ROW types zip_t, address_t and employee_t serve as templates for the fields and columns of the typed table, employee. A typed table is a table that is defined on a named ROW type. The employee_t type that serves as the template for the employee table uses the address_t type as the data type of the address field. The address_t type uses the zip_t type as the data type of the zip field.

The following figure shows the SQL syntax that creates the student table. The s_address column of the student table is defined on an unnamed ROW type. (The s_address column could also have been defined as a named ROW type.)

Figure 2. SQL syntax that creates the student table.

CREATE TABLE student 
(
s_name      VARCHAR(30),
s_address   ROW(street VARCHAR (20), city VARCHAR(20),
              state CHAR(2), zip VARCHAR(9)),
              grade_point_avg DECIMAL(3,2)
)