Oracle Learning - 11

Oracle/PLSQL: FOR Loop

The syntax for the FOR Loop is:
FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
{.statements.}
END LOOP;
You would use a FOR Loop when you want to execute the loop body a fixed number of times.

Let's take a look at an example.
FOR Lcntr IN 1..20
LOOP
LCalc := Lcntr * 31;
END LOOP;
This example will loop 20 times. The counter will start at 1 and end at 20.

The FOR Loop can also loop in reverse. For example:
FOR Lcntr IN REVERSE 1..15
LOOP
LCalc := Lcntr * 31;
END LOOP;
This example will loop 15 times. The counter will start at 15 and end at 1. (loops backwards)

Oracle/PLSQL: CURSOR FOR Loop

The syntax for the CURSOR FOR Loop is:
FOR record_index in cursor_name
LOOP
{.statements.}
END LOOP;
You would use a CURSOR FOR Loop when you want to fetch and process every record in a cursor. The CURSOR FOR Loop will terminate when all of the records in the cursor have been fetched.
Here is an example of a function that uses a CURSOR FOR Loop:
CREATE OR REPLACE Function TotalIncome
( name_in IN varchar2 )
RETURN varchar2
IS
total_val number(6);

cursor c1 is
select monthly_income
from employees
where name = name_in;

BEGIN
total_val := 0;
FOR employee_rec in c1
LOOP
total_val := total_val + employee_rec.monthly_income;
END LOOP;

RETURN total_val;
END;
In this example, we've created a cursor called c1. The CURSOR FOR Loop will terminate after all records have been fetched from the cursor c1.
Oracle/PLSQL: While Loop

The syntax for the WHILE Loop is:
WHILE condition
LOOP
{.statements.}
END LOOP;
You would use a WHILE Loop when you are not sure how many times you will execute the loop body. Since the WHILE condition is evaluated before entering the loop, it is possible that the loop body may not execute even once.
Let's take a look at an example:
WHILE monthly_value <= 4000
LOOP
monthly_value := daily_value * 31;
END LOOP;
In this example, the WHILE Loop would terminate once the monthly_value exceeded 4000.

Oracle/PLSQL: Repeat Until Loop

Oracle doesn't have a Repeat Until loop, but you can emulate one. The syntax for emulating a REPEAT UNTIL Loop is:
LOOP
{.statements.}
EXIT WHEN boolean_condition;
END LOOP;
You would use an emulated REPEAT UNTIL Loop when you do not know how many times you want the loop body to execute. The REPEAT UNTIL Loop would terminate when a certain condition was met.
Let's take a look at an example:
LOOP
monthly_value := daily_value * 31;
EXIT WHEN monthly_value > 4000;
END LOOP;
In this example, the LOOP would repeat until the monthly_value exceeded 4000.
Oracle/PLSQL: Exit Statement

The syntax for the EXIT statement is:
EXIT [WHEN boolean_condition];
The EXIT statement is most commonly used to terminate LOOP statements.
Let's take a look at an example:
LOOP
monthly_value := daily_value * 31;
EXIT WHEN monthly_value > 4000;
END LOOP;
In this example, the LOOP would terminate when the monthly_value exceeded 4000.