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.
In general, SQL99 defines two new groups of complex data types: Abstract types (ADT) and collections.
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.
We already mentioned in Chapter 1 that object-oriented design and principles are very popular today. All modern computer languages (Java, C#, VB.NET) are strictly based on those principles. Nevertheless, we would like to remind you that object-oriented databases are hardly mainstream; their market share is marginal at best, and their future existence is still questionable. Major database vendors seem to prefer embedding some object-oriented features into their existing RDBMS engines (OORDBMS approach). We are not going to list all pros and contrast once again, but in our opinion the major advantage of traditional relational databases is they are based on strong mathematical and theoretical platforms and time-proven standards.
Collections can be of reference type, set type, and list 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.
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.
Until recently, phone numbers didn't cause database programmers and designers any troubles. It was conventional for a person to have only one home number and a work number. The situation has dramatically changed in recent years. Cell phones are now as popular as TVs, VCRs, and watches. Many people even have more than one cell phone! That is not such a big deal for a correctly designed relational database — we simply can store phone numbers in a separate table with pointers to their parent. But using an array data type would be another elegant solution in this situation.
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 supports ADT in the form of object types. Collections are supported as nested tables, varying arrays, and ref types.
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:
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.
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.
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 supports user-defined structured types, reference types, and user-defined distinct 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 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 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);