G-7170
🆓Warning
Avoid using an IN OUT parameter as IN or OUT only.
Reason
Avoid using parameter mode in out unless you actually use the parameter both as input and output. If the code body only reads from the parameter, use in; if the code body only assigns to the parameter, use out. If at the beginning of a project you expect a parameter to be both input and output and therefore choose in out just in case, but later development shows the parameter actually is only in or out, you should change the parameter mode accordingly.
Example
Non-Compliant Example
declare
l_employee employees.employee_id%type;
l_salary_id employees.salary%type;
procedure fetch_salary_by_id(
io_employee_id in out employees.employee_id%type
,io_salary in out employees.salary%type
) is
begin
select salary
into io_salary
from employees
where employee_id = io_employee_id;
exception
when no_data_found or too_many_rows then
raise;
end fetch_salary_by_id;
begin
l_employee := 100;
fetch_salary_by_id(l_employee, l_salary_id);
sys.dbms_output.put_line('Employee ID: ' || l_employee || ', Salary: ' || l_salary_id);
end;
/Issues
| Line | Column | Message |
|---|---|---|
| 5 | 7 | io_employee_id is used as IN only. |
| 6 | 7 | io_salary is used as OUT only. |
Compliant Solution - ★★★★★
declare
l_employee employees.employee_id%type;
l_salary_id employees.salary%type;
procedure fetch_salary_by_id(
in_employee_id in employees.employee_id%type
,out_salary out employees.salary%type
) is
begin
select salary
into out_salary
from employees
where employee_id = in_employee_id;
exception
when no_data_found or too_many_rows then
raise;
end fetch_salary_by_id;
begin
l_employee := 100;
fetch_salary_by_id(l_employee, l_salary_id);
sys.dbms_output.put_line('Employee ID: ' || l_employee || ', Salary: ' || l_salary_id);
end;
/References
- same as Trivadis G-7170