PL/SQL LOOPS

PL/SQL Loops are iterative constructs that enable repeated execution of a block of code based on specified conditions or ranges. They include FOR LOOP, WHILE LOOP, and LOOP-END LOOP, facilitating efficient data processing and task automation within Oracle database programs. These loops enhance flexibility by allowing developers to iterate over collections, execute statements multiple times, and handle complex logic seamlessly.

 

LOOP-END LOOP

The LOOP-END LOOP loop is an indefinite loop that continues until an EXIT or EXIT WHEN statement is encountered.

LOOP

   -- Code block executed indefinitely until an EXIT or EXIT WHEN statement is encountered

   EXIT WHEN condition;

END LOOP;

 

Example:

DECLARE

   total NUMBER := 0;

   i NUMBER := 1;

BEGIN

   LOOP

      total := total + i;

      i := i + 1;

      EXIT WHEN i > 10;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE('Total: ' || total);

END;

This PL/SQL code snippet demonstrates the use of a LOOP statement to calculate the sum of numbers from 1 to 10:

1. DECLARE: This keyword starts the declaration section where variables (total and i in this case) are initialized.
• total NUMBER := 0;: Initializes the variable total to 0, which will store the cumulative sum of numbers.
• i NUMBER := 1;: Initializes the variable i to 1, which will be used as a counter in the loop.
2. BEGIN: Marks the beginning of the executable section of the PL/SQL block, where actual logic and statements are written.
3. LOOP: This keyword starts a loop that will execute repeatedly until a specific condition is met.
• total := total + i;: Adds the current value of i to total, incrementally calculating the sum.
• i := i + 1;: Increments the value of i by 1 in each iteration, progressing through each number from 1 to 10.
• EXIT WHEN i > 10;: Checks if i has reached or exceeded 10. If true, the loop is exited, terminating further iterations.
4. END LOOP;: Marks the end of the loop structure.
5. DBMS_OUTPUT.PUT_LINE('Total: ' || total);: Outputs the final calculated sum (total) to the console using the DBMS_OUTPUT.PUT_LINE procedure.
6. END;: Marks the end of the PL/SQL block.

 

FOR LOOP

The FOR LOOP iterates over a range of values.

FOR loop_counter IN lower_bound..upper_bound LOOP

   -- Code block executed for each iteration of the loop

END LOOP;

Example:

DECLARE

   total NUMBER := 0;

BEGIN

   FOR i IN 1..10 LOOP

      total := total + i;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE('Total: ' || total);

END;

This PL/SQL code snippet demonstrates the use of a FOR LOOP to calculate the sum of numbers from 1 to 10:

1. DECLARE: This keyword starts the declaration section where variables (total in this case) are initialized.

   • total NUMBER := 0;: Initializes the variable total to 0, which will store the cumulative sum of numbers.

2. BEGIN: Marks the beginning of the executable section of the PL/SQL block, where actual logic and statements are written.

3. FOR i IN 1..10 LOOP: This line initiates a FOR LOOP that iterates over a range of values from 1 to 10 (1..10).

   • i: Represents the loop index variable that takes on each value within the specified range in each iteration of the loop.

   • total := total + i;: Adds the current value of i to total in each iteration, calculating the sum of numbers from 1 to 10.

4. END LOOP;: Marks the end of the FOR LOOP structure.

5. DBMS_OUTPUT.PUT_LINE('Total: ' || total);: Outputs the final calculated sum (total) to the console using the DBMS_OUTPUT.PUT_LINE procedure.

6. END;: Marks the end of the PL/SQL block.

 

WHILE LOOP

The FOR LOOP iterates over a range of values. The WHILE LOOP executes a block of code as long as a specified condition is true.

WHILE condition LOOP

   -- Code block executed as long as condition is true

END LOOP;

Example:

DECLARE

   total NUMBER := 0;

   i NUMBER := 1;

BEGIN

   WHILE i <= 10 LOOP

      total := total + i;

      i := i + 1;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE('Total: ' || total);

END;

This PL/SQL code snippet demonstrates the use of a WHILE LOOP to calculate the sum of numbers from 1 to 10:

1. DECLARE: This keyword starts the declaration section where variables (total and i in this case) are initialized.

   • total NUMBER := 0;: Initializes the variable total to 0, which will store the cumulative sum of numbers.
   • i NUMBER := 1;: Initializes the variable i to 1, which serves as a counter for the loop.

2. BEGIN: Marks the beginning of the executable section of the PL/SQL block, where actual logic and statements are written.

3. WHILE i <= 10 LOOP: This line initiates a WHILE LOOP that continues to iterate as long as the condition i <= 10 is true.

   • i: Represents the loop control variable that increments in each iteration.
   • total := total + i;: Adds the current value of i to total in each iteration, calculating the sum of numbers from 1 to 10.
   • i := i + 1;: Increments the value of i by 1 in each iteration to progress through each number from 1 to 10.

4. END LOOP;: Marks the end of the WHILE LOOP structure.

5. DBMS_OUTPUT.PUT_LINE('Total: ' || total);: Outputs the final calculated sum (total) to the console using the DBMS_OUTPUT.PUT_LINE procedure.

6. END;: Marks the end of the PL/SQL block.