Java Reference
In-Depth Information
SQL allows the user to create user defined data types or UDTs with the CREATE
TYPE statement. There are two main kinds of data type which the user can create:
The structured data type
The DISTINCT type
Creating a structured data type
The following SQL statement creates the new data type ADDRESS and registers it
with the database as a data type, so it is available for use as the data type for a table
column or as an attribute of a structured type:
CREATE TYPE ADDRESS
(
STREET VARCHAR(40),
APT_NO INTEGER,
CITY VARCHAR(40),
STATE CHAR(2),
ZIP CHAR(5)
);
In this definition, the new type ADDRESS has five attributes, which are equivalent to
fields in a Java class. The attribute STREET is a VARCHAR(40); the attribute
APT_NO is an INTEGER; the attribute CITY is a VARCHAR(40); the attribute STATE
is a CHAR(2); and the attribute ZIP is a CHAR(5).
Creating a DISTINCT type
A DISTINCT type can be thought of as a structured type with only one attribute.
DISTINCT types are always based on another data type, which must be a predefined
type; they cannot be based on another UDT. DISTINCT types are retrieved or set
using the appropriate method for the underlying type.
For example, a Social Security Number (SSN), which is never going to be used for
arithmetic operations, and may be a good candidate for special handling, can be
created using the command. Here's an example:
CREATE TYPE SSN AS CHAR(9);
This is the equivalent SQL Server command:
EXEC sp_addtype SSN, 'VARCHAR(9)'
Now that User Defined Data Types for Address and Social Security Number have
been created, they can be used to define a new UDT, as shown here:
