Oracle PL/SQL IF Statement

Oracle PL/SQL IF Statement | The if-else statement in Oracle PL/SQL is a selection statement. When dealing with selection statements, there are generally three versions: one-way, two-way, and multi-way. One way decision statements do a particular thing or do nothing. Two-way decision statements can do one thing or do another. Multi-way decision statements can do one of many different things depending on the value of an expression.

Based on these different ways of selection, similar like other languages Oracle PL/SQL also have three different forms of IF statement.

1) IF-THEN
2) IF-THEN-ELSE
3) IF-THEN-ELSIF

IF-THEN Statement in Oracle

The IF-THEN statement in Oracle PL/SQL is a one way decision statement. If the condition is true then do a particular thing otherwise do nothing.

The Syntax for IF-THEN is,

IF condition THEN
   -- statements to execute 
   -- when condition is true
END IF;

When the condition is true then only statements within IF block will be executed otherwise it won’t executed.

Example of IF-THEN statement:-

Write a PL/SQL program to take empno as input and fetch salary of the employee from emp table under scott user. If the salary is less than 3000 then display the String ‘Salary less than 3000’.

DECLARE
   salary NUMBER(10);
BEGIN
   SELECT sal INTO salary
   FROM emp WHERE empno = &empno;
   -- check salary
   IF salary < 3000 THEN
      dbms_output.put_line('Salary less than 3000');
   END IF;
   dbms_output.put_line('This statement will 
                         be always executed');
END;
/

Execution,

SQL> /
Enter value for empno: 7369
Salary less than 3000
This statement will be always executed
PL/SQL procedure successfully completed.

SQL> /
Enter value for empno: 7788
This statement will be always executed
PL/SQL procedure successfully completed.

A Boolean variable is always TRUE, FALSE or NULL. So, IF condition THEN is always same as condition = true THEN, that’s don’t compare unnecessarily.

IF-THEN-ELSE Statement in Oracle

The IF-THEN-ELSE is a two way decision statement in Oracle. If the condition is true then do a particular thing otherwise do another thing.

The syntax of IF-THEN-ELSE statement in Oracle PL/SQL is,

IF condition THEN
   -- statements1 for true
ELSE
   -- statements2 for false
END IF;

When condition is true then statements1 of IF block will be executed, and when condition becomes false then the statements2 of ELSE block will be executed.

Example of IF-THEN-ELSE statement,

Example1) In the previous example of IF-THEN, we were displaying string only for true condition. Now this time,

• display string ‘Salary is less than 3000’ when salary is less than 3000
• else display ‘Salary is more than or equal to 3000’

DECLARE
   salary NUMBER(10);
BEGIN
   SELECT sal INTO salary
   FROM emp WHERE empno = &empno;
   -- check salary
   IF salary < 3000 THEN
      dbms_output.put_line('Salary is less than 3000');
   ELSE
      dbms_output.put_line('Salary is more than or equal to 3000');
   END IF;
   dbms_output.put_line('This statement will be always executed');
END;

Execution,

SQL> /
Enter value for empno: 7788
Salary is more than or equal to 3000
This statement will be always executed
PL/SQL procedure successfully completed.

SQL> /
Enter value for empno: 7369
Salary is less than 3000
This statement will be always executed
PL/SQL procedure successfully completed.

We can also use IF-THEN or IF-ELSE inside another IF-ELSE statement. Writing one IF statement within another IF statement is also possible.

Example2) Write a PL/SQL program to take deptno and sal value as input. Display,

• when deptno = 10 and
  • sal >= 2900 then ‘High Salary’
  • else ‘Low Salary’
• else
  • sal >= 1500 then ‘High Salary’
  • else ‘Low Salary’

DECLARE
   deptno number(10);
   sal number(10);
BEGIN
   -- take input
   deptno := &deptno;
   sal := &sal;
   -- outer if-else 
   IF deptno = 10 THEN
     -- inner if-else-1
     IF sal >= 2900 THEN
        dbms_output.put_line('High Salary');
     ELSE
        dbms_output.put_line('Low Salary');
     END IF;
   ELSE 
     -- inner if-else-2
     IF sal >= 1500 THEN
        dbms_output.put_line('High Salary');
     ELSE
        dbms_output.put_line('Low Salary');
     END IF;
   END IF;
END;

SQL> /
Enter value for deptno: 10
Enter value for sal: 1200
Low Salary
PL/SQL procedure successfully completed.

IF-THEN-ELSIF Statement in Oracle

The IF-THEN-ELSE statement in Oracle PL/SQL is a multi-way decision statement. To check more number of conditions we are using IF-THEN-ELSE statement.

Syntax:-

IF condition1 THEN
   -- statement1
ELSIF condition2 THEN
   -- statement2
.....
.....
ELSIF condition_N THEN
   -- statement_N
ELSE
   -- final_statement
END IF;

The statement1 will be executed when condition1 is true. Similarly, statement2 will be executed when condition1 is false but condition2 is true. Again, statement_N will execute when all the above conditions are false but condition_N becomes true. The final_statement will be executed when all conditions become false.

The program is evaluated from the top to bottom. As soon a true condition is found, the statement associated with it will executed, another condition won’t be checked and the rest of the statements will be bypassed. If none of the conditions are true, then finally else block will be executed.

Note that here ELSE block is optional and if we don’t use it and all other conditions are true then nothing will be executed.

Example1) Write a PL/SQL program to take deptno as input and using emp table under scott user,

• display ‘TEN’ when deptno = 10
• display ‘Twenty’ when deptno = 20
• Similarly, display ‘Thirty’ when deptno = 30
• else display ‘others’

DECLARE
   v_deptno number(10);
BEGIN
   SELECT deptno INTO v_deptno 
   FROM dept WHERE deptno = &deptno;
   -- if else statements
   IF v_deptno = 10 THEN
     dbms_output.put_line('Ten');
   ELSIF v_deptno = 20 THEN
     dbms_output.put_line('Twenty');
   ELSIF v_deptno = 30 THEN
     dbms_output.put_line('Thirty');
   ELSE
     dbms_output.put_line('Others');
   END IF;
END;

SQL> /
Enter value for deptno: 20
Twenty
PL/SQL procedure successfully completed.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• PL/SQL Introduction
• PL/SQL Variable

Search