Home

Description

The rule "LOOP ... END LOOP; constructs should be avoided" in PL/SQL code states that loops should not be used in code. Instead, other methods such as cursors should be used to iterate through data. Loops can be inefficient and can lead to performance issues. Additionally, they can be difficult to debug and maintain. Therefore, it is best to avoid using loops in PL/SQL code.

Key Benefits

• Eliminates potential errors: LOOP...END LOOP; constructs can lead to potential errors due to the difficulty in understanding the structure of the loop.
• Improves code readability: By avoiding LOOP...END LOOP; constructs, code readability is improved as the structure of the loop is more easily understood.
• Makes debugging easier: Without LOOP...END LOOP; constructs, debugging is made easier as the structure of the loop is more easily understood.

Non-compliant Code Example

DECLARE 
   id customers.id%type; 
   name customer.name%type; 
   address customers.address%type; 
   CURSOR customers_c is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN customers_c; 
   <<INNER_LOOP>> 
   LOOP                     --Non compliant code (Constructs of LOOP and END LOOP should be avoided)
   FETCH customers_c into id, name, address; 
      EXIT WHEN customers_c%notfound; 
      dbms_output.put_line(id || ' ' || name || ' ' || address); 
   END LOOP INNER_LOOP; 
   CLOSE customers_c; 
END;

Compliant Code Example

DECLARE 
   id customers.id%type; 
   name customer.name%type; 
   address customers.address%type; 
   CURSOR customers_c is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN customers_c;         --Compliant code
   FETCH customers_c into id, name, address; 
      EXIT WHEN customers_c%notfound; 
      dbms_output.put_line(id || ' ' || name || ' ' || address); 
   CLOSE customers_c; 
END;