The SELECT FOR UPDATE statement is a special variation of the normal SELECT statement, which proactively issues row locks on each row of data retrieved by the query. Use SELECT FOR UPDATE only when you need to reserve data you are querying to ensure that no one changes the data while you are processing it.
The SELECT statement with the FOR UPDATE clause (SELECT FOR UPDATE statement) selects the rows of the result set and locks them. SELECT FOR UPDATE lets you base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them. You can also use SELECT FOR UPDATE to lock rows that you do not want to update.
By default, the SELECT FOR UPDATE statement waits until the requested row lock is acquired. To change this behavior, use the NOWAIT, WAIT, or SKIP LOCKED clause of the SELECT FOR UPDATE statement.
When SELECT FOR UPDATE is associated with an explicit cursor, the cursor is called a FOR UPDATE cursor. Only a FOR UPDATE cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement. (The CURRENT OF clause, a PL/SQL extension to the WHERE clause of the SQL statements UPDATE and DELETE, restricts the statement to the current row of the cursor.)
SQL> DECLARE
2 CURSOR emp_cur
3 IS
4 SELECT * FROM emp FOR UPDATE;
5
6 emp_rec emp_cur%ROWTYPE;
7 vename emp.ename%TYPE;
8 BEGIN
9 OPEN emp_cur;
10 LOOP
11 FETCH emp_cur INTO emp_rec;
12 EXIT WHEN emp_cur%NOTFOUND;
13
14 IF emp_cur%FOUND THEN
15 vename := CONCAT(emp_rec.ename,'-X');
16
17 UPDATE emp SET ename = vename
18 WHERE empno = emp_rec.empno;
19
20 DBMS_OUTPUT.PUT_LINE('CURSOR ROWCOUNT ::: '||emp_cur%ROWCOUNT||' - '||emp_rec.ename||' - '||vename);
21 END IF;
22 END LOOP;
23 CLOSE emp_cur;
24 END;
25 /
--OUTPUT :::
CURSOR ROWCOUNT ::: 1 - KING - KING-X
CURSOR ROWCOUNT ::: 2 - BLAKE - BLAKE-X
CURSOR ROWCOUNT ::: 3 - CLARK - CLARK-X
CURSOR ROWCOUNT ::: 4 - JONES - JONES-X
CURSOR ROWCOUNT ::: 5 - SCOTT - SCOTT-X
CURSOR ROWCOUNT ::: 6 - FORD - FORD-X
CURSOR ROWCOUNT ::: 7 - SMITH - SMITH-X
CURSOR ROWCOUNT ::: 8 - ALLEN - ALLEN-X
CURSOR ROWCOUNT ::: 9 - WARD - WARD-X
CURSOR ROWCOUNT ::: 10 - MARTIN - MARTIN-X
CURSOR ROWCOUNT ::: 11 - TURNER - TURNER-X
CURSOR ROWCOUNT ::: 12 - ADAMS - ADAMS-X
CURSOR ROWCOUNT ::: 13 - JAMES - JAMES-X
CURSOR ROWCOUNT ::: 14 - MILLER - MILLER-X
SQL> DECLARE
2 CURSOR emp_cur
3 IS
4 SELECT * FROM emp FOR UPDATE OF ename;
5
6 emp_rec emp_cur%ROWTYPE;
7 vename emp.ename%TYPE;
8 BEGIN
9 OPEN emp_cur;
10 LOOP
11 FETCH emp_cur INTO emp_rec;
12 EXIT WHEN emp_cur%NOTFOUND;
13
14 IF emp_cur%FOUND THEN
15 vename := CONCAT(emp_rec.ename,'-X');
16
17 UPDATE emp SET ename = vename
18 WHERE empno = emp_rec.empno;
19
20 DBMS_OUTPUT.PUT_LINE('CURSOR ROWCOUNT ::: '||emp_cur%ROWCOUNT||' - '||emp_rec.ename||' - '||vename);
21 END IF;
22 END LOOP;
23 CLOSE emp_cur;
24 END;
25 /
--OUTPUT :::
CURSOR ROWCOUNT ::: 1 - KING - KING-X
CURSOR ROWCOUNT ::: 2 - BLAKE - BLAKE-X
CURSOR ROWCOUNT ::: 3 - CLARK - CLARK-X
CURSOR ROWCOUNT ::: 4 - JONES - JONES-X
CURSOR ROWCOUNT ::: 5 - SCOTT - SCOTT-X
CURSOR ROWCOUNT ::: 6 - FORD - FORD-X
CURSOR ROWCOUNT ::: 7 - SMITH - SMITH-X
CURSOR ROWCOUNT ::: 8 - ALLEN - ALLEN-X
CURSOR ROWCOUNT ::: 9 - WARD - WARD-X
CURSOR ROWCOUNT ::: 10 - MARTIN - MARTIN-X
CURSOR ROWCOUNT ::: 11 - TURNER - TURNER-X
CURSOR ROWCOUNT ::: 12 - ADAMS - ADAMS-X
CURSOR ROWCOUNT ::: 13 - JAMES - JAMES-X
CURSOR ROWCOUNT ::: 14 - MILLER - MILLER-X
SQL> DECLARE
2 CURSOR emp_cur
3 IS
4 SELECT * FROM emp FOR UPDATE NOWAIT;
5
6 emp_rec emp_cur%ROWTYPE;
7 vename emp.ename%TYPE;
8 BEGIN
9 OPEN emp_cur;
10 LOOP
11 FETCH emp_cur INTO emp_rec;
12 EXIT WHEN emp_cur%NOTFOUND;
13
14 IF emp_cur%FOUND THEN
15 vename := CONCAT(emp_rec.ename,'-X');
16
17 UPDATE emp SET ename = vename
18 WHERE empno = emp_rec.empno;
19
20 DBMS_OUTPUT.PUT_LINE('CURSOR ROWCOUNT ::: '||emp_cur%ROWCOUNT||' - '||emp_rec.ename||' - '||vename);
21 END IF;
22 END LOOP;
23 CLOSE emp_cur;
24 END;
25 /
--OUTPUT :::
CURSOR ROWCOUNT ::: 1 - KING - KING-X
CURSOR ROWCOUNT ::: 2 - BLAKE - BLAKE-X
CURSOR ROWCOUNT ::: 3 - CLARK - CLARK-X
CURSOR ROWCOUNT ::: 4 - JONES - JONES-X
CURSOR ROWCOUNT ::: 5 - SCOTT - SCOTT-X
CURSOR ROWCOUNT ::: 6 - FORD - FORD-X
CURSOR ROWCOUNT ::: 7 - SMITH - SMITH-X
CURSOR ROWCOUNT ::: 8 - ALLEN - ALLEN-X
CURSOR ROWCOUNT ::: 9 - WARD - WARD-X
CURSOR ROWCOUNT ::: 10 - MARTIN - MARTIN-X
CURSOR ROWCOUNT ::: 11 - TURNER - TURNER-X
CURSOR ROWCOUNT ::: 12 - ADAMS - ADAMS-X
CURSOR ROWCOUNT ::: 13 - JAMES - JAMES-X
CURSOR ROWCOUNT ::: 14 - MILLER - MILLER-X