Using the cursor to return record sets

You use a cursor to return recordsets from stored procedures and functions. A cursor defines a set of rows and row-by-row operations on the set. You need to know how to open a cursor, fetch data into local variables row-by-row, and close a cursor.

A function called fizzbuzz2 calls another function fizzbuzz, created in the previous topic. The new function declares a cursor of type SYS_REFCURSOR to fetch the contents of the numbers table, also created in the previous topic.

You created the fizzbuzz function.
You created the numbers table.

Use an OPEN-FOR statement to select the number in each row of the numbers table, iteratively FETCH the numerical content of the each row INTO cursor variable num, close the cursor, and print the results.

create function fizzbuzz2() returns string
begin
 declare num int = 0;
 declare result string = '';
 declare curs SYS_REFCURSOR; 
 open curs for select n from numbers;
 fetch curs into num;
 while (SQLCODE = 0) do
    result = result || fizzbuzz(num) || ' ';
    fetch curs INTO num;
 end while;
 close curs;
 return result;
end;
print fizzbuzz2();

The output looks something like this:

...
1 2 FIZZ 4 BUZZ 1 2 FIZZ 4 BUZZ FIZZ 7 8 FIZZ BUZZ
No rows affected