Oracle PL/SQL: Conditional Statements

I’ve written blog posts over time that address common questions about PLSQL fundamentals online such as in forums. It’s become a really useful exercise to have posts about key fundamentals as reference articles. So, I’m going to keep that going, continuing here. There are a few fundamentals posts in the series now, so I will link them below:

Oracle PL/SQL Conditional Statements
IF Statement
- IF Statement: Syntax
- IF Statement: Example
IF..ELSE Statement
- IF..ELSE Statement: Syntax
- IF..ELSE Statement: Example
IF..ELSIF..ELSE Statement
- IF..ELSIF..ELSE Statement: Syntax
- IF..ELSIF..ELSE Statement: Example
Nested IF Statement
- Nested IF Statement: Syntax
- Nested IF Statement: Example
CASE Statement
- CASE Statement: Syntax
- CASE Statement: Example

Oracle PL/SQL Conditional Statements

In PL/SQL, conditional statements allow developers to control the flow of code by executing specific parts of logic based upon certain criteria, or conditions. Conditional logic is another fundamental building block. In this post, I will cover the various PL/SQL conditional statements in detail with the help of examples to illustrate how to use each conditional statement.

For the examples in this post, I will use the widely known “hr schema”. The entity-relationship diagram is as below.

Conditional statements, execute PL/SQL logic when specific conditions are met. Ultimately, these are decision-making statements that determine what the next step to be executed will be. Conditional statements available in PL/SQL are defined below:

IF – Evaluates a single condition and executes a code block only if that condition is TRUE.
IF...ELSE – Evaluates a condition and executes a code block only if that condition is TRUE but executes a different code block if the condition is FALSE.
IF...ELSIF...ELSE – allows multiple conditions to be evaluated sequentially to determine which code block should be executed. The first condition that evaluates to be TRUE will have its code block executed. If no conditions evaluate to be TRUE, the ELSE code block is executed.
Nested IF – evaluates conditions within the evaluation of another condition
CASE – compares a single expression against many possible values and executes the associated code block for the first condition that evaluates as true within the sequence.

IF Statement

Syntax

An IF statement is the most simple decision-making statement that can be used in PL/SQL. It evaluates a single condition and executes a code block only if that condition is TRUE. When the condition evaluates to be FALSE, nothing happens.

			
IF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
END IF;

Example

The following example uses a basic PL/SQL block to evaluate the value of a variable.

			
DECLARE
  --Declare a variable and assign a value
  v_salary NUMBER := 10000;
  
BEGIN
  -- Open an if conditional statement that checks if the value of v_salary is more than 5000
  IF v_salary > 5000 THEN 
    -- Code block that you wish to execute when the condition evaluates to be TRUE
    dbms_output.put_line("Salary is greater than 5000"); -- output a message indicating that the code block executes
  -- close the if conditional statement  
  END IF;
END;

		

An explanation of this example is below:

I have declared a variable of type number with name v_salary. It is intialised with the value 10000
I used a basic IF statement to open a conditional statement. The statement checks if the value of v_salary is more than 5000.
If the condition evaluates as TRUE (which in this example it does since 10000 is more than 5000), an output is written.

The output is as per the following screenshot:

See what happens when I change the assignment of the v_salary variable to be 1000 (no output is written):

IF..ELSE Statement

Syntax

An IF..ELSE statement extends a basic IF statement by including an alternative path of code execution in the event that the IF condition evaluates to be FALSE. It evaluates a single condition and executes a code block only if that condition is TRUE. When the condition evaluates to be FALSE, a different code block is executed.

			
IF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
ELSE
  -- Code block that you wish to execute when the condition evaluates to be FALSE
END IF;

		

Example

The following example uses a basic PL/SQL block to evaluate the value of a variable.

			
DECLARE
  --Declare a variable and assign a value
  v_salary NUMBER := 2000;
  
BEGIN
  -- Open an if conditional statement that checks if the value of v_salary is more than 5000
  IF v_salary > 5000 THEN 
    -- Code block that you wish to execute when the condition evaluates to be TRUE
    dbms_output.put_line('High Salary'); -- output a message indicating that the code block executes
  ELSE
    dbms_output.put_line('Low Salary'); -- output a message indicating that the code block executes
  -- close the if conditional statement  
  END IF;
