If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.
WHERE CURRENT OF works ONLY with SELECT FOR UPDATE/SELECT FOR UPDATE NOWAIT query.
As soon as a cursor with a FOR UPDATE clause is opened, all rows identified in the result set of the cursor are locked and remain locked until your session ends or your code explicitly issues either a COMMIT or a ROLLBACK. When either of these occurs,the locks on the rows are released. As a result, you cannot execute another FETCH against a FOR UPDATE cursor after a COMMIT or ROLLBACK. You will have lost your position in the cursor.
If you ever need to execute a COMMIT or ROLLBACK as you FETCH records from a SELECT FOR UPDATE cursor, you should include code (such as a loop EXIT or other conditional logic) to halt any further fetches from the cursor.
SQL> CREATE TABLE test_emp
2 (
3 name VARCHAR2(30 BYTE),
4 salary NUMBER
5 );
Table created.
SQL> INSERT ALL
2 INTO test_emp (name,salary) VALUES ('AAA',1000)
3 INTO test_emp (name,salary) VALUES ('BBB',2000)
4 INTO test_emp (name,salary) VALUES ('CCC',3000)
5 INTO test_emp (name,salary) VALUES ('AAA',4000)
6 INTO test_emp (name,salary) VALUES ('EEE',5000)
7 INTO test_emp (name,salary) VALUES ('BBB',6000)
8 SELECT * FROM DUAL;
6 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 1000
BBB 2000
CCC 3000
AAA 4000
EEE 5000
BBB 6000
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM test_emp;
4 BEGIN
5 FOR rec IN cur_emp
6 LOOP
7 UPDATE test_emp SET salary = salary + 1000
8 WHERE name = rec.name;
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 3000
BBB 4000
CCC 4000
AAA 6000
EEE 6000
BBB 8000
SQL> DELETE FROM test_emp;
6 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> INSERT ALL
2 INTO test_emp (name,salary) VALUES ('AAA',1000)
3 INTO test_emp (name,salary) VALUES ('BBB',2000)
4 INTO test_emp (name,salary) VALUES ('CCC',3000)
5 INTO test_emp (name,salary) VALUES ('AAA',4000)
6 INTO test_emp (name,salary) VALUES ('EEE',5000)
7 INTO test_emp (name,salary) VALUES ('BBB',6000)
8 SELECT * FROM DUAL;
6 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 1000
BBB 2000
CCC 3000
AAA 4000
EEE 5000
BBB 6000
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM test_emp FOR UPDATE;
4 BEGIN
5 FOR rec IN cur_emp
6 LOOP
7 UPDATE test_emp SET salary = salary + 1000
8 WHERE CURRENT OF cur_emp;
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 2000
BBB 3000
CCC 4000
AAA 5000
EEE 6000
BBB 7000
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM test_emp FOR UPDATE;
4 BEGIN
5 FOR rec IN cur_emp
6 LOOP
7 UPDATE test_emp SET salary = salary + 1000
8 WHERE CURRENT OF cur_emp;
9 COMMIT;
10 END LOOP;
11 END;
12 /
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 5
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 3000
BBB 3000
CCC 4000
AAA 5000
EEE 6000
BBB 7000
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM test_emp FOR UPDATE;
4 BEGIN
5 FOR rec IN cur_emp
6 LOOP
7 UPDATE test_emp SET salary = salary + 1000
8 WHERE CURRENT OF cur_emp;
9 COMMIT;
10 EXIT;
11 END LOOP;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 4000
BBB 3000
CCC 4000
AAA 5000
EEE 6000
BBB 7000SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> UPDATE emp set ename = 'KING' WHERE empno IN (7698,7782,7566,7788);
4 rows updated.
SQL> UPDATE emp set ename = 'QUEEN' WHERE empno IN (7902,7369,7499,7521,7654);
5 rows updated.
SQL> UPDATE emp set ename = 'SERVANT' WHERE empno IN (7844,7876,7900,7934);
4 rows updated.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 KING MANAGER 7839 01-MAY-81 2850 30
7782 KING MANAGER 7839 09-JUN-81 2450 10
7566 KING MANAGER 7839 02-APR-81 2975 20
7788 KING ANALYST 7566 19-APR-87 3000 20
7902 QUEEN ANALYST 7566 03-DEC-81 3000 20
7369 QUEEN CLERK 7902 17-DEC-80 800 20
7499 QUEEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 QUEEN SALESMAN 7698 22-FEB-81 1250 500 30
7654 QUEEN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 SERVANT SALESMAN 7698 08-SEP-81 1500 0 30
7876 SERVANT CLERK 7788 23-MAY-87 1100 20
7900 SERVANT CLERK 7698 03-DEC-81 950 30
7934 SERVANT CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM emp;
4 BEGIN
5 FOR rec IN cur_emp
6 LOOP
7 UPDATE emp SET sal = sal + 1 WHERE ename = rec.ename;
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5005 10
7698 KING MANAGER 7839 01-MAY-81 2855 30
7782 KING MANAGER 7839 09-JUN-81 2455 10
7566 KING MANAGER 7839 02-APR-81 2980 20
7788 KING ANALYST 7566 19-APR-87 3005 20
7902 QUEEN ANALYST 7566 03-DEC-81 3005 20
7369 QUEEN CLERK 7902 17-DEC-80 805 20
7499 QUEEN SALESMAN 7698 20-FEB-81 1605 300 30
7521 QUEEN SALESMAN 7698 22-FEB-81 1255 500 30
7654 QUEEN SALESMAN 7698 28-SEP-81 1255 1400 30
7844 SERVANT SALESMAN 7698 08-SEP-81 1504 0 30
7876 SERVANT CLERK 7788 23-MAY-87 1104 20
7900 SERVANT CLERK 7698 03-DEC-81 954 30
7934 SERVANT CLERK 7782 23-JAN-82 1304 10
14 rows selected.
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM emp;
4 BEGIN
5 FOR rec IN cur_emp
6 LOOP
7 UPDATE emp SET sal = sal + 1 WHERE CURRENT OF cur_emp;
8 END LOOP;
9 END;
10 /
UPDATE emp SET sal = sal + 1 WHERE CURRENT OF cur_emp;
*
ERROR at line 7:
ORA-06550: line 7, column 55:
PLS-00404: cursor 'CUR_EMP' must be declared with FOR UPDATE to use with CURRENT OF
ORA-06550: line 7, column 55:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 7, column 9:
PL/SQL: SQL Statement ignored
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM emp
4 FOR UPDATE;
5 BEGIN
6 FOR rec IN cur_emp
7 LOOP
8 UPDATE emp SET sal = sal + 1 WHERE CURRENT OF cur_emp;
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5006 10
7698 KING MANAGER 7839 01-MAY-81 2856 30
7782 KING MANAGER 7839 09-JUN-81 2456 10
7566 KING MANAGER 7839 02-APR-81 2981 20
7788 KING ANALYST 7566 19-APR-87 3006 20
7902 QUEEN ANALYST 7566 03-DEC-81 3006 20
7369 QUEEN CLERK 7902 17-DEC-80 806 20
7499 QUEEN SALESMAN 7698 20-FEB-81 1606 300 30
7521 QUEEN SALESMAN 7698 22-FEB-81 1256 500 30
7654 QUEEN SALESMAN 7698 28-SEP-81 1256 1400 30
7844 SERVANT SALESMAN 7698 08-SEP-81 1505 0 30
7876 SERVANT CLERK 7788 23-MAY-87 1105 20
7900 SERVANT CLERK 7698 03-DEC-81 955 30
7934 SERVANT CLERK 7782 23-JAN-82 1305 10
14 rows selected.SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5006 10
7698 KING MANAGER 7839 01-MAY-81 2856 30
7782 KING MANAGER 7839 09-JUN-81 2456 10
7566 KING MANAGER 7839 02-APR-81 2981 20
7788 KING ANALYST 7566 19-APR-87 3006 20
7902 QUEEN ANALYST 7566 03-DEC-81 3006 20
7369 QUEEN CLERK 7902 17-DEC-80 806 20
7499 QUEEN SALESMAN 7698 20-FEB-81 1606 300 30
7521 QUEEN SALESMAN 7698 22-FEB-81 1256 500 30
7654 QUEEN SALESMAN 7698 28-SEP-81 1256 1400 30
7844 SERVANT SALESMAN 7698 08-SEP-81 1505 0 30
7876 SERVANT CLERK 7788 23-MAY-87 1105 20
7900 SERVANT CLERK 7698 03-DEC-81 955 30
7934 SERVANT CLERK 7782 23-JAN-82 1305 10
14 rows selected.
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM emp
4 FOR UPDATE;
5 BEGIN
6 FOR rec IN cur_emp
7 LOOP
8 UPDATE emp SET sal = sal + 1 WHERE ename = rec.ename;
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5011 10
7698 KING MANAGER 7839 01-MAY-81 2861 30
7782 KING MANAGER 7839 09-JUN-81 2461 10
7566 KING MANAGER 7839 02-APR-81 2986 20
7788 KING ANALYST 7566 19-APR-87 3011 20
7902 QUEEN ANALYST 7566 03-DEC-81 3011 20
7369 QUEEN CLERK 7902 17-DEC-80 811 20
7499 QUEEN SALESMAN 7698 20-FEB-81 1611 300 30
7521 QUEEN SALESMAN 7698 22-FEB-81 1261 500 30
7654 QUEEN SALESMAN 7698 28-SEP-81 1261 1400 30
7844 SERVANT SALESMAN 7698 08-SEP-81 1509 0 30
7876 SERVANT CLERK 7788 23-MAY-87 1109 20
7900 SERVANT CLERK 7698 03-DEC-81 959 30
7934 SERVANT CLERK 7782 23-JAN-82 1309 10
14 rows selected.SQL> CREATE TABLE test_emp
2 (
3 name VARCHAR2(30 BYTE) PRIMARY KEY,
4 salary NUMBER
5 );
Table created.
SQL> INSERT ALL
2 INTO test_emp (name,salary) VALUES ('AAA',1000)
3 INTO test_emp (name,salary) VALUES ('BBB',2000)
4 INTO test_emp (name,salary) VALUES ('CCC',3000)
5 INTO test_emp (name,salary) VALUES ('DDD',4000)
6 INTO test_emp (name,salary) VALUES ('EEE',5000)
7 INTO test_emp (name,salary) VALUES ('FFF',6000)
8 SELECT * FROM DUAL;
6 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 1000
BBB 2000
CCC 3000
DDD 4000
EEE 5000
FFF 6000
6 rows selected.
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM test_emp;
4 BEGIN
5 FOR rec IN cur_emp
6 LOOP
7 UPDATE test_emp SET salary = salary + 1 WHERE name = rec.name;
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 1001
BBB 2001
CCC 3001
DDD 4001
EEE 5001
FFF 6001
6 rows selected.
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM test_emp
4 FOR UPDATE;
5 BEGIN
6 FOR rec IN cur_emp
7 LOOP
8 UPDATE test_emp SET salary = salary + 1 WHERE CURRENT OF cur_emp;
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 1002
BBB 2002
CCC 3002
DDD 4002
EEE 5002
FFF 6002
6 rows selected.
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM test_emp
4 FOR UPDATE;
5 BEGIN
6 FOR rec IN cur_emp
7 LOOP
8 UPDATE test_emp SET salary = salary + 1 WHERE CURRENT OF cur_emp;
9 COMMIT;
10 END LOOP;
11 END;
12 /
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 6
ORA-06512: at line 6
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 1003
BBB 2002
CCC 3002
DDD 4002
EEE 5002
FFF 6002
6 rows selected.
SQL> DECLARE
2 CURSOR cur_emp IS
3 SELECT * FROM test_emp
4 FOR UPDATE;
5 BEGIN
6 FOR rec IN cur_emp
7 LOOP
8 UPDATE test_emp SET salary = salary + 1 WHERE CURRENT OF cur_emp;
9 COMMIT;
10 EXIT;
11 END LOOP;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM test_emp;
NAME SALARY
------------------------------ ----------
AAA 1004
BBB 2002
CCC 3002
DDD 4002
EEE 5002
FFF 6002
6 rows selected.
