G-4360: Always use a WHILE loop to process a loose array.

Minor

Efficiency

Reason

When a loose array is processed using a NUMERIC FOR LOOP we have to check with all iterations whether the element exist to avoid a NO_DATA_FOUND exception. In addition, the number of iterations is not driven by the number of elements in the array but by the number of the lowest/highest element. The more gaps we have, the more superfluous iterations will be done.

Example (bad)

DECLARE -- raises no_data_found when processing 2nd record
   TYPE t_employee_type IS TABLE OF employees.employee_id%TYPE;
   t_employees t_employee_type;
   co_rogers      CONSTANT INTEGER := 134;
   co_matos       CONSTANT INTEGER := 143;
   co_mcewen      CONSTANT INTEGER := 158;
   co_index_matos CONSTANT INTEGER := 2;
BEGIN
   t_employees := t_employee_type(co_rogers, co_matos, co_mcewen);
   t_employees.DELETE(co_index_matos);

   IF t_employees IS NOT NULL THEN
      <<process_employees>>
      FOR i IN 1..t_employees.COUNT()
      LOOP
         sys.dbms_output.put_line(t_employees(i));
      END LOOP process_employees;
   END IF;
END;
/

Example (good)

DECLARE
   TYPE t_employee_type IS TABLE OF employees.employee_id%TYPE;
   t_employees t_employee_type;
   co_rogers      CONSTANT INTEGER := 134;
   co_matos       CONSTANT INTEGER := 143;
   co_mcewen      CONSTANT INTEGER := 158;
   co_index_matos CONSTANT INTEGER := 2;
   l_index        PLS_INTEGER;
BEGIN
   t_employees := t_employee_type(co_rogers, co_matos, co_mcewen);
   t_employees.DELETE(co_index_matos);

   l_index := t_employees.FIRST();

   <<process_employees>>
   WHILE l_index IS NOT NULL
   LOOP
      sys.dbms_output.put_line(t_employees(l_index));
      l_index := t_employees.NEXT(l_index);
   END LOOP process_employees;
END;
/