END;

		

An explanation of this example is below:

I have declared a variable of type number with name v_salary. It is intialised with the value 2000
I used an IF..ELSE statement to open a conditional statement. The statement checks if the value of v_salary is more than 5000.
If the condition evaluates as TRUE (which in this example, it does not, since 2000 is less than 5000), an output is written.
If the condition evaluates as FALSE (which in this example, it does, since 2000 is less than 5000), an output is written.

The output is as per the following screenshot:

See what happens when I change the assignment of the v_salary variable to be 10000 again (a different output is written):

IF..ELSIF..ELSE Statement

Syntax

An IF..ELSIF..ELSE statement can be used for more complex logic where multiple conditions must be evaluated to determine which code block will execute. It evaluates a single condition at a time and executes it’s associated code block only if that condition is TRUE. When the condition evaluates to be FALSE, a different code block is executed.

			
IF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
ELSIF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
ELSE 
  -- Code block that you wish to execute when all conditions evaluate to be FALSE
END IF;

		

It is worth noting that IF..ELSIF can also exist without the inclusion of an ELSE. In this case, no associated code block would execute when all tested conditions evaluate to be FALSE. It looks like this:

			
IF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
ELSIF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
END IF;

		

Lastly of note, you can use as many ELSIF statements in your logic. So, you can even evaluate conditions like the below:

			
IF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
ELSIF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
ELSIF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
ELSIF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
ELSIF condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
ELSE
  -- Code block that you wish to execute when all conditions evaluate to be FALSE
END IF;

		

Example

The following example uses a basic PL/SQL block to evaluate the value of a variable.

			
DECLARE
  --Declare a variable and assign a value
  v_salary NUMBER := 4000;
  
BEGIN
  -- Open an if conditional statement that checks if the value of v_salary is less than 3000
  IF v_salary < 3000 THEN 
    -- Code block that you wish to execute when the condition evaluates to be TRUE
    dbms_output.put_line('Low Salary'); -- output a message indicating that the code block executes
  ELSIF v_salary BETWEEN 3000 AND 6000 THEN 
    -- Code block that you wish to execute when the condition evaluates to be TRUE
    dbms_output.put_line('Medium Salary'); -- output a message indicating that the code block executes
  ELSE
    -- Code block that you wish to execute when all other conditions evaluate to be FALSE
    dbms_output.put_line('High Salary'); -- output a message indicating that the code block executes
  -- close the if conditional statement  
  END IF;
END;

		

An explanation of this example is below:

I have declared a variable of type number with name v_salary. It is intialised with the value 4000
I used an IF..ELSIF..ELSE statement to open a conditional statement. The statement checks:
- If the value of v_salary is less than 3000
- If the value of v_salary is between 3000 and 6000
- If the value of v_salary is anything else (aka, more than 6000)
At each point, an output is written than varies depending on which code block is executed. In this case, as 4000 is more than 3000 but less than 6000, the code block associated to ELSIF is executed

The output is as per the following screenshot:

See what happens when I change the assignment of v_salary to be 2000:

Now see what happens when I change the assignment of v_salary to be 10000:

Nested IF Statement

Syntax

A nested IF statement allows the ability to place IF statements inside other IF statements. code flow will enter a nested IF if the outer IF statement evaluates to be TRUE. If the outer IF statement evaluates to be false, the code block associated with the inner IF statement will be skipped

			
IF outer_condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
  IF inner_condition THEN
    -- Code block that you wish to execute when the condition evaluates to be TRUE
  END IF
END IF;

		

You can of course make this quite complicated, something like this say:

			
IF outer1_condition THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
  IF inner1_condition THEN
    -- Code block that you wish to execute when the condition evaluates to be TRUE
  ELSIF inner2_condition THEN
    -- Code block that you wish to execute when the condition evaluates to be TRUE
  ELSE
    -- Code block that you wish to execute when all the other inner conditions evaluate to be FALSE
  END IF;
  
