Oracle PL/SQL by Example, Fifth Edition (2015)

Chapter 4. Conditional Control: IF Statements


In this chapter, you will learn about

Image IF Statements

Image ELSIF Statements

Image Nested IF Statements


In almost every program that you write, you need to make decisions. For example, if it is the end of the fiscal year, bonuses must be distributed to the employees based on their salaries. To compute employee bonuses, a program needs to have a conditional control. In other words, it needs to employ a selection structure.

Conditional control allows you to control the flow of the execution of the program based on a condition. In programming terms, it means that the statements in the program are not executed sequentially. Rather, one group of statements or another will be executed depending on how the condition is evaluated.

In PL/SQL, there are three types of conditional control: IF, ELSIF, and CASE statements. In this chapter, you will explore two types of conditional control—IF and ELSIF—and learn how these types can be nested inside of each other. CASE statements are discussed in Chapter 5.

Lab 4.1: IF Statements


After this lab, you will be able to

Image Use IF-THEN Statements

Image Use IF-THEN-ELSE Statements


An IF statement has two forms: IF-THEN and IF-THEN-ELSE. An IF-THEN statement allows you to specify only one group of actions to take. In other words, this group of actions is taken only when a condition evaluates to TRUE. An IF-THEN-ELSE statement allows you to specify two groups of actions, and the second group of actions is taken when a condition evaluates to FALSE or NULL.

IF-THEN Statements

An IF-THEN statement is the most basic kind of a conditional control and has the structure shown in Listing 4.1.

Listing 4.1 IF-THEN Statement Structure

IF CONDITION
THEN
  STATEMENT 1;
  ...
  STATEMENT N;
END IF;

The reserved word IF marks the beginning of the IF statement. Statements 1 through N are a sequence of executable statements that consist of one or more of the standard programming structures. The CONDITION between the keywords IF and THEN determines whether these statements are executed. END IF is a reserved phrase that indicates the end of the IF-THEN construct. This flow of the logic is illustrated in Figure 4.1.

Image

Figure 4.1 IF-THEN Statement

When an IF-THEN statement is executed, a condition is evaluated to either TRUE or FALSE. If the condition evaluates to TRUE, control passes to the first executable statement of the IF-THEN construct. If the condition evaluates to FALSE, control passes to the first executable statement after the END IF statement.

Consider the following example. You have two numeric values stored in the variables v_num1 and v_num2. You need to arrange these values so that the smaller value is always stored in v_num1, and the larger value is always stored in the v_num2.

For Example  ch04_1a.sql

DECLARE
  v_num1 NUMBER := 5;
  v_num2 NUMBER := 3;
  v_temp NUMBER;
BEGIN
  -- if v_num1 is greater than v_num2 rearrange their values
  IF v_num1 > v_num2
  THEN
    v_temp := v_num1;
    v_num1 := v_num2;
    v_num2 := v_temp;
  END IF;

  -- display the values of v_num1 and v_num2
  DBMS_OUTPUT.PUT_LINE ('v_num1 = '||v_num1);
  DBMS_OUTPUT.PUT_LINE ('v_num2 = '||v_num2);
END;

In this example, condition

v_num1 > v_num2

evaluates to TRUE because 5 is greater than 3. Next, the values are rearranged so that 3 is assigned to v_num1 and 5 is assigned to v_num2. This step is done with the help of the third variable, v_temp, which is used for temporary storage.

This example produces the following output:

v_num1 = 3
v_num2 = 5

IF-THEN-ELSE Statement

An IF-THEN statement specifies the sequence of statements to execute only if the condition evaluates to TRUE. When this condition evaluates to FALSE or NULL, there is no special action to take except to proceed with execution of the program.

An IF-THEN-ELSE statement enables you to specify two groups of statements. One group of statements is executed when the condition evaluates to TRUE. Another group of statements is executed when the condition evaluates to FALSE or NULL. This structure is shown in Listing 4.2.

Listing 4.2 IF-THEN-ELSE Statement Structure

IF CONDITION
THEN
  STATEMENT 1;
ELSE
  STATEMENT 2;
END IF;
STATEMENT 3;

When CONDITION evaluates to TRUE, control is passed to STATEMENT 1; when CONDITION evaluates to FALSE or NULL, control is passed to STATEMENT 2. After the IF-THEN-ELSE construct has completed, STATEMENT 3 is executed. This flow of the logic is illustrated in Figure 4.2.

Image

Figure 4.2 IF-THEN-ELSE Statement


Did You Know?

The IF-THEN-ELSE construct should be used when trying to choose between two mutually exclusive actions. Consider the following example:

