In previous chapters, you have learned how to use cursors to return result sets to calling applications. You have an additional consideration, though, with returning result sets when nested SQL procedures are used. You can either
The target where you return the result set can be defined by using the WITH RETURN clause of the DECLARE CURSOR statement (see Figure 8.5).
Figure 8.5. A partial DECLARE CURSOR syntax.
>>-DECLARE--cursor-name--CURSOR----+------------+---------------> '-WITH HOLD--' >-----+------------------------------+--------------------------> | .-TO CALLER--. | '-WITH RETURN--+------------+--' '-TO CLIENT--' >----FOR--+-select-statement-+--------------------------------->< '-statement-name---'
As discussed in Chapter 5, "Understanding and Using Cursors and Result Sets," the DECLARE CURSOR statement defines a cursor. When the WITH RETURN TO CALLER clause is used, the result sets will be returned to the direct caller, which can be either another SQL procedure or an external application. When the WITH RETURN TO CLIENT clause is used, the result sets will be returned to the external application only. The result sets will be invisible to all intermediate SQL procedures.
Valid options on zSeries are WITHOUT RETURN (the default) and WITH RETURN TO CALLER. WITH RETURN TO CLIENT is not supported.
Figure 8.6 shows the differences between how cursors will return their result sets depending on the RETURN TO clause. The stored procedures are described in detailed in the next sections.
Figure 8.6. Returning cursors in nested stored procedures.
Returning Result Sets to the Client
Figure 8.7 shows an example of an SQL procedure that uses a cursor to return result sets to the client. The procedure returns the first names, the last names, and the salaries of all the employees in one department for a given department number.
Figure 8.7. An example of returning result sets to a client.
CREATE PROCEDURE to_client ( IN p_dept CHAR(3) ) LANGUAGE SQL SPECIFIC to_client -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries DYNAMIC RESULT SETS 1 tc: BEGIN -- Procedure logic -- DECLARE v_cur CURSOR WITH RETURN TO CALLER -- use this line for zSeries DECLARE v_cur CURSOR WITH RETURN TO CLIENT -- use this line for LUW and iSeries FOR SELECT firstnme, lastname, salary FROM employee WHERE workdept = p_dept; OPEN v_cur; END tc
A simple caller SQL procedure in Figure 8.8 demonstrates how to receive a result set in the caller procedure.
Figure 8.8. An example of receiving result sets from a client for LUW and iSeries.
CREATE PROCEDURE base_proc ( ) LANGUAGE SQL SPECIFIC base_proc DYNAMIC RESULT SETS 1 bp: BEGIN -- Declare variables DECLARE v_dept CHAR(3) DEFAULT 'A00'; -- Procedure logic CALL to_client(v_dept); END bp
On zSeries, although the procedure in Figure 8.8 builds and runs, it cannot handle the returned result set. Remember that RETURN TO CLIENT is not an option; therefore, the ASSOCIATE RESULT SET and ALLOCATE CURSOR statements have to be used. These statements are explained later in this chapter; you can also find an example of the base_proc for zSeries in Figure 8.16.
On LUW and iSeries, the procedure simply issues a CALL statement to to_client procedure. If you invoke the SQL procedure base_proc from the command window on LUW, you will see the output depicted in Figure 8.9.
Figure 8.9. The output of procedure base_proc.
FIRSTNME LASTNAME SALARY CHRISTINE HAAS 52750.00 VINCENZO LUCCHESSI 46500.00 SEAN O'CONNELL 29250.00 "BASE_PROC" RETURN_STATUS: "0"
For LUW and iSeries, even though there is no cursor declared in the body of the SQL procedure base_proc and there is no result set specified in its header, the calling application still received the result set. This is because the WITH RETURN TO CLIENT clause in the to_client SQL procedure causes DB2 to bypass any intermediate SQL procedures and return the result sets directly to the application. The output you see is actually from the to_client procedure. You should see the same output if you invoke to_client directly without going through the base_proc caller.
The cursor with the WITH RETURN TO CLIENT clause and its result sets are invisible to any intermediate SQL procedures.
If you need to use the result sets in both the caller SQL procedure and the client application, you will have to use WITH RETURN TO CALLER clause explained in the next section and re-return the rows in the caller SQL procedure.
The RETURN TO CLIENT option can be very useful when you have stored procedures with many layers of nested logic. You can use complex logic to determine which procedure should be called to generate your result set and then have it returned directly to the calling program. This allows you to have more flexibility in the modularity of your stored procedure design because the original calling procedure(s) do not need to have any idea of the final form of the result set returned to the caller.
Returning Result Sets to the Caller
To understand the difference between the WITH RETURN TO CLIENT and WITH RETURN TO CALLER clauses, let's look at the two nested SQL procedures in Figures 8.11 and 8.12, which are very similar to the two examples presented earlier. The diagram in Figure 8.10 shows how nested stored procedures can return result sets to each other.
Figure 8.10. Procedures returning cursors to the calling procedure.
Figure 8.11 shows the called SQL procedure using the WITH RETURN TO CALLER clause.
Figure 8.11. An example of returning result sets to the caller.
CREATE PROCEDURE to_caller1 ( IN p_dept CHAR(3) ) LANGUAGE SQL SPECIFIC to_caller1 -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries DYNAMIC RESULT SETS 1 tc1: BEGIN -- Procedure logic DECLARE v_cur CURSOR WITH RETURN TO CALLER FOR SELECT firstnme, lastname, salary FROM employee WHERE workdept = p_dept; OPEN v_cur; END tc1
Figure 8.12 shows the caller SQL procedure that simply invokes the called SQL procedure.
Figure 8.12. An example of invoking a procedure with the RETURN TO CALLER clause.
CREATE PROCEDURE base_proc2 ( ) LANGUAGE SQL SPECIFIC base_proc2 -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries bp2: BEGIN -- Declare variables DECLARE v_dept CHAR(3) DEFAULT 'A00'; -- Procedure logic CALL to_caller1(v_dept); -- (1) END bp2
As you can see, the only difference between these two sets of nested SQL procedures is the WITH RETURN clauses in the called SQL procedures. In procedure to_caller1, the result sets are to be passed to its direct caller, be it an SQL procedure or client application.
If another stored procedure invoked the base_proc2 procedure, you may think you would receive a result set because base_proc2 had a result set returned to it within its body. This would not be the case. The result set from the to_proc procedure is returned to base_proc2 on Line (1). However, this result is then not processed at all. It is therefore not passed back to any procedures that called it. If you wanted to pass the result back to the calling procedure, you would have to explicitly receive the result set and return it to the calling procedure. This is covered in the following section.
If you do not specify TO CLIENT or TO CALLER with the WITH RETURN clause, by default the result sets will be returned to the direct caller.
If you invoke the to_client procedure directly, the output will be identical to that of base_proc2. Obviously, it makes sense because you are calling two separate procedures directly instead of using nested procedures. The caller and the client application are always the same. There is no difference between the WITH RETURN TO CALLER clause and the WITH RETURN TO CLIENT clause outside the context of nested SQL procedures.
Receiving Result Sets as a Caller
To make use of the returned result sets from called procedures, you need to know how to access them in the calling procedure.
From the perspective of client applications, there is no difference in handling result sets from nested or non-nested procedures.
On LUW and zSeries, in order to access and make use of the returned result sets, the ASSOCIATE LOCATORS statement and the ALLOCATE CURSOR statement must be used in the caller SQL procedure. The ASSOCIATE LOCATORS syntax is illustrated in Figure 8.13.
Figure 8.13. ASSOCIATE LOCATORS syntax for LUW and zSeries.
.-RESULT SET--. >>-ASSOCIATE--+-------------+---+-LOCATOR--+--------------------> '-LOCATORS-' .-,----------------------. V | >----(-----rs-locator-variable---+---)--WITH PROCEDURE--procedure-name-><
The caller procedure uses ASSOCIATE LOCATORS to create pointers to the result sets returned by the nested procedure. The ALLOCATE CURSOR statement, shown in Figure 8.14, allocates cursors for the result sets using locator variables obtained by an ASSOCIATE LOCATORS statement.
Figure 8.14. ALLOCATE CURSOR syntax for LUW and zSeries.
>>-ALLOCATE--cursor-name--CURSOR FOR RESULT SET--rs-locator-variable--><
On iSeries, ASSOCIATE LOCATORS and ALLOCATE CURSOR are not supported. In order to have a procedure process result sets, you can create an external stored procedure in C or Java. Appendix H, "Sample Application Code," provides Java alternatives for Figures 8.15 and 8.18.
The example in Figure 8.15 is a caller SQL procedure, which invokes the to_caller1 called procedure from Figure 8.11. The to_caller1 procedure returns the first names, the last names, and the salaries of all employees in one department. The caller SQL procedure, total_salary, receives the result set and uses it to calculate the total salary of the department.
Figure 8.15. An example of receiving result sets from the caller procedure for LUW and zSeries.
CREATE PROCEDURE total_salary ( IN p_dept CHAR(3) , OUT p_total DECIMAL(9,2) ) LANGUAGE SQL SPECIFIC total_salary -- applies to LUW -- WLM ENVIRONMENT <env> -- applies to zSeries ts: BEGIN -- Declare variables DECLARE v_fname VARCHAR(12); DECLARE v_lname VARCHAR(15); DECLARE v_salary DECIMAL(9,2) DEFAULT 0.0; DECLARE v_rs RESULT_SET_LOCATOR VARYING; --(1) -- Declare returncodes DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; -- Procedure logic CALL to_caller1(p_dept); --(2) ASSOCIATE RESULT SET LOCATOR (v_rs) WITH PROCEDURE to_caller1; --(3) ALLOCATE v_rsCur CURSOR FOR RESULT SET v_rs; --(4) SET p_total = 0; WHILE ( SQLSTATE = '00000' ) DO SET p_total = p_total + v_salary; FETCH FROM v_rsCur INTO v_fname, v_lname, v_salary; END WHILE; END ts
The result set locator variable is declared on Line (1). After invoking the called SQL procedure on Line (2), the ASSOCIATE LOCATOR statement is used on Line (3) to obtain the result set locator variable. Then the ALLOCATE CURSOR statement is used on Line (4) to open the result set. The cursor name used in the ALLOCATE CURSOR statement must not be declared anywhere prior to the ALLOCATE CURSOR statement in the procedure.
Once declared with ALLOCATE CURSOR, the cursor can be used to fetch rows from the returned result set. In Figure 8.15, a WHILE loop is used to fetch each row in order to calculate the department total salary. The SQLSTATE from the FETCH statement is used as the exit condition for the loop when the end of the result set is encountered.
Recall that on zSeries, you do not have the option of using RETURN TO CLIENT. To simulate RETURN TO CLIENT, use the ASSOCIATE RESULT SET LOCATOR and ALLOCATE CURSOR FOR RESULT SET statements in each intermediate procedure to pass the result set back one level higher. This process should be repeated until the result set is passed to the client.
The example given in Figure 8.16 demonstrates how you could create the base_proc procedure given in Figure 8.8 by using the ALLOCATE CURSOR statement on zSeries.
Figure 8.16. An example of receiving result sets from the client for zSeries.
CREATE PROCEDURE base_proc ( ) LANGUAGE SQL WLM ENVIRONMENT <env> DYNAMIC RESULT SETS 1 bp: BEGIN -- Declare variables DECLARE v_dept CHAR(3) DEFAULT 'A00'; DECLARE v_rs RESULT_SET_LOCATOR VARYING; -- Procedure logic CALL to_client(v_dept); ASSOCIATE RESULT SET LOCATOR (v_rs) WITH PROCEDURE to_client; ALLOCATE v_rsCur CURSOR FOR RESULT SET v_rs; -- <other statements to manipulate the cursor if you want to -- send it to the caller> END bp
In Figure 8.16, once the cursor v_rsCur has been allocated, it cannot be directly passed to the caller. If you have this requirement, you can fetch each row of the cursor and insert it into a table, which can be a temporary table (as discussed in Chapter 10, "Leveraging DB2 Application Development Features"), and have another cursor declared WITH RETURN TO CALLER defined on this table.
Receiving Multiple Result Sets as a Caller
A procedure is able to return more than one result set. To process multiple result sets in a caller procedure, the same statements as described in the previous section can be used. This is best illustrated using the example in Figure 8.17, which shows a called SQL procedure that returns multiple result sets.
Figure 8.17. Example of returning multiple result sets to caller procedures.
CREATE PROCEDURE emp_multi ( IN p_dept CHAR(3) ) LANGUAGE SQL SPECIFIC emp_multi -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries DYNAMIC RESULT SETS 3 em: BEGIN -- Procedure logic -- Selects firstname DECLARE v_cur1 CURSOR WITH RETURN TO CALLER FOR SELECT firstnme FROM employee WHERE workdept = p_dept; -- Selects lastname DECLARE v_cur2 CURSOR WITH RETURN TO CALLER FOR SELECT lastname FROM employee WHERE workdept = p_dept; -- Selects salary DECLARE v_cur3 CURSOR WITH RETURN TO CALLER FOR SELECT salary FROM employee WHERE workdept = p_dept; OPEN v_cur1; OPEN v_cur2; OPEN v_cur3; END em
The called SQL procedure emp_multi, shown in Figure 8.17, is a simple rewrite of the previous called procedure to_caller1 from Figure 8.11. Instead of returning three columns in one result set, each column is returned individually in a separate result set. Three cursors are declared and opened.
The caller SQL procedure, receive_multi in Figure 8.18, uses ASSOCIATE LOCATORS and ALLOCATE CURSOR statements multiple times to match the number of result sets returned. Three result set locator variables are declared and associated with the various result sets. Three cursors are allocated as well.
Figure 8.18. An example of receiving multiple result sets from a caller procedure on LUW and zSeries.
CREATE PROCEDURE receive_multi ( in p_dept CHAR(3) , OUT p_names VARCHAR(100) , OUT p_total DECIMAL(9,2) ) LANGUAGE SQL SPECIFIC receive_multi -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries rm: BEGIN -- Declare variables DECLARE v_fname VARCHAR(12) DEFAULT ''; DECLARE v_lname VARCHAR(15) DEFAULT ''; DECLARE v_salary DECIMAL(9,2) DEFAULT 0.0; DECLARE v_rs1, v_rs2, v_rs3 RESULT_SET_LOCATOR VARYING; -- Declare returncodes DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; -- Procedure logic CALL emp_multi(p_dept); ASSOCIATE RESULT SET LOCATOR (v_rs1, v_rs2, v_rs3) WITH PROCEDURE emp_multi; ALLOCATE v_rsCur1 CURSOR FOR RESULT SET v_rs1; ALLOCATE v_rsCur2 CURSOR FOR RESULT SET v_rs2; ALLOCATE v_rsCur3 CURSOR FOR RESULT SET v_rs3; SET p_names = 'The employees are:'; WHILE (SQLSTATE = '00000') DO SET p_names = p_names || v_fname || ' ' || v_lname || ' '; FETCH FROM v_rsCur1 INTO v_fname; FETCH FROM v_rsCur2 INTO v_lname; END WHILE; SET p_total = 0; WHILE ( SQLSTATE = '00000' ) DO SET p_total = p_total + v_salary; FETCH FROM v_rsCur3 INTO v_salary; END WHILE; END rm
Another point this example demonstrates is that you can use the multiple result sets received both in parallel and sequentially. The first name and last name result sets are used in parallel to generate the string of employee names in the department. The salary result set is used after the first two result sets have been closed. Note that in this example, it is known that the two result sets used in parallel happen to have the same number of rows. In your project, if you choose to use two result sets with different numbers of rows in parallel, you must check the last row condition for each FETCH statement.