32. LOOPS

Iterations

Loops:-  To execute the same statement or coding for repeated times we use loops

1. Simple loop
2. While loop
3. for loop
1. Numeric for loop
2. Cursor for loop

1. Simple loop:- It is an infinite loop explicitly we have to stop the loop. It is uses in blocks.

Syntax:-

Loop

     Code;

End loop;

Ex:-

Begin

     Loop

     Exit [when condition];

     Code;

     End loop;

End;

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

Ex:-  Write a program to display 1 to 10 numbers?

  declare

      x number(5) :=1;

  begin

  loop

    exit when x > 10;

    dbms_output.put_line(x);

    x:=x+1;

  end loop;

  end;

  /

Output:

Ex:-  To display 1 to 5 numbers?

declare

  x number(5) :=1;

begin

loop

   if x > 5 then

exit;

end if;

   dbms_output.put_line(x);

   x:=x+1;

end loop;

end;

/

Output:

Ex:-

SELECT   deptno, SUM(NVL(sal,100)) sum_sal

FROM emp

GROUPBY deptno

HAVINGSUM(sal)>200

Output:

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

While loop:-  It is a pre conditional loop

Syntax:-  While condition loop

                   Code;

                   End loop;

Ex:-  To display 1 to 10 numbers?

declare

   a number(5) := 1;

begin

   while a <=10 loop

   dbms_output.put_line(a);

   a:=a+1;

end loop;

end;

Output:

·         Write a program to display tables from 1 to 10 by using simple loop

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

Ex:-

declare

  x number(5) :=1;

  y number(5) := 1;

begin

  loop

    y:=1;

     loop

       dbms_output.put_line(x||'*'||y||'='||x*y);

       y:=y+1;

     exit when y>10;

   end loop;

 x:=x+1;

exit when x>10;

end loop;

end;

/

Output:

It will print 1 to 10 tables;

·         Write a program to reverse the give string by using simple loop (without using reverse function)

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

For loop:

Syntax:

For var in [reverse] val1..val2 loop

Code;

End;

Ex:-

Begin

For I in 1..10 loop

Dbms_output.put_line(i);

End loop;

End;

Ex:-

Begin

For I in reverse 1..10 loop

Dbms_output.put_line(i);

End loop;

End;

Continue: (11g introduced):

Syntax:  Continue [When condition];

·         It is a 11g feature. Which is used in loop (in any loop)

·         Continue statement skips the current iteration

Ex:-

Begin                                      

  For I in 1..10 loop

  Continue when i>5;

  Dbms_output.put_line(i);

End loop;

End;

Ex:-

Begin

  For I in 1..10 loop

If I <5 o:p="" then="">

  Continue;

End if;

  Dbms_output.put_line(i);

End loop;

End;