Why use explicit cursors instead of regular loops?

I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data:

for i in (select * from STUDENTS) loop
      htp.prn(i.student_last_name || ', ' || i.student_first_name || ' ' || i.student_dob);
end loop;

But, cursors seem to be the 'right' way to do things. I can find lots of information on what cursors are and different ways to loop through them, but I can't find a solid reason why to use them over regular FOR loops.
Is it dependent on the needs of the procedure? Are there inherent advantages I should be aware of?

The code you posted is using a cursor. It is using an implicit cursor loop.

There are cases where using an explicit cursor loop (i.e. declaring a CURSOR variable in the declaration section) produces either cleaner code or better performance

• If you have more complex queries that you can't refactor out into views, it can make the code easier to read if your loop iterates over student_cursor rather than including a 30 line SQL statement that embeds a bunch of logic. For example, if you were printing out all the students that were cleared to graduate and that involved joining to tables that had their academic records, the requirements of their degree program, tables with information on academic holds, tables with information about overdue library books, tables with information about unpaid fees, administrative overrides, etc. it would probably make sense to refactor the code so that this query wasn't stuck in the middle of code that is concerned with presenting the list to a user. That might involve creating a view that would encapsulate all this logic. Or it might involve creating an explicit cursor that was declared either as part of the current PL/SQL block or in some higher-level PL/SQL block (i.e. a cursor declared in a package) so that it is reusable. Or it might involve doing something else for encapsulation and reusability (say, creating a pipelined table function instead).

• If you want to make use of bulk operations in PL/SQL, you generally want to use explicit cursors. Here is a StackOverflow thread that discusses the performance differences between explicit and implicit cursors. If all you are doing is calling htp.prn, doing a BULK COLLECT probably doesn't buy you anything. In other cases, though, it can result in substantial performance improvements.

StackExchange Database Administrators Q#27045, answer score: 16