Skip to content

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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
declare -- raises no_data_found when processing 2nd record
   type t_employee_type is table of employee.employee_id%type;
   t_employee    t_employee_type;
   k_rogers      constant integer := 134;
   k_matos       constant integer := 143;
   k_mcewen      constant integer := 158;
   k_index_matos constant integer := 2;
begin
   t_employee := t_employee_type(k_rogers, k_matos, k_mcewen);
   t_employee.delete(k_index_matos);

   if t_employee is not null then
      <<process_employees>>
      for i in 1..t_employee.count()
      loop
         sys.dbms_output.put_line(t_employee(i));
      end loop process_employees;
   end if;
end;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
declare
   type t_employee_type is table of employee.employee_id%type;
   t_employee    t_employee_type;
   k_rogers      constant integer := 134;
   k_matos       constant integer := 143;
   k_mcewen      constant integer := 158;
   k_index_matos constant integer := 2;
   l_index       pls_integer;
begin
   t_employee := t_employee_type(k_rogers, k_matos, k_mcewen);
   t_employee.delete(k_index_matos);

   l_index := t_employee.first();

   <<process_employees>>
   while l_index is not null
   loop
      sys.dbms_output.put_line(t_employee(l_index));
      l_index := t_employee.next(l_index);
   end loop process_employees;
end;
/