WHERE CURRENT OF CLAUSE in Oracle

SQL> create table table_w(name varchar2(100),sal number);

Table created.

SQL> insert into table_w values('A',5000);

1 row created.

SQL> insert into table_w values('B',4000);

1 row created.

SQL> insert into table_w values('A',3000);

1 row created.

SQL> insert into table_w values('C',3000);

1 row created.

SQL> insert into table_w values('B',2000);

1 row created.

SQL> COMMIT;

Commit complete.


SQL> CREATE TABLE TABLE_W_BKP AS SELECT * FROM TABLE_W;

Table created.

                      
SET SERVEROUTPUT ON;
DECLARE
CURSOR C1 IS SELECT * FROM TABLE_W;
BEGIN
FOR I IN C1
LOOP
UPDATE TABLE_W SET SAL=SAL+1000 WHERE NAME=I.NAME;
COMMIT;
END LOOP;
END;
/                          

BEFORE :

SQL> SELECT * FROM TABLE_W;

NAME
                            SAL
--------------------------------------------------------------------
A
                           5000
B
                           4000
A
                           3000
C
                           3000
B
                           2000
                          
AFTER :

SQL> SELECT * FROM TABLE_W;

NAME                                      SAL
------------------------------------- ----------
A                                        7000
B                                        6000
A                                        5000
C                                        4000
B                                        4000

SET SERVEROUTPUT ON;
DECLARE
CURSOR C1 IS SELECT * FROM TABLE_W_BKP FOR UPDATE OF SAL NOWAIT;
BEGIN
FOR I IN C1
LOOP
UPDATE TABLE_W_BKP SET SAL=SAL+1000 WHERE CURRENT OF C1;
--OORA-01002: fetch out of sequence.-->If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued --will return the error. Do not issue a COMMIT inside a fetch loop for a cursor that has been opened FOR UPDATE.
--COMMIT;
END LOOP;
COMMIT;
END;
/   

BEFORE :

SQL>  SELECT * FROM TABLE_W_BKP;    

NAME                                      SAL
------------------------------------- ----------
A                                        6000
B                                        4000
A                                        3000
C                                        3000
B                                        2000

AFTER :

SQL>  SELECT * FROM TABLE_W_BKP;

NAME                                      SAL
------------------------------------- ----------
A                                        7000
B                                        5000
A                                        4000
C                                        4000
B                                        3000