EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle While Loop
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Oracle While Loop

By Priya PedamkarPriya Pedamkar

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,650 ratings)
  • 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.

Oracle While Loop flowcha

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:

Oracle While Loop1

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:

current count

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
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more