CREATE TYPE

Create user-defined type.

 

where representation is:

 

and type-attributes is:

 

and cast-option is:

 

and method-specification is:

 

where access-option is:

 

Usage

Embedded, Interactive, Module, ODBC, JDBC

Description

A new type is defined. A user-defined type may be used as the data type for columns in CREATE or ALTER TABLE statements. It can also be used in stored procedures and triggers as the type for variables and parameters.

The type-name should follow the normal rules for naming database objects (see Identifiers). If the type-name is unqualified, the type will be created in the schema with the same name as the current ident. If the type-name is qualified with a schema name, this schema must be owned by the current ident. The permitted values for data-type are described in Data Types in SQL Statements.

A distinct type has a single data type whereas a structured type has a list of attributes.

User-defined types are strongly typed, which means that it is only possible to compare values of the same type. When comparing a predefined data type and a distinct user-defined type a type cast must be used. For this purpose there are two routines created automatically when the type is created. Firstly, a function that can be used for casting from the type on which the user-defined type is based to the distinct type. If a cast-source clause is specified the identifier will be used as the name for the function, otherwise the function will have the same name as the user-defined type. Secondly, a function for casting from the user-defined type to the type on which it is based is also created. If cast distinct as source is specified the identifier in this clause is used for the function otherwise the name depends on the source type as seen in the following table.

Examples

CREATE TYPE weight AS int;

CREATE FUNCTION checkWeight(w weight) RETURNS boolean RETURN integer(w) > 100;

SET :v = checkWeight(weight(200));

 

BEGIN

   DECLARE w weight;

   DECLARE i int;

   …

   SET i = integer(w);

   …

   SET w = weight(i);

   …

END

Access Options

The following access options may be specified:

•CONTAINS SQL

The method may not contain any data-manipulation-statements. All other procedural-sql-statements are permitted. The method may only invoke methods, functions and procedures with the access option CONTAINS SQL. This option effectively prevents a routine from performing read or write operations on data in the database.

•READS SQL DATA

All procedural-sql-statements are permitted except those performing updates (i.e. DELETE, INSERT and UPDATE). The method may only invoke methods, functions and procedures with the access option CONTAINS SQL or READS SQL DATA.

This option effectively prevents a routine from performing write operations on data in the database.

•MODIFIES SQL DATA

All procedural-sql-statements are permitted and any method, function or procedure may be invoked from this type of method.

This option allows a routine to perform read and write operations on data in the database.

If no access options is specified, CONTAINS SQL is implicit

Restrictions

The type-name must be unique within a schema.

A method specification must be unique for a user-defined type with regard to the number of parameters and data types. This means that user-defined type may have multiple method specifications with the same name as long as either the number of parameters differ or if the data types for the parameters differ.

If a parameter name is specified in a parameter list it must be unique within the parameter list.

The ROW data type cannot be used at any place in a type definition.

A domain may not be used as the type for a distinct user-defined type.

The parameter mode for a parameter cannot be specified. It is always IN.

cast-option can only be specified for a distinct type.

Notes

When dropping a type with cascade option, any column using that type will be dropped. If this column is the last column in the table, the table will be dropped as well. See DROP TYPE for more details.

The ALTER TYPE statement can be used for adding and dropping method specifications. (See ALTER TYPE.)

The keywords FINAL and INSTANTIABLE are supported for compliance with SQL-2016. SQL-2016 has support for single inheritance and polymorphism, which is not supported in this version of Mimer SQL.

Standard Compliance