Tuesday, 10 April 2018

WHERE CURRENT OF clause in Oracle PL/SQL

What is WHERE CURRENT OF clause?
The WHERE CURRENT OF clause is used in conjunction with the FOR UPDATE clause to refer to the current row in an explicit cursor. The WHERE CURRENT OF clause is used in the UPDATE or DELETE statement, whereas the FOR UPDATE clause is specified in the cursor declaration.

We can use the combination for updating and deleting the current row from the corresponding database table. This enables us to apply updates and deletes to the row currently being addressed, without the need to explicitly reference the row ID. We must include the FOR UPDATE clause in the cursor query so that the rows are locked on OPEN.

In the syntax:

cursor Is the name of a declared cursor (The cursor must have been declared with the FOR UPDATE clause.)

Requirement: Let us consider the EMP table as shown below.
Now the requirement is to increment the EMP_SALARY of each row by 1000. Thus the EMP_SALARY after incrementing it by 1000 is as below.
Solution: For the solution of the above requirement follow the steps as shown below.

Step 1: Create the EMP table.

CREATE TABLE emp (
    emp_name     VARCHAR2(20),
    emp_salary   NUMBER(10)
);

Step 2: Insert data into the EMP table using the below INSERT statements.

INSERT INTO emp VALUES('Susanto',3000);
INSERT INTO emp VALUES('Paul',4000);
INSERT INTO emp VALUES('Susanto',3000);
INSERT INTO emp VALUES('Paul',6000);
INSERT INTO emp VALUES('Anjali',4000);

After executing the above statement COMMIT the changes.

Thus the data in the EMP table is shown below:
Step 3: Let us create a PROCEDURE as shown below to increment the records by 1000.

DECLARE
    CURSOR c1 IS SELECT * FROM emp;
BEGIN
    FOR r IN c1 LOOP
        UPDATE emp SET emp_salary = emp_salary + 1000 WHERE emp_name = r.emp_name;
    END LOOP;
    COMMIT;
END;
/

Now when we execute the above PL/SQL  procedure, the records get incremented as shown below.
From the above analysis, we can see that the output for EMP 'Susanto' and 'Paul' is wrong as the EMP_SALARY of EMP 'Susanto' and 'Paul' got incremented by 2000 instead of 1000.
To avoid such issue we can use the WHERE CURRENT OF clause as shown below:

DECLARE
    CURSOR c1 IS SELECT * FROM emp FOR UPDATE OF emp_salary NOWAIT;
BEGIN
    FOR r IN c1 LOOP
        UPDATE emp SET emp_salary = emp_salary + 1000 WHERE CURRENT OF c1;
    END LOOP;
    COMMIT;
END;
/

Thus the output is shown below:
To use CURRENT OF clause our SELECT statement should have FOR UPDATE OF <update column_name> nowait;

The main advantage of using CURRENT OF clause is to update a table that does not contain the primary key or unique key.

Hence the solution to our requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.