A function cursor

A function cursor enables you to scan multiple rows of data that the user-defined function returns.

The following user-defined functions can return more than one row:
  • An SPL function that contains the WITH RESUME keywords in its RETURN statement

    For information about how to write this type of SPL function, see the chapter on SPL in the Informix® Guide to SQL: Tutorial.

  • An external function that is an iterator function

    For information about how to write an iterator function, see the Informix® DataBlade® API Programmer's Guide.

You execute a user-defined function with the EXECUTE FUNCTION statement. The DECLARE statement associates the EXECUTE FUNCTION with the function cursor. In the DECLARE statement, the EXECUTE FUNCTION statement can be in either of the following formats:
  • A literal EXECUTE FUNCTION statement in the DECLARE statement
    The following DECLARE statement associates a literal EXECUTE FUNCTION statement with the func1_curs cursor: 
    EXEC SQL declare func1_curs cursor for execute function
   func1();
  • A prepared EXECUTE FUNCTION statement in the DECLARE statement
    The following DECLARE statement associates a prepared EXECUTE FUNCTION statement with the func2_curs cursor: 
    EXEC SQL prepare func_stmt from
      'execute function func1()';
EXEC SQL declare func2_curs cursor for func_stmt;

If the external or SPL function returns only one row, it does not require a function cursor to execute.