CREATE FUNCTION (SQL scalar, table, or row) statement

The CREATE FUNCTION (SQL scalar, table, or row) statement is used to define a user-defined SQL scalar, table, or row function.

A scalar function returns a single value each time it is invoked, and is generally valid wherever an SQL expression is valid. A table function can be used in a FROM clause and returns a table. A row function can be used as a transform function and returns a row.

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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

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

IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the function does not exist
CREATEIN privilege on the schema, if the schema name of the function refers to an existing schema
SCHEMAADM authority on the schema, if the schema name of the function exists
DBADM authority

and at least one of the following authorities on each table, view, or nickname identified in any fullselect:

CONTROL privilege on that table, view, or nickname
SELECT privilege on that table, view, or nickname
SELECTIN privilege on the schema containing the table, view, or nickname
DATAACCESS authority on the schema containing the table, view, or nickname
DATAACCESS authority on the database

Group privileges other than PUBLIC are not considered for any table or view specified in the CREATE FUNCTION statement.

Authorization requirements of the data source for the table or view referenced by the nickname are applied when the function is invoked. The authorization ID of the connection can be mapped to a different remote authorization ID.

The privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the function body.

To replace an existing function, the authorization ID of the statement must be the owner of the existing function (SQLSTATE 42501).

If the SECURED option is specified, the authorization ID of the statement must include SECADM or CREATE_SECURE_OBJECT authority (SQLSTATE 42501).

Syntax

parameter-declaration

data-type1, data-type2

built-in-type

anchored-data-type

anchored-row-data-type

default-clause

column-list

data-type3

option-list

SQL-function-body

Notes:

¹ OUT and INOUT are valid only if RETURNS specifies a scalar result and the SQL-function-body is a compound SQL (compiled) statement.
² The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).
³ DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
⁴ Valid only for compiled scalar function definition and an inlined table function definition. A compiled scalar function defined as MODIFIES SQL DATA can only be used as the only element on the right side of an assignment statement that is within a compound SQL (compiled) statement.
⁵ Valid only if RETURNS specifies a scalar result (data-type2)
⁶ The following apply to the specification of a compound SQL (compiled) statement: a) Must be used if the parameter data types or returned data types include a row type, array type, or cursor type; b) Must be used if the RETURNS TABLE clause specifies any syntax other than a column-list; c) Not supported if RETURNS ROW is specified; d) Not supported when defining a table function in a partitioned database environment.

Description

OR REPLACE

Specifies to replace the definition for the function if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog, with the exception that privileges that were granted on the function are not affected. This option can be specified only by the owner of the object. This option is ignored if a definition for the function does not exist at the current server. To replace an existing function, the specific name and function name of the new definition must be the same as the specific name and function name of the old definition, or the signature of the new definition must match the signature of the old definition. Otherwise, a new function is created.

If the function is referenced in the definition of a row permission or a column mask, the function cannot be replaced (SQLSTATE 42893).

function-name

Names the function being defined. It is a qualified or unqualified name that designates a function. The unqualified form of function-name is an SQL identifier. 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. The qualified form is a schema-name followed by a period and an SQL identifier.

The name, including the implicit or explicit qualifiers, together with the number of parameters and the data type of each parameter (without regard for any length, precision or scale attributes of the data type) must not identify a function described in the catalog (SQLSTATE 42723). The unqualified name, together with the number and data types of the parameters, while of course unique within its schema, need not be unique across schemas.

If a two-part name is specified, the schema-name cannot begin with 'SYS' (SQLSTATE 42939).

A number of names used as keywords in predicates are reserved for system use, and cannot be used as a function-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH, and the comparison operators.

The same name can be used for more than one function if there is some difference in the signature of the functions. Although there is no prohibition against it, an external user-defined table function should not be given the same name as a built-in function.

(parameter-declaration, ...)

Identifies the number of input parameters of the function, and specifies the mode, name, data type, and optional default value of each parameter. One entry in the list must be specified for each parameter that the function will expect to receive. No more than 90 parameters are allowed (SQLSTATE 54023).

