SQL Bible [Electronic resources] نسخه متنی

Object and User-Defined Data Types

You are already familiar with the concept
of complex data types (date and time), but their complexity is hidden from the
users. Now, we are going to talk about some complex data types that do require
more user involvement. Understanding these data types requires some background
in object-oriented programming and design.

SQL99

In general, SQL99 defines two new groups
of complex data types: Abstract types (ADT) and collections.

ADT

ADT provides means to explicitly define
a structural type within the larger context of the database structure. It is a
series of attribute names paired with system data types plus optional methods
to manipulate those properties. ADT can virtually be anything one can imagine
and describe by the means of the language — a car, a person, an invoice.

Collections

Collections can be of reference type,
set type, and list type.

Reference type

Reference type is a special type
through which an instance of another type can be referenced. The reference
values are read-only and unique in database system catalog.

Set and list types

These types extend a field's
capabilities by storing a collection of homogeneous data. For instance, we
could make a good use of array data type for a phone number field.

Sets and lists can consist of
built-in data types (for example, array of strings), abstract data types,
reference data types, or named row data types. The only difference between the
two collection types is that the list has to be ordered.

Oracle
9i

Oracle supports ADT in the form of object
types. Collections are supported as nested tables, varying arrays, and ref
types.

Objects

Objects in Oracle can either simply
describe an entity or have some methods (functions) to manipulate it in
addition to that. We can create an object in Oracle using this syntax:

CREATE TYPE addr_type AS OBJECT
( street VARCHAR(30), city VARCHAR(30), state CHAR(2), zip VARCHAR(10), country
VARCHAR(30) type CHAR(1));

We can now create table
CUST1 with address field of type
ADDR_TYPE:

CREATE TABLE cust1 ( id NUMBER,
name VARCHAR(30), address addr_type);

Figure 3-5 is the illustration of what we've
just created, assuming some records have been populated. Now we can access the
attributes of address field using dot notation:

Figure 3-5: Table with column
declared as an object type

address.street address.city
address.state address.zip address.country address.type

Not much benefit so far — basically we
just store multiple address fields in one column — but we'll take advantage of
our new custom type in later examples.

Nested tables

A nested table in Oracle corresponds to
SQL99 set type. It can be defined as a table that is a column of another table
with unlimited number of rows. The actual data for a nested table is physically
stored in another table, but from a user's viewpoint it looks like an integral
part of the main table.

For example, we have a parent-child
relationship between tables
CUST2 and
ADDRESS and want to eliminate it using a
nested column instead. Assuming we already have
ADDR_TYPE created, what we do next is

CREATE TYPE addr_type_tab AS
TABLE OF addr_type

This creates a new user-defined type
ADDR_TYPE_TAB of nested table type. Now,
we can create the actual table
CUST2 so that each row may contain a
table of addresses:

CREATE TABLE cust2 ( id NUMBER,
name VARCHAR(30), address addr_type_tab) NESTED TABLE address STORE AS
addr_storage;

The last line of code specifies the name
of the storage table where the rows of all the values of the nested table
reside.
Figure
3-6 shows how addresses for
CUST2 are stored in a nested
table.

Figure 3-6: Nested tables in
Oracle

Varying
arrays

VARRAY data
type is very similar to
NESTED
TABLE. The main difference is that
VARRAY has to be ordered. Also, you have
to specify the maximum number of elements for
VARRAY and don't have to indicate the
name for the storage:

CREATE TYPE addr_type_varray AS
VARRAY(50) OF addr_type; 

create table cust3 ( id NUMBER,
name VARCHAR(30), address addr_type_varray);

DB2 UDB 8.1

DB2 supports user-defined structured
types, reference types, and user-defined distinct types.

User-defined types

User-defined types are very similar to
Oracle's object types. This example shows how to create an address
structure:

CREATE TYPE addr_type AS (
street VARCHAR(30), city VARCHAR(30), state CHAR(2), zip VARCHAR(10), country
VARCHAR(30) type CHAR(1)) MODE DB2SQL

Now we can create a table that uses the
new user-defined data type
ADDR_TYPE:

CREATE TABLE cust1 ( id
INTEGER, name VARCHAR(30), address addr_type)

Reference types

Reference types are used to define
references to a row in another table (or user-structured type). The references
are similar to referential integrity constraints but do not enforce
relationships between the tables:

CREATE TABLE cust2 ( id
INTEGER, name VARCHAR(30), address REF(addr_type))

The address column of the
CUST2 table references the user-defined
ADDR_TYPE.

Distinct types

Distinct types are defined using
existing data types and have the same features of the built-in type. For
example, we can create a new data type
usd for U.S. dollars:

CREATE DISTINCT TYPE usd AS
DECIMAL(12,2) WITH COMPARISONS

Now we can use it throughout the whole
database in a way similar to the way MS SQL Server uses its
MONEY data type. For example, we can
create tables with column of a type
USD:

CREATE TABLE employee ( id
INTEGER, same VARCHAR(30), salary USD);