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:

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.

loops in pl/sql

How many types are loops in PL/SQL

Three types are loops in PL/SQL:

  • Basic LOOP Statement
  • WHILE LOOP Statement
  • For LOOP Statement
loops in pl/sql
Loops in PL/SQL

We will discuss all of these by one.

Basic Loop Statement

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;

The WHILE LOOP

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.

The For Loop

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 statement

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; 
/ 

Exit When Statement

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 Statement

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 Statement

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 Statement

Loops in PL/SQL
  • 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; 
/

FAQs:

1. The variable in the loops is always declared. True or False.

Answer: True.

2. SQL does not support conditional or LOOP statements. True or False.

Answer: True. That’s why PL/SQL is invented.

3. What is the Discard File?

Answer: Discard Files are:
1. This file extension is .dsc
2. We must specify the discard files within the control file by using the discard file clause.
3. The discard file also stores reflected records based on when conditions within the control file. This condition must be satisfied with the table name clause.

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.

23 thoughts on “Loops in PL/SQL (Loops + Control Statements)

  1. It’s in reality a nice and helpful piece of information. I
    am glad that you shared this helpful information with us. Please stay us informed
    like this. Thanks for sharing.

Leave a Reply

Your email address will not be published. Required fields are marked *