PL/SQL Conditional Statements Example - Case Statements in PL SQL - If Then Else in PL SQL
Conditional statements in Oracle PL/SQL. IF - Else statement. Case Statement. Full courses : SQL - https://bit.ly/38c91ih | Python - https://bit.ly/3ihca4L | MongoDB - https://bit.ly/38bRJ4K | PL/SQL - https://bit.ly/2Zl4OVw
#OraclePLSQL #LearnPLSQL #Crazy4DB #MunshiSir #LearnOracle
Code for complex programs of this video :
If-then-elsif-then-else-end if;
Problem : Calculate salary with formula salary = basic + allowance
under following terms -
(1) basic can not be less than 1800 and more than 10,000
(2) basic between 1800 and 3000 allownace is 120% of basic
(3) basic above 3000 and up to 5000 allowance rate 130% of basic
(4) basic above 5000 and up to 7000 allowance rate 140% of basic
(5) basic above 7000 allowance rate is 150%
Input to the program is basic
Output print : basic, calculated allowance, and salary = basic + allowance
declare
basic number;
salary number;
allow number;
minbas number := 1800;
maxbas number := 10000;
baslim1 number := 3000;
baslim2 number := 5000;
baslim3 number := 7000;
allow1 number := 120;
allow2 number := 130;
allow3 number := 140;
allow4 number := 150;
invalid exception;
begin
basic := '&basic';
if basic [less than] minbas or basic [greater than] maxbas then
raise invalid;
elsif basic between minbas and baslim1 then
allow := basic * allow1/100;
elsif basic [greater than] baslim1 and basic [less than equal] baslim2 then
allow := basic * allow2/100;
elsif basic [greater than] baslim2 and basic [less than equal] baslim3 then
allow := basic * allow3/100;
else
allow := basic * allow4/100;
end if;
salary := basic + allow;
dbms_output.put_line('Basic : '||basic);
dbms_output.put_line('Allowance : '||allow);
dbms_output.put_line('salary : '||salary);
exception
when value_error then
dbms_output.put_line('basic has to be number');
when invalid then
dbms_output.put_line('basic must be in valid range');
end;
CASE control structure
==========================================================
Case was introduced in oracle 9i. It is equivalent to if-elsif
structure demonstrated above, but simplifies it by taking expression
only for once, in normal use but tests the evaluation of expression
with value for equality.
Problem : consider a school which has alternate day half day and
weekly off on Sunday. We want to display the name of the day
and if it is half day or full day or holiday. The input is
numeric week day, with 1 as Sunday.
declare
daynum number(1);
err exception;
begin
daynum := '&DayNumber';
if daynum [less than] 1 or daynum [greater than] 7 then
raise err;
end if;
case daynum
when 1 then
dbms_output.put_line('it is Sunday : weekly off');
when 2 then
dbms_output.put_line('it is Monday : Full Day');
when 3 then
dbms_output.put_line('it is Tuesday : Half Day');
when 4 then
dbms_output.put_line('it is Wednesday : Full Day');
when 5 then
dbms_output.put_line('it is Thursday : Half Day');
when 6 then
dbms_output.put_line('it is Friday : Full Day');
else
dbms_output.put_line('it is Saturday : Half Day');
End case;
exception
when err then
dbms_output.put_line('numeric week day 1 to 7 only accepted');
when value_error then
dbms_output.put_line('input must be between 1 to 7 only');
end;
CASE in Searched form
declare
basic number;
salary number;
allow number;
minbas number := 1800;
maxbas number := 10000;
baslim1 number := 3000;
baslim2 number := 5000;
baslim3 number := 7000;
allow1 number := 120;
allow2 number := 130;
allow3 number := 140;
allow4 number := 150;
invalid exception;
begin
basic := '&basic';
CASE
when basic [less than] minbas or basic [greater than] maxbas then
raise invalid;
when basic between minbas and baslim1 then
allow := basic * allow1/100;
when basic [greater than] baslim1 and basic [less than equal] baslim2 then
allow := basic * allow2/100;
when basic [greater than] baslim2 and basic [less than equal] baslim3 then
allow := basic * allow3/100;
else
allow := basic * allow4/100;
end case;
salary := basic + allow;
dbms_output.put_line('Basic : '||basic);
dbms_output.put_line('Allowance : '||allow);
dbms_output.put_line('salary : '||salary);
exception
when value_error then
dbms_output.put_line('basic has to be number');
when invalid then
dbms_output.put_line('basic must be in valid range');
end;