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.
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).
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).
>>-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)------'
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.
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).
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).
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).
In this statement, exactly the same considerations apply as for the parameters of SQL functions described above under data-type1 for function parameters.
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).
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.
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.
The phrase NULL CALL may be used in place of CALLED ON NULL INPUT.
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.
For RETURN statement, see: RETURN statement.
For Compound SQL (compiled), see: Compound SQL (compiled) statement.
For Compound SQL (inlined), see: Compound SQL (inlined) statement.
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).
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.
CREATE FUNCTION TAN (X DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SIN(X)/COS(X)
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)
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
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
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
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@
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
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