Compare values and make the decision. VAlues can be campared
with variable or direct.
Syntax: If Then
statement1;
statement2;
else
statement3;
END IF;
IF <condition> then
statement1;
ELSE
statement2
IF <condition> then
statement3;
ELSE
statement4;
END IF;
END IF;
IF <condition> then
statement1;
ELSIF <condition> then
statement2;
ELSE
statement3;
END IF;IF Will compare and returns true when the condition is satisfied and
executes statement given next to if condition
otherwise returns false and statement given after else will be executed.
Example: Read any 2 numbers and find the biggest one
SQL>ed filename.sql
declare
a number:=&a;
b number:=&b;
begin
if a>b then
dbms_output.put_line (a||’ is big ‘);
else
dbms_output.put_line (b ||’ is big’);
end if;
end;
/
SQL> @ if1.sql
Enter value for a: 10
Enter value for b: 5
10 is big
SQL> @ if1.sql
Enter value for a: 10
Enter value for b: 25
25 is big
Example 2: Find biggest of 3 numbers
declare
a number:=&a;
b number:=&b;
c number:=&c;
begin
if a>b and a>c then
dbms_output.put_line (‘a is big’);
elsif b>c then
dbms_output.put_line (‘b is big’);
else
dbms_output.put_line (‘c is big’);
end if;
end;
(or)
declare
a number:=&a;
b number:=&b;
c number:=&c;
begin
if a>b and a>c then
dbms_output.put_line (‘a is big’);
end if;
if b>c and b>a then
dbms_output.put_line ('b is big');
end if;
if c>a and c>b then
dbms_output.put_line ('c is big');
end if;end;
Example 3: SElet the salary from the table and calculate tax based on some conditions
SQL> ed filename.sql
declare
ln_eno number;
lc_ename varchar2(10);
ln_sal number;
ln_tax number;
ln_net number;
begin
select eno,
ename,
sal
into ln_eno,
lc_ename,
ln_sal
from emp
where eno = 105;
if ln_sal<10000 then
ln_tax := ln_sal*2/100;
elsif ln_sal between 10000 and 20000 then
ln_tax := ln_sal*5/100;
elsif ln_sal between 20000 and 30000 then
ln_tax := ln_sal*7/100;
else
ln_tax := ln_sal * 9/100;
end if;
ln_net := ln_sal - ln_tax;
dbms_output.put_line ('Eno = '||ln_eno);
dbms_output.put_line ('Ename = '||lc_ename);
dbms_output.put_line ('Sal = '||ln_sal);
dbms_output.put_line ('Tax = '||ln_tax);
dbms_output.put_line ('Net = '||ln_net);exception
when no_data_found then
dbms_output.put_line (‘No emp exist’);
when too_many_rows then
dbms_output.put_line (‘There are multiple records’);
when others then
dbms_output.put_line (‘Other error ‘||SQLERRM);
end;
SQ> @ filename.sql
Eno = 105
Ename = kumar
Sal = 12200
Tax = 610
Net = 11590
Table: Purchase
PONO Item Qty Price Vendor_ID
------------------------------------------
800 lp001 10 15000 100
801 lp002 12 25000 101
-------------------------------------------Example 4 / task : Select the po details like item,qty,price,pono for a pono from purchase table
then calculate totalamt, discount based on totalamt and net
if totalamount<100000 then discount is 10% if totalamount is between 1000000 and 200000 then discount is 20% if totalamount > 300000 then discount is 30%
-- update the totalamt, dis, net in the table also