Conditional Control Statements in PL/SQL

By Manish Sharma

IF-THEN-ELSIF Control Statements in PL/SQL

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.



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.


  v_Place VARCHAR2(30) := '&Enter Place';
  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');
   DBMS_OUTPUT.PUT_LINE('Please Call Avengers');
 DBMS_OUTPUT.PUT_LINE('Thanks For Contacting us');

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!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn