G-2230
🆓Try to use SIMPLE_INTEGER datatype when appropriate.
Reason
simple_integer does no checks on numeric overflow, which results in better performance compared to the other numeric datatypes.
With Oracle Database 11g, the new data type simple_integer has been introduced. It is a sub-type of pls_integer and covers the same range. The basic difference is that simple_integer is always not null. When the value of the declared variable is never going to be null then you can declare it as simple_integer. Another major difference is that you will never face a numeric overflow using simple_integer as this data type wraps around without giving any error. simple_integer data type gives major performance boost over pls_integer when code is compiled in native mode, because arithmetic operations on simple_integer type are performed directly at the hardware level.
Example
Non-Compliant Example
declare
l_result number(9,0) := 0;
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;
/Issues
| Line | Column | Message |
|---|---|---|
| 2 | 19 | NUMBER could be replaced by SIMPLE_INTEGER. |
Compliant Solution - ★★★★★
declare
l_result simple_integer := 0;
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;
/References
- same as Trivadis G-2230