While Loop in Oracle PL/SQL with Example

What is PL/SQL While Loop?

While Loop in PL/SQL works similar to the basic loop statement, except the EXIT condition is at the very beginning of the loop. It works like an entry-checking loop where the execution block will only execute if the condition is satisfied, as the exit condition is checked before execution.

It does not explicitly require the ‘EXIT’ keyword to exit from the loop since it validates the condition implicitly each time.

PL/SQL While Loop Syntax

WHILE <EXIT condition>
 LOOP
<execution block starts>
.
.
.
<execution_block_ends>
 END LOOP;
  • 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 of Oracle PL/SQL While Loop

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:

PL/SQL While Loop
PL/SQL While Loop Example
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 less than or equal to 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 WHILE Loop
EXIT Criteria Exit when the check condition returns false
Usage Good to use when the loop count is unknown, and exit is based on some other condition.