CREATE FUNCTION

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 CenterLink to Information center.

CREATE FUNCTION (External scalar)

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.

Example:

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 CenterLink to Information center.

CREATE FUNCTION (External table)

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.

Example:

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 CenterLink to Information center.

CREATE FUNCTION (SQL scalar)

The CREATE FUNCTION (SQL scalar) statement creates an SQL function.  This function returns a single result.

Example:

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 CenterLink to Information center.

CREATE FUNCTION (SQL table)

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.

Example:

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 CenterLink to Information center.

CREATE FUNCTION (Sourced)

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.

Example:

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 CenterLink to Information center.