PL/SQL IF Statement

Summary: in this tutorial, you will learn how to use the PL/SQL IF statement to either execute or skip a sequence of statements based on a specified condition.

The IF statement allows you to either execute or skip a sequence of statements, depending on a condition. The IF statement has the three forms:

– IF THEN
– IF THEN ELSE
– IF THEN ELSIF

PL/SQL IF THEN statement

The following illustrates the structure of the IF THEN statement:

IF condition THEN
    statements;
END IF;
Code language: SQL (Structured Query Language) (sql)

The condition is a Boolean expression that always evaluates to TRUE, FALSE, or NULL.

If the condition evaluates to TRUE, the statements after the THEN execute. Otherwise, the IF statement does nothing.

PL/SQL IF THEN statement example

In the following example, the statements between THEN and END IF execute because the sales revenue is greater than 100,000.

DECLARE n_sales NUMBER := 2000000; 
BEGIN 
   IF n_sales > 100000 THEN 
      DBMS_OUTPUT.PUT_LINE( 'Sales revenue is greater than 100K ' ); 
   END IF; 
END;
Code language: SQL (Structured Query Language) (sql)

Tip # 1: Avoid clumsy IF statement

Consider the following example:

DECLARE
  b_profitable BOOLEAN;
  n_sales      NUMBER;
  n_costs      NUMBER;
BEGIN
  b_profitable := false;   
  IF n_sales > n_costs THEN
    b_profitable := true;
  END IF;
END;
Code language: SQL (Structured Query Language) (sql)

In this example, the IF statement determines whether the sales revenue is higher than the cost and updates the b_profitable variable accordingly.

This IF statement called a clumsy IF statement because you can assign the result of a Boolean expression directly to a Boolean variable as follows:

b_profitable := n_sales > n_costs;
Code language: SQL (Structured Query Language) (sql)

Tip #2: Avoid evaluating Boolean variables

A Boolean variable is always TRUE, FALSE or NULL. Therefore the following comparison is unnecessary:

IF b_profitable = TRUE THEN
   DBMS_OUTPUT.PUT_LINE( 'This sales deal is profitable' );
END IF;
Code language: SQL (Structured Query Language) (sql)

Instead, use:

IF b_profitable THEN
   DBMS_OUTPUT.PUT_LINE( 'This sales deal is profitable' );
END IF;
Code language: SQL (Structured Query Language) (sql)

PL/SQL IF THEN ELSE statement

The IF THEN ELSE statement has the following structure:

IF condition THEN
    statements;
ELSE
    else_statements;
END IF;
Code language: SQL (Structured Query Language) (sql)

If the condition evaluates to TRUE, then the statements between THEN and ELSE execute. In case the condition evaluates to FALSE or NULL, the else_statements between ELSE and END IF executes.

IF THEN ELSE statement example

The following example sets the sales commission to 10% if the sales revenue is greater than 200,000. Otherwise, the sales commission is set to 5%.

DECLARE
  n_sales NUMBER := 300000;
  n_commission NUMBER( 10, 2 ) := 0;
BEGIN
  IF n_sales > 200000 THEN
    n_commission := n_sales * 0.1;
  ELSE
    n_commission := n_sales * 0.05;
  END IF;
END;
Code language: SQL (Structured Query Language) (sql)

PL/SQL IF THEN ELSIF statement

The following illustrates the structure of the IF THEN ELSIF statement:

IF condition_1 THEN
  statements_1
ELSIF condition_2 THEN
  statements_2
[ ELSIF condition_3 THEN
    statements_3
]
...
[ ELSE
    else_statements
]
END IF;
Code language: SQL (Structured Query Language) (sql)

In this structure, the condition between IF and THEN, which is the first condition, is always evaluated. Each other condition between ELSEIF and THEN is evaluated only if the preceding condition is FALSE. For example, the condition_2 is evaluated only if the condition_1 is false, the condition_3 is evaluated only if the condition_2 is false, and so on.

If a condition is true, other subsequent conditions are not evaluated. If no condition is true, the else_statements between the ELSE and ENDIF execute. In case you skip the the ELSE clause and no condition is TRUE, then the IF THEN ELSIF does nothing

IF THEN ELSIF statement example

The following example uses the IF THEN ELSIF statement to set the sales commission based on the sales revenue.

DECLARE
  n_sales NUMBER := 300000;
  n_commission NUMBER( 10, 2 ) := 0;
BEGIN
  IF n_sales > 200000 THEN
    n_commission := n_sales * 0.1;
  ELSIF n_sales <= 200000 AND n_sales > 100000 THEN 
    n_commission := n_sales * 0.05;
  ELSIF n_sales <= 100000 AND n_sales > 50000 THEN 
    n_commission := n_sales * 0.03;
  ELSE
    n_commission := n_sales * 0.02;
  END IF;
END;
Code language: SQL (Structured Query Language) (sql)

Nested IF statement

You can nest an IF statement within another IF statement as shown below:

IF condition_1 THEN
    IF condition_2 THEN
        nested_if_statements;
    END IF;
ELSE
    else_statements;
END IF; 
Code language: SQL (Structured Query Language) (sql)

However, if you have too many levels of nesting, the code will be hard to read and maintain, so you should avoid nesting the IF statements.

In this tutorial, you have learned how to use the PL/SQL IF statement to either execute or skip a sequence of statements depending on a specified condition.