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 | begin
for l_counter in 1..5 loop
insert into headers (id, text) values (l_counter, 'Number '||l_counter);
insert into lines (header_id, line_no, text)
select l_counter, rownum, 'Line '||rownum
from dual
connect by level <= 3;
commit;
end loop;
end;
/
|
Example (good)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 | declare
procedure create_rows (
p_header_id in headers.id%type
) is
begin
insert into headers (id, text) values (p_header_id, 'Number '||p_header_id);
insert into lines (header_id, line_no, text)
select p_header_id, rownum, 'Line '||rownum
from dual
connect by level <= 3;
commit;
end;
begin
for l_counter in 1..5 loop
create_rows(l_counter);
end loop;
end;
/
|