DECLARE
  v_num NUMBER := &sv_user_num;
BEGIN
  -- test if the number provided by the user is even
  IF MOD(v_num,2) = 0
  THEN
    DBMS_OUTPUT.PUT_LINE (v_num||' is even number');
  ELSE
    DBMS_OUTPUT.PUT_LINE (v_num||' is odd number');
  END IF;
END;

For any given number of DBMS_OUTPUT.PUT_LINE statements, only one is executed. Hence, the IF-THEN-ELSE construct enables you to specify two and only two mutually exclusive actions.

When run, this example produces the following output:

24 is even number


Null Condition

In some cases, a condition used in an IF statement may evaluate to NULL instead of TRUE or FALSE. For the IF-THEN construct, the statements associated with the construct will not be executed if an associated condition evaluates to NULL. Instead, control of the execution will pass to the first executable statement after END IF. For the IF-THEN-ELSE construct, the statements specified after the keyword ELSE will be executed if an associated condition evaluates to NULL.

For Example  ch04_2a.sql

DECLARE
  v_num1 NUMBER := 0;
  v_num2 NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Before IF statement...');
  IF v_num1 = v_num2
  THEN
    DBMS_OUTPUT.PUT_LINE ('v_num1 = v_num2');
  END IF;
  DBMS_OUTPUT.PUT_LINE ('After IF statement...');
END;

This example produces the following output:

Before IF statement...
After IF statement...

The condition

v_num1 = v_num2

evaluates to NULL because variable v_num2 is not assigned a value; therefore, it remains NULL. Notice that the IF-THEN construct behaves as if the condition evaluated to FALSE. In other words, the DBMS_OUTPUT.PUT_LINE statement associated with the IF-THEN construct does not execute.

Next, consider a similar example that employs the IF-THEN-ELSE construct (the newly added statements are shown in bold).

For Example  ch04_2b.sql

DECLARE
  v_num1 NUMBER := 0;
  v_num2 NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Before IF statement...');
  IF v_num1 = v_num2
  THEN
    DBMS_OUTPUT.PUT_LINE ('v_num1 = v_num2');
  ELSE
    DBMS_OUTPUT.PUT_LINE ('v_num1 != v_num2');
  END IF;
  DBMS_OUTPUT.PUT_LINE ('After IF statement...');
END;

This example produces the following output:

Before IF statement...
v_num1 != v_num2
After IF statement...

Similarly, the condition

v_num1 = v_num2

evaluates to NULL, and the ELSE portion of the IF-THEN-ELSE construct is executed.

Lab 4.2: ELSIF Statements


After this lab, you will be able to

Image Use the ELSIF Statement


An ELSIF statement has the structure shown in Listing 4.3.

Listing 4.3 ELSIF Statement Structure

IF CONDITION 1
THEN
  STATEMENT 1;
ELSIF CONDITION 2
THEN
  STATEMENT 2;
ELSIF CONDITION 3
THEN
  STATEMENT 3;
...
ELSE
  STATEMENT N;
END IF;

The reserved word IF marks the beginning of an ELSIF construct. The words CONDITION 1 through CONDITION N are a sequence of the conditions that evaluate to TRUE or FALSE. These conditions are mutually exclusive. In other words, if CONDITION 1 evaluates to TRUE,STATEMENT 1 is executed and control passes to the first executable statement after the reserved phrase END IF. The rest of the ELSIF construct is ignored. When CONDITION 1 evaluates to FALSE, control passes to the ELSIF part and CONDITION 2 is evaluated, and so forth. If none of the specified conditions evaluates as TRUE, control passes to the ELSE part of the ELSIF construct. An ELSIF statement can contain any number of ELSIF clauses. This flow of the logic is illustrated in Figure 4.3.

Image

Figure 4.3 ELSIF Statement

Figure 4.3 shows that if condition 1 evaluates to TRUE, statement 1 is executed and control passes to the first statement after END IF. If condition 1 evaluates to FALSE, control passes to condition 2. If condition 2 evaluates to TRUE, statement 2 is executed. Otherwise, control passes to the statement following END IF, and so forth. Consider the following example.

For Example  ch04_3a.sql

DECLARE
  v_num NUMBER := &sv_num;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Before IF statement...');
  IF v_num < 0
  THEN
    DBMS_OUTPUT.PUT_LINE (v_num||' is a negative number');
  ELSIF v_num = 0
  THEN
    DBMS_OUTPUT.PUT_LINE (v_num||' is equal to zero');
  ELSE
    DBMS_OUTPUT.PUT_LINE (v_num||' is a positive number');
  END IF;
  DBMS_OUTPUT.PUT_LINE ('After IF statement...');
