Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:

6-23.

The best way to scan the contents of an index-by table is shown in (d). Start with the first row in the table. If this returns NULL, the table is empty, and you stop immediately. Otherwise, display the present and then move on to the next defined row with the NEXT method. It is efficient, very readable, and doesn’t raise NO_DATA_FOUND errors. Now what is wrong with each of the other approaches? Reliance on the numeric FOR loop in this first attempt has a big problem: if the table is empty, FIRST and LAST return NULL. You would like to think that a numeric FOR loop from NULL to NULL—NULL to anything for that matter—would simply not execute. Sadly, this is not the case. PL/SQL will try to run the body of the loop and immediately raise a VALUE_ERROR exception. Very difficult to track down! You have placed the numeric FOR loop inside a conditional. As a result, you execute the FOR loop only when you know that there is something in the table. So you don’t get the VALUE_ERROR exception. There is still a problem, however; You are assuming that the table is filled sequentially (i.e., there is a present for each year between first and last). This is always a dangerous assumption to make about an index-by table, and in this case it is a false assumption. The result? This loop raises the NO_DATA_FOUND exception. Here you use the EXISTS operator to make sure you never raise the NO_DATA_FOUND exception. This loop displays correctly each of my son’s favorite presents. The problem with ...