User-Defined Functions

A user-defined function (UDF) is a routine that you write in SPL or in a language external to the database, such as C or Java™, and that returns a value to its calling context.

A UDF as an expression has the following syntax:

Notes:

¹ See Expression
² See Statement-Local Variable Declaration


Element	Description	Restrictions	Syntax
function	Name of the function	Function must exist	Database Object Name
parameter	Name of an argument that was declared in a CREATE FUNCTION statement	If you use the parameter = option for any argument in the called function, you must use it for all arguments	Identifier

You can call user-defined functions within SQL statements. Unlike built-in functions, user-defined functions can be invoked only by the creator of the function, and by the DBA, and by users who have been granted the Execute privilege on the function. For more information, see Routine-Level Privileges.

The following examples show some user-defined function expressions. The first example omits the parameter option when it lists the function argument:

read_address('Miller')

This second example uses the parameter option to specify the argument value:

read_address(lastname = 'Miller')

When you use the parameter option, the parameter name must match the name of the corresponding parameter in the function registration. For example, the preceding example assumes that the read_address( ) function was registered as follows:

CREATE FUNCTION read_address(lastname CHAR(20))
   RETURNING address_t ...  ;

A statement-local variable (SLV) enables an application to transmit a value from a user-defined function call to another part of the same SQL statement.

To use an SLV with a call to a user-defined function

Write one or more OUT parameters (and for UDRs written in the Java or in the SPL language, INOUT parameters) for the user-defined function.
For information about how to write a UDR with OUT or INOUT parameters, see HCL OneDB™ User-Defined Routines and Data Types Developer's Guide.
When you register the user-defined function, specify the OUT keyword before each OUT parameter, and the INOUT keyword before each INOUT parameter.
For more information, see Specifying INOUT Parameters for a User-Defined Routine, and Specifying OUT Parameters for User-Defined Routines.
Declare the SLV in a function expression that calls the user-defined function with each OUT and INOUT parameter.
The call to the user-defined function must be made within a WHERE clause. For information about the syntax to declare the SLV, see Statement-Local Variable Declaration.
Use the SLV that the user-defined function has initialized within the SQL statement.
After the call to the user-defined function has initialized the SLV, you can use this value in other parts of the same SQL statement in which the SLV was declared, including subqueries of the query whose WHERE clause includes the SLV declaration. For information about the use of an SLV within the SELECT statement, see Statement-Local Variable Expressions.

Besides using a SLV to retrieve a value from an OUT or INOUT parameter, you can also use a local variable or a parameter of an SPL routine to retrieve values from an SPL or C routine that has OUT or INOUT parameters.