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