Unable to find in oracle cursor update that how many records are updated?

Question

Scenario: Write a PL/SQL block that takes a department number from a user and increases the salary of all the employees belonging to the department by 10%. The block should display on the screen how many records are updated.

My Program:

DECLARE
      V_TOT_ROWS NUMBER(3);
      CURSOR emp_cursor IS
      SELECT EMPSAL FROM emp WHERE deptno=&DEPT_NO
      FOR UPDATE OF EMPSAL NOWAIT;
BEGIN
      FOR emp_record IN emp_cursor 
      LOOP
              UPDATE  emp
              SET  EMPSAL=EMPSAL+emp_record.EMPSAL*0.1
              WHERE CURRENT OF emp_cursor;
    --  V_TOT_ROWS := SQL%ROWCOUNT;
    --  DBMS_OUTPUT.PUT_LINE('TOTAL UPDATED RECORDS: ' || V_TOT_ROWS);     
      DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' salaries.'); 
      END LOOP;
      COMMIT;
   --   DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' salaries.'); 

END;

It is giving 1 row updated every time the loop is executed but if I keep the dbms_output outside the loop, it gives 0.

Please help.

Thanks,

Answer 1

Please check below Script:

  declare V_TOT_ROWS NUMBER(3) :=0;
  CURSOR emp_cursor IS
  SELECT EMPSAL FROM emp WHERE deptno=&DEPT_NO
  FOR UPDATE OF EMPSAL NOWAIT;

  begin FOR emp_record IN emp_cursor 
  LOOP
          UPDATE  emp
          SET  EMPSAL=EMPSAL+emp_record.EMPSAL*0.1
          WHERE CURRENT OF emp_cursor;
  V_TOT_ROWS := V_TOT_ROWS+SQL%ROWCOUNT;
 --  DBMS_OUTPUT.PUT_LINE('TOTAL UPDATED RECORDS: ' || V_TOT_ROWS);     
 -- DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' salaries.'); 
  END LOOP;
  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Updated ' || V_TOT_ROWS || ' salaries.'); end;

Answer 2

Use simple update instead of a cursor + forall.
If you use FORALL ... UPDATE, then only 1 record is updated in each loop cycle, so UPDATE returns SQL%ROWCOUNT always = 1.

DECLARE
      V_TOT_ROWS NUMBER(3);
BEGIN
     UPDATE emp
     SET   EMPSAL= EMPSAL+  EMPSAL*0.1
     WHERE deptno=&DEPT_NO;

      V_TOT_ROWS := SQL%ROWCOUNT;
      DBMS_OUTPUT.PUT_LINE('TOTAL UPDATED RECORDS: ' || V_TOT_ROWS);     

      COMMIT; 

END;
/