END;

The value of the variable v_num is provided at run time and evaluated with the help of the ELSIF statement. If the value of v_num is less than 0, the first DBMS_OUTPUT.PUT_LINE statement executes, and the ELSIF construct terminates. If the value of v_num is greater than 0, both conditions

v_num < 0

and

v_num = 0

evaluate to FALSE, and the ELSE part of the ELSIF construct executes.

Assume that the value of the variable v_num equals 5 at run time. This example produces the following output:

Before IF statement...
5 is a positive number
After IF statement...


Did You Know?

For an ELSIF statement:

Image IF must always be matched with END IF.

Image There must be a space between END and IF. When the space is omitted, the compiler produces the following error:

ORA-06550: line 13, column 4:
PLS-00103: Encountered the symbol ";" when expecting one of the following: if

As you can see, this error message is not very clear, and it can take you some time to correct it, especially if you have not encountered it before.

Image There is no second “E” in ELSIF.

Image Conditions of an ELSIF statement must be mutually exclusive. These conditions are evaluated in sequential order, from the first to the last. Once a condition evaluates to TRUE, the remaining conditions of the ELSIF statement are not evaluated at all. Consider this example of an ELSIF construct:

IF v_num >= 0
THEN
  DBMS_OUTPUT.PUT_LINE ('v_num is greater than 0');
ELSIF v_num =< 10
THEN
  DBMS_OUTPUT.PUT_LINE ('v_num is less than 10');
ELSE
  DBMS_OUTPUT.PUT_LINE ('v_num is less than ? or greater than ?');
END IF;

Assume that the value of v_num is equal to 5. Both conditions of the ELSIF statement can evaluate to TRUE because 5 is greater than 0, and 5 is less than 10. However, once the first condition, v_num >= 0, evaluates to TRUE, the rest of the ELSIF construct is ignored.

For any value of v_num that is greater than or equal to 0 and less than or equal to 10, these conditions are not mutually exclusive. Therefore, the DBMS_OUTPUT.PUT_LINE statement associated with the ELSIF clause will not execute for any such value of v_num. For the second condition, v_num <= 10, to evaluate as TRUE, the value of v_num must be less than 0.

How would you rewrite this ELSIF construct to capture any value of v_num between 0 and 10 and display it on the screen with a single condition?


When using an ELSIF construct, it is not necessary to specify which action should be taken if none of the conditions evaluates to TRUE. In other words, an ELSE clause is not required in the ELSIF construct. Consider the following example:

For Example  ch04_3b.sql

DECLARE
  v_num NUMBER := &sv_num;
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Before IF statement...');
  IF v_num < 0
  THEN
    DBMS_OUTPUT.PUT_LINE (v_num||' is a negative number');
  ELSIF v_num > 0
  THEN
    DBMS_OUTPUT.PUT_LINE (v_num||' is a positive number');
  END IF;
  DBMS_OUTPUT.PUT_LINE ('After IF statement...');
END;

As you can see, there is no action specified when v_num is equal to 0. If the value of v_num is equal to 0, both conditions will evaluate to FALSE, and the ELSIF statement will not execute at all. When a value of zero is specified for v_num, this example produces the following output:

Before IF statement...
After IF statement...


Did You Know?

You probably noticed that for all IF statement examples, the reserved words IF, ELSIF, ELSE, and END IF are entered on a separate line and aligned with the word IF. In addition, all executable statements in the IF construct are indented. The format of the IF construct makes no difference to the compiler, but the meaning of the formatted IF construct becomes obvious to us with this style.

The IF-THEN-ELSE statement

IF x = y THEN v_txt := 'YES'; ELSE v_txt := 'NO'; END IF;

is equivalent to

IF x = y
THEN
  v_txt := 'YES';
ELSE
  v_txt := 'NO';
END IF;

The formatted version of the IF construct is easier to read and understand.


Lab 4.3: Nested IF Statements


After this lab, you will be able to

Image Use Nested IF Statements


You have encountered different types of conditional controls: IF-THEN statement, IF-THEN-ELSE statement, and ELSIF statement. These types of conditional controls can be nested inside of one another—for example, an IF statement can be nested inside an ELSIF, and vice versa. Consider the following example:

For Example  ch04_4a.sql

DECLARE
  v_num1  NUMBER := &sv_num1;
  v_num2  NUMBER := &sv_num2;
  v_total NUMBER;
