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;
