- Lab 4.1 IF Statements
- Lab 4.2 ELSIF Statements
- Lab 4.3 Nested IF Statements
This chapter is from the book
This chapter is from the book
This chapter is from the book
Lab 4.3 Nested IF Statements
LAB OBJECTIVES
After completing this lab, you will be able to
- Use nested IF statements
You have encountered different types of conditional controls: the IF-THEN statement, the IF-THEN-ELSE statement, and the ELSIF statement. These types of conditional controls can be nested inside one another. For example, an IF statement can be nested inside an ELSIF, and vice versa. Consider the following:
For Example
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 to –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 that the example produces:
Enter value for sv_num1: -4 old 2: v_num1 NUMBER := &sv_num1; new 2: v_num1 NUMBER := -4; Enter value for sv_num2: 3 old 3: v_num2 NUMBER := &sv_num2; new 3: v_num2 NUMBER := 3; ELSE part of the outer IF Inner IF v_total = 1 PL/SQL procedure successfully completed.
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 test 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 such a need exists.
For Example
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 the 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. It is also important to understand the purpose of the parentheses. In this example, they are only used to improve readability, because the AND operator takes precedence over the OR operator.
When the symbol ? is entered at runtime, this example produces the following output:
Enter value for sv_letter: ? old 2: v_letter CHAR(1) := '&sv_letter'; new 2: v_letter CHAR(1) := '?'; This is not a letter This is not a number PL/SQL procedure successfully completed.
Lab 4.3 Exercises
This section provides exercises and suggested answers, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers and what the effects are of any different answers you may come up with.
4.3.1 Use Nested IF Statements
In this exercise, you use nested IF statements. This script converts the value of a temperature from one system to another. If the temperature is supplied in Fahrenheit, it is converted to Celsius, and vice versa.
Create the following PL/SQL script:
-- ch04_4a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
v_temp_in NUMBER := &sv_temp_in;
v_scale_in CHAR := '&sv_scale_in';
v_temp_out NUMBER;
v_scale_out CHAR;
BEGIN
IF v_scale_in != 'C' AND v_scale_in != 'F' THEN
DBMS_OUTPUT.PUT_LINE ('This is not a valid scale');
ELSE
IF v_scale_in = 'C' THEN
v_temp_out := ( (9 * v_temp_in) / 5 ) + 32;
v_scale_out := 'F';
ELSE
v_temp_out := ( (v_temp_in - 32) * 5 ) / 9;
v_scale_out := 'C';
END IF;
DBMS_OUTPUT.PUT_LINE ('New scale is: '||v_scale_out);
DBMS_OUTPUT.PUT_LINE ('New temperature is: '||v_temp_out);
END IF;
END;
Execute the script, and then answer the following questions:
-
What output is printed on the screen if the value of 100 is entered for the temperature, and the letter "C" is entered for the scale?
ANSWER: The output should look like the following:
Enter value for sv_temp_in: 100 old 2: v_temp_in NUMBER := &sv_temp_in; new 2: v_temp_in NUMBER := 100; Enter value for sv_scale_in: C old 3: v_scale_in CHAR := '&sv_scale_in'; new 3: v_scale_in CHAR := 'C'; New scale is: F New temperature is: 212 PL/SQL procedure successfully completed.
After the values for v_temp_in and v_scale_in have been entered, the condition
v_scale_in != 'C' AND v_scale_in != 'F'
of the outer IF statement evaluates to FALSE, and control is passed to the ELSE part of the outer IF statement. Next, the condition
v_scale_in = 'C'
of the inner IF statement evaluates to TRUE, and the values of the variables v_temp_out and v_scale_out are calculated. Control is then passed back to the outer IF statement, and the new value for the temperature and the scale are displayed on the screen.
-
Try to run this script without providing a value for the temperature. What message is displayed on the screen? Why?
ANSWER: If the value for the temperature is not entered, the script does not compile.
The compiler tries to assign a value to v_temp_in with the help of the substitution variable. Because the value for v_temp_in has not been entered, the assignment statement fails, and the following error message is displayed:
Enter value for sv_temp_in: old 2: v_temp_in NUMBER := &sv_temp_in; new 2: v_temp_in NUMBER := ; Enter value for sv_scale_in: C old 3: v_scale_in CHAR := '&sv_scale_in'; new 3: v_scale_in CHAR := 'C'; v_temp_in NUMBER := ; * ERROR at line 2: ORA-06550: line 2, column 27: PLS-00103: Encountered the symbol ";" when expecting one of the following: ( - + mod not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance cast <a string literal with character set specification> <a number> <a single-quoted SQL string> The symbol "null" was substituted for ";" to continue.You have probably noticed that even though the mistake seems small and insignificant, the error message is fairly long and confusing.
-
Try to run this script providing an invalid letter for the temperature scale, such as "V." What message is displayed on the screen, and why?
ANSWER: If an invalid letter is entered for the scale, the message This is not a valid scale is displayed on the screen.
The condition of the outer IF statement evaluates to TRUE. As a result, the inner IF statement is not executed, and the message This is not a valid scale is displayed on the screen.
Assume that letter "V" was typed by mistake. This example produces the following output:
Enter value for sv_temp_in: 45 old 2: v_temp_in NUMBER := &sv_temp_in; new 2: v_temp_in NUMBER := 45; Enter value for sv_scale_in: V old 3: v_scale_in CHAR := '&sv_scale_in'; new 3: v_scale_in CHAR := 'V'; This is not a valid scale PL/SQL procedure successfully completed.
-
Rewrite this script so that if an invalid letter is entered for the scale, v_temp_out is initialized to 0 and v_scale_out is initialized to C.
ANSWER: The script should look similar to the following. Changes are shown in bold. Notice that the two final DBMS_OUTPUT.PUT_LINE statements have been moved from the body of the outer IF statement.
-- ch04_4b.sql, version 2.0 DECLARE v_temp_in NUMBER := &sv_temp_in; v_scale_in CHAR := '&sv_scale_in'; v_temp_out NUMBER; v_scale_out CHAR; BEGIN IF v_scale_in != 'C' AND v_scale_in != 'F' THEN DBMS_OUTPUT.PUT_LINE ('This is not a valid scale'); v_temp_out := 0; v_scale_out := 'C'; ELSE IF v_scale_in = 'C' THEN v_temp_out := ( (9 * v_temp_in) / 5 ) + 32; v_scale_out := 'F'; ELSE v_temp_out := ( (v_temp_in - 32) * 5 ) / 9; v_scale_out := 'C'; END IF; END IF; DBMS_OUTPUT.PUT_LINE ('New scale is: '||v_scale_out); DBMS_OUTPUT.PUT_LINE ('New temperature is: '||v_temp_out); END;The preceding script produces the following output:
Enter value for sv_temp_in: 100 old 2: v_temp_in NUMBER := &sv_temp_in; new 2: v_temp_in NUMBER := 100; Enter value for sv_scale_in: V old 3: v_scale_in CHAR := '&sv_scale_in'; new 3: v_scale_in CHAR := 'V'; This is not a valid scale. New scale is: C New temperature is: 0 PL/SQL procedure successfully completed.
Try it Yourself
In this chapter you've learned about different types of IF statements. You've also learned that all these different IF statements can be nested inside one another. Here are some exercises that will help you test the depth of your understanding:
-
Rewrite ch04_1a.sql. Instead of getting information from the user for the variable v_date, define its value with the help of the function SYSDATE. After it has been determined that a certain day falls on the weekend, check to see if the time is before or after noon. Display the time of day together with the day.
-
Create a new script. For a given instructor, determine how many sections he or she is teaching. If the number is greater than or equal to 3, display a message saying that the instructor needs a vacation. Otherwise, display a message saying how many sections this instructor is teaching.
-
Execute the following two PL/SQL blocks, and explain why they produce different output for the same value of the variable v_num. Remember to issue the SET SERVEROUTPUT ON command before running this script.
-- Block 1 DECLARE v_num NUMBER := NULL; BEGIN IF v_num > 0 THEN DBMS_OUTPUT.PUT_LINE ('v_num is greater than 0'); ELSE DBMS_OUTPUT.PUT_LINE ('v_num is not greater than 0'); END IF; END; -- Block 2 DECLARE v_num NUMBER := NULL; BEGIN IF v_num > 0 THEN DBMS_OUTPUT.PUT_LINE ('v_num is greater than 0'); END IF; IF NOT (v_num > 0) THEN DBMS_OUTPUT.PUT_LINE ('v_num is not greater than 0'); END IF; END;
The projects in this section are meant to have you use all the skills you have acquired throughout this chapter. The answers to these projects can be found in Appendix D and on this book's companion Web site. Visit the Web site periodically to share and discuss your answers.