9.2.1.CREATE TYPE Statement

CREATE TYPE type_name
   [ UNDER type_name ]
   [ LANGUAGE language_name [ EXTERNAL NAME literal ] ]
   [ AS (type_member, ...) ]
   [ type_option [type option ] .... ]
   [ method_specification, ... ]

type_name :
  [ [ identifier  .] [ identifier ] . ] identifier

type_member :
  identifier data_type [ DEFAULT literal ] [ EXTERNAL NAME string ] [ EXTERNAL TYPE string ]
    [ __SOAP_TYPE literal ] [ __SOAP_NAME literal ]

type_option : SELF AS REF | TEMPORARY | SOAP_TYPE literal

method_specification : original_method_specification | overriding_method_specification

original_method_specification :
  [ STATIC | INSTANCE ] METHOD identifier ( [ decl_parameter, ... ] )
    RETURNS data_type [ method_characteristics ]
  |
  CONSTRUCTOR METHOD identifier ( [ decl_parameter, ... ] )  [ method_characteristics ]

overriding_method_specification :
        OVERRIDING [ INSTANCE ] METHOD identifier ( [ decl_parameter, ... ] )
    RETURNS data_type

method_characteristics :
  [ EXTERNAL TYPE literal ] [ EXTERNAL NAME string | EXTERNAL VARIABLE NAME string ]

language_name : SQL | CLR | JAVA

The CREATE TYPE statements declares a user defined type. Generally speaking the user defined types can be in two states: forward-referenced, declared and instantiable.

A type is in forward reference state if it's name is quoted in some other CREATE TYPE statement (as a supertype, member type or a method parameter type or return type). When a type is in forward reference state it's instances can be copied, passed as parameter values and returned by functions, but it cannot be instantiated, no type members can be accessed and no type methods can be called. Forward references are temporary objects and they disappear at server shutdown.

A type moves to the declared state when a CREATE TYPE is executed for it. In that state type methods can be called, type members can be accessed, but the type cannot be instantiated.

A type goes into instantiable state from declared state when it has no supertype or it's supertype is also in instantiable state. The server tries to move the defined types to instantiable state on every CREATE TYPE statement.

Normally the type definitions are stored into the DB.DBA.SYS_USER_TYPES system table.

This has the following layout:

CREATE TABLE SYS_USER_TYPES
(
 UT_NAME VARCHAR,
 UT_PARSE_TREE LONG VARCHAR,
 UT_ID integer identity,
 UT_MIGRATE_TO integer,
 primary key (UT_NAME));
UT_NAME - the fully qualified user defined type name.
UT_PARSE_TREE - the user defined type definition (in machine readable form).
UT_ID - the ID of the type (used in persisting type instances to/from network/storage).
UT_MIGRATE_TO - reserved for future use.

If a TEMPORARY type_option is specified, the CREATE TYPE does not write the type definition into the table - it declares the type only in server's memory. TEMPORARY types are not persistable. They disappear when the server is restarted. A TEMPORARY type cannot be a supertype or a subtype of a non-TEMPORARY type.

The SELF AS REF option directs the server to return a reference to the type's instance when instantiating the type, as opposed to returning the instance itself. The references are explained in more detail in the NEW operator.

[Note] Note:

The CREATE TYPE is an autocommitting statement.

Example9.3.Creating User Defined Types

This example creates a SQL implemented user defined type UDT_TEST with no supertype. It has two members : A and B, two constructor methods, a static method _ADD, an ADDIT method taking either zero or two arguments and an instance method SUB_IT.

create type UDT_TEST
  as (A integer default 1, B integer default 2)
  CONSTRUCTOR METHOD UDT_TEST(_a integer, _b integer),
  CONSTRUCTOR METHOD UDT_TEST(),
  STATIC METHOD _ADD(_xx integer, _yy integer) returns integer specific DB.DBA.static_add,
  METHOD ADDIT() returns integer,
  METHOD ADDIT(c integer) returns integer,
  METHOD SUB_IT () returns integer;

This creates a subtype of UDT_TEST named UDT_TEST_SUB. UDT_TEST_SUB extends the static method _ADD of UDT_TEST so it can also take 4 arguments, overrides the method ADDIT from UDT_TEST and defines a new instance method MULTIPLY_IT.

create type UDT_TEST_SUB under UDT_TEST
  as (C integer default 12, _D integer default 32)
  STATIC METHOD _ADD(_xx integer, _yy integer, _zz integer, _qq integer) returns integer,
  OVERRIDING METHOD ADDIT() returns integer,
  METHOD MULTIPLY_IT () returns integer;

This is a SQL wrapper for a public Java type testsuite_base (see testsuite_base.java).

create type testsuite_base language java external name 'testsuite_base'
as (
    protected_I integer external name 'protected_I' external type 'I',
    private_I integer external name 'private_I' external type 'I',
    sZ smallint external name 'Z' external type 'Z',
    sfalseZ smallint external name 'falseZ' external type 'Z',
    sB smallint external name 'B' external type 'B',
    sC smallint external name 'C' external type 'C',
    sS smallint external name 'S' external type 'S',
    sI int external name 'I' external type 'I',
    sJ int external name 'J' external type 'J',
    sF real external name 'F' external type 'F',
    sD double precision external name 'D' external type 'D',
    sL any external name 'L' external type 'Ljava/lang/Short;',
    sAI any external name 'AI' external type '[I',
    sAL any external name 'AL' external type '[Ljava/lang/Short;',
    sstr nvarchar external name 'str' external type 'Ljava/lang/String;',
    sdat datetime external name 'dat' external type 'Ljava/util/Date;',

    tF real external name 'F',
    "F" real,

    non_existant_var integer external name 'non_existant_var' external type 'I'
   )
    static method get_static_ro_I ()
        returns integer external type 'I' external variable name 'static_ro_I',
    static method get_static_I ()
        returns integer external type 'I' external variable name 'static_I',
    static method get_protected_static_I ()
        returns integer external type 'I' external variable name 'protected_static_I',
    static method get_private_static_I ()
        returns integer external type 'I' external variable name 'private_static_I',

    static method test_bool (x integer external type 'I')
        returns smallint external type 'Z' external name 'test_bool',

    constructor method testsuite_base (),
    constructor method testsuite_base (i integer external type 'I'),

    static method echoDouble (a double precision external type 'D')
        returns any external type 'Ljava/lang/Double;' external name 'echoDouble',
    static method getObjectType (a any external type 'Ljava/lang/Object;')
        returns varchar external type 'Ljava/lang/String;' external name 'getObjectType',
    static method echoThis (a testsuite_base external type 'Ltestsuite_base;')
        returns integer external type 'I' external name 'echoThis',
    static method static_echoInt (a integer external type 'I')
        returns integer external type 'I' external name 'static_echoInt',

    static method change_it (a testsuite_base)
        returns integer external type 'I' external name 'change_it',

    method "overload_method" (i integer external type 'I')
        returns integer external type 'I',

    method echoInt (a integer external type 'I')
        returns integer external type 'I' external name 'echoInt',

    method echoInt (a double precision external type 'D')
        returns integer external type 'I' external name 'echoInt',

    method protected_echo_int (a integer external type 'I')
        returns integer external type 'I' external name 'protected_echo_int',

    method private_echo_int (a integer external type 'I')
        returns integer external type 'I' external name 'private_echo_int',

    method "echoDbl" (a double precision)
        returns double precision,

    method non_existant_method (a integer external type 'I')
        returns integer external type 'I' external name 'non_existant_method',

    static method non_existant_static_var (a integer external type 'I')
        returns integer external type 'I' external variable name 'non_existant_static_var';