A function cursor

To execute an EXECUTE FUNCTION statement whose user-defined function returns more than one row, you must declare a function cursor. The function cursor enables the application to handle the multiple rows that a user-defined function returns.

Your program must take the following actions to use a function cursor:
  1. Declare host variables to receive the values that the user-defined function returns.
  2. Assemble and prepare the statement.

    A prepared EXECUTE FUNCTION statement can contain input parameters as function arguments.

  3. Declare the function cursor.

    The DECLARE statement associates the prepared EXECUTE FUNCTION statement with the function cursor.

  4. Execute the user-defined function.

    The OPEN statement sends any input parameters that its USING clause specifies to the database server and tells the database server to execute the EXECUTE FUNCTION statement.

  5. Retrieve the rows of values from the function cursor.

    The FETCH statement retrieves one row of values that the user-defined function returns.

Only an external function that is an iterator function can return more than one row of data. For information about how to write an iterator function, see the HCL OneDB™ DataBlade® API Programmer's Guide.

If the RETURN statement of the SPL function contains the WITH RESUME keywords, then the function can return more than one row. You must associate such an SPL function with a function cursor. To execute the SPL function dynamically, associate the EXECUTE FUNCTION statement with a cursor, use the OPEN statement to execute the function, and use the FETCH...INTO statement to retrieve the rows from the cursor into host variables.