Syntax
CREATE TYPE typeName
EXTERNAL NAME singleQuotedString
LANGUAGE JAVA
The type name is composed of an optional schemaName and a
SQLIdentifier. If a
schemaName is not provided, the current schema is the default schema. If
a qualified type name is specified, the schema name cannot begin with SYS.
If the Java class specified by the does not implement
java.io.Serializable, or if it is not public and visible on the
classpath, raises an
exception when preparing statements which refer to the UDT.
A UDT cannot be cast explicitly to any other type, and no other type can be
cast to a UDT.
A UDT has no ordering. This means that you cannot compare and sort UDTs. You
cannot use them in expressions involving the <,
=, >, IN, BETWEEN, and LIKE operators. You
cannot use UDTs in aggregates, DISTINCT expressions, and GROUP/ORDER BY clauses.
You cannot build indexes on them.
You can use subtypes in UDTs. That is, if you use the CREATE TYPE statement
to bind a class named C to a UDT, you can populate that UDT value with an
instance of any subclass of C.
Using user-defined types
You can create tables and views with columns that have UDTs. For example:
CREATE TABLE order
(
orderID INT GENERATED ALWAYS AS IDENTITY,
customerID INT REFERENCES customer( customerID ),
totalPrice typeSchema.price
);
Although UDTs have no natural order, you can use generated columns to provide
useful sort orders:
ALTER TABLE order
ADD COLUMN normalizedValue DECIMAL( 31, 5 ) GENERATED ALWAYS AS
( convert( 'EUR', TIMESTAMP('2005-01-01 09:00:00'), totalPrice ) );
CREATE INDEX normalizedOrderPrice ON order( normalizedValue );
You can use factory functions to construct UDTs. For example:
INSERT INTO order( customerID, totalPrice )
VALUES ( 12345,
makePrice( 'USD',
CAST( 9.99 AS DECIMAL( 31, 5 ) ),
TIMESTAMP('2009-10-16 14:24:43') ) );
Once a UDT column has been populated, you can use it in other INSERT and
UPDATE statements. For example:
INSERT INTO backOrder SELECT * from order;
UPDATE order SET totalPrice = ( SELECT todaysDiscount FROM discount );
UPDATE order SET totalPrice = adjustForInflation( totalPrice );
Using functions, you can access fields inside UDTs in a SELECT statement:
SELECT getCurrencyCode( totalPrice ) from order;
You can use JDBC API setObject() and getObject() methods to
store and retrieve values of UDTs. For example:
PreparedStatement ps = conn.prepareStatement( "SELECT * from order" );
ResultSet rs = ps.executeQuery();
while( rs.next() )
{
int orderID = rs.getInt( 1 );
int customerID = rs.getInt( 2 );
Price totalPrice = (Price) rs.getObject( 3 );
...
}