ELSIF outer2_condition THEN 
  -- Code block that you wish to execute when the condition evaluates to be TRUE
  IF inner1_condition THEN
    -- Code block that you wish to execute when the condition evaluates to be TRUE
  ELSIF inner2_condition THEN
    -- Code block that you wish to execute when the condition evaluates to be TRUE
  ELSE
    -- Code block that you wish to execute when all the other inner conditions evaluate to be FALSE
  END IF;
  
ELSE
  -- code block to execute when all other outer conditions evaluate to be FALSE
END IF;

		

Example

The following example uses a basic PL/SQL block to evaluate the value of a variable.

			
DECLARE
  --Declare a variable and assign a value
  v_salary NUMBER := 5000;
  v_bonus  NUMBER := 1000;
  
BEGIN
  IF v_salary > 3000 THEN 
    -- Code block that you wish to execute when the condition evaluates to be TRUE
    IF v_bonus < 2000 THEN 
      -- Code block that you wish to execute when the condition evaluates to be TRUE
      dbms_output.put_line('Eligible for bonus'); -- output a message indicating that the code block executes
    ELSE 
      dbms_output.put_line('Not eligible for bonus'); -- output a message indicating that the code block executes
    END IF;
  END IF;
END;

		

An explanation of this example is below:

I have declared a variable of type number with name v_salary. It is intialised with the value 5000
I have declared a variable of type number with name v_bonus. It is initialised with the value 1000
I used an IF statement to open a conditional statement. The statement checks if the salary is more than 3000.
When the value of v_salary is more than 3000 (which in this case it is), a futher IF..ELSE statement is triggered.
The inner IF..ELSE statement checks if the bonus is less than 2000. If it is, an output is written noting eligibility for a bonus. If it isn’t, an output is written noting no eligibility for a bonus.

The output is as per the following screenshot:

See what happens when I change the value of the v_bonus variable to be 5000:

CASE Statement

Syntax

A CASE statement provides a cleaner way to evaluate multiple ELSIF conditions. It compares a single expression against many possible values and executes the associated code block for the first condition that evaluates as true within the sequence.

			
CASE expression 
  WHEN condition1 THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
  WHEN condition2 THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
  WHEN condition3 THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
  WHEN condition4 THEN
  -- Code block that you wish to execute when the condition evaluates to be TRUE
ELSE
  -- Code block that you wish to execute when the condition evaluates to be FALSE
END;

		

Example

The following example uses a basic PL/SQL block to evaluate the value of a variable.

			
DECLARE
  v_salary NUMBER := 5000;
  
BEGIN
  CASE v_salary
    WHEN 2500 THEN dbms_output.put_line('Low Salary');
    WHEN 5000 THEN dbms_output.put_line('Medium Salary');
    WHEN 7500 THEN dbms_output.put_line('High Salary');
    WHEN 10000 THEN dbms_output.put_line('Very High Salary');
  END CASE;
END;

		

An explanation of this example is below:

I have declared a variable of type number with name v_salary. It is intialised with the value 5000
I have opened a case statement that evaluates the following:
- If v_salary is equal to 2500, output a message noting ‘Low Salary’
- If v_salary is equal to 5000, output a message noting ‘Medium Salary’ (which in this case it does)
- If v_salary is equal to 7500, output a message noting ‘High Salary’
- If v_salary is equal to 1000, output a message noting ‘Very High Salary’

The output is as per the following screenshot:

Now, lets change to 2500:

Now, lets change to 7500:

Lastly, lets change to 10000:

AMY SIMPSON-GRANGE – BLOG

Oracle PL/SQL: Conditional Statements

Contents

Oracle PL/SQL Conditional Statements

IF Statement

Syntax

Example

IF..ELSE Statement

Syntax

Example

IF..ELSIF..ELSE Statement

Syntax

Example

Nested IF Statement

Syntax

Example

CASE Statement

Syntax

Example

Like this:

Related

Leave a ReplyCancel reply

Contents

Oracle PL/SQL Conditional Statements

IF Statement

Syntax

Example

IF..ELSE Statement

Syntax

Example

IF..ELSIF..ELSE Statement

Syntax

Example

Nested IF Statement

Syntax

Example

CASE Statement

Syntax

Example

Share this:

Like this:

Related

Leave a ReplyCancel reply

Discover more from AMY SIMPSON-GRANGE - BLOG