Systems Engineering and RDBMS

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,
col3 NUMBER(9),
col4 DATE,
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);
COMMIT;

Following anonymous PL/SQL block demonstrates the use of the WHERE CURRENT OF clause:

DECLARE
CURSOR c1 IS SELECT * FROM TEST FOR UPDATE NOWAIT;
test_rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO test_rec;
IF test_rec.col1 = ‘ORACLE’ THEN
UPDATE TEST SET col3 = col3 + 1,col4 = sysdate + 1 WHERE CURRENT OF c1;
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
COMMIT;
END;
/

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: