13Apr
By: Steven Feuerstein On: April 13, 2022 In: APEX Developer Solutions, Feuertips Comments: 0

Cursor for loops are wonderful! Among other things, they are the best demonstration of how Ada was adapted to database programming by the Oracle development team way back in the late 1980s.

“So yeah, we’ve got for loops, of course. But we’re running in a database. Might want to loop over multiple rows….with a cursor…hey, how about if we implement a for loop based on a cursor, not an integer range?”

What else makes the cursor for loop so wonderful?

  • The PL/SQL engine does so much of the work for you. Opens the cursor, fetches each row into a record implicitly declared as %rowtype for the select statement, terminates the loop, and closes the cursor when done.
  • It is automatically optimized to retrieve 100 rows with each fetch – kind of like a miniature bulk collect. So you don’t need to convert cursor for loops to bulk collect operations unless the loop contains non-query DML.

What’s not so wonderful about a cursor for loop?

  • Once the loop terminates, you have no information about what just happened (i.e., cursor attributes don’t help).
  • You can’t adjust the “limit” for how many rows are retrieved with each fetch. It’s hard-coded to 100.
  • It’s (apparently) a constant temptation for developers to use when fetching just a single row, “apparently” since this never seemed like a thing to do, but I often come across it.

Cursor for loops containing DML

Don’t use a cursor FOR loop if the loop body executes non-query data manipulation language (DML inserts, updated, deletes, or merges) and if the loop’s driving query returns more than a few hundred rows. Instead, switch to bulk collect and forall. Check out this LiveSQL tutorial for many, many details.

Check out my LiveSQL script Converting from Row-by-Row to Bulk Processing for an example.

Fetching just one row

If you expect to retrieve at most one row, please don’t use a cursor for loop. Use an implicit select into query instead (which I further recommend that you place inside its own retrieval function) or, under some circumstances, an explicit cursor.

It’s certainly true that using a cursor for loop will save you a bit of time (no need to declare a variable for record (or individual columns) being selected. No need to check for no_data_found and too_many_rows. But…but…this is a primo example of “bad lazy.”

My biggest complaint about this (mis)use of the cursor for loop is that it is confusing. You use a loop when you are iterating through multiple…of something – numbers, rows, whatever. Sure, sometimes, you might have just one thing in the set you are iterating through, but that would be the exception, not the rule.

But when you use a cursor for loop with a query that clearly can retrieve at most one row, I feel “wrongness” in my head. When I see the keyword “loop,” I am assuming multiple rows and also identifying a piece of code that needs close attention (multiple iterations -> possible performance issue).

So, please, don’t do this:

function emp_last_name (
   p_employee_id in number
) return varchar2 is
begin
   for rec in (
      select last_name
        from hr.employees
       where employee_id = p_employee_id
   ) loop
      return rec.last_name;
   end loop;
end;

Resources

On Cursor FOR Loops – an article I wrote for Oracle Magazine

The Cursor FOR loop – an Oracle LiveSQL script showing the basics

Implicit vs. Explicit Cursors in Oracle PL/SQL – an Oracle BASE article

Cursor for loop that gets just one row? NO! – an Oracle LiveSQL script examining use of cursor for loop to get a single row

 

Feuertips

Wednesdays at 11 am ET, we go live on YouTube and Facebook to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch later, but live is so much more rewarding!

A participant is selected to choose from 3 organizations dedicated to the preservation of our planet and its biodiversity. Insum will then make a donation of $25 to that group in your name.

What could be better than leveling up your PL/SQL tips and helping a worthy organization? Join us Wednesdays at 11!

Share this:
Share

Leave reply:

Your email address will not be published.