Oracle PL/SQL WHILE LOOP with Example

What is While Loop?

WHILE loop statement works similar to the Basic loop statement except the EXIT condition is at the very beginning of the loop.

It works like an entry-check loop in which execution block will not even be executed once if the condition is not satisfied, as the exit condition is checking before execution part. It does not require keyword 'EXIT' explicitly to exit from the loop since it is validating the condition implicitly each time of the loop.

WHILE <EXIT condition>
 LOOP
<execution block starts>
.
.
.
<execution_block_ends>
 END LOOP; 

Syntax Explanation:

• In the above syntax, keyword 'WHILE' marks beginning of the loop and 'END LOOP' marks the end of the loop.
• EXIT condition is evaluated each time before the execution part is starting executing.
• The execution block contains all the code that needs to be executed.
• The execution part can contain any execution statement.

Example 1: In this example, we are going to print number from 1 to 5 using WHILE loop statement. For that, we will execute the following code.

DECLARE
a NUMBER :=1;
BEGIN
dbms_output.put_line('Program started');
WHILE (a < 5) 
LOOP
dbms_output.put_line(a);
a:=a+1;
END LOOP;
dbms_output.put_line(‘Program completed' );  
END:
/

Code Explanation:

• Code line 2: Declaring the variable 'a' as 'NUMBER' data type and initializing it with value '1'.
• Code line 4: Printing the statement "Program started".
• Code line 5: Keyword 'WHILE' marks the beginning of the loop, and it also checks whether the value of 'a' is greater than 5
• Code line 7: Prints the value of 'a'.
• Code line 8: Increments the value of 'a' by +1.
• Code line 9: Keyword 'END LOOP' marks the end of execution block.
• The code from line 7 and line 8 will continue to execute till 'a' reaches the value 6, as the condition will return TRUE, and the control will EXIT from the loop.

• Code line 10: Printing the statement "Program completed"

Summary

Loop	FOR Loop
EXIT Criteria	Exit when the counter reaches the limit
Usage	Good to use when loop count to be executed is known.