FOR UPDATE and WHERE CURRENT OF Clause in Oracle

FOR UPDATE and WHERE CURRENT OF Clause in Oracle -Learn how to perform record level locks while performing DML operations on table using Cursors.

Below table is reference for FOR UPDATE, WHERE CURRENT OF CLause working Examples.

CREATE TABLE EMP (NAME VARCHAR2(40),SALARY NUMBER);

INSERT INTO EMP VALUES('A',4000);
INSERT INTO EMP VALUES('B',5000);
INSERT INTO EMP VALUES('A',3000);
INSERT INTO EMP VALUES('B',7000);
INSERT INTO EMP VALUES('C',9000);
INSERT INTO EMP VALUES('D',6000);
INSERT INTO EMP VALUES('A',6000);

SELECT * FROM EMP;

NAME	SALARY
A	4000
B	5000
A	3000
B	7000
C	9000
D	6000
A	6000

Example program- updating salary of an employee with 1000 RS

DECLARE
  CURSOR C1
  IS
    SELECT * FROM EMP;
BEGIN
  FOR CREC IN C1
  LOOP
    UPDATE EMP SET SALARY=SALARY+1000 WHERE NAME=CREC.NAME;
  END LOOP;
END;

To display the output after execution of PL-SQL block use the below select query.

SELECT * FROM EMP;

Output

NAME	SALARY
A	7000
B	7000
A	6000
B	9000
C	10000
D	7000
A	9000

In the above example,for every hit in the loop 1000 rs is added to salary of an employee in emp table.
Like this if we find common names in the emp table 1000 rs updation is affected to all the employees with the same name which leads to improper calulation.

So to avoid  improper results while performing DML commands on cursor values, need to lock the records until the DML operation is completed.

FOR UPDATE and WHERE CURRENT OF Clause in Oracle

To lock the records on cursor with select query , use FOR UPDATE clause.
To update or delete only current record in the cursor use WHERE CURRENT OF CLAUSE

FOR UPDATE Clause

Syntax

CURSOR cursor_name
IS
   select_statement
   FOR UPDATE [OF column_list] [NOWAIT];

Parameters or Arguments

cursor_name
The name of the cursor.

select_statement
A SELECT statement that will populate your cursor result set.

column_list
The columns in the cursor result set that you wish to update.

NOWAIT
Optional. The cursor does not wait for resources.

NOTES
FOR UPDATE clause explicitly locks the records stored in the Context Area.
The FOR UPDATE Clause in the Cursor query is used to lock the affected rows while the cursor is opened.
Explicit commit command is not required to release the lock acquired by using the FOR UPDATE Clause.

USING WHERE CURRENT OF Clause

The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

Syntax

UPDATE table_name
  SET set_clause
  WHERE CURRENT OF cursor_name;

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

NOTES
WHERE CURRENT OF Clause is used to refer the current record, that fetch from explicit cursor.
We need to suffix the name of the explicit cursor with the CURRENT OF clause to refer to current record.

In order to use the WHERE CURRENT OF clause, you need to lock the record fetched from the cursor.

Example program using For Update and Current of Clause.

DECLARE
  CURSOR C1
  IS
    SELECT * FROM EMP FOR UPDATE OF salary nowait;
BEGIN
  FOR CREC IN C1
  LOOP
    UPDATE EMP SET SALARY=SALARY+1000 WHERE CURRENT OF C1;
  END LOOP;
END;

SELECT * FROM EMP;

Output

To display the output after execution of PL-SQL block use the below select query.

NAME	SALARY
A	5000
B	6000
A	4000
B	8000
C	10000
D	7000
A	7000