Positioned Update

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.

    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net