I can’t remember using the PL/SQL construct where current of in code I have written so along with my usual article style of how use to use this feature, this is also an experiment to find out if there are any other reasons to use where current of.
The code examples in this post have been developed using the Oracle Database App Development VM for Oracle VirtualBox which at the time of this post was using Oracle database 220.127.116.11. (Although nothing in this post is 12c specific)
First I create a table that can be used for the experiment
create table t as select * from all_objects /
With the table created, I then use it in the following example which I will explain below. At this point I would like to say that I appreciate the example could be rewritten as a single SQL statement however I wanted to concentrate on illustrating the use of where current of without an overtly complicated example where the point of this post is lost in a forest of code.
declare cursor c is select t.object_name from t where t.object_type in ('PACKAGE', 'PACKAGE BODY') and t.owner = 'APEX_040200' for update of object_name; ln_count pls_integer := 1; begin for i in c loop update t set t.object_name = 'X - ' || ln_count where current of c; ln_count := ln_count + 1; end loop; commit; end;
At line 3 I declare a cursor that identifies the records of interest, which are the package specifications and bodies owned by the APEX user. Note the use of the for update clause. This is mandatory when using where current of . If you remove it and try to run the example you will see the following error message
PLS-00404: cursor 'C' must be declared with FOR UPDATE to use with CURRENT OF
Moving on to the execution section, for each iteration of the cursor for loop, the object name is updated (to something not very useful!) and you can see at line 20 what I think is the primary benefit of using where current of is that instead of repeating the where clause used by the cursor I have used where current of c. So when the cursor has to change to return different records the only change that needs to be made is to the cursor.
The final part of the example is once the loop completes I release the lock on the table by committing the changes.
The primary benefit of where current of is the reduction in duplicated code which is a good thing but am I missing anything else? Please use the comments to let me know.
In this post I have demonstrated how to use the PL/SQL construct where current of along with what I believe is it’s primary benefit, the reduction of duplicated code.
The inspiration for this post came from Steven Feuerstein’s book, Oracle PL/SQL Programming 5th Edition.