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
- 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
- DATAACCESS authority
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
>>-CREATE--+------------+--FUNCTION--function-name-------------->
'-OR REPLACE-'
>--(--+-------------------------------+--)--●------------------->
| .-,-------------------------. |
| V | |
'---| parameter-declaration |-+-'
>--RETURNS--+-| data-type2 |-----------------------------+------>
'-+-ROW--| column-list |-------------------+-'
'-TABLE--+-| column-list |-------------+-'
+-row-type-name---------------+
+-| anchored-row-data-type |--+
'-ELEMENT OF--array-type-name-'
>--| option-list |--| SQL-function-body |----------------------><
parameter-declaration
.-IN------.
|--+---------+--parameter-name--| data-type1 |--+--------------------+--|
| (1) | '-| default-clause |-'
+-OUT-----+
'-INOUT---'
data-type1, data-type2
|--+-| built-in-type |------+-----------------------------------|
+-| anchored-data-type |-+
+-array-type-name--------+
+-cursor-type-name-------+
+-distinct-type-name-----+
+-REF--(--type-name--)---+
+-row-type-name----------+
'-structured-type-name---'
built-in-type
|--+-+-SMALLINT----+----------------------------------------------------------------------+--|
| +-+-INTEGER-+-+ |
| | '-INT-----' | |
| '-BIGINT------' |
| .-(5,0)-------------------. |
+-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+
| | '-DEC-----' | | .-,0-------. | |
| '-+-NUMERIC-+-' '-(integer-+----------+-)-' |
| '-NUM-----' '-,integer-' |
| .-(53)------. |
+-+-FLOAT--+-----------+--+------------------------------------------------------------+
| | '-(integer)-' | |
| +-REAL------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+-' |
| .-(34)-. |
+-DECFLOAT--+------+-------------------------------------------------------------------+
| '-(16)-' |
| .-(1)------------------------. |
+-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+
| | | '-CHAR------' '-(integer-+-------------+-)-' | | (2) | | |
| | | +-OCTETS------+ | '-FOR BIT DATA-----' | |
| | | '-CODEUNITS32-' | | |
| | '-+-VARCHAR----------------+--(integer-+-------------+-)-' | |
| | '-+-CHARACTER-+--VARYING-' +-OCTETS------+ | |
| | '-CHAR------' '-CODEUNITS32-' | |
| | .-(1M)-----------------------------. | |
| '-+-CLOB------------------------+--+----------------------------------+------------' |
| '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-+-------------+-)-' |
| '-CHAR------' +-K-+ +-OCTETS------+ |
| +-M-+ '-CODEUNITS32-' |
| '-G-' |
| .-(1)------------------------. |
+-+-GRAPHIC--+----------------------------+------+-------------------------------------+
| | '-(integer-+-------------+-)-' | |
| | +-CODEUNITS16-+ | |
| | '-CODEUNITS32-' | |
| +-VARGRAPHIC--(integer-+-------------+-)-------+ |
| | +-CODEUNITS16-+ | |
| | '-CODEUNITS32-' | |
| | .-(1M)-----------------------------. | |
| '-DBCLOB--+----------------------------------+-' |
| '-(integer-+---+-+-------------+-)-' |
| +-K-+ +-CODEUNITS16-+ |
| +-M-+ '-CODEUNITS32-' |
| '-G-' |
| .-(1)-------. |
+-+-+-+-NCHAR-------------------+--+-----------+------+-------+------------------------+
| | | '-NATIONAL--+-CHAR------+-' '-(integer)-' | | |
| | | '-CHARACTER-' | | |
| | '-+-NVARCHAR-------------------------+--(integer)-' | |
| | +-NCHAR VARYING--------------------+ | |
| | '-NATIONAL--+-CHAR------+--VARYING-' | |
| | '-CHARACTER-' | |
| | .-(1M)-------------. | |
| '-+-NCLOB---------------------------+--+------------------+-' |
| +-NCHAR LARGE OBJECT--------------+ '-(integer-+---+-)-' |
| '-NATIONAL CHARACTER LARGE OBJECT-' +-K-+ |
| +-M-+ |
| '-G-' |
| .-(1M)-------------. |
+-+-BLOB----------------+--+------------------+----------------------------------------+
| '-BINARY LARGE OBJECT-' '-(integer-+---+-)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
+-+-DATE-------------------------+-----------------------------------------------------+
| +-TIME-------------------------+ |
| | .-(--6--)-------. | |
| '-TIMESTAMP--+---------------+-' |
| '-(--integer--)-' |
+-XML----------------------------------------------------------------------------------+
+-BOOLEAN------------------------------------------------------------------------------+
+-CURSOR-------------------------------------------------------------------------------+
| .-SYSPROC.-. (3) |
'-+----------+--DB2SECURITYLABEL-------------------------------------------------------'
anchored-data-type
.-DATA TYPE-. .-TO-.
|--ANCHOR--+-----------+--+----+--+-variable-name1------------------------+--|
+-table-name.column-name----------------+
| .-OF-. |
'-ROW--+----+--+-table-name-----------+-'
+-view-name------------+
'-cursor-variable-name-'
anchored-row-data-type
.-DATA TYPE-. .-TO-.
|--ANCHOR--+-----------+--+----+--+-variable-name-------------------------+--|
| .-OF-. |
'-ROW--+----+--+-table-name-----------+-'
+-view-name------------+
'-cursor-variable-name-'
default-clause
|--DEFAULT--+-NULL-------------+--------------------------------|
+-constant---------+
+-special-register-+
+-global-variable--+
'-(--expression--)-'
column-list
.-,---------------------------.
V |
|--(----column-name--| data-type3 |-+--)------------------------|
data-type3
|--+-| built-in type |----+-------------------------------------|
+-distinct-type-name---+
+-REF--(--type-name--)-+
'-structured-type-name-'
option-list
.-LANGUAGE SQL-.
|--●--+--------------+--●--+------------------------------+----->
'-PARAMETER CCSID--+-ASCII---+-'
'-UNICODE-'
.-NOT DETERMINISTIC-.
>--●--+-------------------------+--●--+-------------------+----->
'-SPECIFIC--specific-name-' '-DETERMINISTIC-----'
.-EXTERNAL ACTION----. .-READS SQL DATA--------.
>--●--+--------------------+--●--+-----------------------+--●--->
'-NO EXTERNAL ACTION-' +-CONTAINS SQL----------+
| (4) |
'-MODIFIES SQL DATA-----'
.-STATIC DISPATCH-. .-CALLED ON NULL INPUT-.
>--+-----------------+--●--+----------------------+--●---------->
.-INHERIT SPECIAL REGISTERS-.
>--+---------------------------+--●----------------------------->
>--+---------------------------------------------------+-------->
| (5) |
'-PREDICATES--(--| predicate-specification |--)-----'
.-INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST-.
>--+----------------------------------------------+------------->
'-INHERIT ISOLATION LEVEL WITH LOCK REQUEST----'
.-NOT SECURED-.
>--+-------------+----------------------------------------------|
'-SECURED-----'
SQL-function-body
|--+-RETURN----------------------+------------------------------|
| (6) |
+-Compound SQL (compiled)-----+
'-Compound SQL (inlined)------'
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
- 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 types of the anchor object columns have the same limitations
that apply to data-type3.
- 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 objects (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.
- 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.
The default is READS SQL DATA.
- 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).
- 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.
- 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).
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 alternatives are
supported for compatibility with previous versions of DB2® and
with other database products. 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