Understanding WHERE CURRENT OF….

While teaching today in the class, concept of WHERE CURRENT OF did come. Though I explained it in the session, I think it’s better to explain it with an(simple) example here. So here it goes.

Whenever we process data, it’s always a good idea to have in the data set some column which will identify our work uniquely. Not only this unique column helps in fetching the records more promptly, it also facilitates the updates done to the same. But what if we don’t have the such unique column with us? What happens then? Let’s find out.

So I am creating a simple table with two columns. One will hold few character values and second column, few numeric values.

SQL> conn aman/aman@pdb1

Connected.

SQL> create table do( a char, b number(4));

Table created.

 

SQL> insert into do values(‘&b’,&bb);

Enter value for b: a

Enter value for bb: 1

old 1: insert into do values(‘&b’,&bb)

new 1: insert into do values(‘a’,1)

1 row created.

SQL> /

Enter value for b: b

Enter value for bb: 2

old 1: insert into do values(‘&b’,&bb)

new 1: insert into do values(‘b’,2)

1 row created.

SQL> /

Enter value for b: c

Enter value for bb: 3

old 1: insert into do values(‘&b’,&bb)

new 1: insert into do values(‘c’,3)

1 row created.

SQL> /

Enter value for b: d

Enter value for bb: 4

old 1: insert into do values(‘&b’,&bb)

new 1: insert into do values(‘d’,4)

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dp

2

SQL> select * from do;

 

A     B

– ———-

a     1

b     2

c     3

d     4

SQL> insert into do values(‘a’,2);

1 row created.

SQL> commit;

Commit complete.

So this is what we have.

SQL> select * from do;

 

A     B

– ———-

a     1

b     2

c     3

d     4

a     2

 

Now, we haven’t added any Unique/Primary Key in this table. Let’s try to update this table using a simple Pl/SQL block.

SQL> ed

Wrote file afiedt.buf

1 declare

2    cursor c1 is

3    select * from do;

4     begin

5     for do_rec in c1

6     loop

7     update do set b=b+100    where a=do_rec.a;

8     end loop;

9*    end;

/

And this is what we get.

SQL> select * from do;

 

A     B

– ———-

a     201

b     102

c     103

d     104

a     202

 

Interesting. If you see closely, we have actually got a problem. Since there is no unique column or key present, how the code is running is that it has update the rest of the column A’s values one time(added 100 one time) but for the value A, since it has come twice, it has updated twice. Now this is certainly not what we had wanted as we wanted to get each row updated just once. But that’s clearly not what is happening due to the lack of unique key present in the table.

Now, we could overcome this situation by using ROWID clause in the where clause. So the code would had been changed to update do set b=b+100    where ROWID=do_rec.rowid;

This would had solved the lack of uniqueness issue in this table. But we do know that ROWID can’t be relied since it may change due to housekeeping activities on the table like a REORG or it may change due to issues like Migration or Row Chaining.

Fortunately a better option exists – WHERE CURRENT OF.

So let’s run the code using WHERE CURRENT OF. Here is the modified code.

1 declare

2    cursor c1 is

3    select * from do for update;

4     begin

5     for do_rec in c1

6     loop

7     update do set b=b+100    where current of c1;

8     end loop;

9*    end;

SQL> /

PL/SQL procedure successfully completed.

Now we can see that besides adding WHERE CURRENT OF, we have also updated the SELECT statement to include FOR UPDATE clause. This is mandatory for using WHERE CURRENT OF and this puts a lock on the impacted rows under this SELECT and ensures that any other session’s DML activity won’t update these rows till our work won’t complete.

Here is the result of this code on our table.

SQL> select * from do;

A     B

– ———-

a     101

b     102

c     103

d     104

a     102

 

And we can see now the code works as we expected it to. WHERE CURRENT OF internally identifies duplicates rows using ROWID and ensures that the data currently pointed in the cursor is updated and that’s why, even though value A occurred twice, now it’s update one time just as we wanted.

Last but certainly not the least, example shown is for the UPDATE command but WHERE CURRENT OF also works with DELETE.

Hope it helped.

Aman….