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.

Was this tutorial helpful?