It is possible to register a function that has no parameters. In this case, the parentheses must still be coded, with no intervening data types. For example:

   CREATE FUNCTION WOOFER() ...

No two identically-named functions within a schema are permitted to have exactly the same type for all corresponding parameters. Lengths, precisions, and scales are not considered in this type comparison. Therefore, CHAR(8) and CHAR(35) are considered to be the same type, as are DECIMAL(11,2) and DECIMAL (4,3), as well as DECFLOAT(16) and DECFLOAT(34). A weakly typed distinct type specified for a parameter is considered to be the same data type as the source type of the distinct type. For a Unicode database, CHAR(13) and GRAPHIC(8) are considered to be the same type. There is some further bundling of types that causes them to be treated as the same type for this purpose, such as DECIMAL and NUMERIC. A duplicate signature returns an error (SQLSTATE 42723).

If the data type for a parameter is a Boolean data type, array type, cursor type, or row type, the SQL function body can only reference the parameter within a compound SQL (compiled) statement (SQLSTATE 428H2).

IN | OUT | INOUT

Specifies the mode of the parameter. If an error is returned by the function, OUT parameters are undefined and INOUT parameters are unchanged. The default is IN.

IN: Identifies the parameter as an input parameter to the function. Any changes made to the parameter within the function are not available to the invoking context when control is returned.
OUT: Identifies the parameter as an output parameter for the function.; The function must be a scalar function that is defined with a compound SQL (compiled) statement (SQLSTATE 42613).; The function can be referenced only on the right side of an assignment statement that is in a compound SQL (compiled) statement, and the function reference cannot be part of an expression (SQLSTATE 42887).
INOUT: Identifies the parameter as both an input and output parameter for the function.; The function must be a scalar function that is defined with a compound SQL (compiled) statement (SQLSTATE 42613).; The function can be referenced only on the right side of an assignment statement that is in a compound SQL (compiled) statement, and the function reference cannot be part of an expression (SQLSTATE 42887).

parameter-name

Specifies a name for the parameter. The name cannot be the same as any other parameter-name in the parameter list (SQLSTATE 42734).

data-type1

Specifies the data type of the parameter.

built-in-type

Specifies a built-in data type. For a more complete description of each built-in data type except BOOLEAN and CURSOR, which cannot be specified for a table, see CREATE TABLE.

BOOLEAN: For a Boolean.
CURSOR: For a reference to an underlying cursor.

anchored-data-type

Identifies another object used to define the parameter data type. The data type of the anchor object can be any of the data types explicitly allowed as data-type1. The data type of the anchor object has the same limitations that apply to specifying the data type directly, or in the case of a row, to creating a row type.

ANCHOR DATA TYPE TO

Indicates an anchored data type is used to specify the data type.

variable-name1

Identifies a global variable. The data type of the global variable is used as the data type for parameter-name.

table-name.column-name

Identifies a column name of an existing table or view. The data type of the column is used as the data type for parameter-name.

ROW OF table-name or view-name

Specifies a row of fields with names and data types that are based on the column names and column data types of the table identified by table-name or the view identified by view-name. The data type of parameter-name is an unnamed row type.

ROW OF cursor-variable-name

Specifies a row of fields with names and data types that are based on the field names and field data types of the cursor variable identified by cursor-variable-name. The specified cursor variable must be one of the following elements (SQLSTATE 428HS):

A global variable with a strongly typed cursor data type
A global variable with a weakly typed cursor data type that was created or declared with a CONSTANT clause specifying a select-statement where all the result columns are named.

If the cursor type of the cursor variable is not strongly typed using a named row type, the data type of parameter-name is an unnamed row type.

array-type-name: Specifies the name of a user-defined array type. If array-type-name is specified without a schema name, the array type is resolved by searching the schemas in the SQL path.

cursor-type-name: Specifies the name of a cursor type. If cursor-type-name is specified without a schema name, the cursor type is resolved by searching the schemas in the SQL path.

