Forum

Forums
PL SQL
Interview Questions...
Understanding Loops...
 
Share:
Notifications
Clear all

Understanding Loops in PL/SQL with Practical Examples

 
Interview Questions & Answers
Last Post by SQL Admin 8 months ago
1 Posts
1 Users
0 Reactions
1,439 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Jul 24, 2025 2:34 am
(@sql-admin)
Reputable Member
Joined: 6 years ago

Understanding Loops in PL/SQL with Practical Examples

Loops are essential in PL/SQL when you want to execute a block of statements repeatedly. They allow you to automate repetitive tasks such as processing records, generating sequences, or performing calculations until a condition is met. PL/SQL supports three main types of loops: Simple Loop, WHILE Loop, and FOR Loop.

Simple Loop in PL/SQL

A simple loop continues indefinitely until explicitly exited using the EXIT or EXIT WHEN statement.

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

This loop will print numbers from 1 to 5 and then exit. Use simple loops when the exit condition is inside the loop body.

WHILE Loop in PL/SQL

A WHILE loop checks the condition before each iteration. The loop executes only if the condition is TRUE.

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

Use the WHILE loop when the number of iterations depends on a condition that can change within the loop.

FOR Loop in PL/SQL

A FOR loop is the cleanest way to iterate over a known range of values.

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

This is the most readable and concise loop when you know the start and end values in advance.

Nested Loops Example

PL/SQL allows nesting loops to handle multi-level operations like matrix processing or combinations:

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;

This will print every combination of i and j values from the nested loop.

When to Use Each Loop

  • Use Simple Loop when you need full control over loop execution.

  • Use WHILE Loop when the loop depends on a dynamic condition.

  • Use FOR Loop when the number of iterations is known or fixed.

Common Mistakes to Avoid

  • Forgetting the EXIT condition in simple loops causes infinite loops.

  • Not updating the counter variable in WHILE loops results in stuck loops.

  • Using loops for operations that can be done with SQL set-based queries can reduce performance.

Summary Table

Loop TypeEntry ConditionExit RequiredUse Case Example
Simple LoopAfter executionYesCustom stop condition
WHILE LoopBefore executionNoConditional iteration
FOR LoopAutomatic rangeNoFixed range of iterations

 


Topic Tags
plsql loops example for loop in plsql plsql while loop syntax plsql simple loop exampl plsql nested loops

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  plsql loops example (1) , for loop in plsql (1) , plsql while loop syntax (1) , plsql simple loop exampl (1) , plsql nested loops (1) ,
Share: