Skip to content

G-4330: Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.

Minor

Maintainability

Reason

It is easier for the reader to see that the complete data set is processed. Using SQL to define the data to be processed is easier to maintain and typically faster than using conditional processing within the loop.

Since an exit statement is similar to a goto statement, it should be avoided whenever possible.

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
declare
   cursor employee_cur is
      select employee_id, last_name
        from employee;
   r_employee employee_cur%rowtype;
begin
   open employee_cur;

   <<output_employee_last_names>>
   loop
      fetch employee_cur into r_employee;
      exit read_employees when employee_cur%notfound;
      sys.dbms_output.put_line(r_employee.last_name);
   end loop output_employee_last_names;

   close employee_cur;
end;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
declare
   cursor employee_cur is
      select employee_id, last_name
        from employee;
begin
   <<output_employee_last_names>>
   for r_employee in employee_cur
   loop
      sys.dbms_output.put_line(r_employee.last_name);
   end loop output_employee_last_names;
end;
/