 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 beyond the operators defined for its base type. For example, you might create a distinct type namedADDRESS, based on the built-in type VARCHAR(50). You might then create a user-defined function (UDF) TIMEZONE(ADDRESS) that computes the time zone of a given address. Depending on the database, the UDF TIMEZONE function would be written in a proprietary language or a commercial language like Java and C++. Typically, UDFs need to be registered in the database in which they will be used.
In an object-relational system, it is important to capture not only the state of stored objects but their behavior as well. The behavior of a distinct type is captured by the functions that are defined on that type. For example, unary functions such asTIMEZONE(ADDRESS) and ZIPCODE(ADDRESS) might be considered to be "methods" that define the behavior of the ADDRESS type. Therefore, it’s possible to enforce the integrity of the data and the behavior of the data as a resource that can be shared by multiple applications.
Comparing Apples to Apples
With the use of User Defined Types and Functions, we can place certain business rules directly into the database. In the above example, we have not set up any UDFs to handle the division and equals operation. The database can then enforce how apples and oranges are manipulated, and if it’s legal to compare apples.
# # #