Loop - (PL/SQL Control Statements)

In PL/SQL, a loop is used to repeatedly execute a block of code until a specified condition is met. There are three types of loops available in PL/SQL: FOR, WHILE, and LOOP.

Syntax

FOR Loop:

FOR counter_variable IN [REVERSE] lower_limit..upper_limit LOOP
    statement(s);
END LOOP;

Here, counter_variable is the loop counter variable, lower_limit is the lower value of the loop counter, upper_limit is the upper value of the loop counter, and statement(s) is the block of code to be executed.

WHILE Loop:

WHILE condition LOOP
    statement(s);
END LOOP;

Here, condition is a Boolean expression that evaluates to TRUE or FALSE, and statement(s) is the block of code to be executed.

LOOP Loop:

LOOP
    statement(s);
    EXIT [WHEN condition];
END LOOP;

Here, statement(s) is the block of code to be executed, and condition is a Boolean expression that is evaluated after each execution of the loop block. If condition evaluates to TRUE, the loop is exited.

Example

FOR Loop:

DECLARE
    sum INTEGER := 0;
BEGIN
    FOR i IN 1..5 LOOP
        sum := sum + i;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Sum of first 5 natural numbers: ' || sum);
END;

WHILE Loop:

DECLARE
    count INTEGER := 1;
BEGIN
    WHILE count <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE(count);
        count := count + 1;
    END LOOP;
END;

LOOP Loop:

DECLARE
    count INTEGER := 0;
BEGIN
    LOOP
        count := count + 1;
        EXIT WHEN count = 5;
        DBMS_OUTPUT.PUT_LINE(count);
    END LOOP;
END;

Output

FOR Loop:

Sum of first 5 natural numbers: 15

WHILE Loop:

1
2
3
4
5

LOOP Loop:

1
2
3
4

Explanation

In the above examples, we have demonstrated three types of loops available in PL/SQL. In the FOR loop example, we have calculated the sum of the first 5 natural numbers. In the WHILE loop example, we have printed the first 5 natural numbers. In the LOOP loop example, we have printed the first 4 natural numbers.

Use

Loops are used in PL/SQL to iteratively execute a block of code until a specified condition is met. They are commonly used in programming to perform repetitive tasks.

Important Points

• There are three types of loops available in PL/SQL: FOR, WHILE, and LOOP.
• The FOR loop iterates over a range of values.
• The WHILE loop executes the loop body as long as the condition is TRUE.
• The LOOP loop continues to execute the body until a specified condition is met.
• The EXIT statement can be used to exit a loop.

Summary

In summary, loops are used in PL/SQL to iteratively execute a block of code until a specified condition is met. There are three types of loops available in PL/SQL: FOR, WHILE, and LOOP. Each type has its own syntax and usage. Loops are commonly used in programming to perform repetitive tasks.