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. PL/SQL contains declaration block, execution block, and exception block. Declare and exception blocks are optional.

In this article, we delve into the versatility of the PL/SQL FOR loop, a key construct for procedural programming in Oracle databases. We’ll 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.

PL/SQL FOR LOOP

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;

/

Examples of PL/SQL FOR Loop

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

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:

PL/SQL FOR LOOP

Explanation:

SET SERVEROUTPUT ON is used to display the output. Loop variable i.e. ‘counter ‘ is declared in declaration block.Loop variable can be used directly without declaring it.FOR loop is declared with start value as 1 and end value as 5. The END LOOP keyword is used to end the loop.At last,END keyword is used to terminate the execution.Number from 1 to 5 are printed along with the text information using for loop.

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: Print 123 for 3 Times Using Nested For Loop

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:

PLSQL NESTED FOR LOOP

Explanation:

Both the Outer loop and Inner loop are declared with start and end value.For each iteration of outer loop ,inner loop run for 3 times.After the end of inner loop , DBMS_OUTPUT.NEW_LINE is used to print the output on the new line.

FOR LOOP With REVERSE Keyword

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;

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

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:

FOR LOOP WITH REVERSE KEYWORD

Explanation:

Reverse keyword is used to iterate in reverse order.In the FOR loop ,REVERSE keyword is used to print the number from 5 to 1.Number from 5 to 1 are printed using reverse keyword in for loop.

Conclusion

FOR loop is used to perform repetitive tasks. It is used to execute the set of statements for a specific number of times. FOR loop contains lower bound and upper bound.We have studied for loop,nested for loop and reverse keyword in for loop. Its ability to streamline repetitive tasks and iterate over ranges or collections makes it an invaluable tool for developers working with Oracle databases.