CREATE TYPE (Define type)

Function

CREATE TYPE defines an abstract data type.

Privileges

Owner of a schema

A user can define abstract data types that will be owned by that user.

Format

CREATE TYPE [authorization-identifier.] data-type-identifier
            [subtype-clause]
            [default-constructor-option]
            [member-list]
subtype-clause::=UNDER [authorization-identifier.] data-type-identifier
default-constructor-option::=CONSTRUCTOR {PRIVATE|PROTECTED|PUBLIC}

member-list::=(member[,member]...)
  member::={attribute-definition|routine-declaration}
     attribute-definition::=[encapsulation-level]
                          attribute-name data-type [NO SPLIT]
     encapsulation-level::= {PRIVATE|PROTECTED|PUBLIC}
     routine-declaration::=[encapsulation-level] routine-body
     routine-body::={function-body|procedure-body}

Operands

authorization-identifier
Specifies the authorization identifier of the owner of the abstract data type that is being defined.
data-type-identifier
Specifies a name for the abstract data type being defined.

A subtype clause is specified when what is being defined is a subtype that will inherit a specified abstract data type. The subtype clause is used to specify the authorization identifier and data type identifier of the abstract data type that will become the super-type for the abstract data type being defined.

Specifying a subtype clause causes all the attributes and routines defined in the super-type to be inherited by the abstract data type that is being defined.

authorization-identifier.
Specifies the authorization identifier of the owner of the super-type abstract data type.
If the authorization identifier is omitted, the default authorization identifier does not have an abstract data type of the same name, and an abstract data type of the same name exists in the 'MASTER' authorization identifier, that abstract data type is assumed to have been specified.
data-type-identifier
Specifies the super-type abstract data type.

Specifies the encapsulation level for the default constructor function. The default is PRIVATE.

For the default constructor function, a function that has the same name as the defined abstract data type is defined. The default constructor function takes no arguments. This function returns values of an abstract data type in which null values are set for the attributes of the abstract data type.

PRIVATE
Specifies that the default constructor function of the abstract data type can be used only in this abstract data type definition statement.
PROTECTED
Specifies that the abstract data type is partially encapsulated, which means that the default constructor function of the abstract data type can be used only in the abstract data type being defined and in subtype definitions for that abstract data type.
PUBLIC
Specifies that the default constructor function of the abstract data type can be used regardless of the inheritance.

Specifies an attribute that makes up the abstract data type.

encapsulation-level
Specifies one of three encapsulation levels. The encapsulation level can be specified for routines that code operations on attributes and abstract data types. The default is the encapsulation level that is next higher than the encapsulation level in the definition part. If no encapsulation level is specified at the first level, PUBLIC is assumed as the default.
PRIVATE
This option encapsulates the specified attribute, in which case the attribute can be used only in the definition of the abstract data type.
PROTECTED
This option partially encapsulates the specified attribute, in which case the attribute can be used only in the definition of the abstract data type and in the definition of all subtypes of that abstract data type.
PUBLIC
This option allows the specified attribute to be used regardless of any inheritance relationships that may be in effect.
attribute-name
Specifies the name of the attribute of the abstract data type.
data-type
Specifies the data type of the attribute of the abstract data type.
If the specified data type is an abstract data type, no authorization identifier is specified, and the default authorization identifier does not have an abstract data type of the same name, and if there is an abstract data type of the same name in the 'MASTER' authorization identifier, that abstract data type is assumed to have been specified.
NO SPLIT
When the actual data length of a variable-length character string is at least 256 bytes, specifies that each row is to be stored on a single page.
In some cases, the NO SPLIT option reduces the database storage space requirements. This option is called the no-split option; for details, see the HiRDB Version 8 Installation and Design Guide.
The no-split option is applicable only to the variable-length character string types (VARCHAR, NVARCHAR, and MVARCHAR).

The routine declaration is where the routine in which data manipulations are specified is written. The same encapsulation levels as for attribute definition are applicable to routine declarations.

