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
declare
   l_employee_found boolean := false;
   cursor c_employee is
      select employee_id, last_name
        from employee;
begin
   <<check_employees>>
   for r_employee in c_employee
   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 c_employee is
      select employee_id, last_name
        from employee;
   r_employee c_employee%rowtype;
begin
   open c_employee;
   fetch c_employee into r_employee;
   l_employee_found := c_employee%found;
   close c_employee;
end;
/