When is the query in a cursor executed?

Question

Suppose I have something like:

CURSOR foo_cur IS
  SELECT * FROM foo;

...

DELETE FROM foo WHERE bar=1;

FOR row IN foo_cur 
LOOP
...
END LOOP;

If I delete rows from foo before I open the cursor, will these rows still be part of the cursor result? Is the query SELECT * FROM foo executed at the line FOR row IN foo_cur?

Answer 1

The set of rows that will be returned from a cursor is determined at the point that the cursor is opened (either via an explicit OPEN or implicitly by the FOR loop). In this case, the row(s) that you deleted will not be returned in your loop.

Generally, the query is not executed all at once. Oracle executes the query enough to fetch the next set of rows, returns those rows to the PL/SQL VM, and waits until the request comes to fetch more rows. In 11g, Oracle will do an implicit BULK COLLECT of 100 rows at a time so every 100 iterations of the loop the query is executed further until there are no more rows to return. Because of multi-version read consistency, Oracle will always return the data to you as it existed when the cursor was opened even if other sessions are making and committing changes while your code is running.