of SQL are usually not sufficient for multimedia database applications. It is apparent that the requirements of these applications are so diverse that they could not be satisfied by a set of predefined language extensions. This is why ANSI’s SQL standards committee and database vendors have added facilities to let users define new data types and functions of their own.
The built-in SQL data types such as Number, Decimal and Double are often used to represent data that has a specialized meaning. For example, a column defined with a Decimal data type may contain amounts in US Dollars. Another column defined with a Decimal column may contain amounts in Japanese Yen. Similarly, a column defined with a Double (floating point) data type may contain measurements in feet, and another column with the same data type may contain measurements in meters.
The database designer may have some rules in mind about the kinds of operations that make sense on various types of data. For example, it may make sense to add two US Dollar amounts, but it is probably an error to add a US Dollar amount to a Yen amount. It may make sense to multiply a dollar amount by an integer or floating point number, but it is probably an error to multiply a US Dollar amount by another US Dollar amount.
If you use only the built-in data types, a database has no way of knowing what specialized rules may apply to the data. Therefore, many databases allow you to create new data types of your own and specify the operations that apply to them. In most databases, a user-defined data type is called a "type." Each type shares a common representation with one of the built-in types, called its "base type," but it has its own set of valid operations.
The following statements create two types named DOLLARS and YEN, each based on the built-in Decimal type. The phrase WITH COMPARISONS is a DB2 UDB extension that means that two dollar values may be compared, and that two Yen values may be compared. However, the database can prevent a dollar value from successfully being compared with a Yen value or with an ordinary Decimal value.
The ANSI SQL99 (SQL3) syntax for creating a distinct type is:
When you create a distinct type, DB2 UDB generates casting functions that convert a value between the distinct type and its base type. For example, creation of theDOLLARS type causes creation of the cast functions DOLLARS(DECIMAL), which returns DOLLARS, and DECIMAL(DOLLARS), which returns DECIMAL(10,2).
When a distinct type is first created in DB2 UDB, the only operators that apply to it are comparisons between two values of the same distinct type. For example, ifSALARY and BONUS are data columns of type DOLLARS, then SALARY = BONUS and SALARY > BONUS are valid predicates, but SALARY + BONUS and SALARY * BONUS are not valid because no arithmetic operations have been defined on the DOLLARS type.
It is easy to specify which of the operators of the base type are meaningful for the distinct type. Each built-in operator such as "+" is implemented by a function with the same name as the operator. To make an operator apply to the distinct type, you can create a new function with the same name as the operator that accepts parameters and/or returns results of your distinct type. The operator function can be "sourced" on the built-in operator function provided by DB2 UDB, which means that it relies on the DB2 UDB function for its implementation. The following statements define the operator "+" for two dollar values, and the operator "*" for an integer and a dollar value:
After executing these statements, ifSALARY and BONUS are columns of type DOLLARS, then SALARY + BONUS and 2 * SALARY are valid, but SALARY * BONUS is invalid because no multiplication operator has been defined between two dollar values.
By creating sourced functions, you can specify exactly the set of operators that are meaningful for the distinct type. You can then rely on the database to catch any type-related errors in a query or application program.
Of course, you may wish the distinct type to have some additional functionality b