Using Dynamic SQL in Cursors

Sometimes it may be necessary to iterate through a set of rows of a result set and determine where the result set was created using dynamic SQL. In this section, examples of dynamic SQL in cursors will be introduced.

Consider using the same example as shown in the previous section, except that the c_managers cursor is defined dynamically. Figure 7.7 demonstrates the code.

Figure 7.7. Dynamic SQL cursors

 CREATE PROCEDURE change_mgr_bonus2 ( IN p_bonus_increase DECIMAL                                    , OUT p_num_changes INT)     LANGUAGE SQL     SPECIFIC change_mgr_bonus2                    -- applies to LUW and iSeries -   WLM ENVIRONMENT <env>                         -- applies to zSeries cmb2: BEGIN     DECLARE v_dynMgrSQL      VARCHAR(200);                     -- (1)     DECLARE v_dynSQL         VARCHAR(200);     DECLARE v_new_bonus      DECIMAL;     DECLARE v_no_data        SMALLINT DEFAULT 0;     DECLARE v_mgrno          CHAR(6);     DECLARE v_bonus          DECIMAL;     -- cursor of all employees who are also managers     DECLARE c_managers CURSOR FOR v_cur_stmt;                 -- (2)     DECLARE CONTINUE HANDLER FOR NOT FOUND     SET v_no_data=1;     -- SQL for c_managers cursor     SET v_dynMgrSQL = 'SELECT e.empno,e.bonus FROM EMPLOYEE e, DEPARTMENT d ' ||                       'WHERE e.empno=d.mgrno';     SET v_dynSQL = 'UPDATE EMPLOYEE SET BONUS= ? WHERE EMPNO=?';     SET p_num_changes=0;     PREPARE v_stmt1 FROM v_dynSQL;     PREPARE v_cur_stmt FROM v_dynMgrSQL;                 -- (3)     OPEN c_managers;                                     -- (4)     FETCH c_managers INTO v_mgrno, v_bonus;     WHILE (v_no_data=0) DO        SET p_num_changes = p_num_changes + 1;        SET v_new_bonus = v_bonus + p_bonus_increase;        EXECUTE v_stmt1 USING v_new_bonus, v_mgrno;        -- fetch the next row to be processed        FETCH c_managers INTO v_mgrno, v_bonus;    END WHILE;    CLOSE c_managers; END cmb2 

First, a VARCHAR variable large enough to contain the SQL statement must be declared for the cursor as shown on Line (1). In the cursor declaration on Line (2), notice how the cursor has been declared for the v_cur_stmt statement name which will eventually identify the prepared statement held by the v_dynMgrSQL. Finally, before opening the c_managers cursor, the v_cur_stmt statement name must be prepared on Line (3) using the v_dynMgrSQL variable. When the c_managers cursor on Line (4) is opened, DB2 expects that the v_cur_stmt statement name references a prepared SQL statement.

Note that in this example, the parameter markers for p_bonus and p_empno are untyped. Depending on the situation, you may run into a problem where a variable of the wrong type is supplied for the parameter marker. You can protect the application from errors by using CAST to guarantee the proper type. For example, the v_dynSQL variable can be changed to use the following SQL statement instead:

 SET v_dynSQL = 'UPDATE EMPLOYEE SET BONUS= CAST(? AS DECIMAL) WHERE EMPNO= CAST (? AS CHAR(6))'; 

Using CAST to guarantee the proper type may not always be necessary. You should only use it if there is the opportunity for type mismatch because CAST adds execution overhead.

Before running the SQL procedure, take a look at the current bonus values for department managers:

 SELECT e.empno, e.bonus FROM EMPLOYEE e, DEPARTMENT d WHERE e.empno=d.mgrno EMPNO  BONUS ------ ----------- 000010     1100.00 000020      900.00 000030      900.00 000050      900.00 000060      600.00 000070      800.00 000090      700.00 000100      600.00 

Then, call the SQL procedure with the value for the bonus increase as the input parameter. Again, you can use the CLP in LUW:

 CALL CHANGE_MGR_BONUS2 (100,?) 

The output should look something like the following:

 Value of output parameters   --------------------------   Parameter Name  : P_NUM_CHANGES   Parameter Value : 8   Return Status = 0 

In this case, eight rows were updated as indicated by P_NUM_CHANGES. To further verify that the code is correct, query the data once again.

 SELECT e.empno, e.bonus FROM EMPLOYEE e, DEPARTMENT d WHERE e.empno=d.mgrno EMPNO  BONUS ------ ----------- 000010     1200.00 000020     1000.00 000030     1000.00 000050     1000.00 000060      700.00 000070      900.00 000090      800.00 000100      700.00