Oracle PL/SQL FOR Loop

 In Oracle PL/SQL, the FOR loop statement is a control structure used to iterate a specific number of times or to process a set of records returned by a cursor or a SQL query.

There are two primary types of FOR loops in Oracle PL/SQL:

Numeric FOR Loop: This type of FOR loop iterates a predefined number of times, controlled by a numeric index variable.

FOR loop_index IN [REVERSE] lower_bound .. upper_bound LOOP

-- Statements to be executed in each iteration

END LOOP;

loop_index: An implicitly declared integer variable that is local to the FOR loop. Its value cannot be changed within the loop.

lower_bound: The starting value for the loop_index.

upper_bound: The ending value for the loop_index.

REVERSE: An optional keyword that causes the loop to iterate in descending order, from upper_bound down to lower_bound.

Cursor FOR Loop: This type of FOR loop iterates through each row returned by a SQL query or a predefined cursor.

FOR record_name IN (SELECT column1, column2 FROM table_name WHERE condition) LOOP

-- Statements to be executed for each fetched record

-- You can access columns using record_name.column1, record_name.column2

END LOOP;

Or, using an explicitly declared cursor:

DECLARE

CURSOR my_cursor IS SELECT column1, column2 FROM table_name WHERE condition;

BEGIN

FOR record_name IN my_cursor LOOP

-- Statements to be executed for each fetched record

-- You can access columns using record_name.column1, record_name.column2

END LOOP;

END;

/

record_name: An implicitly declared record variable that holds the data for the current row fetched by the cursor or query.

The SELECT statement or my_cursor defines the data set to be iterated over.

Key characteristics of FOR loops in Oracle PL/SQL:

The loop variable (index or record) is implicitly declared and local to the loop.

The loop variable's value cannot be modified within the loop.

The loop terminates when the index reaches its specified value or when all records from a cursor are processed.

EXIT or EXIT WHEN statements can be used to exit a loop prematurely based on a condition.

CONTINUE or CONTINUE WHEN statements can be used to skip the rest of the current iteration and proceed to the next.