PL/SQL For Loop

PL/SQL stands for Procedural Language/ Structured Query Language. It has block structure programming features. With PL/SQL, you can fetch data from the table, add data to the table, make decisions, perform repetitive tasks, and handle errors.PL/SQL supports SQL queries. To fetch records, process data, or execute complex calculations, the FOR loop helps to efficiently iterate over a range of values or collections

Here, we look into the versatility of the PL/SQL FOR loop, a key construct for procedural programming in Oracle databases, explore its syntax, provide examples of its application, demonstrate the use of the REVERSE keyword for reverse iteration, and discuss the effectiveness of nested FOR loops.

FOR LOOP in PL/SQL

Along with SQL queries PL/SQL supports looping. FOR loop is a type of control statement. It is used to perform repetitive tasks. It is used to execute the set of statements for a specific number of times. To execute for loop, start and end values are provided. During each iteration counter is incremented by 1.

Syntax

DECLARE
        --declare loop variable and provide its datatype
loop_varaible datatype;
BEGIN
        --for loop with start and end value
FOR loop_variable in start_value .. end_value LOOP
set of statements 
END LOOP;
END;
/

The loop_variable automatically increments by 1 in each iteration, and the loop continues until it reaches the end_value. There's no need to declare the loop variable separately unless needed elsewhere in the program

Example: Print Number From 1 to 5 Using FOR Loop in PL/SQL

Here’s a simple example to demonstrate the PL/SQL FOR loop:

Query:

SET SERVEROUTPUT ON;
DECLARE
counter NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('PL/SQL FOR LOOP EXECUTION');
FOR counter IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('COUNTER VALUE: '|| counter);
END LOOP;
END;
/

Output:

Explanation:

• SET SERVEROUTPUT ON is used to enable output in Oracle SQL*Plus or other tools.
• The loop variable counter is automatically initialized and used within the loop to print numbers from 1 to 5.
• The FOR loop iterates over the range from 1 to 5 and displays the output using DBMS_OUTPUT.PUT_LINE.

PL/SQL NESTED FOR LOOP

PL/SQL supports nested for loop. The nested for loop contains an outer loop and one or more inner loop. For each increment of the loop variable , of the outer loop, the inner loops executes the set of statements within it for a specific number of times.This process repeats until loop variable of outer loop reaches its end value.Nested for loops are used for executing complex operations, designing patterns, and many more operations.

Syntax

BEGIN
          --outer loop 
FOR loop_variable1 IN start_value1 ..end_value1 LOOP
          --inner loop
FOR  loop_variable2 IN start_value2 ..end_value2 LOOP
          --set of statements 
END LOOP;
          --inner loop end
END LOOP;
          --outer loop end
END;
/

Example: Using Nested FOR Loops to Print a Pattern

In this example, we will print the numbers 1, 2, and 3 in a pattern using nested loops.

Query:

SET SERVEROUTPUT ON;
BEGIN
    DBMS_OUTPUT.PUT_LINE('PL/SQL NESTED FOR LOOP EXECUTION');
    FOR counter IN 1..3 LOOP
        FOR counter1 IN 1..3 LOOP
            DBMS_OUTPUT.PUT( counter1);
        END LOOP;
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;
END;
/

Output:

Explanation:

• The outer loop runs three times, and for each iteration of the outer loop, the inner loop runs three times, printing the values from 1 to 3.
• DBMS_OUTPUT.NEW_LINE is used to move to a new line after the inner loop finishes.

Using the REVERSE Keyword in a PL/SQL FOR Loop

Reverse keyword is used in FOR loop to iterate from end value to start value.REVERSE keyword is mentioned before the start value.

Syntax

BEGIN
FOR loop_variable IN REVERSE start_value .. end_value  LOOP
    set_of_statements
END LOOP;
END;
/

Example: Print Number From 5 to 1 Using the REVERSE Keyword

Here’s how you can print numbers in reverse order using a FOR loop:

Query:

SET SERVEROUTPUT ON;
DECLARE
counter NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('FOR LOOP WITH REVERSE KEYWORD');
FOR counter IN REVERSE 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('REVERSE VALUE: '|| counter);
END LOOP;
END;
/

Output:

Explanation:

• The REVERSE keyword is used to reverse the iteration, starting from 5 and decrementing to 1.
• The loop prints the numbers in descending order.

Important Points About PL/SQL For Loop

• The loop variable is automatically declared and incremented within the FOR loop, so there's no need for explicit initialization or incrementation.
• The loop iterates from a specified start_value to end_value. The loop variable increases by 1 after each iteration.
• The loop variable is scoped to the loop itself. It cannot be accessed outside the loop unless explicitly declared in the declaration section.
• The REVERSE keyword can be used to iterate in reverse order, starting from the higher value and decrementing to the lower value.