Conditional Control Statements in PL/SQL

By Manish Sharma

IF-THEN-ELSE Control Statements in PL/SQL

The previous tutorial was all about IF-THEN statement in Oracle PL/SQL. There we learnt that a simple IF-THEN statement enables us to specify the sequence of statements to be executed only if the condition is evaluated to be true. In case this condition is evaluated to be false then no special action is to be taken except to proceed with execution of the program.

To overcome this drawback in oracle PL/SQL we have IF-THEN-ELSE statement widely pronounced as IF-ELSE statement.

With IF-THEN-ELSE in PL/SQL we have two groups of executable statements, one which gets executed if the condition is evaluated to be true and another group gets executed if the condition is evaluated to be false. Once the IF-THEN-ELSE construct gets completed the next statement right after IF-THEN-ELSE block is executed.

conditional control statements in PL/SQL by Manish Sharma


 IF condition THEN
  Statement 1;
  Statement 2;
  Statement 3
And here is the syntax as you can see IF-THEN-ELSE construct starts with the keyword IF and ends with the reserved phrase END IF. Followed by IF keyword we have to specify a valid condition which will get evaluated. If this condition is evaluated to be TRUE then control is passed to the statement 1, and if this condition is evaluated to be false then control will jump over to statement 2. Once the construct is completed then statement 3 is executed.


Here in this example we will take a numeric input from the user and will check whether a user has entered an even number or an odd number using IF THEN ELSE statement. This is going to be a very easy example.

   v_num NUMBER := &enter_a_number;
   IF MOD (v_num, 2) = 0 THEN
     DBMS_OUTPUT.PUT_LINE (v_num || ' Is Even');
     DBMS_OUTPUT.PUT_LINE (v_num ||' is odd');
   END IF;
     DBMS_OUTPUT.PUT_LINE ('IF THEN ELSE Construct complete ');

In the declaration section of this block I have declared a variable v_num with data type NUMBER and using substitution operator I am taking values from the user.

Next in the execution section of the block we have our IF THEN ELSE construct where we are checking whether the number entered by the user is even or odd. For this test I have used MOD function of Oracle library as our IF condition which will divide the first parameter by the second parameter and return the remainder or say Modulus. If this modulus is zero then number will be even otherwise number will be odd. Thus if the modulus comes as zero then control will jump over first DBMS_OUTPUT statement and will print the given string but if modulus is not zero then control will jump over the 2nd DBMS_OUTPUT statement and will display its string. Once this is complete then control will come out from the IF THEN ELSE construct and will print the third DBMS_OUTPUT statement.

I am always willing to learn new things from you guys. So if you have any better example for IF THEN ELSE statement in PL/SQL demonstration then please feel free to comment or you can also tweet me the screenshot of your code @RebellionRider.

That’s all guys. Hope this article was useful. Kindly please share it on your social networking and help me reach out to more people. Thanks & 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