# 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

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
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>>>

*/