A row-type column is a column that is defined
on a named ROW type or unnamed ROW type. You use the same SQL syntax
to query a named ROW type and an unnamed row-type column.
A
query on a row-type column returns data from all the fields of the
ROW type. A field is a component data type within a ROW
type. For example, the address column of the employee table
contains the street, city, state, and zip fields.
The following query shows how to construct a query that returns all
fields of the address column. Figure 1: Query
SELECT address FROM employee
Figure 2: Query result
address ROW(102 Ruby, Belmont, CA, 49932, 1000)
address ROW(133 First, San Jose, CA, 85744, 4900)
address ROW(152 Topaz, Willits, CA, 69445, 1000))
;
To access individual fields that a column contains, use
single-dot notation to project the individual fields of the column.
For example, suppose you want to access specific fields from the address column
of the employee table. The following SELECT statement projects
the city and state fields from the address column. Figure 3: Query
SELECT address.city, address.state FROM employee
Figure 4: Query result
city state
Belmont CA
San Jose CA
Willits CA
;
You construct a query on an unnamed row-type column in
the same way you construct a query on a named row-type column. For
example, suppose you want to access data from the s_address column
of the student table in SQL syntax that creates
the student table.. You can
use dot notation to query the individual fields of a
column that are defined on an unnamed row type. The following query
shows how to construct a SELECT statement on the student table
that returns rows for the city and state fields of the s_address column. Figure 5: Query
SELECT s_address.city, s_address.state FROM student
Figure 6: Query
result
city state
Belmont CA
Mount Prospect IL
Greeley CO
;