distinct-type-name: Specifies the name of a distinct type. The length, precision, and scale of the parameter are, respectively, the length, precision, and scale of the source type of the distinct type. A distinct type parameter is passed as the source type of the distinct type. If distinct-type-name is specified without a schema name, the distinct type is resolved by searching the schemas in the SQL path.

REF (type-name): Specifies a reference type without a scope. The specified type-name must identify a user-defined structured type (SQLSTATE 428DP). The system does not attempt to infer the scope of the parameter or result. Inside the body of the function, a reference type can be used in a dereference operation only by first casting it to have a scope. Similarly, a reference returned by an SQL function can be used in a dereference operation only by first casting it to have a scope. If a type name is specified without a schema name, the type-name is resolved by searching the schemas in the SQL path.

row-type-name: Specifies the name of a user-defined row type. The fields of the parameter are the fields of the row type. If row-type-name is specified without a schema name, the row type is resolved by searching the schemas in the SQL path.

structured-type-name: Specifies the name of a user-defined structured type. If structured-type-name is specified without a schema name, the structured type is resolved by searching the schemas in the SQL path.

DEFAULT

Specifies a default value for the parameter. The default can be a constant, a special register, a global variable, an expression, or the keyword NULL. The special registers that can be specified as the default are that same as those that can be specified for a column default (see default-clause in the CREATE TABLE statement). Other special registers can be specified as the default by using an expression.

The expression can be any expression of the type described in "Expressions". If a default value is not specified, the parameter has no default and the corresponding argument cannot be omitted on invocation of the procedure. The maximum size of the expression is 64K bytes.

The default expression must not modify SQL data (SQLSTATE 428FL or SQLSTATE 429BL). The expression must be assignment compatible to the parameter data type (SQLSTATE 42821).

A default cannot be specified in the following situations:

For INOUT or OUT parameters (SQLSTATE 42601)
For a parameter of type ARRAY, ROW, or CURSOR (SQLSTATE 429BB)
For a parameter to a function definition that also specified RETURNS ROW or a PREDICATES clause (SQLSTATE 42613)

RETURNS

This mandatory clause identifies the type of output of the function.

If the data type of the output of the function is a Boolean data type, array type, cursor type, or row type, the SQL function body must be a compound SQL (compiled) statement (SQLSTATE 428H2).

data-type2

Specifies the data type of the output.

In this statement, exactly the same considerations apply as for the parameters of SQL functions described previously in data-type1 for function parameters.

ROW

Specifies that the output of the function is a single row. If the function returns more than one row, an error is returned (SQLSTATE 21505).

This form of a row function can be used only as a transform function for a structured type (having one structured type as its parameter and returning only built-in data types).

column-list

The list of column names and data types returned for a ROW function. The column-list must include at least two columns (SQLSTATE 428F0).

column-name: Specifies the name of this column. The name cannot be qualified and the same name cannot be used for more than one column in the list.
data-type3: Specifies the data type of the column, and can be any data type supported by a parameter of the SQL function.
The same considerations apply as for the parameters of SQL functions described previously in data-type1 for function parameters. However, data-type3 does not support anchored-data-type, array-type-name, cursor-type-name, and row-type-name.

TABLE

Specifies that the output of the function is a table.

column-list

The list of column names and data types returned for a TABLE function

column-name: Specifies the name of this column. The name cannot be qualified and the same name cannot be used for more than one column in the list.
data-type3: Specifies the data type of the column, and can be any data type supported by a parameter of the SQL function.
The same considerations apply as for the parameters of SQL functions described previously in data-type1 for function parameters. However, data-type3 does not support anchored-data-type, array-type-name, cursor-type-name, and row-type-name.

row-type-name

Specifies a row type from which the fields are used to derive the column list. The field names of the row type are used as the column names.

anchored-row-data-type

Identifies row information from another object to use as the columns of the returned table.

ANCHOR DATA TYPE TO

