IF Statement
Loop…End Loop
Exit command
While Loop
For loop
Goto statement
IF statement
IF statement is used to check the condition and
execute statements depending upon the result of the condition.
The following is the syntax of IF statement.
IF condition-1 THEN
statements_set_1;
[ELSIF condition-2 THEN
statements_set_2;] ...
[ELSE
statements_set_3; ]
END IF;
Condition is
formed using relational operators listed in table 1.
|
Operator
|
Meaning
|
|
>
|
Greater than
|
|
>=
|
Greater than or equal to
|
|
<
|
Less than
|
|
<=
|
Less than or equal to
|
|
=
|
Equal to
|
<>, !=, ~=, ^= |
Not equal to
|
LIKE |
Returns true if the character pattern matches
the given value.
|
BETWEEN..AND |
Returns true if the value is in the given range.
|
IN |
Returns true if the value is in the list.
|
IS NULL |
Return true if the value is NULL.
|
Table 1:
Relational Operators.
In order to combine two conditions, logical
operators – AND and OR are used. When two conditions are combined with AND then
both the conditions must be true to make the entire condition true. If
conditions are combined with OR then if any one condition is true then the
entire condition will be true.
The following are valid conditions:
If amt
> 5000 then
If
rate > 500 and qty < 10 then
If
rate between 100 and 200 then
Now, let us write a simple PL/SQL block that
uses IF statement.
The following program will increase the course
fee of Oracle by 10% if more than 100 students have joined for Oracle,
otherwise it will decrease the course fee by 10%.
declare
v_ns number(5);
begin
-- get
no. of students of the course
select count(*) into v_ns
from
students
where bcode in ( select bcode
from batches
where ccode = 'ora');
if v_ns > 100 then
update courses set fee = fee * 1.1
where ccode = 'ora';
else
update courses set fee = fee * 0.9
where ccode = 'ora';
end
if;
commit;
end;
/
The above block uses IF statement to check
whether the variable V_NS is greater than 100. If the condition is satisfied it
will increase the course fee by 10% otherwise it will decrease the course fee
by 10%.
The above program will either increase the
course fee by 10% or decrease it by 10%.
That means it will take either of two possible
actions. But in some cases we may have more than two actions. For instance,
what if we have to change the course fee as follows based on the number of
students joined in Oracle course:
No. of Students Percentage of change
>100 Increase
by 20
>50 Increase
by 15
>10 Increase
by 5
<=10 Decrease
by 10
The following program will change course fee of
Oracle according to the above table.
declare
v_ns number(5);
v_fee
courses.fee%type;
begin
-- get
no. of students of the course
select count(*) into v_ns
from
students
where bcode in ( select bcode
from batches
where ccode = 'ora');
select
fee into v_fee
from courses
where ccode = 'ora';
if v_ns > 100 then
v_fee := v_fee * 1.2; -- 20%
elsif v_ns > 50 then
v_fee := v_fee * 1.15; -- 15%
elsif v_ns > 10 then
v_fee := v_fee * 1.15; -- 15%
else
v_fee := v_fee * 0.90; -- 10%
decrease
end
if;
--
update fee in table
update
courses set fee = v_fee
where ccode = 'ora';
end;
/
The above program first checks whether the
number of students is more than 100. If so, it increases course fee by 20%. If
the first condition is not satisfied then it checks whether second condition is
true (v_no > 50) and if so it executes the statement after that ELSIF.
The IF statement will be terminated once a
condition is true and the corresponding statements are executed.
If none of the conditions is true then it
executes statements given after ELSE.
Every IF must have a matching END IF. However,
ELSIF that is used to check for a condition need not have corresponding END IF.
Also note that ELSIF can be used only after an IF is used.
Apart from checking condition and executing
statements depending on the result of the condition, PL/SQL also allows you to
repeatedly execute a set of statements.
Repeatedly executing a set of statements is called as looping structure.
In the next few sections we will discuss about
looping structures.
LOOP
This is used to repeatedly execute a set of
statements. This is the simplest form of
looping structures.
LOOP
Statements;
END LOOP;
1.
Note: Loop… End Loop has no termination
point. So unless you terminate loop using EXIT command (discussed next) it
becomes an infinite loop.
Statements are the statements that are to be
repeatedly executed. In case of LOOP, these statements must make sure they exit
the loop at one point or other. Otherwise the statements will be executed
indefinitely.
EXIT
This is used to exit out of a Loop. This is
mainly used with LOOP statement, as there is no other way of terminating the
LOOP.
The following is the syntax of EXIT command.
EXIT
[WHEN condition];
If EXIT is used alone, it will terminate the
current loop as and when it is executed.
If EXIT is used with WHEN clause, then the
current loop is terminated only when the
condition given after WHEN is satisfied.
The following examples show how you can use EXIT
and EXIT WHEN to exit a Loop.
Example 1:
LOOP
...
IF
count > 10 THEN
EXIT; -- terminates
loop
END IF;
...
END LOOP;
Example 2:
LOOP
...
EXIT WHEN count >10; -- terminates loop
...
END LOOP;
The following program will display numbers 1 to
10 using LOOP.
declare
i
number(2) := 1;
begin
loop
dbms_output.put_line(i);
i
:= i + 1;
exit when i > 10;
end
loop;
end;
/
In the above program, we first initialized
variable I to 1. Then LOOP starts and displays the value of I on
the screen. Then I is incremented by one. EXIT statement checks whether I
is greater than 10. If the condition is true then EXIT is executed and LOOP is
terminated otherwise it enters into loop again and redisplays the value of I.
Nested Loops
It is possible to have a loop within another
loop. When a loop is placed within
another loop it is called as nested loop. The inner loop is executed for each iteration
of outer loop.
The following example will display table up to
10 for numbers from 1 to 5.
declare
i
number(2);
j
number(2);
begin
i :=
1;
loop
j:=
1;
loop
dbms_output.put_line(i || '*' || j || '=' || i * j);
j := j + 1;
exit when j > 10;
end
loop;
i
:= i + 1;
exit when i > 5;
end
loop;
end;
/
Exiting from nested loop
It is possible to exit current loop using EXIT
statement. It is also possible to use EXIT statement to exit any enclosing
loop. This is achieved using loop
label.
A loop label is a label that is assigned to a
loop. By using this label that is assigned to a loop, EXIT can exit a specific
loop instead of the current loop.
The following example uses EXIT to exit the
outer loop.
<<outerloop>>
LOOP
...
LOOP
...
EXIT outerloop WHEN ... – exits outer loop
END LOOP;
...
END LOOP;
EXIT statement uses label to specify which
enclosing loop is to be terminated. EXIT uses outerloop, which is the
label given to outer loop, to terminate outer loop.
WHILE
Executes a series of statements as long as the
given condition is true.
WHILE
condition LOOP
Statements;
END
LOOP;
As long as the condition is true then
statements will be repeatedly executed. Once the condition is false then loop
is terminated.
The following example will display numbers from
1 to 10.
declare
i number(2) := 1;
begin
while
i <= 10
loop
dbms_output.put_line(i);
i := i + 1;
end loop;
end;
/
As long as the condition (I<=10) is true
statements given within LOOP and END LOOP are executed repeatedly.
The condition is checked at the beginning of
iteration. Statements are executed only when the condition is true otherwise
loop is terminated.
FOR
This looping structure is best suited to cases
where we have to repeatedly execute a set of statements by varying a variable
from one value to another.
FOR counter
IN [REVERSE] lowerrange .. upperrange
LOOP
Statements;
END LOOP;
lowerrange and upperrange
may also be expressions.
FOR loop sets counter to lower range and
checks whether it is greater than upper range. If counter is less than
or equal to upper range then statements given between LOOP and END LOOP will be
executed. At the end of execution of
statements, counter will be incremented by one and the same process will
repeat.
The following is the sequence of steps in FOR
LOOP.
Steps
The following is the sequence in which FOR will
take the steps.
2.
Counter is set to
lowerrange.
3.
If counter is less than or
equal to upperrange then
statements are executed otherwise
loop is terminated.
4.
Counter is incremented by one
and only one. It is not possible to increment counter by more than one.
5.
Repeats step2.
The following example will display numbers from
1 to 10.
begin
for i
in 1..10
loop
dbms_output.put_line(i);
end
loop;
end;
/
If REVERSE option is used the following steps
will take place:
1.
Counter is set to upper range.
2.
If counter is greater than or
equal to lower range then statements are executed otherwise loop is terminated.
3.
Counter is decremented by one.
4.
Go to step 2.
The following FOR loop uses REVERSE option to
display number from 10 to 1.
begin
for i
in REVERSE 1..10
loop
dbms_output.put_line(i);
end
loop;
end;
/
5.
Note: It is not possible to change the step value for FOR loop.
Sample program using FOR loop
The following example will display the missing
roll numbers. The program starts at lowest available roll number and goes up to
largest roll number. It will display the roll numbers that are within in the
range and not in the STUDENTS table.
declare
v_minrollno students.rollno%type;
v_maxrollno students.rollno%type;
v_count number(2);
begin
-- get
min and max roll numbers
select min(rollno) , max(rollno)
into v_minrollno, v_maxrollno
from students;
for
i in v_minrollno .. v_maxrollno
loop
select count(*) into v_count
from
students
where rollno = i;
--
display roll number if count is 0
if v_count = 0 then
dbms_output.put_line(i);
end
if;
end
loop;
end;
/
The above program takes minimum and maximum roll
numbers using MIN and MAX functions. Then it sets a loop that starts at minimum
roll number and goes up to maximum roll number. In each iteration it checks
whether there is any student with the current roll number (represented by
variable i). If no row is found then COUNT (*) will be 0. So it displays
the roll number if count is zero.
GOTO statement
Transfers control to the named label. The label must be unique and should precede
an executable PL/SQL statement or PL/SQL block.
The following example shows how to create label
and how to transfer control to the label using GOTO statement.
BEGIN
...
GOTO change_details;
...
<<change_details>>
update students ... ;
END;
GOTO statement transfers control to UPDATE
statement that is given after the label change_details
Label is created by enclosing a name within two
sets of angle brackets (<< >>). The label must be given either
before an executable PL/SQL statement or a block.
Restrictions
The following are the restrictions on the usage
of GOTO statement.
q Cannot branch into an IF statement
q Cannot branch into a LOOP
q Cannot branch into a Sub block.
q Cannot branch out of a subprogram – a procedure or function.
q Cannot branch from exception handler into current block.
The following is an invalid usage of GOTO statement as it tries to enter into an IF
block:
BEGIN
...
/* following is invalid because GOTO can not
branch into an IF statement */
GOTO change_details;
...
IF condition THEN
...
<<change_details>>
update students ... ;
...
END IF;
END;
Summary
Any programming language has basic control
structures like IF statement, looping structures etc. These control structures
are an important part of PL/SQL, because they allow data processing. In this chapter, we have covered basic
control structure, but the practical usage of these control constructs will be
better understood as you write more programs.
