java2db

PL/SQL FOR and WHILE loop controls



FOR and WHILE loop controls are called iterative control statements.These statements are mainly used to  execute a set of statements repeatedly based on specific conditions.

 

PL/SQL FOR and WHILE loop controls  FOR loop :
The number of iterations through a FOR loop is known before the loop enters with specified range of  integers.The range is represents by the range operator (..)  in between the integers.The range is evaluated  when the FOR loop is first entered and is never re-evaluated.

PL/SQL FOR and WHILE loop controls For each iteration of the loop , the bound will increment by 1 (PL/SQL wont allow to increment more than 1 ) .
PL/SQL FOR and WHILE loop controls The bounds of a loop range can be literals, variables or expressions , but must evaluate to numbers.
PL/SQL FOR and WHILE loop controls The sequence of statements will execute once if the lower bound equals to the  higher bound of the loop

Syntax:

FOR counter IN lower_bound .. upper_bound LOOP
Statements_to_execute
END LOOP;
/* reverse loop iteration*/
FOR counter IN REVERSE lower_bound .. upper_bound LOOP
Statements_to_execute
END LOOP;

Example :

For Loop Dynamic Ranges:
The range of the loop (lower or higher bound) determines at run time is called dynamic ranges.

FOR loop scope rules:
PL/SQL FOR and WHILE loop controls  The loop counter must define within the loop and the declaration is specific to the loop.Once the loop is exited , the loop counter is undefined.
PL/SQL FOR and WHILE loop controls  The local declaration will hide the global declaration.
PL/SQL FOR and WHILE loop controls  Must to use label and dot notation to reference the global variable.

 

PL/SQL FOR and WHILE loop controls WHILE loop :

The WHILE  loop executes set of statements with specific condition.The statements enclosed by the keywords LOOP and END LOOP.The number of iterations through WHILE loop is unknown until the loop completes.

Syntax:

WHILE <condition> LOOP
Statements to execute
END LOOP;

 

PL/SQL FOR and WHILE loop controls Infinite loop :

If  any loop executes continuously forever is called infinite loop.

Syntax:

LOOP
Statements to execute
END LOOP;
or
WHILE TRUE LOOP
Statements to execute
END LOOP;

 

PL/SQL FOR and WHILE loop controlsEXIT  in  FOR and WHILE loops :

The loop completes immediately and control passes to the next statements, when it reach Exit statement.It can be used in both FOR and WHILE loops.

Syntax:

LOOP
IF <condition> THEN
EXIT;
END IF;
END LOOP;

 

PL/SQL FOR and WHILE loop controls EXIT-WHEN :

The loop completes immediately and control passes to the next statements, if the Exit condition is true .

Syntax:

LOOP
EXIT WHEN <condition>;
END LOOP;

 

PL/SQL FOR and WHILE loop controlsLoop Labels:

The loop label is an undefined identifier enclosed by double angle brackets. To exit more than one outer loop, from inner loop without complete all iterations , use loop labels.The  EXIT will helps to terminate the current loop only.But EXIT with label will help to terminate the loops, according to the requirement. In the same way the CONTINUE with label also will work.

Syntax:

<<outer_loop>>
LOOP
————
————
<<inner_loop>>
LOOP
IF <condition>  THEN
EXIT inner_loop;
IF <condition> THEN
EXIT outer_loop;
END LOOP inner_loop;
END LOOP outer_loop;

 




Related Posts :
PL/SQL Arrays and nested tables ( Collections )
IF THEN ELSE and CASE in PL/SQL
PL/SQL Operators
PL/SQL Procedures
PL/SQL Triggers
PL/SQL Functions
PL/SQL Variables
How to use while and do while loop control flow statements in java
   [will not be published]



^ <