Indicates an anchored data type is used to specify the data type.

variable-name

Identifies a global variable. The data type of the referenced variable must be a row type.

ROW OF table-name or view-name

ROW OF cursor-variable-name

A global variable with a strongly typed cursor data type.
A global variable with a weakly typed cursor data type that was created or declared with a CONSTANT clause specifying a select-statement where all the result columns are named.

ELEMENT OF array-type-name

Specifies an array type from which the element data type is used to derive the column list. If array-type-name identifies an array type with elements that are a row type, the field names of the row type are used as the column names. If the array-type-name identifies an array type with elements that are not row types, the single result column name is COLUMN_VALUE.

built-in-type

See "CREATE TABLE" for the description of built-in data types.

SPECIFIC specific-name

Provides a unique name for the instance of the function that is being defined. This specific name can be used when sourcing on this function, dropping the function, or commenting on the function. It can never be used to invoke the function. The unqualified form of specific-name is an SQL identifier. The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another function instance that exists at the application server; otherwise an error is raised (SQLSTATE 42710).

The specific-name may be the same as an existing function-name.

If no qualifier is specified, the qualifier that was used for function-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of function-name or an error is raised (SQLSTATE 42882).

If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmssxxx.

LANGUAGE SQL

Specifies that the function is written using SQL.

PARAMETER CCSID

Specifies the encoding scheme to use for all string data passed into and out of the function. If the PARAMETER CCSID clause is not specified, the default is PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID ASCII for all other databases.

ASCII: Specifies that string data is encoded in the database code page. If the database is a Unicode database, PARAMETER CCSID ASCII cannot be specified (SQLSTATE 56031).
UNICODE: Specifies that character data is in UTF-8, and that graphic data is in UCS-2. If the database is not a Unicode database, PARAMETER CCSID UNICODE cannot be specified (SQLSTATE 56031).

DETERMINISTIC or NOT DETERMINISTIC

This optional clause specifies whether the function always returns the same results for given argument values (DETERMINISTIC) or whether the function depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC function must always return the same table from successive invocations with identical inputs. Optimizations taking advantage of the fact that identical inputs always produce the same results are prevented by specifying NOT DETERMINISTIC.

EXTERNAL ACTION or NO EXTERNAL ACTION

Specifies whether the function takes an action that changes the state of an object that the database manager does not manage. An example of an external action is sending a message or writing a record to a file. The default is EXTERNAL ACTION.

EXTERNAL ACTION: Specifies that the function takes an action that changes the state of an object that the database manager does not manage.
NO EXTERNAL ACTION: Specifies that the function does not take any action that changes the state of an object that the database manager does not manage. The database manager uses this information during optimization of SQL statements.

READS SQL DATA, CONTAINS SQL, or MODIFIES SQL DATA

Specifies the classification of SQL statements that the function can run. The database manager verifies that the SQL statements that the function issues are consistent with this specification.

For the classification of each statement, see SQL statements that can be executed in routines and triggers

READS SQL DATA: Specifies that the function can run statements with a data access classification of READS SQL DATA, CONTAINS SQL, or NO SQL. The function cannot run SQL statements that modify data (SQLSTATE 42985). This is the default.
CONTAINS SQL: Specifies that the function can run only SQL statements with a data access classification of CONTAINS SQL. The function cannot run any SQL statements that read or modify data (SQLSTATE 42985).
MODIFIES SQL DATA: Specifies that the function can run any SQL statement except those statements that are not supported in any function.

ALLOW PARALLEL or DISALLOW PARALLEL

This clause specifies whether a UDF can be parallelized, that is, whether a single invocation of the UDF can cause several instances of the UDF (usually one instance per partition) to run in parallel. Parallelization usually improves overall performance, but is allowed only when all the following conditions are met:

The CONTAINS SQL clause is specified.
All invocations of the UDF are completely independent of each other.

DISALLOW PARALLEL is the default.

STATIC DISPATCH

