Monthly Archives: August 2015

The PL/SQL Continue statement

Introduced in Oracle Database 11.1 The PL/SQL CONTINUE statement allows you to skip the current  loop iteration. It can be used conditionally and unconditionally.

Unconditional Example

BEGIN

   FOR i IN 1 .. 10
   LOOP
 
      IF i = 2
      THEN
         CONTINUE; 
      END IF;
 
      DBMS_OUTPUT.PUT(i || ', ');
 
    END LOOP;

    DBMS_OUTPUT.NEW_LINE;

END;

In the example above, there is no output when the loop executes iteration 2, Running this code shows the following output.

1, 3, 4, 5, 6, 7, 8, 9, 10,

 Conditional Example

BEGIN

   FOR i IN 1 .. 10
   LOOP
 
      CONTINUE WHEN i = 2; 
 
      DBMS_OUTPUT.PUT(i || ', ');
 
   END LOOP;
 
   DBMS_OUTPUT.NEW_LINE;

END;

Again in this example there is no output for iteration 2. Running this code shows the following output.

1, 3, 4, 5, 6, 7, 8, 9, 10,

The conditional use removes the need for the IF statement used in the unconditional example and makes the code more concise without losing readability.

Summary

In this article I have shown with short code examples how to use the PL/SQL Continue statement.

This article has not discussed whether the use of a CONTINUE statement is a bad programming practise, there are already enough arguments discussions about that.

Source: The Continue Statement within the Database documentation