for loop enclosing PL/SQL blocks in pro*C

Question

I have seen many pro*C programs, using a for loop to execute a set a statements "only once". For example,

for(i = 0; i < 1; i++)
{
    EXEC SQL EXECUTE
    DECLARE

        /* some declarations here */

    BEGIN

        /* some PL/SQL code here  */

    END-EXEC;
}

Why is this for loop necessary ?

Answer 1

Just a wild guess: using such a loop might somehow simplify error handling when using WHENEVER DO BREAK or WHENEVER DO GOTO:

Consider the following code fragment:

for(i = 0; i < 1; i++)
{
    EXEC SQL WHENEVER SQLERROR DO BREAK; 
    EXEC SQL UPDATE emp SET sal = sal * 10; 
}
printf("%d",i);

If I'm not too wrong (don't have pro*C at hand right now), this would print 1 if the SQL query has completed without error. But 0 otherwise as we break before incrementing i.

---

Somewhat at the margin of that, there is a common idiom using an infinite for loop and a WHENEVER DO BREAK statement to fetch results:

EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
    EXEC SQL FETCH...
}
EXEC SQL CLOSE my_cursor; 

Answer 2

One of the common scenarios of using such a loop is when data is available in an array that would be used somewhere else in the program but in some sections, you need to process only the first record of that array.

This loop helps in singling out the first record of such arrays.

However, when you need to single out the first record of an array, make sure you use 'order by' in your fetching cursor so that you can control what would be your first record and perform required operations on it accordingly.