Learning Oracle PL/SQL

2.7. Executing in Circles: Loop Statements

A very common requirement in programming is to execute the same functionality repetitively—in a loop. Programmers call this iteration, and it is a mainstay of virtually all procedural languages.

Why would you want to use a loop? You might want to display all the book titles reserved for a given individual or separate a string of comma-delimited words. PL/SQL offers three kinds of loops to help you with this kind of processing:

FOR loop (numeric and cursor)

This loop executes its body of code for a specific, limited number of iterations.

Simple or infinite loop

This loop executes its body of code until it encounters an EXIT statement.

WHILE loop

This loop executes its body of code until the WHILE condition evaluates to FALSE.

Oracle offers three different types of loops so that you can write the most straightforward code to handle any particular situation. Most situations that require a loop could be written with any of the three loop constructs. If you do not pick the construct that is best suited for that particular requirement, however, you might write more (and more complex) code than is necessary. The resulting program would also be harder to understand and maintain.

Let's take a look at each of these different kinds of loops.

2.7.1. FOR Loop

Use the FOR loop when you know in advance how many times you want the loop to execute (its number of iterations). This doesn't mean you have to know the exact, literal number, ...