CREATE METHOD

---

Create a method for a user-defined type.

where method-definition is:

Usage

Embedded/Interactive/ODBC/JDBC

Description

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 TYPE statement (see CREATE TYPE), or using the ALTER TYPE statement (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 CREATE TYPE statement.

Restrictions

If the method specification for the method is DETERMINISTIC the routine-body may not contain references to SESSION_USER, CURRENT_PROGRAM, CURRENT_DATE, LOCALTIME, LOCALTIMESTAMP and 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.

Examples

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

Standard Compliance

Standard	Compliance	Comments
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". Mimer SQL Engine: Feature S024, "Enhanced structured types".

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se