Creation of user-defined functions

You can create your own functions in DB2® to simplify your queries.

There are three primary types of user-defined functions.

Sourced functions: Functions that are based on existing functions.
External functions: Functions that are developed by users.
SQL functions: Functions that are defined to the database by use of SQL statements only.

External user-defined functions can return a single value or a table of values.

External functions that return a single value are called user-defined scalar functions.
External functions that return a table are called user-defined table functions.

User-defined functions, like built-in functions or operators, support the manipulation of distinct types.

Begin general-use programming interface information.

The following two examples demonstrate how to define and use both a user-defined function and a distinct type.

Example 1: Suppose that you define a table called EUROEMP. One column of this table, EUROSAL, has a distinct type of EURO, which is based on DECIMAL(9,2). You cannot use the built-in AVG function to find the average value of EUROSAL because AVG operates on built-in data types only. You can, however, define an AVG function that is sourced on the built-in AVG function and accepts arguments of type EURO:

CREATE FUNCTION AVG(EURO)
  RETURNS EURO
  SOURCE SYSIBM.AVG(DECIMAL);

Example 2: You can then use this function to find the average value of the EUROSAL column:

SELECT AVG(EUROSAL) FROM EUROEMP;

The next two examples demonstrate how to define and use an external user-defined function.

Example 3: Suppose that you define and write a function, called REVERSE, to reverse the characters in a string. The definition looks like the following example:

CREATE FUNCTION REVERSE(VARCHAR(100))
  RETURNS VARCHAR(100)
  EXTERNAL NAME 'REVERSE'
  PARAMETER STYLE SQL
  LANGUAGE C;

Example 4: You can then use the REVERSE function in an SQL statement wherever you would use any built-in function that accepts a character argument, as shown in the following example:

SELECT REVERSE(:CHARSTR)
  FROM SYSDUMMY1;

Although you cannot write user-defined aggregate functions, you can define sourced user-defined aggregate functions that are based on built-in aggregate functions. This capability is useful in cases where you want to refer to an existing user-defined function by another name or where you want to pass a distinct type.

The next two examples demonstrate how to define and use a user-defined table function.

Example 5: You can define and write a user-defined table function that users can invoke in the FROM clause of a SELECT statement. For example, suppose that you define and write a function called BOOKS. This function returns a table of information about books on a specified subject. The definition looks like the following example:

CREATE FUNCTION BOOKS     (VARCHAR(40))
  RETURNS TABLE (TITLE_NAME     VARCHAR(25),
                 AUTHOR_NAME    VARCHAR(25),
                 PUBLISHER_NAME VARCHAR(25),
                 ISBNNO         VARCHAR(20),
                 PRICE_AMT      DECIMAL(5,2),
                 CHAP1_TXT      CLOB(50K))
  LANGUAGE COBOL
  PARAMETER STYLE SQL        
  EXTERNAL NAME BOOKS;

Example 6: You can then include the BOOKS function in the FROM clause of a SELECT statement to retrieve the book information, as shown in the following example:

SELECT B.TITLE_NAME, B.AUTHOR_NAME, B.PUBLISHER_NAME, B.ISBNNO
  FROM TABLE(BOOKS('Computers')) AS B
  WHERE B.TITLE_NAME LIKE '%COBOL%';

End general-use programming interface information.