Returning Multiple Result Sets

So far, the examples presented have returned a single result set to the calling application. It is also possible to define multiple cursors and return multiple result sets to the caller. To do this, the following are required:

• Specify DYNAMIC RESULT SETS clause in the CREATE PROCEDURE statement corresponding to the number of result sets you intend to return.

• Declare the cursors for each result set using the WITH RETURN clause.

• Keep all cursors to be returned open for the client application.

Figure 5.22 shows a procedure that returns multiple result sets. To demonstrate the use of multiple result sets, the procedure read_emp from Figure 5.20 is rewritten to return each of the columns via three separate cursors, instead of one cursor with three columns. An example of a Java and C program used to receive multiple result sets has been provided in Appendix H, "Sample Application Code."

Figure 5.22. An example of returning multiple result sets.

 CREATE PROCEDURE read_emp_multi ( )     LANGUAGE SQL     SPECIFIC read_emp_multi                -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                  -- applies to zSeries     DYNAMIC RESULT SETS 3                  --(1) re: BEGIN     -- Procedure logic     DECLARE c_salary CURSOR WITH RETURN FOR         SELECT salary           FROM employee;     DECLARE c_bonus CURSOR WITH RETURN FOR         SELECT bonus           FROM employee;     DECLARE c_comm CURSOR WITH RETURN FOR         SELECT comm           FROM employee;     OPEN c_salary;     OPEN c_bonus;     OPEN c_comm; END re 

Because three result sets are returned, the DYNAMIC RESULT SETS value is set to 3 in Line (1). Each cursor is declared and left open for the client application. The order in which the cursors are opened reflects the order in which the result sets are returned to the client. In this example, the first result set your client code can access is the salary result set, followed by the bonus and then the commission result sets.