A noncursor function

You can use the PREPARE and EXECUTE statement to execute a user-defined noncursor function. A noncursor function returns only one row of values.

Your program must take the following actions:

Declare host variables to receive the values that the database server returns.
For a prepared EXECUTE FUNCTION statement, these values are the return values of the user-defined function.
Assemble and prepare the statement.
A prepared EXECUTE FUNCTION statement can contain input parameters as function arguments.
Execute the prepared user-defined function with the EXECUTE...INTO statement, with the host variables after the INTO keyword.
If the EXECUTE FUNCTION contains input parameters, include the USING clause of EXECUTE.

Important: To execute a noncursor function, EXECUTE...INTO is more efficient than the DECLARE, OPEN, and FETCH statements. However, you often do not know the number of returned rows. When you do not use a cursor to execute a cursor function that returns multiple rows, generates a runtime error. Therefore, it is a good practice to always associate a user-defined function with a cursor.

Most external functions can return only one row of data and only a single value. For example, the following code fragment executes an external function called stnd_dev():

strcpy(func_name, "stnd_dev(ship_date)");
sprintf(exfunc_stmt, "%s %s %s", 
         "execute function",
         func_name);
EXEC SQL prepare exfunc_id from :exfunc_stmt;
EXEC SQL execute exfunc_id into :ret_val;

To return more than one value, the external function must return a complex data type, such as a collection or a row type.

An SPL function can return one or more values. If the RETURN statement of the SPL function does not contain the WITH RESUME keywords, then the function returns only one row. To execute the SPL function dynamically, prepare the EXECUTE FUNCTION and execute it with the EXECUTE...INTO statement.