PL/SQL Cursor Locking Mechanism

Where current of, for update clauses used in cursors.

UPDATE, DELETE Statement used in Cursors

In all database system whenever we are using UPDATE, DELETE Statements, Database server automatically uses default locking mechanism.

If you want to perform locks before update or before delete, then ANSI ISO SQL provided explicit locking mechanism through cursor, If you want to perform locks then we are using ‘FOR UPDATE’ clause in cursor select statement, Below is the syntax.

CURSOR CursorName

IS

select * from TableName

where Condition FOR UPDATE of ColumnName NOWAIT;

Whenever we are opening the cursor then only oracle server automatically establishes exclusive locks.

WHERE CURRENT OF

This clause is used in update and delete statements, this clause internally uses ROWID, that’s why this clause uniquely identify records.

Whenever we are using ‘WHERE CURRENT OF’ clause than we must use ‘FOR UPDATE’ clause in cursor select statement, below is the syntax

UPDATE TableName SET ColumnName = NewValue

WHERE CURRENT OF CursorName;

DELETE FROM TableName

WHERE CURRENT OF CursorName;

Question: What is ‘WHERE CURRENT OF’ clause?

Answer: ‘WHERE CURRENT OF’ Clause is used to update or delete latest fetched row from the cursor.

 Question: Write a PL/SQL cursor program modify salaries of ANALYST from EMP table using CURSOR locking mechanism.

DECLARE

CURSOR C1 is select * from emp for update;

BEGIN

FOR I IN C1

LOOP

IF I.JOB = ‘ANALYST’ THEN

UPDATE EMP SET sal = i.sal + 500 WHERE CURRENT OF C1;

END IF;

END LOOP;

COMMIT;

END;

Below some more example:

DECLARE

CURSOR C1 IS select * from emp where job = ‘ANALYST’ FOR UPDATE;

i.emp%ROWTYPE

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO I;

EXIT WHEN C1%NOTFOUND;

UPDATE EMP SET sal = i.sal+500;

END LOOP;

COMMIT;

Dbms_output.put_line(C1%ROWCOUNT);

CLOSE C1;

END;

Write PL/SQL cursor program all employees having ‘KING’ as their manager get 5% salary increase from emp table using cursor mechanism.

DECLARE

CURSOR C1(p_mgr NUMBER) IS select * from emp where mgr = p_mgr for update;

A Number(10);

BEGIN

Select empno into A from emp where ename = ‘KING’;

FOR I in C1(A)

LOOP

UPDATE emp SET sal = i.sal*1.05 where current of C1;

END LOOP;

COMMIT;

END;

Oracle provides the ‘FOR UPDATE’ clause in SQL syntax to allow the developer to clock a set of table rows for the duration of transaction the lock records are released automatically when the next commit or Rollback statement is issued