SQL Reference

CREATE METHOD

This statement is used to associate a method body with a method specification that is already part of the definition of a user-defined structured type.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

SYSADM or DBADM authority
CREATEIN privilege on the schema of the structured type referred to in CREATE METHOD
The DEFINER of the structured type referred to in the CREATE METHOD statement.

If the authorization ID of the statement does not have SYSADM or DBADM authority, and the method identifies a table or view in the RETURN statement, the privileges that the authorization ID of the statement holds (without considering group privileges) must include SELECT WITH GRANT OPTION for each identified table and view.

If the authorization ID has insufficient authority to perform the operation, an error is raised (SQLSTATE 42502).

Syntax

>>-CREATE------------------------------------------------------->
 
>-----+-METHOD--+-method-name----------+---FOR--type-name--+---->
      |         '-| method-signature |-'                   |
      '-SPECIFIC METHOD--specific-name---------------------'
 
>-----+-*----EXTERNAL--+-----------------------+--*----+------------------------------+--*--+>
      |                '-NAME--+-'string'---+--'       '-TRANSFORM GROUP--group-name--'     |
      |                        '-identifier-'                                               |
      '-RETURN--+-scalar-expression-+-------------------------------------------------------'
                '-NULL--------------'
 
>--------------------------------------------------------------><
 
method-signature
 
|---method-name--(--+---------------------------------------------------------+---)-->
                    |  .-,--------------------------------------------------. |
                    |  V                                                    | |
                    '----+-----------------+---data-type1--+-------------+--+-'
                         '-parameter-name--'               '-AS LOCATOR--'
 
>----+------------------------------------------------------------------+->
     '-RETURNS--+-data-type2--+-------------+------------------------+--'
                |             '-AS LOCATOR--'                        |
                '-data-type3--CAST FROM--data-type4--+-------------+-'
                                                     '-AS LOCATOR--'
 
>---------------------------------------------------------------|

Description

METHOD

Identifies an existing method specification that is associated with a user-defined structured type. The method-specification can be identified through one of the following means:

method-name

Names the method specification for which a method body is being defined. The implicit schema is the schema of the subject type (type-name). There must be only one method specification for type-name that has this method-name (SQLSTATE 42725).

method-signature

Provides the method signature which uniquely identifies the method to be defined. The method signature must match the method specification that was provided on the CREATE TYPE or ALTER TYPE statement (SQLSTATE 42883).

method-name

Names the method specification for which a method body is being defined. The implicit schema is the schema of the subject type (type-name).

parameter-name: Identifies the parameter name. If parameter names are provided in the method signature, they must be exactly the same as the corresponding parts of the matching method specification. Parameter names are supported in this statement solely for documentation purposes.
data-type1: Specifies the data type of each parameter.
AS LOCATOR: For the LOB types or distinct types which are based on a LOB type, the AS LOCATOR clause can be added.

RETURNS

This clause identifies the output of the method. If a RETURNS clause is provided in the method signature, it must be exactly the same as the corresponding part of the matching method specification on CREATE TYPE. The RETURNS clause is supported in this statement solely for documentation purposes.

data-type2

Specifies the data type of the output.

AS LOCATOR: For LOB types or distinct types which are based on LOB types, the AS LOCATOR clause can be added. This indicates that a LOB locator is to be returned by the method instead of the actual value.

data-type3 CAST FROM data-type4

This form of the RETURNS clause is used to return a different data type to the invoking statement from the data type that was returned by the function code.

AS LOCATOR: For LOB types or distinct types which are based on LOB types, the AS LOCATOR clause can be used to indicate that a LOB locator is to be returned from the method instead of the actual value.

FOR type-name

Names the type for which the specified method is to be associated. The name must identify a type already described in the catalog. (SQLSTATE 42704) In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names.

SPECIFIC METHOD specific-name

Identifies the particular method, using the specific name either specified or defaulted to at CREATE TYPE time. The specific-name must identify a method specification in the named or implicit schema; otherwise, an error is raised (SQLSTATE 42704).

EXTERNAL

This clause indicates that the CREATE METHOD statement is being used to register a method, based on code written in an external programming language, and adhering to the documented linkage conventions and interface. The matching method-specification in CREATE TYPE must specify a LANGUAGE other than SQL. When the method is invoked, the subject of the method is passed to the implementation as an implicit first parameter.

If the NAME clause is not specified, "NAME method-name" is assumed.

NAME

This clause identifies the name of the user-written code which implements the method being defined.

'string': The 'string' option is a string constant with a maximum of 254 characters. The format used for the string is dependent on the LANGUAGE specified. See CREATE FUNCTION (External Scalar) for more information of the specific language conventions.
identifier: This identifier specified is an SQL identifier. The SQL identifier is used as the library-id in the string. Unless it is a delimited identifier, the identifier is folded to upper case. If the identifier is qualified with a schema name, the schema name portion is ignored. This form of NAME can only be used with LANGUAGE C (as defined in the method-specification on CREATE TYPE).

TRANSFORM GROUP group-name

Indicates the transform group that is used for user-defined structured type transformations when invoking the method. A transform is required since the method definition includes a user-defined structured type.

It is strongly recommended that a transform group name be specified; if this clause is not specified, the default group-name used is DB2_FUNCTION. If the specified (or default) group-name is not defined for a referenced structured type, an error results (SQLSTATE 42741). Likewise, if a required FROM SQL or TO SQL transform function is not defined for the given group-name and structured type, an error results (SQLSTATE 42744).

RETURN scalar-expression or NULL

The RETURN statement is an SQL control statement that specifies the value returned by the method.

scalar-expression

An expression that specifies the body of the method when the method-specification on CREATE TYPE specifies LANGUAGE SQL. Parameter names can be referenced in the expression. The subject of the method is passed to the method implementation in the form of an implicit first parameter named SELF. The result data type of the expression must be assignable (using store assignment rules) to the data type defined in the RETURNS clause of the method-specification on CREATE TYPE (SQLSTATE 42866).

The expression must comply with the following parts of the method-specification:

DETERMINISTIC or NOT DETERMINISTIC (SQLSTATE 428C2)
EXTERNAL ACTION or NO EXTERNAL ACTION (SQLSTATE 428C2)
CONTAINS SQL or READS SQL DATA (SQLSTATE 42985)

NULL

Specifies that the function returns a null value. The null value is of the data type defined in the RETURNS clause of the method-specification created with the CREATE TYPE statement.

Rules

The method specification must be previously defined using the CREATE TYPE or ALTER TYPE statement before CREATE METHOD can be used (SQLSTATE 42723).

Examples

Example 1:

     CREATE METHOD BONUS (RATE DOUBLE)
        FOR EMP
        RETURN SELF..SALARY * RATE

Example 2:

     CREATE METHOD SAMEZIP (addr address_t)
        RETURNS INTEGER
        FOR address_t
        RETURN
           (CASE
              WHEN (self..zip = addr..zip)
                 THEN 1
              ELSE 0
           END)

Example 3:

     CREATE METHOD DISTANCE (address_t)
        FOR address_t
        EXTERNAL NAME 'addresslib!distance'
        TRANSFORM GROUP func_group

[ Top of Page | Previous Page | Next Page ]