Using Cursors in SQL Procedures

In order to use cursors inside SQL procedures, you need to do the following:

• Declare the cursor

• Open the cursor to establish the result set

• Fetch the data into local variables as needed from the cursor, one row at a time

• Close the cursor when finished

The simplified syntax of the DECLARE CURSOR and FETCH statements is shown in Figures 5.1 and 5.2, respectively. The complete syntax of the DECLARE CURSOR statement is covered in Chapter 8, "Nested SQL Procedures" where returning nested results are discussed. The syntax of the OPEN cursor and CLOSE cursor statements is very straightforward, and no syntax diagram is presented here. They are illustrated in the following examples.

Figure 5.1. A simplified DECLARE CURSOR syntax.

 <<-DECLARE--cursor-name--CURSOR---------------------------------> <----FOR----select-statement----------------------------------->< 

Figure 5.2. A simplified FETCH syntax.

 >>-FETCH--+-------+---cursor-name------------------------------->           '-FROM--'               .-,----------------.               V                  | >------INTO-----host-variable----+---------------------------->< 

The FETCH statement positions the cursor at the next row of the result set and assigns values of the current cursor position to the specified procedure variables.

Figure 5.3 demonstrates the use of a cursor inside a SQL procedure to access data one row at a time. The procedure calculates the cost of giving raises to the employees of a company based on the following rules:

• The company has decided that everyone in the company except for the president will receive at least the minimum raise, which is defined by the input parameter p_min.

• Any employee with a bonus greater than $600 will receive an extra 4 percent raise.

• Employees with higher commissions will receive a smaller raise. Employees with commissions of less than $2,000 will receive an extra 3 percent raise, while those with commissions between $2,000 and $3,000 will receive an extra 2 percent raise.

• Anyone with commissions greater than $3,000 will receive an extra 1 percent raise.

• Finally, no matter how much of a raise an employee might receive, the total amount cannot be higher than a maximum limit, which is provided by the other input parameter p_max.

Figure 5.3. An example of simple cursor usage.

 CREATE PROCEDURE total_raise ( IN  p_min DEC(4,2)                              , IN  p_max DEC(4,2)                              , OUT p_total DEC(9,2) )     LANGUAGE SQL     SPECIFIC total_raise                          -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries tr: BEGIN     -- Declare variables     DECLARE v_salary DEC(9,2);     DECLARE v_bonus  DEC(9,2);     DECLARE v_comm   DEC(9,2);     DECLARE v_raise  DEC(4,2);     DECLARE v_job    VARCHAR(15) DEFAULT 'PRES';     -- Declare returncode     DECLARE SQLSTATE CHAR(5);     -- Procedure logic     DECLARE c_emp CURSOR FOR         SELECT salary, bonus, comm         FROM   employee         WHERE  job != v_job;                                 -- (1)         OPEN c_emp;                                          -- (2)           SET p_total = 0;         FETCH FROM c_emp INTO v_salary, v_bonus, v_comm;     -- (3)           WHILE ( SQLSTATE = '00000' ) DO            SET v_raise = p_min;            IF ( v_bonus >= 600 ) THEN                SET v_raise = v_raise + 0.04;            END IF;            IF ( v_comm < 2000 ) THEN                SET v_raise = v_raise + 0.03;            ELSEIF ( v_comm < 3000 ) THEN                SET v_raise = v_raise + 0.02;            ELSE                SET v_raise = v_raise + 0.01;            END IF;            IF ( v_raise > p_max ) THEN                SET v_raise = p_max;            END IF;            SET p_total = p_total + v_salary * v_raise;            FETCH FROM c_emp INTO v_salary, v_bonus, v_comm;  -- (4)        END WHILE;        CLOSE c_emp;                                          -- (5)    END tr 

Given the complexity of these rules, a cursor can be used here to simplify the programming logic.

This procedure shown in Figure 5.3 works on the employee table. The SELECT statement in Line (1) within the DECLARE CURSOR statement defines the columns and rows that make up of the result set. Once the cursor is declared, it needs to be opened using the OPEN cursor statement shown in Line (2) so that you can then later fetch data from the resulting table. The FETCH cursor statement shown in Line (3) retrieves data from a row and assigns the values into local variables. The second FETCH cursor statement on Line (4) is used to retrieve more data as the processing repeats in the WHILE loop. As a good programming practice, the CLOSE cursor statement on Line (5) should be issued once the cursor is no longer needed.

Normally the FETCH cursor statements are used in conjunction with loops to step through all rows of the result set. The SQLSTATE can be used to check if the last line is reached. The SQLCODE value is also commonly used to determine when the end of the result set has been reached. For detailed discussions on SQLCODE, SQLSTATE, and error handling, refer to Chapter 6, "Condition Handling."

Tip

If the SELECT statement of the cursor declaration contains multiple occurrences of CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP, all FETCH statements will return the same date, time, and timestamp value. This is because the value for these special registers is determined at the time of OPEN CURSOR.

In SQL procedures, the SET parameter statement shown in the following example will fail if more than one row is returned. DB2 will not simply assign the first value to the local variable.

 SET v_c1 = (SELECT c1 FROM t1); 

A scalar fullselect is a fullselect, enclosed in parentheses, that returns a single row consisting of a single column. A scalar fullselect can be used wherever expressions are allowed.

Creating a cursor solely for the purpose of fetching the first value to a local variable (as shown in Figure 5.4) is highly inefficient and is not recommended because opening a cursor is an expensive operation for a database.

Figure 5.4. Improper use of cursor to fetch only one row.

 DECLARE c_tmp CURSOR FOR         SELECT c1         FROM t1; OPEN c_emp; FETCH FROM c_emp INTO v_c1; CLOSE c_emp; 

The proper way of handling this situation is to use the FETCH FIRST 1 ROW ONLY clause.

 SELECT c1 INTO v_c1 FROM t1 FETCH FIRST 1 ROW ONLY; 

This statement will set the local variable to the value in the first row should more than one row be returned. It is important to realize, however, that if multiple rows are returned, there is no guarantee that the same row being returned with FETCH FIRST 1 ROW ONLY will always be the same row unless an ORDER BY clause is used.

DB2 for zSeries limits the use of the FETCH FIRST 1 ROW ONLY clause; it is not allowed in a scalar fullselect. The statement

 SET v_c1 = (SELECT c1 FROM t1 FETCH FIRST 1 ROW ONLY); 

can be rewritten without a scalar fullselect to work on all platforms:

 SELECT c1 INTO v_c1 FROM t1 FETCH FIRST 1 ROW ONLY;