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 1;
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 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
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;
GOTO Statement
Branches to a label unconditionally
When executed, it changes the flow of control in a PL/SQL block
Two parts needed to code a GOTO statement
–Define a label name
–Use the GOTO statement to pass control to the label
Label name
–optionally used to name a PL/SQL block or statements within the block
–Defined using angular brackets (<< >>)
<>
IF condition THEN
statements;
END IF;
statements;
GOTO if_fare_label;
Transfer of control using GOTO statement is allowed in the following places
– from a block to an executable statement
– branch from an exception handler into an enclosing block
Transfer of control using GOTO statement is NOT allowed in the following places
– from within one IF statement or loop clause to another
– from an enclosing block into a sub-block
– from an exception handler into the current block
– out of a subprogram
– to keywords
Examples of GOTO –
1)
create table prec
(name varchar2(20),
dept varchar2(20));
create table mahrec
(name varchar2(20),
dept varchar2(20),
city varchar2(30));
/*Pune records should go in both the tables prec and mahrec.
Any other city record should go in mahrec table only.*/
declare
n prec.name%type;
d prec.dept%type;
c mahrec.city%type;
begin
n := '&n';
d := '&d';
c := '&c';
if c = 'Pune' then
goto s1;
else
goto s2;
end if;
<>
Insert into prec
values(n,d);
<>
Insert into mahrec
values(n,d,c);
end;
Wrong usage of goto
declare
x number;
begin
x := 90;
<>
dbms_output.put_line('S1');
if x = 90 then
dbms_output.put_line('Condition is true');
goto s1;
else
dbms_output.put_line('Condition is false');
end if;
end;
/
Discussion about Loops in PL/SQL
| Author | Body |
Rahul
7/12/2009 4:08 AM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you.
|