FOR Loop in Oracle PL SQL
FOR Loop is the most common looping construct in all programming languages.Using for loop we can define the number of times the loop will cycle before exiting.
Syntax For declaring a FOR Loop is given below
FOR loop_counter IN [REVERSE]start_value ..final_value
LOOP
–Action to be performed
END LOOP;
An Example Follows
SQL>set serveroutput on;
SQL>FOR counter IN 1..10 LOOP
dbms_0utput.put_line (counter) ;
END LOOP;
END;
/;
Output
——-
1
2
3
4
5
6
7
8
9
10
The REVERSE keyword in FOR loop syntax causes PL/SQL engine to start with the final_value and decrement down to the start_value.
For Example
SQL>set serveroutput on;
SQL>FOR counter IN 1..10 LOOP
dbms_0utput.put_line (counter) ;
END LOOP;
END;
/;
Output
——-
10
9
8
7
6
5
4
3
2
1
The variable used in the FOR Loop is referred as control variable.There is no need to declare this variable like other variables in PL/SQL block.The PL/SQL runtime engine automatically declares the loop index(variable loop_counter above).Memory allocation for the running variables and de-allocation takes place automatically.So the scope of control variable is within the FOR Loop.
Working of FOR Loop
>>Implicit declaration of loop index is done.
>>initial value is assigned to loop index.
>>control variable is compared with the given highest value .
>>The loop body(statements between LOOP and END LOOP) is executed.
>>PL SQL Engine automatically increments the ioop index.
These process repeated until loop index reaches the higher value(or lowest value when executed with REVERSE keyword).
Cursor FOR Loop
The Cursor FOR Loop implicitly declares its loop index as a record of
type %ROWTYPE, opens a cursor, repeatedly fetches rows of the values
from the active set into fields in the record, then closes the cursor when
all rows have been processed or when the EXIT command is
encountered.
The syntax for the FOR Loop is:
FOR <variable name> IN <cursor_name> LOOP
<statements>;
END LOOP;
It is a machine defined loop exit i.e. when all the values in the FOR
construct are exhausted looping stops.
Read More about Cursor FOR Loop