Difference Between Fetch/For to Loop a Cursor in Pl/Sql

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

From a performance standpoint, the difference is a lot more complicated than the Tim Hall tip that OMG Ponies linked to would imply. I believe that this tip is an introduction to a larger section that has been excerpted for the web-- I expect that Tim went on to make most if not all of these points in the book. Additionally, this entire discussion depends on the Oracle version you're using. I believe this is correct for 10.2, 11.1, and 11.2 but there are definitely differences if you start going back to older releases.

The particular example in the tip, first of all, is rather unrealistic. I've never seen anyone code a single-row fetch using an explicit cursor rather than a SELECT INTO. So the fact that SELECT INTO is more efficient is of very limited practical importance. If we're discussing loops, the performance we're interested in is how expensive it is to fetch many rows. And that's where the complexity starts to come in.

Oracle introduced the ability to do a BULK COLLECT of data from a cursor into a PL/SQL collection in 10.1. This is a much more efficient way to get data from the SQL engine to the PL/SQL collection because it allows you to minimize context shifts by fetching many rows at once. And subsequent operations on those collections are more efficient because your code can stay within the PL/SQL engine.

In order to take maximum advantage of the BULK COLLECT syntax, though, you generally have to use explicit cursors because that way you can populate a PL/SQL collection and then subsequently use the FORALL syntax to write the data back to the database (on the reasonable assumption that if you are fetching a bunch of data in a cursor, there is a strong probability that you are doing some sort of manipulation and saving the manipulated data somewhere). If you use an implicit cursor in a FOR loop, as OMG Ponies correctly points out, Oracle will be doing a BULK COLLECT behind the scenes to make the fetching of the data less expensive. But your code will be doing slower row-by-row inserts and updates because the data is not in a collection. Explicit cursors also offer the opportunity to set the LIMIT explicitly which can improve performance over the default of 100 for an implicit cursor in a FOR loop.

In general, assuming that you're on 10.2 or greater and that your code is fetching data and writing it back to the database,

Fastest

1. Explicit cursors doing a BULK COLLECT into a local collection (with an appropriate LIMIT) and using FORALL to write back to the database.
2. Implicit cursors doing a BULK COLLECT for you behind the scenes along with single-row writes back to the datbase.
3. Explicit cursors that are not doing a BULK COLLECT and not taking advantage of PL/SQL collections.

Slowest

On the other hand, using implicit cursors gets you quite a bit of the benefit of using bulk operations for very little of the upfront cost in refactoring old code or learning the new feature. If most of your PL/SQL development is done by developers whose primary language is something else or who don't necessarily keep up with new language features, FOR loops are going to be easier to understand and maintain than explicit cursor code that used all the new BULK COLLECT functionality. And when Oracle introduces new optimizations in the future, it's far more likely that the implicit cursor code would get the benefit automatically while the explicit code may require some manual rework.

Of course, by the time you're troubleshooting performance to the point where you really care about how much faster different variants of your looping code might be, you're often at the point where you would want to consider moving more logic into pure SQL and ditching the looping code entirely.

PL/SQL Cursor FOR loop using FETCH

You did it right and you don't need a fetch, in fact you did the fetch but you did it implicitly, to use a fetch keyword you need a record type and also you will need to open and close the cursor and also check for is it open or not and also check for if it has rows(in the loop), following is another for of your cursor which uses fetch and a record type:

DECLARE
    CURSOR blog_cursor IS SELECT * FROM blog;
    blog_item blog%rowtype;
BEGIN
  OPEN blog_cursor;
  LOOP
    FETCH blog_cursor INTO blog_item;
    EXIT WHEN blog_cursor%NOTFOUND;
    IF( blog_item.blog_id > 4 ) THEN
      -- Insert a row in the "table log"
      INSERT INTO log( log_id, log_url, log_desc )
      VALUES( blog_item.blog_id, blog_item.blog_url, blog_item.blog_desc );
    END IF;
  END LOOP;
  CLOSE blog_cursor;
END;

why we don't need to open and fetch explicit cursor in for loop in pl sql?

Because CURSOR FOR LOOP does OPEN, FETCH, and CLOSE implicitly. And that is is why these cursors called implicit

For select_statement, PL/SQL declares, opens, fetches from, and closes an implicit cursor.

See here

What is the difference between foreach cursor explicit and foreach cursor implicit

An explicit cursor should explicitly be defined and declared pointing to a private SQL area, while implicit cursor is just a SQL statement which doesn't need to be opened by the anyone, as already been opened and closed by the database on its own.

For your case, using such code

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  v_ort varchar(45);
BEGIN
  FOR v_rec IN 
  (
    SELECT l.ort, lb.lnr, lb.lfndnr, lb.datum, lb.stueck, lb.anr
      FROM lager l
      JOIN lagerbuchung lb
     WHERE lb.lnr = l.lnr 
  )  
  LOOP
    DBMS_OUTPUT.PUT_LINE(': ' || v_rec.lnr || ' : ' || v_rec.lfndnr ||
                         ' : ' || v_rec.datum || ' : ' || v_rec.stueck ||
                         ' : ' || v_rec.anr || ' : ' || v_rec.ort);
  END LOOP;
END;

will be enough to convert the current one to an implicit cursor

PL/SQL-Cursor: iterate with FOR and IN

Unfortunally it is not available to use for in cycle with ref cursor because for in cycle requires column list defined at compile time. For example

begin
    for i in (select dummy, sysdate dt from dual) loop
       dbms_output.put_line(i.dummy || ': ' || i.dt);
    end loop;
end;
/

---

Related Topics