Invoking User-Defined Functions

In Chapter 4, "Working with Databases and Database Objects," we saw that user-defined functions (UDFs) are special objects that are used to extend and enhance the support provided by the built-in functions available with DB2 9. Like user-defined data types, user-defined functions (or methods) are created and named by a database user. However, unlike DB2's built-in functions, user-defined functions can take advantage of system calls and DB2's administrative APIs, thereby providing more synergy between applications and databases.

Five types of user-defined functions can be created:

• Sourced (or Template). A sourced function is constructed from a function that is already registered with a database (referred to as the source function). Sourced functions can be columnar, scalar, or table in nature or they can be designed to overload a specific operator such as +, -, *, and /. When a sourced function is invoked, all arguments passed to it are converted to the data types that are expected by the underlying source function, and the source function itself is invoked. Upon completion, the source function performs any conversions necessary on the results produced and returns them to the calling SQL statement. The most common use of sourced functions is to enable a user-defined distinct data type to selectively inherit some of the semantics of the built-in data type on which it is based.

• SQL Scalar, Table, or Row. Whereas a sourced function is constructed from a function that already exists, an SQL function is constructed from the ground up, using only SQL statements. An SQL function can be scalar in nature (scalar functions return a single value and can be specified in an SQL statement wherever a regular expression can be used) or can return a single row or an entire table.

• External Scalar. An external scalar function is a function that is written using a high-level programming language such as C, C++, or Java that returns a single value. The function itself resides in an external library and is registered in the database, along with any related attributes.

• External Table. Like external scalar functions, external table functions are written using a high-level programming language. But where an external scalar function returns a single value, an external table function returns a result data set, in the form of a table, to the SQL statement that references it. External table functions are powerful because they enable you to make almost any source of data appear to be a DB2 base table; the result data set returned can be used in join operations, grouping operations, set operations (for example, UNIONs), or any other operation that can be applied to a read-only view. Again, the function itself resides in an external library and is registered in the database, along with any related attributes.

• OLE DB External Table. OLE DB is designed to provide access to all types of data in an OLE Component Object Model (COM) environment. Like external table functions, external OLE DB table functions are written using a high-level programming language and return a result data set, in the form of a table, to SQL statements that references them. However, with OLE DB table functions, a generic built-in OLE DB consumer can be used to interface with any OLE DB provider to access data; you need only to register an OLE DB table function and refer to the appropriate OLE DB provider as the data source. No additional programming is needed. Again, the function resides in an external library and is registered in the database, along with any related attributes.

How a user-defined function is invoked depends a lot on what it has been designed to do; scalar user-defined functions can be invoked as an expression in the select list of a query, whereas table and row functions must be referenced by the FROM clause. For example, you could invoke a scalar user-defined function named CONVERT_TEMP that is designed to convert temperatures in degrees Fahrenheit to degrees Celsius by executing a SELECT statement that looks something like this:

 SELECT temp AS tempf, convert_temp(temp, 'F') AS tempc FROM climate_info 

On the other hand, if you wanted to obtain records from a user-defined function named MGR_LIST that is designed to produce a list of managers and their contact information be examining records stored in a table named EMPLOYEE, you would so by executing a SELECT statement that looks something like this:

 SELECT firstname, lastname, phone FROM TABLE(MGR_LIST())