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