Skip to content

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

Critical

Efficiency

Reason

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

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
declare
   l_employee_found boolean := false;
   cursor c_employees is
      select employee_id,last_name
        from employees;
begin
   <<check_employees>>
   for r_employee in c_employees
   loop
      l_employee_found := true;
   end loop check_employees;
   if l_employee_found then
      null; -- some processing;
   end if;
end;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
declare
   l_employee_found boolean := false;
   cursor c_employees is
      select employee_id,last_name
        from employees;
   r_employee       c_employees%rowtype;
begin
   open c_employees;
   fetch c_employees into r_employee;
   l_employee_found := c_employees%found;
   close c_employees;
   if l_employee_found then
      null; -- some processing;
   end if;
end;
/