G-4387
Error
Never use a FOR LOOP for a query that should return not more than one row.
Reason
A for loop can hide a too_many_rows exception. The more complex a query is, the higher is the risk that more than one row will be processed. This affects performance and can lead to a wrong result.
A for loop can also hide a no_data_found exception and the reader cannot determine whether this is intentional or not.
Examples
Non-Compliant Example
create or replace package body employee_api is
function emp_name(in_empno in integer) return varchar2 is -- dbLinter ignore(G-7460) non-deterministic function
l_ename emp.ename%type;
begin
<<fetch_name>>
for r in (
select ename
from emp
where empno = in_empno
)
loop
l_ename := r.ename;
end loop fetch_name;
return l_ename;
end emp_name;
end employee_api;
/Issues
| Line | Column | Message |
|---|---|---|
| 6 | 7 | Never use a FOR LOOP for a query that should return not more than one row. |
Compliant Solution - ★★★★☆
create or replace package body employee_api is
function emp_name(in_empno in integer) return varchar2 is -- dbLinter ignore(G-7460) non-deterministic function
cursor c_emp is
select ename
from emp
where empno = in_empno;
r_emp c_emp%rowtype;
begin
open c_emp;
fetch c_emp into r_emp;
close c_emp;
return r_emp.ename;
end emp_name;
end employee_api;
/Handles no_data_found as intended, but hides a too_many_rows exception.
Compliant Solution - ★★★★★
create or replace package body employee_api is
function emp_name(in_empno in integer) return varchar2 is -- dbLinter ignore(G-7460) non-deterministic function
l_ename emp.ename%type;
begin
select ename
into l_ename
from emp
where empno = in_empno;
return l_ename;
exception
when no_data_found then
return null;
when too_many_rows then
raise;
end emp_name;
end employee_api;
/Handles no_data_found and too_many_rows exception explicitly.
References
- same as Trivadis G-4387