Loops in PL/SQL (Loops + Control Statements)

In this post, we will discuss the of Loops in PL/SQL. There may be many situations when you need to execute a block of code several times. Generally, the statements are executed sequentially, the first statement in a function is performed first, followed by the second, and so on.

If you missed the previous article, then you must read Conditional Control Structure in PL/SQL.

What you will learn from here:

Topics:

• Basic LOOP Statement
• WHILE LOOP Statement
• For LOOP Statement
• EXIT Statement
• EXIT WHEN Statement
• CONTINUE Statement
• CONTINUE WHEN Statement
• GOTO Statement
• FAQs

Like all Programming languages provide various control structures that allow for more complicated execution paths. Oracle’s PL/SQL also provides various control structures.

What is Loop?

Loops allow a certain part of the code in a program to get executed for the desired number of times.

How many types are loops in PL/SQL

Three types are loops in PL/SQL:

• Basic LOOP Statement
• WHILE LOOP Statement
• For LOOP Statement

We will discuss all of these by one.

In a simple loop, the keyword Loop should be placed before the first statement in the sequence, and the keyword end loop should be written at the end of the loop.

Syntax:

Loop
<Sequence of statement>
End loop;

For eaxmple:

DECLARE
i := i+2;
BEGIN
LOOP
i :=i+2;
EXIT WHEN i>10:
END LOOP;
dbms_output.put_line ("Value of i"|| i);
END;

It is not very like a simple loop. While loop starts with WHILE keyword with a Condition. The WHILE LOOP statement repeatedly executes a target statement as long as a given condition goes true.

Syntax:

WHILE <condition>
LOOP 
  < sequence_of_statements >
END LOOP; 

For Example:

DECLARE 
   a number(2) := 20; 
BEGIN 
   WHILE a < 30 
LOOP 
      dbms_output.put_line('value of a: ' || a); 
      a := a + 2; 
   END LOOP; 
END; 
/ 

Output:

value of a: 10 
value of a: 12 
value of a: 14 
value of a: 16 
value of a: 18 
value of a: 20
value of a: 22  
value of a: 24  
value of a: 26  
value of a: 28 
PL/SQL procedure successfully completed.

Like other programming languages, the FOR LOOP of PL/SQL is a repetition control structure that allows us to efficiently write a loop that needs to execute a particular number of times.

Syntax:

FOR variable IN REVERSE initial_value .. final_value 
LOOP 
   sequence_of_statements; 
END LOOP;

Example: Write a PL/SQL block of code for inverting a number 5639 to 9365.

DECLARE
given_number BARCHAR2(5) :=5639;
str length   number(5);
inverted_number varchar2(5);
BEGIN
str_lenth :=(given_number);
FOR cntr IN REVERSE 1...str_lenth
/* Variable used as counter need not be declared i.e. cntr declaration is not required*/
LOOP
inverted_number :=inverted_number ||sbstr (given_number, cntr, 1);
END LOOP;
dbms_output.put_line("The given number is"|| given_number);
dbms_output.put_line("The inverted_numberr is"|| inverted_number);
END;

Output:

The given number is 5639
The inverted_number is 9365

Loops Control Statements in Loops in PL/SQL

The statements that exit a loop is:

EXIT: The EXIT statement exits in the current iteration of a loop without any conditions and transfers control to the end of the current loop or an enclosing labeled loop. We can say that when the EXIT statement is executed inside a loop. The loop is instantly turn off, and the program control resumes at the next statement following the loop.

For example:

DECLARE 
   i number(2) := 10; 
BEGIN 
   -- while loop execution  
   WHILE i < 20 LOOP 
      dbms_output.put_line ('value of i: ' || a); 
      i := i + 1; 
      IF i > 15 THEN 
         -- terminate the loop using the exit statement 
         EXIT; 
      END IF; 
   END LOOP; 
END; 
/ 

2.EXIT WHEN: An EXIT WHEN statement exits the current iteration of a loop when the condition in its WHEN condition is satisfied and transfers the control to the end of either of two the current loop or an enclosing labeled loop.

Example:

DECLARE
i := i+2;
BEGIN
LOOP
i :=i+2;
EXIT WHEN i>10:
END LOOP;
dbms_output.put_line ("Value of i"|| i);
END;

The statements that exit the current iteration of a loop are:

CONTINUE: The CONTINUE statement exits when the current iteration of a loop flatly and transfers the control to the next iteration of the current loop or an enclosing labelled loop.

Example:

DECLARE 
   i number(2) := 10; 
BEGIN 
   WHILE a < 20 LOOP 
      dbms_output.put_line ('value of i: ' || a); 
      i := i + 1; 
      IF i = 15 THEN 
         i := a + 1; 
         CONTINUE; 
      END IF; 
   END LOOP; 
END; 
/ 

Output:

value of i: 10 
value of i: 11 
value of i: 12 
value of i: 13 
value of i: 14 
value of i: 16 
value of i: 17 
value of i: 18 
value of i: 19 

CONTINUE WHEN: The CONTINUE WHEN statement exits the loop when the current iteration of a loop’s WHEN condition is true and transfers the control to the next iteration to the current loop or an enclosing marked loop.

Example:

DECLARE
  x NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE ('Inside the loop:  x = ' || TO_CHAR(x));
    x := x + 1;
    CONTINUE WHEN x < 3;
    DBMS_OUTPUT.PUT_LINE
      ('Inside the loop, after CONTINUE:  x = ' || TO_CHAR(x));
    EXIT WHEN x = 5;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE (' After the loop:  x = ' || TO_CHAR(x));
END;
/

Output:

Inside the loop:  x = 0
Inside the loop:  x = 1
Inside the loop:  x = 2
Inside the loop, after CONTINUE:  x = 3
Inside the loop:  x = 3
Inside the loop, after CONTINUE:  x = 4
Inside the loop:  x = 4
Inside the loop, after CONTINUE:  x = 5
After the loop:  x = 5

A statement that changes the Flow of control in PL/SQL block:

• GOTO: The GOTO statement transfers control to a label unconditionally. The label must be unique in its scope and must precede an executable statement or a PL/SQL block.

Syntax

GOTO label;
..
..
<< label >>
statement;

Example:

DECLARE 
   a number(2) := 10; 
BEGIN 
   <<loopstart>> 
   -- while loop execution  
   WHILE a < 20 LOOP
   dbms_output.put_line ('value of a: ' || a); 
      a := a + 1; 
      IF a = 11 THEN 
         a := a + 1; 
         GOTO loopstart; 
      END IF; 
   END LOOP; 
END; 
/

In this article, we are discussed about the Loops in PL/SQL, their types, use, and also different types of Control statements in PL/SQL. Loops in PL/SQL are the most important part of Oracle PL/SQL Tutorials. In the next article, we will discuss the Arrays in PL/SQL.