In the previous tutorial we saw that IF ELSE condition gives us the provision of executing statements not only when the condition is evaluated to be true but also when the condition is evaluated to be false. But using IF ELSE statement we can only check one condition at a time, there is no provision for checking multiple conditions. This becomes its major drawback.
To overcome this we have IF THEN ELSIF condition in Oracle PL/SQL. Using this statement you can check multiple conditions unlike the IF conditions discussed in the previous tutorials.
Syntax
IF CONDITION 1 THEN
STATEMENT 1;
ELSIF CONDITION 2 THEN
STATEMENT 2;
ELSIF CONDITION 3 THEN
STATEMENT 3;
…
ELSE
STATEMENT N; END IF;Â
Similar to the other IF conditions, keyword IF marks the beginning and reserved phrase END IF marks the ending of the block. Make sure to put a white space in between END and IF of ending phrase. In this ELSIF construct we have multiple conditions. CONDITION 1 through CONDITION N are a sequence of conditions that have to be evaluated for TRUE or FALSE. These conditions are mutually exclusive. This means that if condition 1 is evaluated to be TRUE then statement 1 is executed and control will jump over the first executable statement outside the ELSIF construct and rest of the conditions will be ignored. If condition 1 is evaluated to be false then the compiler will jump inside and check the rest ELSIF conditions to look for the one which is true. If it finds any then it will execute the corresponding statements otherwise it will run the else statement.
In simple words the IF THEN ELSIF statement is responsible for running the first statement for which the condition is true. Once this is done the rest of the conditions are not evaluated. In case none of the conditions are true, then the else_statements run provided that they exist; otherwise no action is taken by the IF THEN ELSIF statement.
Example
DECLARE
 v_Place VARCHAR2(30) := ‘&Enter Place’;
BEGIN
 IF v_Place = ‘Metropolis’ THEN
 DBMS_OUTPUT.PUT_LINE(‘This City Is Protected By Superman’);
ELSIF v_Place = ‘Gotham’ THEN
 DBMS_OUTPUT.PUT_LINE(‘This City is Protected By Batman’);
ELSIF v_Place = ‘Amazon’ THEN
DBMS_OUTPUT.PUT_LINE(‘This City is protected by Wonder Woman’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Please Call Avengers’);
END IF;
DBMS_OUTPUT.PUT_LINE(‘Thanks For Contacting us’);END;
This is a very simple Example where I have declared a variable v_place which has data type varchar2 and we are taking input in this variable from the user. As you can see here I have used substitution operator (&). Don’t forget to enclose this input string along with ampersand operator (or substitution operator &) inside single quotes as variable is of varchar2 data type.
In the execution section we have ELSIF construct. Where we have one IF condition one else condition and 2 ELSIF conditions. Every condition is accompanied with a DBMS_OUTPUT statement which will get executed if the respective condition is evaluated to be true. Otherwise the DBMS_OUTPUT statement in ELSE section will run. Once this block is executed then control will come out and execute the first executable statement outside the ELSIF construct which is our last DBMS_OUTPUT statement.
That’s all about ELSIF condition. Hope you found this helpful. Kindly please share it with your friends and help me reach out to more people. Thanks and have a great day!
