Creating user-defined functions
The CREATE FUNCTION statement registers a user-defined function with a database server.
Procedure
To create a user-defined function:
- External scalar
- The function is written in a programming language and returns a scalar value. The external executable routine (package) is registered with a database server along with various attributes of the function. Each time that the function is invoked, the package executes one or more times. See CREATE FUNCTION statement (external scalar function).
- External table
- The function is written in a programming language. It returns a table to the subselect from which it was started by returning one row at a time, each time that the function is started. The external executable routine (package) is registered with a database server along with various attributes of the function. Each time that the function is invoked, the package executes one or more times. See CREATE FUNCTION statement (external table function).
- Sourced
- The function is implemented by invoking another function (either built-in, external, SQL, or sourced) that exists at the server. The function inherits the attributes of the underlying source function. A sourced function does not have an associated package. See CREATE FUNCTION statement (sourced function).
- SQL scalar
- The function is written exclusively in SQL statements and returns a scalar value. The body of an SQL scalar function is written in the SQL procedural language (SQL PL). The function is defined at the current server along with various attributes of the function.Db2 supports two types of SQL scalar functions, inlined and compiled:
- Inlined SQL scalar functions contain a single RETURN statement, which returns the value of a simple expression. The function is not invoked as part of a query; instead, the expression in the RETURN statement of the function is copied (inlined) into the query itself. Therefore, a package is not generated for an inlined SQL scalar function.
- Compiled SQL scalar functions support a larger set of functionality, including all of the SQL PL statements. A package is generated for a compiled SQL scalar function. It contains the body of the function, including control statements. It might also contain statements generated by Db2. Each time that the function is invoked, the package executes one or more times.
When a CREATE FUNCTION statement for an SQL scalar function is processed, Db2 attempts to create an inlined SQL scalar function. If the function cannot be created as an inlined function, Db2 attempts to create a compiled SQL scalar function. For more information on the syntax and rules for these types of functions, see CREATE FUNCTION statement (inlined SQL scalar function) and CREATE FUNCTION statement (compiled SQL scalar function).
To determine what type of SQL scalar function is created, refer to the INLINE column of the SYSIBM.SYSROUTINES catalog table.
- SQL table
- The function is written exclusively as an SQL RETURN statement and returns a set of rows. The body of an SQL table function is written in the SQL procedural language. The function is defined at the current server along with various attributes. The function is not invoked as part of a query. Instead, the expression in the RETURN statement of the function is copied (inlined) into the query itself. Therefore, a package is not generated for an SQL table function. See CREATE FUNCTION statement (SQL table function).
Example
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%';