This optional clause indicates that at function resolution time, a function is chosen based on the static types (declared types) of the parameters of the function.

CALLED ON NULL INPUT

This clause indicates that the function is called regardless of whether any of its arguments are null. It can return a null value or a non-null value. Responsibility for testing null argument values lies with the user-defined function.

The phrase NULL CALL may be used in place of CALLED ON NULL INPUT.

INHERIT SPECIAL REGISTERS

This optional clause indicates that updatable special registers in the function will inherit their initial values from the environment of the invoking statement. For a function that is invoked in the select-statement of a cursor, the initial values are inherited from the environment when the cursor is opened. For a routine that is invoked in a nested object (for example, a trigger or a view), the initial values are inherited from the runtime environment (not the object definition).

No changes to the special registers are passed back to the caller of the function.

Some special registers, such as the datetime special registers, reflect a property of the statement currently executing, and are therefore never inherited from the caller.

PREDICATES

For predicates using this function, this clause identifies those that can exploit the index extensions, and can use the optional SELECTIVITY clause for the predicate's search condition. If the PREDICATES clause is specified, the function must be defined as DETERMINISTIC with NO EXTERNAL ACTION (SQLSTATE 42613). If the PREDICATES clause is specified, and the database is not a Unicode database, PARAMETER CCSID UNICODE must not be specified (SQLSTATE 42613). PREDICATES cannot be specified if SQL-function-body is a compound SQL (compiled) statement (SQLSTATE 42613).

predicate-specification: For details on predicate specification, see CREATE FUNCTION (External Scalar).

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST or INHERIT ISOLATION LEVEL WITH LOCK REQUEST

Specifies whether or not a lock request can be associated with the isolation-clause of the statement when the function inherits the isolation level of the statement that invokes the function. The default is INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST.

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST: Specifies that, as the function inherits the isolation level of the invoking statement, it cannot be invoked in the context of an SQL statement which includes a lock-request-clause as part of a specified isolation-clause (SQLSTATE 42601).
INHERIT ISOLATION LEVEL WITH LOCK REQUEST: Specifies that, as the function inherits the isolation level of the invoking statement, it also inherits the specified lock-request-clause.

SQL-function-body

Specifies the body of the function. Parameter names can be referenced in the SQL-function-body. Parameter names may be qualified with the function name to avoid ambiguous references.

For RETURN statement, see: RETURN statement.

For Compound SQL (compiled), see: Compound SQL (compiled) statement.

For Compound SQL (inlined), see: Compound SQL (inlined) statement.

NOT SECURED or SECURED

Specifies whether the function is considered secure for row and column access control. The default is NOT SECURED.

NOT SECURED: Indicates that the function is not considered secure. When the function is invoked, the arguments of the function must not reference a column for which a column mask is enabled and column level access control is activated for its table (SQLSTATE 428HA). This rule applies to the non secure user-defined functions that are invoked anywhere in the statement.
SECURED: Indicates that the function is considered secure. The function must be secure when it is referenced in a row permission or a column mask (SQLSTATE 428H8).

Rules

Use of anchored data types: An anchored data type cannot refer to the following objects (SQLSTATE 428HS): a nickname, typed table, typed view, statistical view that is associated with an expression-based index, declared temporary table, row definition that is associated with a weakly typed cursor, object with a code page or collation that is different from the database code page or database collation.
Use of cursor and row types: A function that uses a cursor type or row type for a parameter or returns a cursor type or row type can only be invoked from within a compound SQL (compiled) statement (SQLSTATE 428H2).
Table access restrictions: If a function is defined as READS SQL DATA, no statement in the function can access a table that is being modified by the statement that invoked the function (SQLSTATE 57053). For example, suppose the user-defined function BONUS() is defined as READS SQL DATA. If the statement UPDATE EMPLOYEE SET SALARY = SALARY + BONUS(EMPNO) is invoked, no SQL statement in the BONUS function can read from the EMPLOYEE table.
If a function defined with MODIFIES SQL DATA contains nested CALL statements, read access to the tables being modified by the function (by either the function definition or the statement that invoked the function) is not allowed (SQLSTATE 57053).

