G-7180
🆓Try to keep the complexity of functions, procedures and triggers simple.
Reason
The Cyclomatic Complexity is a metric to assess the complexity of code. The metric basically determines the number of paths through the code. The more paths, the more test cases are required for full branch coverage. The Cyclomatic Complexity is categorised as follows:
| Cyclomatic Complexity | Complexity |
|---|---|
| < 11 | low |
| < 51 | medium |
| other values | high |
PL/SQL units with low complexity are typically easier to understand, test and maintain.
Examples
Non-Compliant Example
create or replace package job_util as
function max_salary(in_job_id in varchar2) return integer deterministic;
end job_util;
/
create or replace package body job_util as
function max_salary(in_job_id in varchar2) return integer deterministic is
l_result integer;
begin
case in_job_id
when 'AC_ACCOUNT' then l_result := 9000;
when 'AC_MGR' then l_result := 16000;
when 'AD_ASST' then l_result := 6000;
when 'AD_PRES' then l_result := 40000;
when 'AD_VP' then l_result := 30000;
when 'FI_ACCOUNT' then l_result := 9000;
when 'FI_MGR' then l_result := 16000;
when 'HR_REP' then l_result := 9000;
when 'IT_PROG' then l_result := 10000;
when 'MK_MAN' then l_result := 15000;
when 'MK_REP' then l_result := 9000;
when 'PR_REP' then l_result := 10500;
when 'PU_CLERK' then l_result := 5500;
when 'PU_MAN' then l_result := 15000;
when 'SA_MAN' then l_result := 20080;
when 'SA_REP' then l_result := 12008;
when 'SH_CLERK' then l_result := 5500;
when 'ST_CLERK' then l_result := 5000;
when 'ST_MAN' then l_result := 8500;
else l_result := 0;
end case;
return l_result;
end max_salary;
end job_util;
/Issues
| Line | Column | Message |
|---|---|---|
| 8 | 4 | job_util.max_salary has a complexity of 20. |
This example has a complexity of 20. Why? Each when clause in the case statement makes the code more complex. The else branch does not add to the complexity.
In this case, the function is easy to read. The case statement (switch statement in other languages) is one of the criticisms of this metric. There is no perfect metric. But the technical solution is simple.
Compliant Solution - ★★★★☆
create or replace package job_util as
function max_salary(in_job_id in varchar2) return integer deterministic;
end job_util;
/
create or replace package body job_util as
function max_salary(in_job_id in varchar2) return integer deterministic is
begin
return case in_job_id
when 'AC_ACCOUNT' then 9000
when 'AC_MGR' then 16000
when 'AD_ASST' then 6000
when 'AD_PRES' then 40000
when 'AD_VP' then 30000
when 'FI_ACCOUNT' then 9000
when 'FI_MGR' then 16000
when 'HR_REP' then 9000
when 'IT_PROG' then 10000
when 'MK_MAN' then 15000
when 'MK_REP' then 9000
when 'PR_REP' then 10500
when 'PU_CLERK' then 5500
when 'PU_MAN' then 15000
when 'SA_MAN' then 20080
when 'SA_REP' then 12008
when 'SH_CLERK' then 5500
when 'ST_CLERK' then 5000
when 'ST_MAN' then 8500
else 0
end;
end max_salary;
end job_util;
/This solution has a Cyclomatic Complexity of 1. Why? Because we use a case expression in the return statement. There is no additional path through the PL/SQL function.
Compliant Solution - ★★★★★
create or replace package job_util as
function max_salary(in_job_id in varchar2) return integer result_cache;
end job_util;
/
create or replace package body job_util as
function max_salary(in_job_id in varchar2) return integer result_cache is
l_result integer;
begin
select max_salary into l_result
from jobs
where job_id = in_job_id;
return l_result;
exception
when no_data_found then
return 0;
when too_many_rows then
raise; -- data inconsistency, we cannot determine max salary
end max_salary;
end job_util;
/The Cyclomatic Complexity is 3. Why? Because each exception handler adds to the complexity.
This solution is superior to the previous one, because it queries the table content without compromising the performance, thanks to the result_cache clause. There aren't many values, so it doesn't take up much memory. However, when the underlying data is changed, which should not happen often, you need to call the sys.dbms_result_cache.invalidate('HR', 'job_util') procedure to ensure that the latest changes are considered.
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| MaxCyclomaticComplexity | Maximum Cyclomatic Complexity of a function, procedure and trigger. | 10 |