Intelligent Enterprises of the 21st Century

Positioned update works similar to a positioned delete, except that the cursor has to meet requirements for being updatable. A cursor is updatable if all of the following are true:

• The cursor is deletable (as previously defined).

• A column being updated resolves to a column of the base table.

• Any columns being updated must be specified explicitly or implicitly in the FOR UPDATE clause.

Similar to deletable cursors, the definition for updatable cursors is not identical across all platforms. Refer to the individual SQL references for the complete requirements.

The syntax diagram for positioned update is shown in Figure 5.7.

Figure 5.7. A simplified positioned UPDATE syntax.

>>-UPDATE----+-table-name-------------------+-------------------> +-view-name--------------------+ >-----SET--| assignment-clause |--------------------------------> >-----WHERE CURRENT OF--cursor-name----------------------------><

The example in Figure 5.8 is similar to the total_raise procedure in Figure 5.3. Instead of calculating the total cost of issuing a raise, the upd_raise procedure applies the raise directly by updating the salary field of the employee record.

Figure 5.8. An example of a positioned update.

CREATE PROCEDURE upd_raise ( IN p_min DEC(4,2) , IN p_max DEC(4,2) ) LANGUAGE SQL SPECIFIC upd_raise -- applies to LUW and iSeries -- WLM ENVIRONMENT <env> -- applies to zSeries ur: 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 returncode DECLARE SQLSTATE CHAR(5); -- Procedure logic DECLARE c_emp CURSOR FOR SELECT salary, bonus, comm FROM employee WHERE job!='PRES' FOR UPDATE OF salary; OPEN c_emp; FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; 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; UPDATE employee -- (1) SET salary = v_salary * (1 + v_raise) WHERE CURRENT OF c_emp; FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; END WHILE; CLOSE c_emp; END ur

The logic used to determine the appropriate raise amount is the same as in the total_raise procedure of Figure 5.3. After the raise amount is calculated for the current employee, the salary is updated immediately using a positioned update on Line (1) before the cursor moves forward. The WHERE CURRENT OF clause indicates that the update should occur on the row where the cursor is currently positioned. In our case, the cursor is still positioned at the employee whose information was just fetched.

If you are going to be updating only a few columns for a table, then you can use the FOR UPDATE OF <column list>. This will improve performance because the DB2 engine will know that only certain columns are going to be updated.