Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL IF Statement

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;

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;

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;

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;

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;

Instead, use:

IF b_profitable THEN DBMS_OUTPUT.PUT_LINE( 'This sales deal is profitable' ); END IF;

PL/SQL IF THEN ELSE statement

The IF THEN ELSE statement has the following structure:

IF condition THEN statements; ELSE else_statements; END IF;

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;

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;

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;

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;

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?
  • YesNo
Previous PL/SQL Constants
Next PL/SQL CASE Statement

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.