Oracle PL/SQL WHERE CURRENT OF & FOR UPDATE

The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.

The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted. We must declare the cursor with the FOR UPDATE clause to use this feature.

Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.

When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.

The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows:

WHERE [CURRENT OF cursor_name | search_condition]

The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.

The FOR UPDATE clause in the SELECT statement can only be specified in the top level; subqueries cannot have this clause.

Another Example of WHERE CURRENT OF

Go through all Maths students and set all GPA’s under 4.0 to 4.0!

DECLARE

thisStudent Student%ROWTYPE;

CURSOR Maths_Student IS

SELECT * FROM Student WHERE SID IN

(SELECT SID FROM Take WHERE CID = ’CS145’)

FOR UPDATE;

BEGIN

OPEN Maths_Student;

LOOP

FETCH Maths_Student INTO thisStudent;

EXIT WHEN (Maths_Student%NOTFOUND);

IF (thisStudent.GPA < 4.0) THEN

UPDATE Student SET GPA = 4.0

WHERE CURRENT OF Maths_Student;

END IF;

END LOOP;

CLOSE Maths_Student;

END;

.

RUN;