oracle - Difference between FETCH/FOR to loop a CURSOR in PL/SQL -

i know fetching cursor give me access variables %rowcount, %rowtype, %found, %notfound, %isopen

...but wondering if there other reasons use

open - fetch - close instructions loop cursor

rather than

loop cursor cycle... (in opinion better becase simple)

what think?

from performance standpoint, difference lot more complicated tim hall tip omg ponies linked to imply. believe tip introduction larger section has been excerpted web-- expect tim went on make if not of these points in book. additionally, entire discussion depends on oracle version you're using. believe correct 10.2, 11.1, , 11.2 there differences if start going older releases.

the particular example in tip, first of all, rather unrealistic. i've never seen code single-row fetch using explicit cursor rather select into. fact select more efficient of limited practical importance. if we're discussing loops, performance we're interested in how expensive fetch many rows. , that's complexity starts come in.

oracle introduced ability bulk collect of data cursor pl/sql collection in 10.1. more efficient way data sql engine pl/sql collection because allows minimize context shifts fetching many rows @ once. , subsequent operations on collections more efficient because code can stay within pl/sql engine.

in order take maximum advantage of bulk collect syntax, though, have use explicit cursors because way can populate pl/sql collection , subsequently use forall syntax write data database (on reasonable assumption if fetching bunch of data in cursor, there strong probability doing sort of manipulation , saving manipulated data somewhere). if use implicit cursor in loop, omg ponies correctly points out, oracle doing bulk collect behind scenes make fetching of data less expensive. code doing slower row-by-row inserts , updates because data not in collection. explicit cursors offer opportunity set limit explicitly can improve performance on default of 100 implicit cursor in loop.

in general, assuming you're on 10.2 or greater , code fetching data , writing database,

fastest

1. explicit cursors doing bulk collect local collection (with appropriate limit) , using forall write database.
2. implicit cursors doing bulk collect behind scenes along single-row writes datbase.
3. explicit cursors not doing bulk collect , not taking advantage of pl/sql collections.

slowest

on other hand, using implicit cursors gets quite bit of benefit of using bulk operations little of upfront cost in refactoring old code or learning new feature. if of pl/sql development done developers primary language else or don't keep new language features, loops going easier understand , maintain explicit cursor code used new bulk collect functionality. , when oracle introduces new optimizations in future, it's far more implicit cursor code benefit automatically while explicit code may require manual rework.

of course, time you're troubleshooting performance point care how faster different variants of looping code might be, you're @ point want consider moving more logic pure sql , ditching looping code entirely.