Home

Description

The rule "Collections should not be iterated in FOR loops" in PL/SQL code means that collections should not be used in a FOR loop. Instead, collections should be iterated using a cursor, which is a looping construct that allows for the iteration of a collection. This rule is important because it ensures that the code is efficient and that it does not cause any unnecessary performance issues. Additionally, using a cursor allows for more flexibility when iterating over a collection, as it allows for the use of different types of iterators.

Key Benefits

• Improved Performance: Using collections instead of FOR loops can improve the performance of your code.
• Ease of Use: Collections are easier to use than FOR loops, making them a great choice for beginners.
• More Flexible: Collections are more flexible than FOR loops, allowing you to easily modify and expand your code.
• Better Readability: Collections are more readable than FOR loops, making it easier to understand and debug your code.

Non-compliant Code Example

BEGIN
  FOR i IN 1 .. CUSTOMER_TABLE.COUNT --Non compliant code (Table data is iterated in the for loop)
  LOOP
		IF CUSTOMER_TABLE(i) IS NOT NULL THEN
			DBMS_OUTPUT.PUT( i || ' = (' || CUSTOMER_TABLE(i).Name || ', ' || CUSTOMER_TABLE(i).PhoneNumber || ')' );
		ELSE
			DBMS_OUTPUT.PUT( i || ' IS NULL' );
		END IF;
  END LOOP;
END;

Compliant Code Example

BEGIN

i := CUSTOMER_TABLE.FIRST;
  WHILE i IS NOT NULL LOOP --Compliant code (Table data is iterated in the while loop)
    IF CUSTOMER_TABLE(i) IS NOT NULL THEN
		DBMS_OUTPUT.PUT( i || ' = ('|| CUSTOMER_TABLE(i).Name || ', ' || CUSTOMER_TABLE(i).PhoneNumber || ')' );
    ELSE
		DBMS_OUTPUT.PUT( i || ' IS NULL' );
	END IF;
  END LOOP;
END