Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


CREATE TYPE


Create user-defined type.



where representation is:



and type-attributes is:



and method-specification is:



where access-option is:



and cast-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.

Source type
Function name
Character
CHAR
Character varying
VARCHAR
National character
NCHAR
National character varying
NVARCHAR
Binary
BINARY
Binary varying
VARBINARY
Integer
INTEGER
Decimal
DECIMAL
Numeric
NUMERIC
Float
FLOAT
Real
REAL
Double precision
DOUBLE
Date
DATE
Time
TIME
Timestamp
TIMESTAMP
Any interval type
INTERVAL
Boolean
BOOLEAN
Binary large object
BLOB
Character large object
CLOB
National character large object
NCLOB

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:

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


Mimer
Mimer Information Technology AB
Phone: +46 18 780 92 00
info@mimer.com
Mimer SQL Documentation TOC PREV NEXT INDEX