Back to the basics: WHERE CURRENT OF
Posted by decipherinfosys on January 27, 2009
This clause is normally used in the DELETE and UPDATE statements in order to work on the latest row fetched by a cursor.
Why is it advantageous to use this clause? Normally delete and update statements are followed by a select statement. We give a where criteria in the select clause to fetch interested records into the cursor and then work on those records in order to either update it or delete it. WHERE CURRENT OF clause saves us from defining this filter criteria or where clause during the update and/or delete statement.
Here’s an example. First we will create table and populate it with some data.
CREATE TABLE TEST
col1 VARCHAR2(10) NOT NULL,
col2 VARCHAR2(10) NOT NULL,
CONSTRAINT PK_TEST PRIMARY KEY(col1,col2)
INSERT INTO TEST(col1,col2,col3) VALUES(‘ORACLE’,’11G’,1111);
INSERT INTO TEST(col1,col2,col3) VALUES(‘ORACLE’,’10G’,2222);
INSERT INTO TEST(col1,col2,col3) VALUES(‘SQLSERVER’,’2000′,3333);
INSERT INTO TEST(col1,col2,col3) VALUES(‘SQLSERVER’,’2005′,4444);
INSERT INTO TEST(col1,col2,col3) VALUES(‘SQLSERVER’,’2008′,5555);
Following anonymous PL/SQL block demonstrates the use of the WHERE CURRENT OF clause:
CURSOR c1 IS SELECT * FROM TEST FOR UPDATE NOWAIT;
FETCH c1 INTO test_rec;
IF test_rec.col1 = ‘ORACLE’ THEN
UPDATE TEST SET col3 = col3 + 1,col4 = sysdate + 1 WHERE CURRENT OF c1;
EXIT WHEN c1%NOTFOUND;
PL/SQL procedure successfully completed.
Though in our example we haven’t used the where clause, the main idea here is to avoid duplicating where clause in each update/delete statement after cursor is opened to update the same record. This will help synchronize code only in one place without worrying about changing it everywhere specially when the where clause is changed.