CREATE FUNCTION
The CREATE FUNCTION statement registers a user-defined function with a database server. Each type of function that you can register with this statement is described separately.
- External scalar
- The function is written in a programming language and returns a scalar value. The external executable routine is registered with a database server along with various attributes of the function. See CREATE FUNCTION (external scalar).
- 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 is registered with a database server along with
various attributes of the function. See CREATE FUNCTION (external table).
- Sourced
- The function is implemented by starting another function (either built-in, external, SQL, or sourced) that exists at the server. The function inherits the attributes of the underlying source function. See CREATE FUNCTION (sourced).
- 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. 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.
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 (inlined SQL scalar) and CREATE FUNCTION (compiled SQL scalar).
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 of the function. See CREATE FUNCTION (SQL table).