PL/SQL For Loop

Last Updated : 17 Oct, 2024

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:

PLSQL-NESTED-FOR-LOOP — PLSQL NESTED FOR LOOP

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:

FOR-LOOP-WITH-REVERSE-KEYWORD — FOR LOOP WITH REVERSE KEYWORD

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.

Solidity For Loop

pritamvinodfulari

Improve

Article Tags :

PL/SQL For Loop

FOR LOOP in PL/SQL

Syntax

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

PL/SQL NESTED FOR LOOP

Syntax

Example: Using Nested FOR Loops to Print a Pattern

Using the REVERSE Keyword in a PL/SQL FOR Loop

Syntax

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

Important Points About PL/SQL For Loop

Similar Reads