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:
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.