Executing a cursor function

A cursor function can return one or more rows of return values to the application. To execute a cursor function, you must associate the EXECUTE FUNCTION statement with a function cursor and use the FETCH...INTO SQL DESCRIPTOR statement to save the return value or values in a system-descriptor area.

About this task

To use a system-descriptor area to hold cursor-function return values:

Procedure

  1. Declare a function cursor for the user-defined function.

    Use the DECLARE statement to associate the EXECUTE FUNCTION statement with a function cursor.

  2. Use the OPEN statement to execute the function and open the cursor.
  3. Use the FETCH...USING SQL DESCRIPTOR statement to retrieve the return values from the cursor into the system-descriptor area.

    For more information, see Put column values into a system-descriptor area.

  4. Use the GET DESCRIPTOR statement to retrieve the return values from the system-descriptor area into host variables.

    The DATA field of each item descriptor contains the return values. For more information, see Assign and obtain values from a system-descriptor area.

  5. Deallocate the system-descriptor area with the DEALLOCATE DESCRIPTOR statement.

    For more information, see Free memory allocated to a system-descriptor area.

Results

Only an external function that is defined as an iterator function can return more than one row of data. Therefore, you must define a function cursor to execute an iterator function dynamically. Each row of data consists of only one value because an external function can only return a single value. For each row, the system-descriptor area contains only one item descriptor with the single return value.

An SPL function whose RETURN statement includes the WITH RESUME keywords can return one or more rows of data. Therefore, you must define a function cursor to execute these SPL functions dynamically. Each row of data can consists of one or more values because an SPL function can return one or more values at one time. For each row, the system-descriptor area contains an item descriptor for each return value.