7.7 Typed Tables

In addition to declaring some user-defined type to be the data type of a site (such as a column, a parameter, an SQL variable, a field of a row type, or an attribute of another user-defined type), SQL:1999 also allows you to declare that a user-defined type is the type of an entire table! For example, you could define a table corresponding to the movies_type type by writing a table definition that looks like the one shown in Example 7.49.

CREATE TABLE movies <b class="bold">OF movie_type</b>   ( REF IS OID SYSTEM GENERATED );

This table definition creates a new table, but it doesn't follow the usual practice of specifying a list of column definitions enclosed in parentheses (there is that " REF IS" business, but we'll get to that in Section 7.7.2). Instead, it says that the table is a typed table (sometimes called a table of type). In this table, each row is an instance of the type that we've called movie_type. Compare that table definition with the alternative approach shown in Example 7.50. This second table definition creates an ordinary table that has a single column whose data type is our UDT movie_type; in this case, each row contains an instance (possibly null) of movie_type in that single column. (By the way, in the typed table approach, there can never be a null instance of the type because SQL doesn't...