Oracle PL/SQL Conditional Statements

Yusuf SEZER

4 years ago

This article contins information about PL/SQL Conditional Statements such as IF,IF ELSE,ELSEIF,CASE.

Conditional statements allow to execute commands according to a certain condition.

The condition in conditional statements is created by using the operators in Oracle PL / SQL.

What are the PL/SQL Conditional Statements?

IF Statement in Oracle PL/SQL

If the specified condition is true, commands run in the block.

IF Your_Condition THEN
    -- Commands to be execute if condition is true
END IF;

IF Your_Condition THEN

-- Commands to be execute if condition is true

END IF;

Sample IF Condition Usage is as follows;

SET SERVEROUTPUT ON;
BEGIN
    IF TRUE THEN
        DBMS_OUTPUT.put_line('Hello, I am Yusuf SEZER');
    END IF;
END;

SET SERVEROUTPUT ON;

BEGIN

IF TRUE THEN

DBMS_OUTPUT.put_line('Hello, I am Yusuf SEZER');

END IF;

END;

IF ELSE Statement in Oracle PL/SQL

ELSE condition contains commands to run if the condition set by IF is not true.

IF Your_Condition THEN
    -- Commands to be executed if condition is true
ELSE
    -- Commands to be executed if condition is false
END IF;

IF Your_Condition THEN

-- Commands to be executed if condition is true

ELSE

-- Commands to be executed if condition is false

END IF;

Sample IF ELSE Condition Usage is as follows;

SET SERVEROUTPUT ON;
BEGIN
    IF FALSE THEN
        DBMS_OUTPUT.put_line('Hello, I am Yusuf SEZER');
    ELSE
        DBMS_OUTPUT.put_line('There may be other commands in this section.');
    END IF;
END;

SET SERVEROUTPUT ON;

BEGIN

IF FALSE THEN

DBMS_OUTPUT.put_line('Hello, I am Yusuf SEZER');

ELSE

DBMS_OUTPUT.put_line('There may be other commands in this section.');

END IF;

END;

ELSIF Statement in Oracle PL/SQL

Used to specify multiple conditions.

IF Your_Condition1 THEN
    -- Commands-1
ELSIF Your_Condition2 THEN
    -- Comamnds-2
ELSIF Your_Condition3 THEN
    -- Comamnds-3
ELSIF Your_Condition4 THEN
    -- Comamnds-4
ELSE
    -- Comamnds-5
END IF;

IF Your_Condition1 THEN

-- Commands-1

ELSIF Your_Condition2 THEN

-- Comamnds-2

ELSIF Your_Condition3 THEN

-- Comamnds-3

ELSIF Your_Condition4 THEN

-- Comamnds-4

ELSE

-- Comamnds-5

END IF;

Sample ELSIF Condition Usage is as follows;

SET SERVEROUTPUT ON;
DECLARE
  v_number PLS_INTEGER := 7;
BEGIN
    IF v_number = 0 THEN
        DBMS_OUTPUT.put_line('zero');
    ELSIF v_number = 1 THEN
        DBMS_OUTPUT.put_line('one');
    ELSIF v_number = 2 THEN
        DBMS_OUTPUT.put_line('two');
    ELSIF v_number = 3 THEN
        DBMS_OUTPUT.put_line('three');
    ELSIF v_number = 4 THEN
        DBMS_OUTPUT.put_line('four');
    ELSE
        DBMS_OUTPUT.put_line('unknown number.');
    END IF;
END;

SET SERVEROUTPUT ON;

DECLARE

v_number PLS_INTEGER := 7;

BEGIN

IF v_number = 0 THEN

DBMS_OUTPUT.put_line('zero');

ELSIF v_number = 1 THEN

DBMS_OUTPUT.put_line('one');

ELSIF v_number = 2 THEN

DBMS_OUTPUT.put_line('two');

ELSIF v_number = 3 THEN

DBMS_OUTPUT.put_line('three');

ELSIF v_number = 4 THEN

