PL/SQL - Loops

Complete Tutorial 

LOOPS

  •  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 + 2;
              exit when x > 10;
      End Loop;
dbms_output.put_line(‘end’);
 End;

create table five
(no number);

/*Inserting multiples 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;

While Loop v/s Basic Loop

While Loop

declare
  x number;
Begin
   x := 1;
   while x > 15
    Loop
            dbms_output.put_line(x);
            x := x + 1;
    End Loop;
    dbms_output.put_line('End of program');
end;
/

The loop will never get executed since the condition is wrong from the start of the iteration.


Basic Loop


Declare
     x number;
 Begin
     x :=  1;
      Loop
              dbms_output.put_line(x);
               exit when x = 1;
               x := x + 1;
       End Loop;
dbms_output.put_line('End of program');
  End;
/

The loop gets executed at least once.


Nested Loops


create table discount_details
 (quarter number(2),
 month number(2),
 discount varchar2(5));



Expected Output –


QUARTER    MONTH      DISCOUNT                                                 

         1             1                      12%                                                      
         1             2                      11%                                                       
         1             3                      10%                                                      
         2             4                      9%                                                       
         2             5                      8%                                                        
         2             6                      7%                                                       
         3              7                     6%                                                       
         3              8                     5%                                                        
         3             9                      4%                                                       
         4             10                    3%                                                       
         4             11                    2%                                                        
         4              12                   1%                                                       

12 rows selected.

declare
  q number;
  m number;
  d number;
  dis varchar2(10);
  c number;
begin
  q := 1;
  m := 0;
  d := 12;
     loop
       exit when q > 4;
        c := 0;
        loop
           exit when c >= 3;
                m := m + 1;
                dis := d || '%';
                insert into discount_details
                values(q,m,dis);
                d := d - 1;
                c := c + 1;
         end loop;
         q := q + 1;
   end loop;
end;


<<<Previous                                                                                                                                                                                  Next>>>

Complete Tutorial 

*/