BEGIN
  IF v_num1 > v_num2
  THEN
    DBMS_OUTPUT.PUT_LINE ('IF part of the outer IF');
    v_total := v_num1 - v_num2;
  ELSE
    DBMS_OUTPUT.PUT_LINE ('ELSE part of the outer IF');
    v_total := v_num1 + v_num2;

    IF v_total < 0
    THEN
      DBMS_OUTPUT.PUT_LINE ('Inner IF');
      v_total := v_total * (-1);
    END IF;
  END IF;
  DBMS_OUTPUT.PUT_LINE ('v_total = '||v_total);
END;

The IF-THEN-ELSE statement is called an outer IF statement because it encompasses the IF-THEN statement (shown in bold). The IF-THEN statement is called an inner IF statement because it is enclosed by the body of the IF-THEN-ELSE statement.

Assume that the values for v_num1 and v_num2 are –4 and 3, respectively. First, the condition

v_num1 > v_num2

of the outer IF statement is evaluated. Because –4 is not greater than 3, the ELSE part of the outer IF statement is executed. As a result, the message

ELSE part of the outer IF

is displayed, and the value of v_total is calculated. Next, the condition

v_total < 0

of the inner IF statement is evaluated. Because that value of v_total is equal –l, the condition yields TRUE, and the message

Inner IF

is displayed. Next, the value of v_total is calculated again. This logic is demonstrated by the output produced by the example:

ELSE part of the outer IF
Inner IF
v_total = 1

Logical Operators

So far in this chapter, you have seen examples of different IF statements. All of these examples used test operators, such as >, <, and =, to evaluate a condition. Logical operators can be used to evaluate a condition as well. In addition, they allow a programmer to combine multiple conditions into a single condition if there is such a need.

For Example  ch04_5a.sql

DECLARE
  v_letter CHAR(1) := '&sv_letter';
BEGIN
  IF (v_letter >= 'A' AND v_letter <= 'Z') OR
     (v_letter >= 'a' AND v_letter <= 'z')
  THEN
     DBMS_OUTPUT.PUT_LINE ('This is a letter');
  ELSE
     DBMS_OUTPUT.PUT_LINE ('This is not a letter');
     IF v_letter BETWEEN '0' and '9'
     THEN
       DBMS_OUTPUT.PUT_LINE ('This is a number');
     ELSE
       DBMS_OUTPUT.PUT_LINE ('This is not a number');
     END IF;
  END IF;
END;

In this example, the condition

(v_letter >= 'A' AND v_letter <= 'Z') OR
(v_letter >= 'a' AND v_letter <= 'z')

uses logical operators AND and OR. Two conditions

(v_letter >= 'A' AND v_letter <= 'Z')

and

(v_letter >= 'a' AND v_letter <= 'z')

are combined into one with the help of the OR operator. Notice the purposes of the parentheses. In this example, they are used to improve readability only, because the operator AND takes precedence over the operator OR.

When the symbol “?” is entered at run time, this example produces the following output:

This is not a letter
This is not a number


Did You Know?

You can nest IF statements to any depth level up to maximum length of a PL/SQL block, and blocks themselves may be nested 255 levels deep. Consider the following example, where IF statements are nested inside each other four levels deep:

DECLARE
  v_var1 PLS_INTEGER := 100;
  v_var2 PLS_INTEGER := 200;
  v_var3 PLS_INTEGER := 300;
  v_var4 PLS_INTEGER := 400;
BEGIN
  IF v_var1 >= 100
  THEN
    IF v_var2 >= 200
    THEN
      IF v_var3 >= 300
      THEN
        IF v_var4 >= 400
        THEN
          DBMS_OUTPUT.PUT_LINE
            ('v_var1 = '||v_var1||', v_var2 = '||v_var2||
             ', v_var3 = '||v_var3||', v_var4 = '||v_var4);
        END IF;
      END IF;
    END IF;
  END IF;
END;


While this script is very simple and does not accomplish much, such deep nesting of IF statements is much more difficult to follow and may become very complex very quickly when implementing complex business solutions.

In this example, the four nested IF statements could be restructured as a single IF statement by combining these conditions with the AND operator:

IF v_var1 >= 100 AND v_var2 >= 200 and v_var3 >= 300 AND v_var4 >= 400
THEN
  ...
END IF;

Summary

In the chapter, you explored different types of IF statements and saw how they can be nested inside one another. You also learned how to employ logical operators when combining multiple distinct conditions into one unified condition for the purpose of evaluation. Conditional control structures are supported by almost every programming language; while the syntax may vary, the manner in which they are used remains unchanged.

In the next chapter, you will continue to learn about conditional control via CASE statements and CASE expressions. In addition, you will learn about the NULLIF and COALESCE functions that are supported by the SQL and PL/SQL languages.


By the Way

The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.