The CREATE FUNCTION statement creates a user-defined function, that is then registered with the system. You can create several types of functions:
For more information, see User-Defined Functions in the SQL Programming topic in the
Information Center.
Returns a single value from an external program written in one of the following languages:
The program that is called by the function needs to be designed correctly, based on the PARAMETER STYLE.
Write an external function service program in C that implements the following logic:
output = 2 * input - 4
The function should return a null value if and only if one of the input arguments is null. A simple way that avoids a function call and returns a null result when an input value is null is to specify RETURNS NULL ON NULL INPUT on the CREATE FUNCTION statement. The following example, however, assumes the code to return null if the input parameter is null in the C program. Write the statement needed to create the function, using the specific name MINENULL1.
CREATE FUNCTION NTEST1 (INTEGER) RETURNS INTEGER EXTERNAL NAME 'MYLIB/NTESTMOD(nudft1)' SPECIFIC MINENULL1 LANGUAGE C DETERMINISTIC NO SQL PARAMETER STYLE DB2SQL CALLED ON NULL INPUT NO EXTERNAL ACTION
For a complete listing of parameters, see CREATE FUNCTION (EXTERNAL) and SQL control statements in the SQL Reference topic in the
Information Center.
The CREATE FUNCTION (External table) statement creates an external table function. The table function may be used in the FROM clause of a SELECT, and returns a table to the SELECT by returning one row at a time.
The following registers a table function written to traverse a directory. It will return a row consisting of names and characteristics of files in a directory where the directory name is the input parameter.
CREATE FUNCTION listDirectory VARCHAR(250)) RETURNS TABLE (F_Name VARCHAR(250), F_Type VARCHAR(20), F_Link VARCHAR(250), F_Len INT) EXTERNAL NAME 'Udfsrv!listDirectory' LANGUAGE JAVA DETERMINISTIC NO SQL PARAMETER STYLE DB2GENERAL CALLED ON NULL INPUT NO EXTERNAL ACTION SCRATCHPAD FINAL CALL CARDINALITY 50 DISALLOW PARALLEL
For more information, see CREATE FUNCTION (External Table) in the SQL Reference topic in the
Information Center.
The CREATE FUNCTION (SQL scalar) statement creates an SQL function. This function returns a single result.
Create the SQL function NTEST1 to implement the rule:
output = 2 * input - 4 CREATE FUNCTION NTEST1 (p_input INTEGER) RETURNS INTEGER
LANGUAGE SQL SPECIFIC MINENULL1 func1_lab: BEGIN DECLARE p_output INT; IF p_input IS NULL THEN SET p_output = NULL; ELSE SET p_output = 2 * p_input - 4; END IF; RETURN p_output; END
For more information, see CREATE FUNCTION (SQL) and SQL control statements in the SQL Reference topic in the
Information Center.
The CREATE FUNCTION (SQL table) statement creates an SQL table function. The table function may be used in the FROM clause of a SELECT, and returns a table to the SELECT by returning one row at a time.
The following table function returns the employees in a specified department number.
CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3) RETURNS TABLE (EMPNO CHAR (6), LASTNAME VARCHAR(15), FIRSTNME VARCHAR(12)) LANGUAGE SQL DETERMINISTIC READS SQL DATA CALLED ON NULL INPUT NO EXTERNAL ACTION CARDINALITY 20 DISALLOW PARALLEL RETURN SELECT EMPNO, LASTNAME, FIRSTNME FROM CORPDATA.EMPLOYEE WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO
For more information, see CREATE FUNCTION (SQL table) in the SQL Reference topic in the
Information Center.
This CREATE FUNCTION statement is used to create a user-defined function, based on another existing scalar or column function. For a listing of data types, see Function list.
You created a distinct type HATSIZE that is based on the built-in INTEGER data type. Create an AVG function to compute the average hat size.
CREATE FUNCTION AVG (HATSIZE) RETURNS HATSIZE SOURCE AVG (INTEGER)
For more information, see CREATE FUNCTION (SOURCED) in the SQL Reference topic in the
Information Center.