FOR UPDATE OF, Cursor lock

Question

I've run into trouble, I've tried everything ( in my oppinion, of course it's wrong 'cause the answer is out there)

The code block updates all the necessery rows in the proposed_raises table, although it should update the proposed_new_salary field with different values, because the cursor retrieves six different values for e.salary.

The code:

DECLARE
CURSOR cur_rai_sal (p_salary_num NUMBER) IS
   SELECT e.employee_id, e.department_id, e.salary
      FROM employees e, proposed_raises rs
         WHERE e.salary <= p_salary_num
           FOR UPDATE OF rs.date_proposed, rs.date_approved, rs.proposed_new_salary    NOWAIT;              

BEGIN
FOR v_cur_rai_sal IN cur_rai_sal(5000) LOOP

   --DBMS_OUTPUT.PUT_LINE(v_cur_rai_sal.employee_id ||''||v_cur_rai_sal.department_id|| ' ' || v_cur_rai_sal.salary);
UPDATE proposed_raises
SET date_proposed = SYSDATE,
    date_approved = NULL,
    proposed_new_salary = v_cur_rai_sal.salary /100*105
WHERE CURRENT OF cur_rai_sal;

END LOOP;
END;

No error messages, only "1 row(s) updated".

Thank you in advance :)

Answer 1

It's not clear what your problem is. I wrote some similar code using a table defined like this:

create table t1 (id int, val number);
insert into t1 select rownum, rownum*1000 from user_objects where rownum < 10;

SQL> select * from t1;

        ID        VAL
---------- ----------
         1       1000
         2       2000
         3       3000
         4       4000
         5       5000
         6       6000
         7       7000
         8       8000
         9       9000
DECLARE
   CURSOR c (p_salary_num NUMBER) IS
      SELECT id, val
      FROM t1
      FOR UPDATE OF val  NOWAIT;
BEGIN
   FOR v_c IN c(5000) LOOP
      UPDATE t1
      SET val = v_c.val /100*105
      WHERE CURRENT OF c;
   END LOOP;
END;

and it worked fine:

SQL> select * from t1;

        ID        VAL
---------- ----------
         1       1050
         2       2100
         3       3150
         4       4200
         5       5250
         6       6300
         7       7350
         8       8400
         9       9450

However you must be doing something different, because your code would never produce the message "1 row(s) updated", it would say "PL/SQL procedure successfully completed." So it appears that you are performing a stand-alone UPDATE statement not in a PL/SQL block.