G-3320: Try to move transactions within a non-cursor loop into procedures.
Major
Maintainability, Reusability, Testability
Reason
Commit inside a non-cursor loop (other loop types than loops over cursors - see also G-3310) is either a self-contained atomic transaction, or it is a chunk (with suitable restartability handling) of very large data manipulations. In either case encapsulating the transaction in a procedure is good modularity, enabling reuse and testing of a single call.
Example (bad)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 | declare
co_upper_bound constant integer := 5;
co_max_level constant integer := 3;
co_number constant types_up.short_string := 'Number';
co_line constant types_up.short_string := 'Line';
co_space constant types_up.short_string := ' ';
l_counter integer := 0;
begin
<<create_headers>>
loop
insert into headers (id,text)
values (
l_counter,co_number
|| co_space
|| l_counter
);
insert into lines (header_id,line_no,text)
select l_counter
,rownum
,co_line
|| co_space
|| rownum
from dual
connect by level <= co_max_level;
commit;
l_counter := l_counter + 1;
exit create_headers when l_counter > co_upper_bound;
end loop create_headers;
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
23
24
25
26
27
28
29
30
31
32
33
34
35 | declare
co_upper_bound constant integer := 5;
co_max_level constant integer := 3;
co_number constant types_up.short_string := 'Number';
co_line constant types_up.short_string := 'Line';
co_space constant types_up.short_string := ' ';
procedure create_rows(
in_header_id in headers.id%type
) is
co_header_id constant headers.id%type := in_header_id;
begin
insert into headers (id,text)
values (in_header_id,co_number
|| co_space
|| co_header_id);
insert into lines (header_id,line_no,text)
select co_header_id
,rownum
,co_line
|| co_space
|| rownum
from dual
connect by level <= co_max_level;
commit;
end create_rows;
begin
<<create_headers>>
for l_counter in 1..co_upper_bound
loop
create_rows(l_counter);
end loop create_headers;
end;
/
|