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 | 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
21 | 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;
/
|