Unit-4 : Iterative Statements
Unit-4 : Iterative Statements
4.1 Iterative statements :
4.1.1 Loop..End Loop
4.1.2 For.. Loop
4.1.3 While Loop
4.1.4 EXIT Loop
4.1.5 Continue
Iterative Statements in PL/SQL.
Introduction.
Iterative statements in PL/SQL allow a set of statements to be executed repeatedly based on a condition.
These loops help in automating repetitive tasks, reducing code redundancy, and improving efficiency.
PL/SQL provides different types of loops:
LOOP…END LOOP (Basic loop)
FOR LOOP (Loop with a fixed number of iterations)
WHILE LOOP (Loop based on a condition)
EXIT Loop (To terminate a loop explicitly)
CONTINUE Statement (To skip the current iteration and continue to the next)
4.1.1 LOOP…END LOOP.
The
LOOP...END LOOPis an infinite loop unless explicitly terminated using anEXITstatement.It repeatedly executes a set of statements until an exit condition is met.
SYNTAX
LOOP -- Statements to execute EXIT WHEN condition; END LOOP;
Example: Basic Loop
DECLARE
counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
counter := counter + 1;
-- Exit condition
EXIT WHEN counter > 5;
END LOOP;
END;
/ Explanation:
The loop starts with
counter = 1.Prints the counter value and increments it.
The loop terminates when
counter > 5.
4.1.2 FOR LOOP.
The
FOR LOOPis used when the number of iterations is known in advance.It executes a block of statements for a fixed range of values.
SYNTAX
FOR counter_variable IN start_value..end_value LOOP -- Statements to execute END LOOP;
Example: Using FOR LOOP
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;
END;
/ Explanation:
The loop runs from
i = 1toi = 5.The iteration variable (
i) automatically increments by 1 in each iteration.No need for an explicit
EXITcondition.
Reverse FOR LOOP:
To execute the loop in reverse order, use the
REVERSEkeyword.EXAMPLE
BEGIN
FOR i IN REVERSE 5..1 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;
END;
/ 4.1.3 WHILE LOOP.
The
WHILE LOOPexecutes a block as long as a condition remains TRUE.The condition is checked before each iteration.
Syntax
WHILE condition LOOP -- Statements to execute END LOOP;
Example: Using WHILE LOOP
DECLARE
counter NUMBER := 1;
BEGIN
WHILE counter <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
counter := counter + 1;
END LOOP;
END;
/ Explanation:
The loop starts with
counter = 1Executes as long as
counter <= 5.The condition is checked before each iteration.
4.1.4 EXIT Statement in Loop.
The
EXITstatement is used to terminate a loop explicitly when a specific condition is met.Syntax:
LOOP -- Statements EXIT WHEN condition; END LOOP;
Example: Using EXIT in a Loop
DECLARE
counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
counter := counter + 1;
-- Exit the loop when counter reaches 5
EXIT WHEN counter > 5;
END LOOP;
END;
/ Explanation:
The
EXITstatement stops the loop whencounter > 5.Useful for breaking an infinite loop.
Using EXIT with an IF Condition
DECLARE
x NUMBER := 1;
BEGIN
LOOP
IF x > 5 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('Value of x: ' || x);
x := x + 1;
END LOOP;
END;
/
4.1.5 CONTINUE Statement in Loop.
The
CONTINUEstatement skips the remaining statements in the current iteration and moves to the next iteration.Syntax:
LOOP CONTINUE WHEN condition; -- Statements to execute END LOOP;
Example: Using CONTINUE to Skip an Iteration
BEGIN
FOR i IN 1..5 LOOP
-- Skip printing when i is 3
CONTINUE WHEN i = 3;
DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;
END; Explanation:
The loop runs from
i = 1toi = 5.When
i = 3, theCONTINUEstatement skips the remaining code in that iteration.The output does not print
"Iteration: 3".
| Loop Type | Condition Check | Iteration Count | Exit Mechanism | Use Case |
|---|---|---|---|---|
| LOOP...END LOOP | Inside the loop (with EXIT) | Can be infinite | Uses EXIT condition | When exit condition is dynamic |
| FOR LOOP | Implicitly defined | Fixed number | Stops automatically | When the number of iterations is known |
| WHILE LOOP | Before execution | Variable count | Stops when condition is FALSE | When a condition-based iteration is required |
| EXIT Loop | Inside the loop | Can be infinite | Uses explicit EXIT | To break out of a loop at any point |
| CONTINUE | Inside the loop | Any loop type | Skips to the next iteration | To skip specific iterations |
Major key Point.
LOOP…END LOOP is a basic loop that requires an explicit
EXITcondition.FOR LOOP is best when the number of iterations is known beforehand.
WHILE LOOP executes as long as the condition is TRUE.
EXIT statement terminates the loop based on a condition.
CONTINUE statement skips the current iteration and moves to the next.