Create a method for a user-defined type.
Creates a new method. A method returns a single value and can thus be used wherever an expression is allowed. A method is always associated with a user-defined type. Before a method can be created, there must exist a method specification for the type with matches the method with regard to parameters and result type. (As it is possible to have method specifications with parameter overloading there must be an exact match.) Method specifications are created using the
CREATE TYPEstatement (see CREATE TYPE), or using the
ALTER TYPEstatement (see ALTER TYPE).
There are three types of methods, static, constructor and instance methods. If no method modifier is specified, instance is default. The main difference between these types of methods is how they are invoked. See Mimer SQL Programmer's Manual, Invoking Methods.
The method name should follow the normal rules for naming database objects unless it is a constructor method. A constructor method must have the same name as the user-defined type to which it belongs and it must have a returns clause that specifies this type. A constructor method can only be defined for a structured type.
The specific name for a method can be retrieved by using the information_schema views.
A specific name can be used in DROP, GRANT and REVOKE statements. It is particularly useful when dealing with routines with parameter overloading. Instead of having to specify a list of data types, in order to distinguish the routine, the specific name can be used.
If no schema name is specified, the method is created in a schema with the same name as the current ident. The ident creating the method must be the owner of the schema. It is only the creator of a user-defined type that can create methods for that type.
The parameter names should follow the normal rules for naming SQL identifiers. All parameters have the parameter mode IN. The data type for a parameter may be a pre-defined type (see Data Types in SQL Statements) or a user-defined type. The same applies to the result type for the method.
Within the routine body of an instance or constructor method it is possible to use SELF to reference to the actual object that invokes the method.
In a constructor method the attributes have their default values as specified in the
If the method specification for the method is
DETERMINISTICthe routine-body may not contain references to
BUILTIN.UTC_TIMESTAMP. It is also not possible to invoke procedures, functions or methods that are deterministic.
Likewise the access option for the method specification will govern which operations that are allowed.
Create a constructor method for a type.CREATE TYPE person AS (firstname NCHAR VARYING(30), lastname NCHAR VARYING(30), birth DATE, sex VARCHAR(7) DEFAULT 'UNKNOWN'); ALTER TYPE person ADD CONSTRUCTOR METHOD person( firstName NCHAR VARYING(30), lastName NCHAR VARYING(30), birth DATE, sex VARCHAR(7)) RETURNS person; CREATE CONSTRUCTOR METHOD person(firstName NCHAR VARYING(30), lastName NCHAR VARYING(30), birth DATE, sex VARCHAR(7)) FOR person BEGIN SET self.firstName = firstName; SET self.lastName = lastName; SET self.birth = birth; SET self.sex = sex; RETURN self; END
SQL-2011 Core Fully compliant. SQL-2011 Features outside core Feature S023 "Basic structured types".Feature S027, "Create method by specific method name"Feature T322 "Overloading of SQL-invoked functions and procedures".Feature S024, "Enhanced structured types".
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40