4. Conditional Control: 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.

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.

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.

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

Click here to view code image

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

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.

For Example  ch04_2a.sql

Click here to view code image

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

Click here to view code image

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.

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.

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

Click here to view code image

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...

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

Click here to view code image

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...

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

Click here to view code image

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

For Example  ch04_5a.sql

Click here to view code image

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

Click here to view code image

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

uses logical operators AND and OR. Two conditions

Click here to view code image

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

and

Click here to view code image

(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

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:

Click here to view code image

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

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.