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
