You can receive result sets from procedures you invoke
from within an SQL-bodied routine.
About this task
To accept procedure result sets from within an SQL-bodied
routine:
Before you begin
You must know how many result sets the invoked procedure
will return. For each result set that the invoking routine receives,
a result set must be declared.
Procedure
- DECLARE result set locators for each result set that the
procedure will return. For example:
DECLARE result1 RESULT_SET_LOCATOR VARYING;
DECLARE result2 RESULT_SET_LOCATOR VARYING;
DECLARE result3 RESULT_SET_LOCATOR VARYING;
- Invoke the procedure. For example:
CALL targetProcedure();
- ASSOCIATE the result set locator variables (defined above)
with the invoked procedure. For example:
ASSOCIATE RESULT SET LOCATORS(result1, result2, result3)
WITH PROCEDURE targetProcedure;
- ALLOCATE the result set cursors passed from the invoked
procedure to the result set locators. For example:
ALLOCATE rsCur CURSOR FOR RESULT SET result1;
- FETCH rows from the result sets. For example:
FETCH rsCur INTO ...