G-4330

🆓

Warning

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

Rule Details

Severity Level: Major
Quality Area: Maintainability
Fixing times: 10 mins
Local-high
Database Support: OracleDB (v7.3.4 +)

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

Non-Compliant Example

declare
   cursor c_employees is
      select employee_id,last_name
        from employees;
   r_employee c_employees%rowtype;
begin
   open c_employees;

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

   close c_employees;
end;
/

Issues

Line	Column	Message
10	4	Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.

Compliant Solution - ★★★★★

declare
   cursor c_employees is
      select employee_id,last_name
        from employees;
begin
   <<read_employees>>
   for r_employee in c_employees
   loop
      sys.dbms_output.put_line(r_employee.last_name);
   end loop read_employees;
end;
/

References

same as Trivadis G-4330