INTO Clause with Cursors

If EXECUTE FUNCTION calls a UDF that returns more than one row of values, it must execute a cursor function. A cursor function can return one or more rows of values and must be associated with a Function cursor to execute.

If the SPL function returns more than one row or a collection data type, you must access the rows or collection elements with a cursor.

To return more than one row of values, an external function (one written in the C or Java™ language) must be defined as an iterator function. For more information on iterator functions, see the HCL OneDB™ DataBlade® API Programmer's Guide.

In an SPL routine, if a SELECT returns more than one row, you must use the FOREACH statement to access the rows individually. The INTO clause of the SELECT statement can store the fetched values. For more information, see FOREACH.

To return more than one row of values, an SPL function must include the WITH RESUME keywords in its RETURN statement. For more information on how to write SPL functions, see the HCL OneDB Guide to SQL: Tutorial.

In programs, the DECLARE statement can declare a Function cursor and the FETCH statement can return rows individually from the cursor. You can put the INTO clause in the EXECUTE FUNCTION or in the FETCH statement, but you cannot put it in both. The following code examples show different ways you can use the INTO clause:

Using the INTO clause in the EXECUTE FUNCTION statement:

EXEC SQL declare f_curs cursor for
   execute function get_orders(customer_num)
   into :ord_num, :ord_date;
EXEC SQL open f_curs;
while (SQLCODE == 0)
   EXEC SQL fetch f_curs;
EXEC SQL close f_curs;

Using the INTO clause in the FETCH statement:

EXEC SQL declare f_curs cursor for
   execute function get_orders(customer_num);
EXEC SQL open f_curs;
while (SQLCODE == 0)
   EXEC SQL fetch f_curs into :ord_num, :ord_date;
EXEC SQL close f_curs;