Intelligent Enterprises of the 21st Century

Besides using cursors to process data within the SQL procedure, you can also use cursors to return result sets to the calling program for processing. To contrast these two cursor usage scenarios, the total_raise procedure in Figure 5.3 is rewritten as an SQL procedure which returns all qualifying employees' salary, bonus, and commission fields as a result set. The total raise is then calculated at the client. The SQL procedure called read_emp is shown in Figure 5.20.

Figure 5.20. An example of using a cursor to return a single result set.

CREATE PROCEDURE read_emp ( ) LANGUAGE SQL SPECIFIC read_emp -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries DYNAMIC RESULT SETS 1 --(1) re: BEGIN -- Procedure logic DECLARE c_emp CURSOR WITH RETURN FOR --(2) SELECT salary, bonus, comm FROM employee WHERE job!='PRES'; OPEN c_emp; --(3) END re

The result sets can be received by either SQL procedures or client programs developed in programming languages such as Java or C. Receiving result sets from another SQL procedure is covered in Chapter 8, "Nested SQL Procedures." The Java and C client program used to receive this result set is provided in Appendix H, "Sample Application Code."

When using a cursor to return the result set to a calling application, you need to

• Specify DYNAMIC RESULT SETS clause in CREATE PROCEDURE statement on Line (1).

• Declare the cursor using the WITH RETURN clause on Line (2).

• Keep the cursor open for the client application on Line (3).

If you do not follow the steps outlined above the cursor will not return the results out of procedure. The DYNAMIC RESULT SETS clause is optional on DB2 for LUW and iSeries. Refer to Chapter 2, "Basic SQL Procedure Structure," for a further discussion on this clause. In the example shown in Figure 5.22, the cursor has been defined and then opened. The result set will be passed back to the calling procedure or program where the results can be processed. This example is quite straightforward.

In SQL procedures, besides Data Manipulation Language (DML) statements such as SELECT, DELETE, and UPDATE, Data Definition Language (DDL) statements (such as the CREATE TABLE statement) are also supported. You can create a table, populate it, and then use a cursor to return the result set from the same table. The tricky part is that the DECLARE CURSOR statement has to be at the beginning of the BEGIN ... END block. The table creation has to be in the body of the block. If you put the DECLARE CURSOR statement at the beginning, the table is not created yet. On LUW, you will receive a compile-time error complaining the table is not found. If you put the CURSOR statement after the CREATE TABLE statement, you will run into another compile-time error indicating your DECLARE CURSOR statement is not supposed to be there. What should you do now?

The solution to this requires understanding the concept of scope. In DB2, the BEGIN ... END blocks can be nested. The scope of any declarations within a BEGIN ... END block is the block itself. The DECLARE CURSOR statement is required to be at the beginning of a BEGIN ... END block, before any SELECT, DELETE, UPDATE, or CREATE TABLE statements in that block. Hence, you can use a nested BEGIN ... END block to declare a cursor at the end of procedure. Figure 5.21 illustrates this.

Figure 5.21. An example of using cursor on newly created table for LUW and iSeries.

CREATE PROCEDURE create_and_return ( ) LANGUAGE SQL SPECIFIC create_and_return -- applies to LUW and iSeries DYNAMIC RESULT SETS 1 cr: BEGIN -- Procedure logic CREATE TABLE mytable (sid INT); INSERT INTO mytable VALUES (1); INSERT INTO mytable VALUES (2); BEGIN --(1) DECLARE c_cur CURSOR WITH RETURN FOR SELECT * FROM mytable; OPEN c_cur; --(2) END; --(3) END cr

Tip

In order to return result sets from a table created in the same BEGIN ... END block, you need to declare cursors inside a nested BEGIN ... END block.

You can see from the example in Figure 5.21 how a new BEGIN ... END block from Lines (1) to (3) allows cursor declaration for the table created in the same procedure. The OPEN CURSOR statement at Line (2) must also be inside the new BEGIN ... END block because the cursor c_cur is only valid in the inner BEGIN ... END block. It is not visible outside the block.

A very practical usage of this scheme is when you need to create and use a temporary table in your SQL procedure. For more information on DB2 user temporary tables, refer to Chapter 10, "Leveraging DB2 Application Development Features."

Another approach is to use dynamic SQL, which will be covered in Chapter 7, "Working with Dynamic SQL."

The example in Figure 5.21 will not work on zSeries because the example exploits the use of nested compound statements which are not supported on DB2 for zSeries. Nonetheless, the problem the example is trying to resolve may not happen on zSeries. DB2 for zSeries has the VALIDATE bind parameter set to RUN by default. This means that any dependency error like 'object not found' or 'not authorized' will not stop the bind process, and the procedures can be built regardless of dependency issues. Specifically for the example, on zSeries a cursor can be declared even if it references a table that is to be created later in the procedure. This is also discussed in Chapter 10, "Leveraging DB2 Application Development Features."