7. NESTED IF

PL/SQL

NESTED IF: Condition inside a condition is called Nested IF.

Syntax: IF Then
statment(s);
if then
statement(s);
else
statement(s);
end if;
else
statement(s);
end if;

In the above syntax if the first condition returns true then
statement(s) if any given after the condition wil be executed then
next if condition will be performed.

If the first condition returns false then statements given after else
will be executed.

Sample Data:

Eno Ename Sal Job Doj Deptno

115 CHANDU 25000 manager 3/10/2012 222
105 kumar 12200 Analyst 555
111 RAM 12000 Analyst 12/12/2014 222
112 RAMESH 25000 Sr Analyst 12/29/2014 333
113 KRISHNA 30000 Sr Analyst 1/3/2014 555
114 SHEKAR 56000 manager 1/1/2014 555

Example: Calcuate the bonus based on job and anual salary

DECLARE
ln_eno number:=&ln_eno;
lc_ename emp.ename%type;
ln_sal number;
lc_job emp.job%type;
ln_asal number;
ln_bonus number:=0;
ln_ctc number:=0;

BEGIN
SELECT eno,
ename,
sal,
job
INTO ln_eno,
lc_ename,
ln_sal,
lc_job
FROM emp
WHERE ENO = ln_eno;

ln_asal := ln_sal*12;


if lc_job = 'manager' then
  if ln_asal < 1000000 then
    ln_bonus := ln_asal * 10/100;
   elsif ln_asal between 1000000 and 1200000 then
    ln_bonus :=ln_asal*8/100;
   else  ln_bonus := ln_asal * 7/100;
  end if;

elsif lc_job = 'Analyst' then
  if ln_asal < 300000 then
         ln_bonus := ln_asal * 15/100;
  elsif ln_asal betweeN 300000 and 500000 then
     ln_bonus := ln_asal * 10/100;
  else ln_bonus := ln_asal * 8/100;
  end if;
else
  if ln_asal < 100000 then
   ln_bonus:=ln_asal * 5/100;
  else 
   ln_bonus := ln_asal * 6/100;
  end if;
end if;

ln_ctc := ln_asal + ln_bonus;

dbms_output.put_line (‘Emp no ‘||ln_eno);
dbms_output.put_line (‘Emp NAme ‘||lc_ename);
dbms_output.put_line (‘Emp Sal ‘||ln_sal);
dbms_output.put_line (‘Anual Sal ‘||ln_asal);
dbms_output.put_line (‘Bonus ‘||ln_bonus);
dbms_output.put_line (‘CTC ‘||ln_ctc);

exception
when no_data_found then
dbms_output.put_line (‘No Emp exist’);
when too_many_rows then
dbms_output.put_line (‘Multiple records’);
when others then
dbms_output.put_line (‘Other error ‘||SQLERRM);
END;
/