G-4360: Always use a WHILE loop to process a loose array.
Reason
When a loose (also called sparse) 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.
Please note that:
- Varrays are always dense
- Associative arrays (or index-by tables) are either dense or sparse
- Nested tables start dense and may become sparse
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 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)
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 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;
/
|