In order to use cursors inside SQL procedures, you need to do the following:
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.
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:
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."
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;