Skip to content

G-4385: Never use a cursor for loop to check whether a cursor returns data.

Major

Efficiency

Reason

You might process more data than required, which leads to bad performance.

Also, check out rule G-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
declare
   l_employee_found boolean := false;
   cursor employee_cur is
      select employee_id, last_name
        from employee;
   r_employee employee_cur%rowtype;     
begin
   <<check_employees>>
   for r_employee in employee_cur
   loop
      l_employee_found := true;
   end loop check_employees;
end;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
declare
   l_employee_found boolean := false;
   cursor employee_cur is
      select employee_id, last_name
        from employee;
   r_employee employee_cur%rowtype;
begin
   open employee_cur;
   fetch employee_cur into r_employee;
   l_employee_found := employee_cur%found;
   close employee_cur;
end;
/