The routine body is where either the function or the procedure to be used is written (for details of functions and procedures, see the CREATE FUNCTION and CREATE PROCEDURE statements).

Common rules

  1. When an authorization identifier is specified in a routine, it must be the authorization identifier of the user who executes the routine.
  2. The procedure in a CALL statement or the function call function specified in the function body or procedure body must be one of the following:
    • A function or procedure that was already defined outside of the CREATE TYPE statement
    • A function or procedure that was already defined in a routine declaration prior to declaration of this routine
  3. The attribute name of each abstract data type must be unique among all abstract data types that have an interconnected inheritance relationship.
  4. The maximum permissible number of generations for inherited subtypes is 30,000.
  5. Abstract data types that are defined in a CREATE TYPE statement must satisfy the following formula; Table 3-40 shows the lengths of attributes (data lengths):

    [Figure]

    Table 3-40 Data lengths

    CategoryData typeDate length (bytes)
    Numeric dataINTEGER (4-byte binary integer)4
    SMALLINT (2-byte binary integer)2
    [LARGE] DECIMAL [m, n]* (packed decimal floating-point number)[Figure]m/2[Figure] + 1
    FLOAT (8-byte floating point number)8
    SMALLFLT (4-byte floating point number)4
    Character dataCHARACTER [n] (n-byte fixed-length character string)n
    VARCHAR [n] (n-byte variable-length character string)35
    National character dataNCHAR [n] (n-character fixed-length national character string)2n
    NVARCHAR [n] (n-character variable-length national character string)35
    Mixed character dataMCHAR [n] (n-byte fixed-length mixed character string)n
    MVARCHAR [n] (n-byte variable-length mixed character string)35
    Date dataDATE (date)4
    Date interval dataINTEVAL YEAR TO DAY (date interval)5
    Time dataTIME (time)3
    Time interval dataINTERVAL HOUR TO SECOND (time interval)4
    Time stamp dataTIMESTAMP[p] (fractional second with p digits) (time stamp)7 + p/2
    Large object dataBLOB (binary string in n bytes)35
    Abstract dataUser-defined data type defined in CREATE TYPE35
    m, n: Positive integers.
    p: Integer 0, 2, 4, or 6
    * This is a fixed-point number with a total of m digits and n decimal places. If m is omitted, the default that is assumed is 15.
  6. When the SQL compile option is specified in an ALTER PROCEDURE or ALTER ROUTINE statement, the length of the SQL statement that is created by incorporating the SQL compile option into the source CREATE PROCEDURE statement of the routine being recreated must not exceed the maximum permissible length for SQL statements.
  7. CREATE TYPE cannot be executed from within a Java procedure if the execution results in invalidation of the SQL object being executed.

Notes

  1. The CREATE TYPE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. When defining an abstract data type, if there is an SQL object for which functions, procedures, and triggers are in effect that use an abstract data type (all subtypes of the highest-level data type) that has the same inheritance relationship as that abstract data type, that SQL object is nullified. If the authorization identifier MASTER contains the same abstract data type as the abstract data type being defined, of the SQL objects for which functions, procedures, and triggers are in effect that use the data type with the authorization identifier MASTER, the SQL object of the owner of the abstract data type being defined is nullified.
  3. When defining a function by specifying a function body, if there is an SQL object for which functions, procedures, and triggers are in effect that use a function that has the same owner, the same routine identifier, and the same number of SQL parameters as the function being defined, that SQL object is nullified.
  4. If an SQL object for which functions, procedures, and triggers are in effect is nullified, any rows of nullified functions, procedures, and triggers in the SQL_ROUTINE_RESOURCES dictionary table are deleted.
  5. Before executing the SQL object for nullified functions, procedures, and triggers, you need to execute ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER to recreate the SQL object for the functions, procedures, and triggers.
  6. The subtype of an abstract data type that is provided by the developer of a plug-in cannot be defined by inheriting an abstract data type that is registered in the system through use of the pdplgrgst command.