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.

A key component of loop control in PL/SQL is the CONTINUE statement, which enhances code readability and efficiency by skipping specific iterations within loops based on specified conditions. In this article, we’ll explore the syntax, applications, and advantages of the CONTINUE statement in PL/SQL.

CONTINUE Statement in PL/SQL

In Oracle 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: CONTINUE Statement in a Simple Loop

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

In this example, the CONTINUE statement skips processing for i = 3, effectively moving to the next iteration and continuing with the loop until i exceeds 5.

Using the CONTINUE Statement in Different Loops

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

The CONTINUE statement offers numerous benefits for efficient and readable PL/SQL code:

• Selective Iteration Skipping: Provides a way to skip some specific iterations.
• Simplified Logic Structure: Useful to skip the necessary processing in the loop.
• Enhanced Readability: The nesting levels can be reduced as we may want to skip the rest of the loops.
• Improves Loop Control: We can selectively skip certain iterations based on specific conditions.

Conclusion

In conclusion, the 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.