PL/SQL IF Statement

Summary: in this tutorial, you will learn how to use the PL/SQL IF statement to execute 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 three forms:

IF THEN
IF THEN ELSE
IF THEN ELSIF

PL/SQL IF THEN statement #

Here’s the syntax of the IF THEN statement:

IF condition THEN
    statements;
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Tip # 1: Avoid clumsy IF statement #

Consider the following example:

DECLARE
  b_profitable BOOLEAN;
  n_sales      NUMBER := 100;
  n_costs      NUMBER := 50;
BEGIN
  b_profitable := false;   
  IF n_sales > n_costs THEN
    b_profitable := true;
  END IF;
END;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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:

DECLARE
  b_profitable BOOLEAN;
  n_sales      NUMBER := 100;
  n_costs      NUMBER := 50;
BEGIN
  b_profitable := n_sales > n_costs;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Instead, use:

IF b_profitable THEN
   DBMS_OUTPUT.PUT_LINE( 'This sales deal is profitable' );
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If the condition evaluates to TRUE, then the statements between THEN and ELSE execute. If 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PL/SQL IF ELSIF statement #

Here’s the syntax of the IF 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.

Summary #

• Use the PL/SQL IF statement to either execute or skip a sequence of statements depending on a specified condition.