I am trying to understand how the loop termination works at a deeper level. My understanding is that when using simple loop with a fetch, we can exit the loop using [cursor_name]%NOTFOUND or when [collection_var_name].count = 0;

I sort of understand how the %NOTFOUND logic works i.e. it will loop through and fetch all items in the cursor and finally return TRUE if the last fetch failed to return a row.(Please correct me if I am wrong about this).

What I don't understand is how the code below exits the loop when fname.count = 0;

I have displayed the output of fname.count after bulk collect fetch was done. My understanding of this is that the bulk collect allows all the records to be retrieved at once in the first iteration resulting in the count being 107 (which is the total number of employees). In the second iteration I am not what happens. My guess is that the pl/sql engine tries another fetch into fname, and after the context switch, the pointer of the cursor is already at the last record and so the sql engine says there is no more records and returns 0 back which is why the fname.count is 0 after the second iteration.

output excluding the employee names:
1st iteration -> fname.count: 107
2nd iteration -> fname.count: 0

Apologies for the long question, but I am just trying to get a correct understanding.

DECLARE
  CURSOR exp_cur IS 
  select first_name from employees;
  
  TYPE nt_fname IS TABLE OF VARCHAR2(20);
  fname nt_fname;
BEGIN
    open exp_cur;
    loop
      fetch exp_cur bulk collect into fname;
      dbms_output.put_line('fname.count: '||fname.count);
      exit when fname.count = 0; 
      for i in fname.first..fname.last loop
        dbms_output.put_line(i || ' ' || fname(i));
      end loop;
    end loop;
    close exp_cur;
END;
/

CodePudding user response：

Your guess is correct. Leaving aside the fact that it doesn't make sense to have the fetch-bulk collect inside a loop (because you're not using a limit on the bulk collect - your first fetch will always return all rows in the cursor, so your second fetch will never bring back any more rows), the first time you fetched from the cursor, you already hit cursor%notfound, so the second time you fetched, it's going to immediately detect cursor%notfound and not return any rows.

If you changed your bulk collect to limit it, say fetch bulk collect into fname limit 100, you'd see your code run 3 iterations. The first loop would fetch 100 rows, the second loop would fetch 7 rows (and also hit cursort%notfound), and the third loop would fetch 0 rows.

The reason why you use the fname.count rather than exp_cur%notfound in your exit condition is because in the example where we limited the fetch to 100 rows at a time, because the exp_cur%notfound condition is returned by the second fetch, your loop would exit there, and you would have 7 rows left that are unprocessed.