PL/SQL Looping Statements Explained with Examples (FOR, WHILE, LOOP)

Introduction: Why Looping Statements Are Essential in PL/SQL

In real life, many actions are repeated until a goal is achieved.

For example:

We keep dialing a number until the call connects
We read pages until a chapter ends
We add items to a cart until shopping is complete

Programming works in the same way. Instead of writing the same statement again and again, PL/SQL uses looping statements to repeat a block of code automatically.

Looping statements help PL/SQL programs to:

Reduce code length
Improve readability
Handle repetitive tasks efficiently
Work with large data sets

Without loops, PL/SQL programs would be long, slow, and difficult to maintain.

What Is a Loop in PL/SQL?

A loop is a control structure that allows a set of statements to be executed repeatedly based on a condition or a fixed number of times.

In simple words:

A loop tells PL/SQL: “Repeat this task until a certain rule is satisfied.”

Every loop has three important parts:

Initialization – starting point
Condition / Termination – when to stop
Iteration – how repetition happens

Types of Looping Statements in PL/SQL

PL/SQL provides the following looping statements:

Basic LOOP
WHILE LOOP
FOR LOOP
Nested LOOP
EXIT and EXIT WHEN
CONTINUE statement

Each one is explained in very small detail below.

1. Basic LOOP Statement in PL/SQL

Concept Explanation

The basic LOOP is the simplest looping structure in PL/SQL.

It repeats statements indefinitely until an explicit EXIT condition is given.

Important point:

PL/SQL does not stop this loop automatically
You must write an EXIT condition manually

Syntax


LOOP
   statements;
   EXIT WHEN condition;
END LOOP;

Real-Life Example

Situation:

A security guard keeps checking the gate until the supervisor arrives.

Loop → checking gate
EXIT → supervisor arrives

PL/SQL Example


DECLARE
   counter NUMBER := 1;
BEGIN
   LOOP
      DBMS_OUTPUT.PUT_LINE('Counter value: ' || counter);
      counter := counter + 1;
      EXIT WHEN counter > 5;
   END LOOP;
END;

Detailed Clarification

LOOP starts execution
Statements execute repeatedly
EXIT WHEN checks stopping condition
END LOOP marks loop end

If EXIT is missing, the loop becomes infinite, which is dangerous.

2. WHILE LOOP in PL/SQL

Concept Explanation

The WHILE LOOP checks a condition before executing the loop body.

If the condition is false at the beginning, the loop will not execute even once.

This loop is useful when:

The number of iterations is not fixed
Execution depends on a condition

Syntax


WHILE condition LOOP
   statements;
END LOOP;

Real-Life Example

Situation:

While there is money in your wallet, you can continue shopping.

Condition → money > 0
Action → buy items

PL/SQL Example


DECLARE
   number NUMBER := 1;
BEGIN
   WHILE number <= 5 LOOP
      DBMS_OUTPUT.PUT_LINE('Number is: ' || number);
      number := number + 1;
   END LOOP;
END;

Key Characteristics

Condition checked first
Loop may execute zero times
Safe compared to basic LOOP

When to Use WHILE LOOP

Reading records until condition fails
Processing user input
Monitoring status flags

3. FOR LOOP in PL/SQL

Concept Explanation

The FOR LOOP is used when:

Number of repetitions is known in advance
Loop control variable is required automatically

PL/SQL handles:

Initialization
Condition checking
Increment / decrement

Internally, making it clean and error-free.

Syntax


FOR counter IN start_value .. end_value LOOP
   statements;
END LOOP;

Real-Life Example

Situation:

A teacher calls roll numbers from 1 to 30 for attendance.

Start → 1
End → 30
Action → call name

PL/SQL Example


BEGIN
   FOR i IN 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration number: ' || i);
   END LOOP;
END;

Important Clarifications

Counter variable is automatically declared
Counter cannot be modified inside loop
Loop runs fixed number of times

Reverse FOR LOOP

PL/SQL also supports reverse iteration.


FOR i IN REVERSE 1..5 LOOP
   DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

4. Nested Loops in PL/SQL

Concept Explanation

A nested loop means:

One loop inside another loop

Used when:

Repetition depends on another repetition
Working with tables, matrices, or grouped data

Real-Life Example

Situation:

In a classroom:

For each student
For each subject
Record marks

PL/SQL Example


BEGIN
   FOR i IN 1..3 LOOP
      FOR j IN 1..2 LOOP
         DBMS_OUTPUT.PUT_LINE('i = ' || i || ', j = ' || j);
      END LOOP;
   END LOOP;
END;

Important Notes

Inner loop completes fully for each outer loop cycle
Complexity increases quickly
Use carefully for performance

5. EXIT and EXIT WHEN Statement

Concept Explanation

The EXIT statement is used to terminate a loop immediately, even if iterations are remaining.

Syntax


EXIT;


EXIT WHEN condition;

Real-Life Example

Situation:

Searching for a file:

Stop searching once file is found

PL/SQL Example


DECLARE
   num NUMBER := 1;
BEGIN
   LOOP
      DBMS_OUTPUT.PUT_LINE(num);
      EXIT WHEN num = 3;
      num := num + 1;
   END LOOP;
END;

Why EXIT Is Important

Prevents infinite loops
Improves performance
Allows early termination

6. CONTINUE Statement in PL/SQL

Concept Explanation

The CONTINUE statement skips the current iteration and moves to the next iteration of the loop.

Important:

Loop does not terminate
Only current cycle is skipped

Syntax


CONTINUE;


CONTINUE WHEN condition;

Real-Life Example

Situation:

While checking exam papers:

Skip absent students
Continue checking others

PL/SQL Example


BEGIN
   FOR i IN 1..5 LOOP
      CONTINUE WHEN i = 3;
      DBMS_OUTPUT.PUT_LINE('Value: ' || i);
   END LOOP;
END;

Output Explanation

Value 3 is skipped
Loop continues normally

Common Mistakes Students Make

Forgetting EXIT in basic LOOP
Modifying FOR loop counter
Creating infinite loops
Misplacing END LOOP
Not updating loop variable in WHILE

Comparison of PL/SQL Looping Statements

Loop Type	Condition Check	Use Case
Basic LOOP	Inside loop	Unlimited repetition
WHILE LOOP	Before loop	Condition-based repetition
FOR LOOP	Automatic	Fixed repetition

Final Conclusion

Looping statements are the engine of repetition in PL/SQL.

They allow programs to:

Handle large data efficiently
Reduce manual coding
Implement real-world logic

By mastering:

LOOP
WHILE
FOR
EXIT
CONTINUE

You gain the ability to write professional PL/SQL programs used in:

Database processing
Reports
Payroll systems
Data validation