Notes

Resolution of function calls inside the function body is done according to the SQL path that is effective for the CREATE FUNCTION statement and does not change after the function is created.
If an SQL function contains multiple references to any of the date or time special registers, all references return the same value, and it will be the same value returned by the register invocation in the statement that called the function.
The body of an SQL function cannot contain a recursive call to itself or to another function or method that calls it, since such a function could not exist to be called.
If an object referenced in the SQL function body does not exist or is marked invalid, or the definer temporarily doesn't have privileges to access the object, and if the database configuration parameter auto_reval is not set to DISABLED, then the SQL function will still be created successfully. The SQL function will be marked invalid and will be revalidated the next time it is invoked.
The following rules are enforced by all statements that create functions or methods:
- A function may not have the same signature as a method (comparing the first parameter-type of the function with the subject-type of the method).
- A function and a method may not be in an overriding relationship. That is, if the function were a method with its first parameter as subject, it must not override, or be overridden by, another method. For more information about overriding methods, see the CREATE TYPE (Structured) statement.
- Because overriding does not apply to functions, it is permissible for two functions to exist such that, if they were methods, one would override the other.
For the purpose of comparing parameter-types in the preceding rules:
- Parameter-names, lengths, AS LOCATOR, and FOR BIT DATA are ignored.
- A subtype is considered to be different from its supertype.
Privileges: The definer of a function always receives the EXECUTE privilege on the function, as well as the right to drop the function. The definer of a function is also given the WITH GRANT OPTION on the function if the definer has WITH GRANT OPTION on all privileges required to define the function, or if the definer has SYSADM or DBADM authority.
The definer of a function only acquires privileges if the privileges from which they are derived exist at the time the function is created. The definer must have these privileges either directly, or because PUBLIC has the privileges. Privileges held by groups of which the function definer is a member are not considered. When using the function, the connected user's authorization ID must have the valid privileges on the table or view that the nickname references at the data source.
Setting of the default value: Parameters of a function that are defined with a default value are set to their default value when the functions is invoked, but only if a value is not supplied for the corresponding argument, or is specified as DEFAULT, when the function is invoked.
EXTERNAL ACTION functions: If an EXTERNAL ACTION function is invoked in other than the outermost select list, the results are unpredictable since the number of times the function is invoked will vary depending on the access plan used.
Creating a secure function: Normally users with SECADM authority do not have privileges to create database objects such as triggers or functions. Typically they will examine the data accessed by the function, ensure it is secure, then grant the CREATE_SECURE_OBJECT authority to someone who currently has required privileges to create a secure user-defined function. After the function is created, they will revoke the CREATE_SECURE_OBJECT authority from the function owner.
The SECURED attribute is considered to be an assertion that declares the user has established a change control audit procedure for all changes to the user-defined function. The database manager assumes that such a control audit procedure is in place for all subsequent ALTER FUNCTION statements or changes to external packages.
Invoking other user-defined functions in a secure function: If a secure user-defined function invokes other user-defined functions, the database manager does not validate whether those nested user-defined functions have the SECURED attribute. If those nested functions can access sensitive data, the user with SECADM authority needs to ensure those functions are allowed to access those data and a change control audit procedure has been established for all changes to those functions.
Replacing an existing function such that the secure attribute is changed (from SECURED to NOT SECURED and vice versa): Packages and dynamically cached SQL statements that depend on the function may be invalidated because the secure attribute affects the access path selection for statements involving tables for which row or column level access control is activated.
Rebinding dependent packages: Every compiled SQL function has a dependent package. The package can be rebound at any time by using the REBIND_ROUTINE_PACKAGE procedure. Explicitly rebinding the dependent package does not revalidate an invalid function. Revalidate an invalid function with automatic revalidation or explicitly by using the ADMIN_REVALIDATE_DB_OBJECTS procedure. Function revalidation automatically rebinds the dependent package.
Syntax alternatives: The following syntax is supported for cross-product compatibility only. These alternatives are non-standard and should not be used:
- NULL CALL can be specified in place of CALLED ON NULL INPUT
The following syntax is accepted as the default behavior:
- CCSID UNICODE in a Unicode database
- CCSID ASCII in a non-Unicode database

