WITH RESUME Keyword

If you use the WITH RESUME keywords, then after the RETURN statement completes execution, the next invocation of the SPL function (upon the next FETCH or FOREACH statement) starts from the statement that follows the RETURN statement. Any function that executes a RETURN WITH RESUME statement must be invoked within a FOREACH loop, or else in the FROM clause of a SELECT statement. If an SPL routine executes a RETURN WITH RESUME statement, a FETCH statement in the application can call the SPL routine.

The following example shows a cursor function that another UDR can call. After the RETURN WITH RESUME statement returns each value to the calling UDR or program, the next line of series executes the next time series is called. If the variable backwards equals zero (0), no value is returned to the calling UDR or program, and execution of series stops:

CREATE FUNCTION series (limit INT, backwards INT) RETURNING INT;
   DEFINE i INT;
   FOR i IN (1 TO limit)
      RETURN i WITH RESUME;
   END FOR;
   IF backwards = 0 THEN
      RETURN;
   END IF;
   FOR i IN (limit TO 1 STEP -1)
      RETURN i WITH RESUME;
   END FOR;
END FUNCTION; -- series