- 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.
- 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). 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 hand 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 hand 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 (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 above under data-type1 for function
parameters.
- ROW column-list
- 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). The column-list must include at
least two columns (SQLSTATE 428F0).
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).
- TABLE column-list
- Specifies that the output of the function is a table.
- column-list
- The list of column names and data types returned for a ROW or
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 of the row.
- 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
above under data-type1 for function parameters.
However, data-type3 does not support the
following: anchored-data-type, array-type-name, cursor-type-name,
and row-type-name.
- 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.
- CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA
- Indicates what type of SQL statements can be executed.
- CONTAINS SQL
- Indicates that SQL statements that neither read nor modify SQL
data can be executed by the function (SQLSTATE 42985).
- READS SQL DATA
- Indicates that SQL statements that do not modify SQL data can
be executed by the function (SQLSTATE 42985).
- MODIFIES SQL DATA
- Indicates that all SQL statements supported in the
SQL-function-body can be executed by the function.
- STATIC DISPATCH
- This optional clause indicates that at function resolution time, DB2® chooses a function 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.
DB2 Version 9.7 for Linux, UNIX, and Windows
>>-CREATE--+------------+--FUNCTION--function-name--------------> '-OR REPLACE-' >--(--+-------------------------------+--)--●-------------------> | .-,-------------------------. | | V | | '---| parameter-declaration |-+-' >--RETURNS--+-| data-type2 |-------------+--| option-list |-----> '-+-ROW---+--| column-list |-' '-TABLE-' >--| 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 BYTE)------------. | +-+-+-+-CHARACTER-+--+---------------------+----------+--+--------------+-+-+ | | | '-CHAR------' | .-BYTE-. | | '-FOR BIT DATA-' | | | | | '-(integer-+------+-)-' | | | | | | .-BYTE-. | | | | | '-+-VARCHAR----------------+--(integer-+------+-)-' | | | | '-+-CHARACTER-+--VARYING-' | | | | '-CHAR------' | | | | .-(1M)-------------. | | | '-+-CLOB------------------------+--+------------------+-----------------' | | '-+-CHARACTER-+--LARGE OBJECT-' '-(integer-+---+-)-' | | '-CHAR------' +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-GRAPHIC--+-----------+-------+------------------------------------------+ | | '-(integer)-' | | | +-VARGRAPHIC--(integer)--------+ | | | .-(1M)-------------. | | | '-DBCLOB--+------------------+-' | | '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-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.-. (2) | '-+----------+--DB2SECURITYLABEL--------------------------------------------' anchored-data-type .-DATA TYPE-. .-TO-. |--ANCHOR--+-----------+--+----+--+-variable-name1------------------------+--| +-table-name.column-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----------+ | (3) | '-MODIFIES SQL DATA-----' .-STATIC DISPATCH-. .-CALLED ON NULL INPUT-. >--+-----------------+--●--+----------------------+--●----------> .-INHERIT SPECIAL REGISTERS-. >--+---------------------------+--●-----------------------------> >--+---------------------------------------------------+--------> | (4) | '-PREDICATES--(--| predicate-specification |--)-----' .-INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST-. >--+----------------------------------------------+-------------| '-INHERIT ISOLATION LEVEL WITH LOCK REQUEST----' SQL-function-body |--+-RETURN----------------------+------------------------------| | (5) | +-Compound SQL (compiled)-----+ '-Compound SQL (inlined)------'