Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Oracle PL/SQL Continue – Querychat

This article discusses the Oracle PL/SQL CONTINUE statement which is used to conditionally or unconditionally exit the current loop iteration.

While programming, we often use loops such as while loop, for loop, etc. to iterate through a group or list of values. Sometimes we might have to skip the iteration for some of the values based on some conditions. The Oracle CONTINUE statement is what we need in such situations. The PL/SQL CONTINUE statement allows us to exit the current loop iteration and continue to the next iteration of that loop.

The syntax for PL/SQL CONTINUE statement is as follows:

CONTINUE [ label ] [ WHEN boolean_expression ] ;

Continue Statement Syntax

 In the above syntax, the label and conditional WHEN statement is optional. If only the CONTINUE statement is used, then the current iteration of the loop exits unconditionally. The label indicates which loop to exit from in case of nested loops. If it is not defined, then the iteration of the current loop is exited. The WHEN clause is used to specify a condition to skip the iteration. 

Oracle Continue Loop

The Oracle CONTINUE statement can be used anywhere inside of a loop but cannot be used outside of a loop. 

In the following part of this article, we will see examples of how we can use the CONTINUE statement in Oracle to continue the loop.

The following PL/SQL query prints number from 1 to 5 using a FOR LOOP.

SET SERVEROUTPUT ON;

 

BEGIN

  FOR i IN 1..5

  LOOP

    DBMS_OUTPUT.PUT_LINE(i);

  END LOOP;

END;

Sample Loop Query

The above query gives following output.

PL/SQL procedure successfully completed.

1

2

3

4

5

The output of Sample Query

EXAMPLE 1: PL/SQL Continue with When

In this example, we will see how we can skip an iteration in the loop using a conditional statement. We will use the for loop mentioned earlier. 

The following query prints numbers from 1 to 5 except 3. 

SET SERVEROUTPUT ON;

 

BEGIN

  FOR i IN 1..5

  LOOP

    CONTINUE WHEN i = 3;

    DBMS_OUTPUT.PUT_LINE(i);

  END LOOP;

END;

Query with Continue When Statement

In the above query, the conditional when the statement is checked for every iteration. If the result is true then the iteration is skipped and the next iteration is started. The above query returns the following output.

PL/SQL procedure successfully completed.

1

2

4

5

The output of Query with Continue When Statement

EXAMPLE 3: PL/SQL Continue with If..Then

We can also achieve the same output using IF THEN statement instead of a conditional statement as follows.

SET SERVEROUTPUT ON;

 

BEGIN

  FOR i IN 1..5

  LOOP

    IF i = 3 THEN

      CONTINUE;

    END IF;

    DBMS_OUTPUT.PUT_LINE(i);

  END LOOP;

END;

Query with Continue with IF THEN

EXAMPLE 3: PL/SQL Continue with Label

With the use of the label, we have more control over the continue statement on which of the loop to exit from in case of nested loops. 

The following query prints out numbers from 1 to 3 two thrice using 2 FOR LOOP labeled outer_loop and inner_loop.

SET SERVEROUTPUT ON;

 

BEGIN

  <<outer_loop>>

  FOR i IN 1..3

  LOOP

    DBMS_OUTPUT.PUT_LINE('Outer Loop:'||i);

    <<inner_loop>>

    FOR j IN 1..3 

    LOOP

      DBMS_OUTPUT.PUT_LINE('  Inner Loop:'||j);

    END LOOP;

  END LOOP;

END;

Query with multiple loops

The above query returns the following output. 

PL/SQL procedure successfully completed.

Outer Loop:1

  Inner Loop:1

  Inner Loop:2

  Inner Loop:3

Outer Loop:2

  Inner Loop:1

  Inner Loop:2

  Inner Loop:3

Outer Loop:3

  Inner Loop:1

  Inner Loop:2

  Inner Loop:3

The output of query with multiple loops

Now, if we just use CONTINUE statement in the inner loop, only that iteration of the inner loop is skipped and the inner loop continues as shown by the following example.

SET SERVEROUTPUT ON;

 

BEGIN

  <<outer_loop>>

  FOR i IN 1..3

  LOOP

    DBMS_OUTPUT.PUT_LINE('Outer Loop:'||i);

    <<inner_loop>>

    FOR j IN 1..3 

    LOOP

      CONTINUE WHEN j = 2;

      DBMS_OUTPUT.PUT_LINE('  Inner Loop:'||j);

    END LOOP;

  END LOOP;

END;

Continue Statement without label

PL/SQL procedure successfully completed.

Outer Loop:1

  Inner Loop:1

  Inner Loop:3

Outer Loop:2

  Inner Loop:1

  Inner Loop:3

Outer Loop:3

  Inner Loop:1

  Inner Loop:3

The output of Continue Statement without label

But, if we mention the label of the outer loop after the CONTINUE statement, then not only that iteration of the inner loop but also the iteration of the outer loop is skipped and the loop continues with the next iteration of the outer loop. The following example illustrates this. 

SET SERVEROUTPUT ON;

 

BEGIN

  <<outer_loop>>

  FOR i IN 1..3

  LOOP

    DBMS_OUTPUT.PUT_LINE('Outer Loop:'||i);

    <<inner_loop>>

    FOR j IN 1..3 

    LOOP

      CONTINUE outer_loop WHEN j = 2;

      DBMS_OUTPUT.PUT_LINE('  Inner Loop:'||j);

    END LOOP;

  END LOOP;

END;

Continue Statement with label

PL/SQL procedure successfully completed.

Outer Loop:1

  Inner Loop:1

Outer Loop:2

  Inner Loop:1

Outer Loop:3

  Inner Loop:1

The output of Continue Statement with label

Still need some help? Connect with a live SQLExpert here for some 1 on 1 help. Your first session is always free!

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc