Oracle For Beginners Page : 1
srikanthtechnologies.com
Chapter 16
CONTROL STRUCTURES
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;
Oracle For Beginners Page : 2
srikanthtechnologies.com
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 studen