Examples

Example 1: Define a scalar function that returns the tangent of a value using the existing sine and cosine functions.

   CREATE FUNCTION TAN (X DOUBLE)
     RETURNS DOUBLE
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN SIN(X)/COS(X)

Example 2: Define a transform function for the structured type PERSON.

     
   CREATE FUNCTION FROMPERSON (P PERSON)
     RETURNS ROW (NAME VARCHAR(10), FIRSTNAME VARCHAR(10))
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN VALUES (P..NAME, P..FIRSTNAME)

Example 3: Define a table function that returns the employees in a specified department number.

     
   CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
     RETURNS TABLE (EMPNO CHAR(6),
                    LASTNAME VARCHAR(15),
                    FIRSTNAME VARCHAR(12))
     LANGUAGE SQL
     READS SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN
       SELECT EMPNO, LASTNAME, FIRSTNME
         FROM EMPLOYEE
         WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO

Example 4: Define the table function from Example 3 with auditing.

   CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
     RETURNS TABLE (EMPNO CHAR(6),
                    LASTNAME VARCHAR(15),
                    FIRSTNAME VARCHAR(12))
     LANGUAGE SQL
     MODIFIES SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
     BEGIN ATOMIC
       INSERT INTO AUDIT 
       VALUES (USER, 
               'Table: EMPLOYEE Prd: DEPTNO = ' CONCAT DEPTNO);
       RETURN
         SELECT EMPNO, LASTNAME, FIRSTNME
           FROM EMPLOYEE
           WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO
     END

Example 5: Define a scalar function that reverses a string.

   CREATE FUNCTION REVERSE(INSTR VARCHAR(4000))
     RETURNS VARCHAR(4000)
     DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
     BEGIN ATOMIC
     DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';
     DECLARE LEN INT;
     IF INSTR IS NULL THEN
     RETURN NULL;
     END IF;
     SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));
     WHILE LEN > 0 DO
     SET (REVSTR, RESTSTR, LEN) 
       = (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR, 
       SUBSTR(RESTSTR, 2, LEN - 1),
       LEN - 1);
     END WHILE;
     RETURN REVSTR;
   END

Example 6: Create a function that increments a variable passed as an INOUT parameter and return any error as the return code.

   CREATE FUNCTION increment(INOUT result INTEGER, IN delta INTEGER)
     RETURNS INTEGER
     BEGIN
       DECLARE code INTEGER DEFAULT 0;
       DECLARE SQLCODE INTEGER;
       DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
       SET code = SQLCODE;
       RETURN code;
     END;
     SET result = result + delta;
     RETURN code;
   END@

Example 7: Create a compiled SQL function that takes an XML document as input and returns the customer name.

   CREATE FUNCTION get_customer_name_compiled(doc XML)
     RETURNS VARCHAR(25)
     BEGIN
       RETURN XMLCAST(XMLQUERY
          ('$d/customerinfo/name' PASSING doc AS "d")AS VARCHAR(25));
     END

Example 8: Create a compiled SQL function that takes a phone number and a region number passed as IN parameters and returns the complete number in an OUT XML parameter.

   CREATE FUNCTION construct_xml_phone
     (IN  phoneNo VARCHAR(20),
      IN  regionNo VARCHAR(8),
      OUT full_phone_xml XML)
     RETURNS VARCHAR(28)
     LANGUAGE SQL
     NO EXTERNAL ACTION
     BEGIN
       SET full_phone_xml = XMLELEMENT (NAME "phone", regionNo || phoneNo);
       RETURN regionNo || phoneNo;
     END