Create user-defined type.
A new type is defined. A user-defined type may be used as the data type for columns in
ALTER TABLEstatements. 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.
There are two categories of user-defined types; distinct types and structured types. 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 method 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 method otherwise the name depends on the source type as seen in the following table.
National character varying
Any interval type
ExampleCREATE TYPE weight AS int; CREATE FUNCTION checkWeight(w weight) RETURNS boolean RETURN w.integer > 100; SET :v = checkWeight(weight(200));
It is also possible to use an explicit cast to convert from and to a distinct type:BEGIN DECLARE w weight; DECLARE i int; ... SET i = CAST(w AS int); ... SET w = CAST(i AS weight); ... END
As mentioned, a structured type has multiple attributes. The type for an attribute may be a predefined type or a user-defined type. It is also possible to specify a default value for an attribute.create type person as (ssn VARCHAR(12), name VARCHAR(30)); create type marriage as (wife person, husband person, dateOfmarriage date default current_date);
A structured type must be instantiated before any attribute can be accessed. This is done by using the new specification which has the formatnew type_name()
This will set the default values for all attributes in the structured user-defined type. Attributes that does not have an explicit default value will be null. The new specification would typically be used when assigning a column in an insert or update statement or when assigning a variable in a stored procedure.
Exampleinsert into persons (id, person) values(next value for sp, new person()); update persons set person.ssn = '123456789012', person.name = 'Berg' where id = current value for sp;
It is possible to create constructor methods for a structured user-defined type which can be used to initiate attributes to other values than the default values given when the type was created. A constructor method is invoked when the new specification is used with parameters (see below). A constructor method is created in two steps. The first is to give a method specification which can be done with the create type statement or the alter type statement.alter type person add constructor method person( ssn varchar(12), name varchar(30)) returns person
A constructor method must have the same name as the type and the return type for the method must be the structured type. A constructor method will have an implicit parameter named self which is an instance of the user-defined type where all attributes have their default values. To create the actual constructor method, the create method statement is used.create constructor method person(ssn varchar(12),name varchar(30)) for person begin set self.ssn = ssn; set self.name = name; return self; end
To invoke this method this form of the new specification can be usednew person('123456789012','Berg')
A constructor method is useful if there is a need to use a more complex expression when giving an attribute an initial value. The default value in an attribute value must be a constant whereas a constructor method may use any expression available for stored procedures in order to define the initial value for an attribute.
When a structured type is created, two methods for each attribute are created automatically. These methods have the same name as the attribute.
The first method is used to modify the value for an attribute and it takes one parameter with the same type as the attribute and it returns an instance of the type. This method is called a mutator method.
The second method is used for retrieving the attribute value and has no parameters. The return type of this method is the data type for the attribute and it returns the value of the attribute. This method is called an observer method.
There are different ways of invoking these methods, which can be seen in the following examples:CREATE TYPE book AS (ISBN VARCHAR(20), title NCHAR VARYING(100), publishingDate DATE); -- -- usage of mutator methods -- CREATE PROCEDURE defineBook(IN p_isbn VARCHAR(20), p_title NCHAR VARYING(100)) BEGIN -- -- create a new instance of the book type -- DECLARE v_book book; SET v_book = NEW book(); -- -- set the ISBN, title and publishingDate attribute for the book -- by invoking the mutator method for each attribute -- SET v_book = v_book.ISBN(p_isbn); SET v_book = v_book.title(p_title); SET v_book = v_book.publishingDate(CURRENT_DATE); -- -- there is an alternative syntax for invoking the mutator method, i.e. -- SET v_book.ISBN = p_isbn; SET v_book.title = p_title; SET v_book.publishingDate = current_date; END -- -- usage of observer methods -- CREATE PROCEDURE getBook(OUT p_isbn VARCHAR(20), p_title NCHAR VARYING(100), p_publishingDate DATE, IN p_book book) BEGIN SET p_isbn = p_book.ISBN(); SET p_title = p_book.title(); SET p_publishingDate = p_book.publishingDate(); -- -- the parenthesis may be omitted -- i.e. the following is also valid -- SET p_isbn = p_book.ISBN; END -- -- using a user-defined type as the type for an attribute -- (using the types marriage and person as defined previously) -- BEGIN DECLARE m marriage; DECLARE ssn CHARACTER(12); SET m = NEW marriage(); ... SET ssn = m.wife.ssn; END
For each type it is possible to specify zero or more method specifications. A method specification is a template for a method that can be created with the CREATE METHOD statement (see CREATE METHOD). The attributes defined for a method specification will be inherited by the method.
A method specification can either be declared as STATIC, CONSTRUCTOR or INSTANCE. These methods are invoked differently, see Mimer SQL Programmer's Manual, Invoking Methods.
ExampleCREATE TYPE book (ISBN VARCHAR(20), title NCHAR VARYING(100), publishingDate DATE) CONSTRUCTOR METHOD book(VARCHAR(20), NCHAR VARYING(100), DATE) RETURNS book
DETERMINISTIC CONTAINS SQL, INSTANCE METHOD countBook(NCHAR VARYING(100)) RETURNS INTEGER READS SQL DATA
If neither DETERMINISTIC nor NOT DETERMINISTIC is specified, then NOT DETERMINISTIC is implicit.
If DETERMINISTIC is specified, then the method is guaranteed to produce the same result every time it is invoked with the same set of input values and repeated invocations of it can, therefore, be optimized.
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.
- MODIFIES SQL DATA
If no access options is specified, CONTAINS SQL is implicit
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 or an attribute in a structured user-defined type.
The parameter mode for a parameter cannot be specified. It is always IN.
cast-optioncan only be specified for a distinct type.
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.
It is possible to add or drop attributes from a structured user-defined type using the ALTER TYPE statement. The ALTER TYPE statement can also be used for adding and dropping method specifications. (See ALTER TYPE.)
The keywords FINAL and INSTANTIABLE are supported for compliance with SQL-2011. SQL-2011 has support for single inheritance and polymorphism, which is not supported in this version of Mimer SQL.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40