I have generally recommended in the past that whenever you are writing code to iterate through the elements of a collection, you should use a while loop, combined with the FIRST-NEXT or LAST-PRIOR collection methods.
The key advantage of this approach is that the code will not raise a NO_DATA_FOUND exception if your collection is sparse (there is an index value between FIRST and LAST that is not defined). And if your collection is empty, the loop will not execute at all, whereas with a for loop, an empty collection could cause a VALUE_ERROR exception if you are not careful.
Here is an example of the kind of code that requires the use of the while loop:
DECLARE
TYPE employee_tt
IS
TABLE OF employees%ROWTYPE
INDEX BY pls_integer;
employee_cache employee_tt;
BEGIN
/* Fill the collection using the employee ID
as the index value - most like these values are
NOT sequentially defined; primary keys can
certainly have "gaps". */
FOR rec IN (SELECT *
FROM employees)
LOOP
employee_cache (rec.employee_id) := rec;
END LOOP;
FOR indx IN 1 .. employee_cache.COUNT
LOOP
DBMS_OUTPUT.put_line (employee_cache (indx).last_name);
END LOOP;
END;
/
When I execute this block, I get the following error:
Instead, I should use a while loop:
DECLARE
TYPE employee_tt
IS
TABLE OF employees%ROWTYPE
INDEX BY pls_integer;
employee_cache employee_tt;
l_index pls_integer;
BEGIN
/* Fill the collection using the employee ID
as the index value - most like these values are
NOT sequentially defined; primary keys can
certainly have "gaps". */
FOR rec IN (SELECT *
FROM employees)
LOOP
employee_cache (rec.employee_id) := rec;
END LOOP;
l_index := employee_cache.FIRST;
WHILE (l_index IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (employee_cache (l_index).last_name);
l_index := employee_cache.NEXT (l_index);
END LOOP;
END;
/
and then I will not see any errors.
So, no doubt about it, this is good advice – but should you always use the while loop?
I suggest that in situations when you know, when you are absolutely sure, that your collection is densely-filled, you should use the FOR loop, for two reasons:
- It is a simpler solution, requiring less code (reducing the chance of a bug creeping into your code and making it easier to maintain).
- It is more self-documenting. By using the for loop construct, you are stating that an assumption of this code is that the collection is densely-filled.
- It is more efficient than the while loop.
So when you can be certain that your collection is densely-filled? When....
- The collection is populated by a BULK COLLECT query. In this situation, the first index used is always 1, and the collection is filled sequentially (2, 3, 4...) from that index.
- The collection is a nested table assigned its contents from a MULTISET operator (UNION, INTERSECT and EXCEPT). These set level operators always fill sequentially a nested table from index value 1.
And what kind of difference in performance can you expect to see? Not a very big difference, but the FOR loop is
definitely faster. I put together the script below (relying on the sf_timer package, included in the
demo.zip.
DECLARE
l_index pls_integer;
l_source DBMS_SQL.varchar2a;
BEGIN
sf_timer.start_timer;
SELECT text
BULK COLLECT INTO l_source
FROM all_source;
sf_timer.show_elapsed_time (
'Retrieved ' || TO_CHAR(l_source.COUNT) || ' elements'
);
--
sf_timer.start_timer;
FOR indx IN 1 .. l_source.COUNT
LOOP
NULL;
END LOOP;
sf_timer.show_elapsed_time ('FOR loop through collection');
--
sf_timer.start_timer;
l_index := l_source.FIRST;
WHILE (l_index IS NOT NULL)
LOOP
NULL;
l_index := l_source.NEXT (l_index);
END LOOP;
sf_timer.show_elapsed_time ('Full collection scan with NEXT');
END;
I saw the following results:
Retrieved 3079394 elements - Elapsed CPU : 11.14 seconds.
FOR loop through collection - Elapsed CPU : .05 seconds.
Full collection scan with NEXT - Elapsed CPU : .48 seconds.
In other words, the while loop is an order of magnitude slower than the for loop. That sounds like a big deal, but it's only a difference of .4 seconds with a scan of over 3,000,000 elements. With smaller collections, you'll probably never notice the difference.