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 |