Skip to content

G-2220: Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values.

Critical

Efficiency

Reason

pls_integer having a length of -2,147,483,648 to 2,147,483,647, on a 32bit system.

There are many reasons to use pls_integer instead of number:

  • pls_integer uses less memory
  • pls_integer uses machine arithmetic, which is up to three times faster than library arithmetic, which is used by number.

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
declare
   l_result       number(9,0)          := 0;   -- violates also G-2130, G-2230
   co_upper_bound constant pls_integer := 1e8;
begin
   <<burning_cpu>>
   for i in 1..co_upper_bound
   loop
      if i > 0 then
         l_result := l_result + 1;
      end if;
   end loop burning_cpu;
   sys.dbms_output.put_line(l_result);
end;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
declare
   l_result       pls_integer          := 0;
   co_upper_bound constant pls_integer := 1e8;
begin
   <<burning_less_cpu>>
   for i in 1..co_upper_bound
   loop
      if i > 0 then
         l_result := l_result + 1;
      end if;
   end loop burning_less_cpu;
   sys.dbms_output.put_line(l_result);
end;
/