PL/SQL Loop With Example

Looping is the basic fundamental thing of any programming language. It plays an important role in the iteration of records dynamically. PL/SQL loop is a very interesting topic as you have more than one methods to perform it.

Generally, if your requirement is that you need same code to be executed for multiple times, looping comes into the picture.

In this topic we will see below things:

  • Simple PL/SQL Loop
  • For Loop
  • While Loop
  • Labelling Loop
  • GOTO and CONTINUE

Simple PL/SQL Loop

In this looping method, written PL/SQL codes reside between LOOP and END LOOP. It is the simplest loop structure in PL/SQL.

Note: There is no semi-colon after LOOP

This loop iterates until all the rows of the SQL statement are executed. In some conditions the loop iterates for infinite times which is of course not a good programming practice.

EXIT keyword can be used here to stop infinite loop execution.

Conditional exit from the loop is possible with the help of EXIT WHEN statement.

In this LOOP example, the LOOP would continue till the temp reaches 1000.

FOR Loop

FOR Loop iterates the looping statement for a specific number of times.

The syntax for the FOR Loop in Oracle/PLSQL is:

Parameters or Arguments

counter :  Variable to be used for counting
first_number : Initial value for counter
last_number : End value for counter
statements : Code statements

Let’s look at an example of how to use a FOR LOOP in Oracle.

This FOR LOOP example will loop 5 times. The counter called rec will start at 1 and end at 5.

 

Reverse FOR Loop

When REVERSE keyword is used, FOR loop iterates from final value to initial value. However, the syntax is same except for an addition of REVERSE keyword.

You can use the REVERSE modifier to run the FOR LOOP in reverse order.

This FOR LOOP example will loop 5 times. However, because REVERSE is specified, the counter called rec will start at 5 and end at 1.

CURSOR FOR Loop

Cursor FOR loop executes the statements for the number of times cursor query retrieves records. The CURSOR FOR LOOP will terminate when all of the records in the cursor are fetched.

In this example, we’ve created a cursor called cursor_1. The CURSOR FOR Loop will terminate after all records have been fetched from the cursor c1.

Parameters or Arguments

rec : Count of rec
cursor_1 : Name of the cursor to fetch records from
statements: Statements of code to execute through the CURSOR FOR LOOP.

WHILE Loop

WHILE LOOP is very useful when you are not sure how many times you will execute the loop body.

The syntax for the WHILE Loop in Oracle/PLSQL is:

Parameters or Arguments

condition : Condition for execution of loop. If the condition evaluates to TRUE, the loop body is executed. If the condition evaluates to FALSE, the loop is terminated.
statements : Code statements

Let’s look at a WHILE LOOP example in Oracle:

In this WHILE LOOP example, the loop would terminate once the temp value exceeded 2000

Labeling Loops

A loop can be labeled with the help of identifier enclosed by << >>.

Below is the example of labeling loops:

GOTO and CONTINUE

GOTO, CONTINUE and EXIT are three loop control statements used in PL/SQL.

GOTO

Note:

  • There should be at least one statement after the <<labelname>>
  • Label name should be unique within the scope of code

CONTINUE

CONTINUE statement if used inside the loop, will skip all the code after that till the end of the loop and the loop will be reiterated for next record.

If you liked the above post, please leave your comments below.

You can also Subscribe here to stay updated on latest posts of OracleMine.com.

Leave a Reply

Your email address will not be published. Required fields are marked *