Home

Description

The EXIT statement should not be used in loops in PL/SQL code. This is because the EXIT statement immediately exits the loop, which can lead to unexpected results. Instead, the loop should be ended by using the EXIT WHEN condition, which will exit the loop when the condition is met. This allows for more control over the loop and ensures that the loop will end when expected.

Key Benefits

• Prevent Endless Loops: EXIT should not be used in loops as it can cause an infinite loop, which can be difficult to debug.
• Easier Debugging: By avoiding the use of EXIT in loops, it is easier to debug the code and identify errors.
• Better Performance: EXIT can slow down performance, so avoiding its use in loops can help improve performance.

Non-compliant Code Example

DECLARE errorLog_ EXCEPTION; 
BEGIN
	FOR i IN 1 .. CUSTOMER_TABLE.COUNT  --Non compliant code (EXIT is used within loop)
		LOOP
			IF CUSTOMER_TABLE(i) IS NOT NULL THEN
				DBMS_OUTPUT.PUT( i || ' = (' || CUSTOMER_TABLE(i).Name || ', ' || CUSTOMER_TABLE(i).PhoneNumber || ')' );
			ELSIF CUSTOMER_TABLE(i).Id > 25 THEN 
				EXIT;
			END IF;
		END LOOP;
EXCEPTION
  WHEN ERRORS THEN
	RETURN;
END CUSTOMER_TABLE_ITERATION;

Compliant Code Example

DECLARE errorLog_ EXCEPTION; 
i PLS_INTEGER;
BEGIN
	 i := 1;
     WHILE i <= CUSTOMER_TABLE.COUNT AND CUSTOMER_TABLE(i).Id <= 25 LOOP --Compliant code
			IF CUSTOMER_TABLE(i) IS NOT NULL THEN
				DBMS_OUTPUT.PUT( i || ' = (' || CUSTOMER_TABLE(i).Name || ', ' || CUSTOMER_TABLE(i).PhoneNumber || ')' );
			END IF;
		END LOOP;
EXCEPTION
  WHEN ERRORS THEN
	RETURN;
END CUSTOMER_TABLE_ITERATION;