Explain PL/SQL loops in detail.

Q.4 Explain PL/SQL loops in detail.

Answer :- 

• PL/SQL loops are control structures that allow the repetition of a block of statements multiple times based on a specified condition.

• They help manage repetitive tasks efficiently and are an integral part of procedural programming in PL/SQL.

• Oracle PL/SQL provides different types of loops to cater to various programming needs.

Types of Loops in PL/SQL

1. Simple Loop  :- 

• A simple loop is the most basic type of loop in PL/SQL. It executes a block of statements repeatedly until an explicit `EXIT` statement is encountered.

Syntax:

LOOP
— Statements to execute
EXIT; — Condition to terminate the loop
END LOOP;

Key Features:

• Requires an explicit `EXIT` statement.

• Runs indefinitely if no `EXIT` condition is provided.

Example:

DECLARE
counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(‘Counter: ‘ || counter);
counter := counter + 1;

— Exit condition
IF counter > 5 THEN
EXIT;
END IF;
END LOOP;
END;

2. WHILE Loop :- 

• A ‘WHILE’ loop executes as long as a specified condition evaluates to `TRUE`. The condition is checked before entering the loop.

Syntax :- 

WHILE condition LOOP
— Statements to execute
END LOOP;

Key Features:

• The loop may not execute at all if the condition is initially `FALSE`.

• The condition is evaluated before each iteration.

Example :- 

DECLARE
counter NUMBER := 1;
BEGIN
WHILE counter <= 5 LOOP
DBMS_OUTPUT.PUT_LINE(‘Counter: ‘ || counter);
counter := counter + 1;
END LOOP;
END;

3. FOR Loop :-

• A ‘FOR’ loop iterates a specific number of times based on a defined range. It is particularly useful when the number of iterations is known beforehand.

Syntax:- 

FOR counter_variable IN start_value..end_value LOOP
— Statements to execute
END LOOP;

Key Features:

• The loop variable is implicitly declared.

• The range can be either ascending (‘start_value..end_value’) or descending (‘start_value DOWNTO end_value’).

Example:

BEGIN
FOR counter IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(‘Counter: ‘ || counter);
END LOOP;
END;

4. Nested Loops 

• Loops can be nested inside other loops to handle complex logic involving multiple levels of iteration.

Example:

BEGIN
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE(‘Outer Loop: ‘ || i);
FOR j IN 1..2 LOOP
DBMS_OUTPUT.PUT_LINE(‘ Inner Loop: ‘ || j);
END LOOP;
END LOOP;
END;

Exiting a Loop

• ‘EXIT’ Statement: Used to terminate the loop explicitly.

• ‘EXIT WHEN’ Condition: Combines the `EXIT` statement with a condition.

• EXIT WHEN counter > 5;