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
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.
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
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;
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;
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
dbms_output.put_line(x);
End Loop ;
end;
/*Reverse for loop 10,9,8 … 1*/
Begin
for i in REVERSE 1 ..10
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
/* 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>
statements;
END LOOP ;
Example 1 of while
loop to show 1 to 15
declare
x number;
Begin
x := 1;
while x <=15
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
dbms_output.put_line(z);
z := z +
1;
exit when
z = 8;
End Loop ;
end;
