RESULT SETS
From SQL PL Guide for DB2
RESULT SETS in DB2 Stored Procedures
Instead of fetching rows in a loop by the fetch statement, you can return a complete result set to the calling program. This calling programm can be a stored procedure or a client application program.
There are five steps required to work with result sets:
- the stored procedure must be specified with the DYNAMIC RESULT SETS clause
- a cursor must be declared with the WITH RETURN clause
- the cursor must be opened by the OPEN statement to return the result set
- don't close the cursor for the client application
It's possible to return multiple result sets. Then the number of result sets must be increased in the DYNAMIC RESULT SET clause and for each result set a cursor must be declared.
Example:
create procedure proc_result_set ()
language sql
result sets 1
begin
declare mycur cursor with return to caller for
select firstnme, lastname from employee order by lastname, firstnme;
open mycur;
end!