Oracle PL/SQL by Example, Fifth Edition (2015)
Chapter 4. Conditional Control: IF Statements
In this chapter, you will learn about
IF Statements
ELSIF Statements
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
Use IF-THEN Statements
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.
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.
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
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.
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:
IF must always be matched with END IF.
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.
There is no second “E” in ELSIF.
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
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.