PL/SQL Best Practices [16][Iterative Processing]- FOR Loop Index

Never Declare the FOR Loop Index

FOR year_ind IN 1 .. 20
LOOP
   calc_profits (year_ind);
END LOOP;

Do not declare the loop index variable (year_ind in the example above). PL/SQL does that for you automatically. For both numeric and cursor FOR loops, the identifier after the FOR keyword is automatically declared by PL/SQL with type BINARY_INTEGER or a record to match the cursor. If you declare a variable with same name as loop index, it is a different variable.

You could refer to the FOR loop index outside the loop and your code will compile, but it will not be doing what you think or what you want.

This code will compile, but it will not work as intended. This kind of code is very hard to understand and debug.

 1DECLARE
 2   CURSOR emp_cur IS
 3      SELECT empno, ename FROM emp;
 4   emp_rec emp_cur%ROWTYPE;
 5BEGIN
 6   FOR emp_rec IN emp_cur
 7   LOOP
 8      display_emp (emp_rec.ename);
 9   END LOOP;
10   IF emp_rec.ename = 'FEUERSTEIN'
11   THEN
12      give_raise 
13        (emp_rec.empno, 1000000);
14   END IF;
15END;

Suppose you need the value of the loop index (year_count in the following example) for debugging:

 1DECLARE   year_count INTEGER := NULL;
 2BEGIN
 3   FOR year_count IN 1 .. 20
 4   LOOP
 5      calc_pnl (year_count);
 6   END LOOP;
 7EXCEPTION
 8   WHEN NO_DATA_FOUND 
 9   THEN
10      DBMS_OUTPUT.PUT_LINE 
11        ('Error in year ' || 
12          TO_CHAR (year_count));
13END;
14

In this case use a local variable and copy the loop index to local variable:

 1DECLARE   my_count INTEGER := NULL;
 2BEGIN
 3   FOR year_count IN 1 .. 20
 4   LOOP
 5      my_count := year_count;
 6      calc_pnl (year_count);
 7   END LOOP;
 8EXCEPTION
 9   WHEN NO_DATA_FOUND 
10   THEN
11      DBMS_OUTPUT.PUT_LINE 
12        ('Error in year ' || 
13          TO_CHAR(my_count));  
14END;