Oracle – Quick Tip – Replace Explicit Cursors With FOR LOOP In PL/SQL

Caution:

If your cursor logic has a “FOR UPDATE” clause, please leave it in place as it locks the records in the Resultset for the update (until next COMMIT or ROLLBACK) and you will open up your code for unnecessary race conditions by replacing that with FOR LOOP (like two people reserving the same seat on an airplane!).

I hate don’t like OPEN/FETCH/CLOSE

Back in the day, the most commonly used method to loop through and process “a small number of records” in PL/SQL was to use the WHILE/OPEN/FETCH/CLOSE explicit cursor operations.

Not only is the syntax cryptic and cumbersome, it can also lead to errors if you miss something really simple inside your loop.

The easier way to do this is using implicit cursors. By the way, this is a fully working anonymous block.

BEGIN
    FOR v_Rec IN (SELECT * FROM My_Table) LOOP
        DBMS_OUTPUT.PUT_LINE(v_Rec.Client_Id);
    END LOOP;
END;

Here are a few things to note above

  • V_REC variable is not even declared. PL/SQL implicitly declared it for
  • There is no explicit CURSOR definition. Inline SQL was used
  • The looping logic is as straightforward as it can get.

Please note that this mechanism is good only when you want to loop through a small number of records. There are other ways to do the same if you will work with a large number of records like using set based SQL statements and by using BULK operations.

Advertisements

One thought on “Oracle – Quick Tip – Replace Explicit Cursors With FOR LOOP In PL/SQL

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