loop

WHILE Loop in Oracle PLSQL

November 23, 2012 by techhoneyadmin

A WHILE Loop in Oracle PLSQL is used when we are not sure as to how many times the loop’s body should be executed.

Unlike LOOP statement, a WHILE LOOP may not get executed even once as the condition to execute a WHILE LOOP is evaluated before execution of the loop.

A WHILE LOOP gets terminated whenever the condition to execute the loop evaluates to false.

The Syntax for the WHILE LOOP in Oracle PLSQL is:
WHILE condition
LOOP
{
Statements to be executed;
}
END LOOP;

Example of a WHILE LOOP in Oracle PLSQL is:

1WHILE sum <= 35
2LOOP
3{
4  sum := sum + 1;
5}
6END LOOP;

The above loop will execute the “sum := sum+1” statement and as soon as the value of the “sum” variable crosses 35 the loop will terminate.
It may happen that the variable “sum” is already assigned a value higher than 35, in this case the WHILE LOOP will not get executed even once.

CURSOR FOR Loop in Oracle PLSQL

November 23, 2012 by techhoneyadmin

A CURSOR FOR Loop in Oracle PLSQL is used whenever we want to retrieve and process every record within a cursor.

The CURSOR FOR loop automatically gets terminated as soon as all the records in the cursor are fetched.

The Syntax for the CURSOR FOR LOOP in Oracle PLSQL is:

FOR cursor_records IN cursor_name
LOOP
{
Statements to be executed;
}
END LOOP;

Example of a CURSOR FOR Loop in Oracle PLSQL is:

1CREATE OR REPLACE function TotalSalary
2   ( emp_id_in IN NUMBER)
3   RETURN VARCHAR2
4IS
5   total_sal NUMBER(6);
6 
7   CURSOR cur_salary IS
8     SELECT salary
9     FROM employee
10     WHERE employee_id = emp_id_in;
11BEGIN
12   total_sal := 0;
13   FOR employee_record IN cur_salary
14   LOOP
15      total_sal := total_sal + employee_record.salary;
16   END LOOP;
17   RETURN total_sal;
18END;

In the above example the CURSOR FOR loop will terminate automatically when all the records have been fetched from the CURSOR ‘cur_salary’.

Loop Statement in Oracle PLSQL

November 23, 2012 by techhoneyadmin

A Loop in Oracle PLSQL is used to execute a portion of code i.e. the body of loop until a specific (exit) condition is not met.

In other words, we can say that we use the LOOP statement in Oracle PLSQL when we are not sure as to how many times the loop’s body should execute and we want the loop’s body to get executed at least once.
A LOOP gets terminated when it encounters an EXIT or EXIT WHEN statement which evaluates to true.

The Syntax for the LOOP Statement in Oracle PLSQL is:
LOOP
{
Statements to be executed;
}
END LOOP;

Example of a LOOP Statement in Oracle PLSQL is:

1LOOP
2{
3  sum := sum + 1;
4  EXIT WHEN sum = 35;
5}
6END LOOP;

The above loop will execute the “sum := sum+1; statement 35 times. The loop will start counting from 1 and as soon as the value of the “sum” variable crosses above 35 the loop will terminate.

FOR Loop in Oracle PLSQL

November 23, 2012 by techhoneyadmin

A FOR Loop in Oracle PLSQL is used to execute a portion of code i.e. the body of loop, a fixed number of times.

The Syntax for the FOR LOOP in Oracle PLSQL is:
FOR loop_counter IN [REVERSE] low_number .. high_number
LOOP
{
Statements to be executed;
}
END LOOP;

Example of a FOR Loop in Oracle PLSQL is:

1FOR cnt IN 1..35
2LOOP
3{
4  sum := sum + 1;
5}
6END LOOP;

The above loop will execute the “sum := sum + 1; statement 35 times. The loop will start counting from 1 and ends at 35.

Example of a FOR Loop IN REVERSE in Oracle PLSQL is:

1FOR cnt IN REVERSE 1..30
2LOOP
3{
4  sum := sum + 1;
5}
6END LOOP;

The above loop will execute the “sum := sum+1; statement 30 times. The loop will start counting from 30 and ends at 1 looping backwards.