Skip to content

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