Where Current of:
If the cursor is declared with the FOR UPDATE clause then the WHERE CURRENT OF clause can be used in an UPDATE or DELETE statement. Those statements submit to the latest row fetched from the cursor. Syntax for this clause is given below
WHERE CURRENT OF cursorname
where cursorname is the name of a cursor which has been declared with a FOR UPDATE clause. The clause WHERE CURRENT OF evaluates to the row which is just retrieved through the cursor.
Note that the UPDATE statement updates only the column listed in the FOR UPDATE clause of the cursor declaration. If no columns are scheduled then any column can be updated.
It is legal to execute a query with a FOR UPDATE clause other than not reference the rows fetched through WHERE CURRENT OF. In case, the rows are still locked and therefore can only be modified via the current session (that cintains the lock).UPDATE and DELETE statements which modify these rows will not block if they are executed through the session containing the lock. The subsequent example is described of this:
DECLARE
CURSOR c1 IS SELECT * FROM emp FOR UPDATE;
CURSOR c2 IS SELECT * FROM emp;
BEGIN
FOR emp_rec IN c1
LOOP
IF emp_rec.job='MANAGER' THEN
UPDATE emp SET sal=sal+500 WHERE CURRENT OF c1;
ELSIF emp_rec.job='CLERK' THEN
UPDATE emp SET sal=sal+200 WHERE CURRENT OF c1;
END IF; END LOOP;
FOR erec IN c2
LOOP
DBMS_OUTPUT.PUT_LINE(erec.ename ||' now earns '||erec.sal);
END LOOP;
END;
In the above-given example the program first checks whether the job of the employee is CLERK or MANAGER.
If the condition assess to TRUE the cursor updates which particular record's salary as produced in the program. This is achieved through using WHERE CURRENT of clause. Or else if the fetch results in TRUE condition the salary will be updated as mentioned for the all table. The output of the above program will be:
KING now earns 5000
BLAKE now earns 3350
CLARK now earns 2950
JONES now earns 3475
MARTIN now earns 1250
ALLEN now earns 1600
TURNER now earns 1500
JAMES now earns 1150
WARD now earns 1250
FORD now earns 3000
SMITH now earns 1000
SCOTT now earns 3000
ADAMS now earns 1300
MILLER now earns 1500