Oracle While Loop

Introduction to Oracle While Loop

Oracle While Loop can be defined as an entry controlled loop (A loop is defined as a sequence of instructions which gets executed repeatedly) which means the body of the loop will not be executed even once if the exit condition which is present at the very beginning of the loop is not satisfied since the exit condition is checking before the loop execution starts and one point to note that after every execution of the instructions present in the body of the loop the control goes back to the exit condition until the condition becomes false.

Syntax

Let us now look at the syntax of the Oracle While loop.

WHILE condition
LOOP
{Body of the Loop}
END LOOP;

Parameters to Oracle While Loop

Let us now look at the parameters

• Condition: This parameter refers to the exit condition at the beginning of the while loop. The statements will get executed only if this condition is true otherwise it will be not executed.
• LOOP: It refers to the start of the loop.
• Body of the loop: It refers to the statements which will be executed each time the condition statement is true
• END LOOP: It refers to the end of the loop.

Flowchart of Oracle While Loop

Let us now take a look at a flowchart below to understand the concept of WHILE LOOP better.

How does While Loop Works in Oracle?

• In this section, we discuss exactly how the WHILE LOOP works in Oracle. In the definition section, we discussed that while Loop is an exit controlled loop. It means that whenever the program control reaches the beginning of the WHILE loop, the first statement it encounters is an expression that is executed first.
• On execution of the expression, the expression returns a Boolean value that is true or false. In case the value returned is false, the control immediately skips the Loop and the While Loop is not executed. The second case that arises is that in case the Boolean value returned is true, in this case, the control goes inside the Loop and starts executing each statement present in the body of the Loop one by one.
• Once they have executed all the statements and reach the end of the loop the control goes back again to the beginning of the Loop and executes the exit condition to check if it is true or false. If the value is False then control exits the Loop and in case it is true then the control again goes inside the Loop and executes each statement one by one.
• This process continues until the exit condition returns False and the controlled exit the Loop. Due to this reason, it is also called an entry controlled Loop as the entry into the Loop is determined by the exit condition of the Loop.

Examples to Implement Oracle While Loop

Let us now look into examples mentioned:

Example #1

In the first example, we will use a simple WHILE LOOP to see the behavior of the Loop. The example aims to print even numbers from two to ten. It will include both the number two and number ten. So it can be done in many ways but for this article, we are going to use the WHILE LOOP. So let us look at the PL/SQL program for the same using WHILE LOOP.

Code:

SET SERVEROUTPUT ON
DECLARE
p_count NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('The even numbers between 2 and 10');
WHILE p_count < 10
LOOP
p_count := p_count + 2;
DBMS_OUTPUT.PUT_LINE('Current count is :' ||p_count);
END LOOP;
END;

Output:

Explanation: As we can see the first statement is used to set the server output to ON so that we can print the results in the output. If we look at the program we will see the exit condition is used to check whether the variable p_count is less than ten or not. The initial value of p_count is zero. So, in the first run it will be true and the body of the Loop will get executed. After every execution, the value of the p_count will increase by two digits. At the instance when the value becomes more than ten, the exit statement is false and the control goes out of the loop and that marks the end of the loop. As we can see in the output that the PL/SQL program returns the list of even numbers from 2 to 10.

Example #2

In the second example, we will tweak the previous example using the EXIT WHEN clause of the WHILE LOOP. The EXIT WHEN clause is used to terminate the Loop based on a specific condition. In this example, we will use the above example and will terminate the Loop when the value of the p_number variable is eight. So it will only print the even numbers from two to eight. Let us look at the PL/SQL program.

Code:

SET SERVEROUTPUT ON
DECLARE
p_count NUMBER := 0;
BEGIN
WHILE p_count < 10
LOOP
p_count := p_count + 2;
DBMS_OUTPUT.PUT_LINE('Current count is :' ||p_count);
EXIT WHEN p_count = 8;
END LOOP;
END;

Output:

Explanation: As you can see in the above program the EXIT WHEN statement is used and the condition given is when p_count is eight. So, the control should terminate the loop when the p_count value is eight even though the exit condition is true. As we can see in the output that due to EXIT WHEN condition the Loop gets terminated when the value is eight.

Conclusion

In this article, we discussed the definition of WHILE LOOP and its syntax. We also looked into the working of WHILE LOOP using a flow chart. We saw a couple of examples to get a better understanding of different cases and how we can use the WHILE LOOP.

Recommended Articles

This is a guide to Oracle While Loop. Here we discuss an introduction to Oracle While Loop, syntax, flowchart and examples for better understanding. You can also go through our other related articles to learn more –

1. Cursor in Oracle
2. Function in Oracle
3. Oracle Alter Table
4. Oracle Constraints
5. Oracle Window Functions
6. Guide to Oracle While Loop