Loops in Oracle

The ability to repeat or skip sections of a block can be achieved with the usage of LOOP or GOTO statements

 There are three forms of the LOOP statement

–      LOOP

–      WHILE-LOOP

–      FOR-LOOP

LOOP Statement

LOOP repeats a sequence of statements

Statements to be repeated are placed between keyword LOOP and END LOOP

With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop.

LOOP

statements;

END LOOP;

EXIT Statement:

 Used to complete a loop if further processing in a loop is undesirable or impossible

There are two forms of the EXIT statement

–      EXIT

–      EXIT-WHEN

Forces a loop to complete unconditionally

Must be placed inside a loop

LOOP

statements;

IF <condition> THEN

EXIT;   -- exit loop immediately

END IF;

END LOOP;

-- control resumes here 

Example of Loop –

/*To show 1 to 10 on screen*/

Declare

    x number;

Begin

    x :=  1;

     Loop

             dbms_output.put_line(x);

              x := x + 1;

              exit when x > 10;

      End Loop;

 End;

create table five

(no number);

/*Inserting mutliples of five in table five*/

Declare

    x number;

Begin

        x := 5;

         Loop

               Insert into five

                values(x);

                x := x + 5;

                exit when x > 50;

          End Loop;

End;

FOR LOOP

  

Advantages --

  1) No need of declaring loop variable

  2) No need of giving condition

 3) No need of updation statement (increment or decrement )

 4)Code becomes small and compact

 Disadvantage --

   Updation can be done by only one.

                                

   Syntax –

FOR <counter> IN [REVERSE]

lower_bound .. higher_bound LOOP

statements;

END LOOP

Example 1 of for loop   

/*To show 1 to 10 on screen*/

begin

    for x in 1..10

    Loop

              dbms_output.put_line(x);

   End Loop;

end;

 Example 2

   /*Reverse for loop  10,9,8 … 1*/

Begin

           for  i in REVERSE 1 ..10

            Loop

                    dbms_output.put_line(i);

             End Loop;

end;

Example 3 –

 Calculating compound interest for a principal of Rs.100 @10% for each year.

  Values will be shown of the CI after each year.

create table CI_100

(year number(2),

total number(4));

----------------------------------

Declare

     p number := 100;

     tot number;

/*Calculation of compound interest.

   Rs.100 is principal.

   Rate of interest is 10%.

    Period is 5 years.

 */

Begin

 for y in 1..5

    Loop

    /* Tot variable is getting 10% more than p */

    tot := p + p * 0.10;

    Insert into CI_100

    values(y,tot);

    /*Since the next interest is based on the current interest

      so the tot will be considered as p for the next year*/

    p := tot;

    End Loop;

end;

WHILE-LOOP Statement:

 Associates a condition with a sequence of statements enclosed within LOOP-END LOOP

 Condition evaluated before each iteration

 If condition evaluates to TRUE, sequence of statements is executed and control resumes at the top of the loop

 If condition evaluates to FALSE or NULL, loop is bypassed and control passes to next statement

 Number of iterations depends on the condition and is unknown until the loop completes.

WHILE <condition>

LOOP

statements;

END LOOP;

Example 1 of while loop to show 1 to 15

declare

  x number;

Begin

   x := 1;

   while x <=15

    Loop

            dbms_output.put_line(x);

            x := x + 1;

    End Loop;

end;

Example 2  Forces a loop to complete unconditionally

declare

 z number;

/*Using break after z reaches to 8*/

Begin

   z := 1;

   while z <=15

    Loop

            dbms_output.put_line(z);

            z := z + 1;

             exit when z = 8;

    End Loop;

end;

Ur's

AmarAlam