Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL WHILE Loop

PL/SQL WHILE Loop

Summary: in this tutorial, you will learn about PL/SQL WHILE loop statement to execute a sequence of statements as long as a specified condition is TRUE.

The WHILE loop syntax

Here is the syntax for the WHILE loop statement:

WHILE condition LOOP statements; END LOOP;

The condition in the WHILE is a Boolean expression that evaluates to TRUE, FALSE or NULL.

The WHILE loop statement continues to execute the statements between the LOOP and END LOOP as long as the condition in the WHILE clause evaluates to TRUE.

PL/SQL evaluates the condition in the WHILE clause before each loop iteration. If the condition is TRUE, then the loop body executes. In case it is FALSE or NULL, the loop terminates.

If the condition is FALSE before entering the loop, the WHILE loop does not execute at all. This behavior is different from the LOOP statement whose loop body always executes once.

To terminate the loop prematurely, you use an EXIT or EXIT WHEN statement.

PL/SQL WHILE loop examples

Let’s take some examples of using the WHILE loop statement to see how it works.

A) Simple WHILE loop example

The following example illustrates how to use the WHILE loop statement:

DECLARE n_counter NUMBER := 1; BEGIN WHILE n_counter <= 5 LOOP DBMS_OUTPUT.PUT_LINE( 'Counter : ' || n_counter ); n_counter := n_counter + 1 END LOOP; END;

Here is the output:

Counter : 1 Counter : 2 Counter : 3 Counter : 4 Counter : 5

In this example:

  • First, the counter was initialized to zero.
  • Second, the condition in the WHILE clause was evaluated before each loop iteration.
  • Third, inside the loop body, the counter was increased by one in each loop iteration. After five iterations, the condition was FALSE that caused the loop terminated.

B) WHILE loop example terminated by EXIT WHEN statement

The following example is the same as the one above except that it has an additional EXITWHEN statement.

DECLARE n_counter NUMBER := 1; BEGIN WHILE n_counter <= 5 LOOP DBMS_OUTPUT.PUT_LINE( 'Counter : ' || n_counter ); n_counter := n_counter + 1; EXIT WHEN n_counter = 3; END LOOP; END;

The following is the output:

Counter : 1 Counter : 2

The condition in the EXIT WHEN clause evaluated to true when the counter is three. Therefore, the loop body only executed two times before it terminated.

In this tutorial, you have learned how to use the PL/SQL WHILE loop statement to execute a sequence of statements as long as a specified condition is TRUE.

  • Was this tutorial helpful?
  • YesNo
Previous PL/SQL FOR LOOP
Next PL/SQL CONTINUE

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.