DBMS_OUTPUT.put_line('four');

ELSE

DBMS_OUTPUT.put_line('unknown number.');

END IF;

END;

CASE Statement in Oracle PL/SQL

IF AND ELSIF cause multiple operators to be used for a value.

The CASE keyword is used to check the result of a single value.

CASE v_variable
    WHEN 'value-1' THEN
        -- commands-1
    WHEN 'value-2' THEN
        -- commands-2
    ELSE
        -- commands-3
END CASE;

CASE v_variable

WHEN 'value-1' THEN

-- commands-1

WHEN 'value-2' THEN

-- commands-2

ELSE

-- commands-3

END CASE;

Sample CASE Condition Usage is as follows;

SET SERVEROUTPUT ON;
DECLARE
  v_number PLS_INTEGER := 7;
BEGIN
    CASE v_number
    WHEN 0 THEN
        DBMS_OUTPUT.put_line('zero');
    WHEN 1 THEN
        DBMS_OUTPUT.put_line('one');
    WHEN 2 THEN
        DBMS_OUTPUT.put_line('two');
    WHEN 3 THEN
        DBMS_OUTPUT.put_line('three');
    WHEN 4 THEN
        DBMS_OUTPUT.put_line('four');
    ELSE
        DBMS_OUTPUT.put_line('unknown number.');
    END CASE;
END;

SET SERVEROUTPUT ON;

DECLARE

v_number PLS_INTEGER := 7;

BEGIN

CASE v_number

WHEN 0 THEN

DBMS_OUTPUT.put_line('zero');

WHEN 1 THEN

DBMS_OUTPUT.put_line('one');

WHEN 2 THEN

DBMS_OUTPUT.put_line('two');

WHEN 3 THEN

DBMS_OUTPUT.put_line('three');

WHEN 4 THEN

DBMS_OUTPUT.put_line('four');

ELSE

DBMS_OUTPUT.put_line('unknown number.');

END CASE;

END;

You see that less operators are used compared to the previous ELSIF example.

The CASE condition can also be used with operators.

CASE 
    WHEN Your_Condition_1 THEN
        -- commands-1
    WHEN Your_Condition_2 THEN
        -- commands-2
    WHEN Your_Condition_3 THEN
        -- commands-3
    ELSE
        -- commands-4
END CASE;

CASE

WHEN Your_Condition_1 THEN

-- commands-1

WHEN Your_Condition_2 THEN

-- commands-2

WHEN Your_Condition_3 THEN

-- commands-3

ELSE

-- commands-4

END CASE;

Sample usage is as follows;

SET SERVEROUTPUT ON;
DECLARE
  v_number PLS_INTEGER := 3;
BEGIN
    CASE
    WHEN v_number = 0 THEN
        DBMS_OUTPUT.put_line('zero');
    WHEN v_number = 1 THEN
        DBMS_OUTPUT.put_line('one');
    WHEN v_number = 2 THEN
        DBMS_OUTPUT.put_line('two');
    WHEN v_number = 3 THEN
        DBMS_OUTPUT.put_line('three');
    WHEN v_number = 4 THEN
        DBMS_OUTPUT.put_line('four');
    ELSE
        DBMS_OUTPUT.put_line('unknown number.');
    END CASE;
END;

SET SERVEROUTPUT ON;

DECLARE

v_number PLS_INTEGER := 3;

BEGIN

CASE

WHEN v_number = 0 THEN

DBMS_OUTPUT.put_line('zero');

WHEN v_number = 1 THEN

DBMS_OUTPUT.put_line('one');

WHEN v_number = 2 THEN

DBMS_OUTPUT.put_line('two');

WHEN v_number = 3 THEN

DBMS_OUTPUT.put_line('three');

WHEN v_number = 4 THEN

DBMS_OUTPUT.put_line('four');

ELSE

DBMS_OUTPUT.put_line('unknown number.');

END CASE;

END;

Conditional expressions can also be used in loops.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

You can find more information about Conditinal Statement at docs.oracle.com