PL/SQL CONTINUE Statement

PL/SQL is a block-structured language that enables developers to combine the power of SQL with procedural statements. All the statements of a block are passed to the Oracle engine all at once which increases processing speed and decreases the traffic.

CONTINUE Statement

In PL/SQL (Procedural Language/Structured Query Language), the CONTINUE statement is used within loops to skip the remaining statements within the loop for the current iteration and move on to the next iteration. The CONTINUE statement is commonly used in combination with conditional statements to control the flow of execution in loops.

Syntax:

CONTINUE;

Example:

DECLARE
   i NUMBER := 1;
BEGIN
   LOOP
      IF i = 3 THEN
         -- Skip the rest of the loop for i = 3
         i := i + 1;
         CONTINUE;
      END IF;

      -- Process other statements inside the loop
      DBMS_OUTPUT.PUT_LINE('Current Value of i: ' || i);

      i := i + 1;

      EXIT WHEN i > 5; -- Exit the loop when i exceeds 5
   END LOOP;
END;
/

When the above code is executed in SQL prompt, it produces the following output.

Output:

Current Value of i: 1
Current Value of i: 2
Current Value of i: 4
Current Value of i: 5

CONTINUE WHEN Statement

In PL/SQL, there isn’t a direct “CONTINUE WHEN” statement, but you can achieve similar functionality using conditional logic with IF statements. Here are multiple examples demonstrating how you can use conditional logic to control the flow within loops in PL/SQL.

Example 1: Using IF-CONTINUE to Skip Iterations

DECLARE
   i NUMBER := 1;
BEGIN
   FOR i IN 1..5 LOOP
      IF i = 4 THEN
         -- Skip the rest of the loop for i = 4
         CONTINUE;
      END IF;

      DBMS_OUTPUT.PUT_LINE('Current Value of i: ' || i);
   END LOOP;
END;
/

When the above code is executed in SQL prompt, it produces following output.

Output:

Current Value of i: 1
Current Value of i: 2
Current Value of i: 3
Current Value of i: 5

Example 2: Using IF-CONTINUE in a WHILE Loop

DECLARE
   i NUMBER := 1;
BEGIN
   WHILE i <= 5 LOOP
      IF i = 3 THEN
         -- Skip the rest of the loop for i = 3
         i := i + 1;
         CONTINUE;
      END IF;

      DBMS_OUTPUT.PUT_LINE('Current Value of i: ' || i);
      i := i + 1;
   END LOOP;
END;
/

When the above code is executed in SQL prompt, it produces the following output.

Output:

Current Value of i: 1
Current Value of i: 2
Current Value of i: 4
Current Value of i: 5

Example 3: Using IF-CONTINUE in a Nested Loop

DECLARE
   i NUMBER := 1;
   j NUMBER := 1;
BEGIN
   FOR i IN 1..3 LOOP
      FOR j IN 1..3 LOOP
         IF j = 2 THEN
            -- Skip the rest of the inner loop for j = 2
            CONTINUE;
         END IF;

         DBMS_OUTPUT.PUT_LINE('i: ' || i || ', j: ' || j);
      END LOOP;
   END LOOP;
END;
/

When the above code is executed in SQL prompt, it produces the following output.

Output:

i: 1, j: 1
i: 1, j: 3
i: 2, j: 1
i: 2, j: 3
i: 3, j: 1
i: 3, j: 3

In each example, the CONTINUE-like behavior is achieved using an IF statement to skip the remaining code for a specific condition. The loop then continues with the next iteration. These examples illustrate how conditional logic can be used to control the flow within loops in PL/SQL.

Advantages of PL/SQL CONTINUE Statement

• Provides a way to skip some specific iterations.
• Useful to skip the necessary processing in the loop.
• The nesting levels can be reduced as we may want to skip the rest of the loops.
• We can selectively skip certain iterations based on specific conditions.

Conclusion

CONTINUE statement terminates the current iteration of a loop within a PL/SQL code block, and moves to the next iteration of the loop. This statement can be embedded within a FOR LOOP , or WHILE statement, or a PL/SQL procedure, function.It provides a way to efficiently manage loop iterations by bypassing unnecessary code execution based on certain conditions.