Oracle / PLSQL: Loop Statement

This Oracle tutorial explains how to use the LOOP statement in Oracle with syntax and examples.

Description

In Oracle, the LOOP statement is used when you are not sure how many times you want the loop body to execute and you want the loop body to execute at least once.

Syntax

The syntax for the LOOP statement in Oracle/PLSQL is:

LOOP
   {...statements...}
END LOOP;

Parameters or Arguments

statements

The statements of code to execute each pass through the loop.

Note

• You would use a LOOP statement when you are unsure of how many times you want the loop body to execute.
• You can terminate a LOOP statement with either an EXIT statement or when it encounters an EXIT WHEN statement that evaluates to TRUE.

Example

Let's look at a LOOP example in Oracle:

LOOP
   monthly_value := daily_value * 31;
   EXIT WHEN monthly_value > 4000;
END LOOP;

In this LOOP example, the LOOP would terminate when the monthly_value exceeded 4000.