- 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.2 ELSIF Statements
LAB OBJECTIVES
After completing this lab, you will be able to
- Use the ELSIF statement
An ELSIF statement has the following 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. 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 is passed 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 is passed to the ELSIF part and CONDITION 2 is evaluated, and so forth. If none of the specified conditions yields TRUE, control is passed to the ELSE part of the ELSIF construct. An ELSIF statement can contain any number of ELSIF clauses. Figure 4.3 shows this flow of logic.
)
Figure 4.3 ELSIF statement
Figure 4.3 shows that if condition 1 evaluates to TRUE, statement 1 is executed, and control is passed to the first statement after END IF. If condition 1 evaluates to FALSE, control is passed to condition 2. If condition 2 yields TRUE, statement 2 is executed. Otherwise, control is passed to the statement following END IF, and so forth. Consider the following example.
For Example
DECLARE
v_num NUMBER := &sv_num;
BEGIN
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;
END;
The value of v_num is provided at runtime and is 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 v_num equals 5 at runtime. This example produces the following output:
Enter value for sv_num: 5 old 2: v_num NUMBER := &sv_num; new 2: v_num NUMBER := 5; 5 is a positive number PL/SQL procedure successfully completed.
When using an ELSIF construct, it is not necessary to specify what 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
DECLARE
v_num NUMBER := &sv_num;
BEGIN
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 ('Done...');
END;
As you can see, no action is specified when v_num is equal to 0. If the value of v_num is equal to 0, both conditions evaluate to FALSE, and the ELSIF statement does not execute. When a value of 0 is specified for v_num, this example produces the following output:
Enter value for sv_num: 0 old 2: v_num NUMBER := &sv_num; new 2: v_num NUMBER := 0; Done... PL/SQL procedure successfully completed.
Lab 4.2 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.2.1 Use the ELSIF Statement
In this exercise, you use an ELSIF statement to display a letter grade for a student registered for a specific section of course number 25.
Create the following PL/SQL script:
-- ch04_3a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
v_student_id NUMBER := 102;
v_section_id NUMBER := 89;
v_final_grade NUMBER;
v_letter_grade CHAR(1);
BEGIN
SELECT final_grade
INTO v_final_grade
FROM enrollment
WHERE student_id = v_student_id
AND section_id = v_section_id;
IF v_final_grade BETWEEN 90 AND 100 THEN
v_letter_grade := 'A';
ELSIF v_final_grade BETWEEN 80 AND 89 THEN
v_letter_grade := 'B';
ELSIF v_final_grade BETWEEN 70 AND 79 THEN
v_letter_grade := 'C';
ELSIF v_final_grade BETWEEN 60 AND 69 THEN
v_letter_grade := 'D';
ELSE
v_letter_grade := 'F';
END IF;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||
v_letter_grade);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no such student or section');
END;
Note that you may need to change the values for the variables v_student_id and v_section_id as you see fit to test some of your answers.
Try to answer the following questions, and then execute the script:
-
What letter grade is displayed on the screen:
- if the value of v_final_grade is equal to 85?
- if the value of v_final_grade is NULL?
- if the value of v_final_grade is greater than 100?
ANSWER:
-
If the value of v_final_grade is equal to 85, the value "B" of the letter grade is displayed on the screen.
The conditions of the ELSIF statement are evaluated in sequential order. The first condition:
v_final_grade BETWEEN 90 AND 100
evaluates to FALSE, and control is passed to the first ELSIF part of the ELSIF statement. Then, the second condition:
v_final_grade BETWEEN 80 AND 89
evaluates to TRUE, and the letter "B" is assigned to the variable v_letter_grade. Control is then passed to the first executable statement after END IF, and the message
Letter grade is: B
is displayed on the screen.
-
If the value of v_final_grade is NULL, value "F" of the letter grade is displayed on the screen.
If the value of the v_final_grade is undefined or NULL, all conditions of the ELSIF statement evaluate to NULL (notice that they do not evaluate to FALSE). As a result, the ELSE part of the ELSIF statement is executed, and letter "F" is assigned to the v_letter_grade.
-
If the value of v_final_grade is greater than 100, value "F" of the letter grade is displayed on the screen.
The conditions specified for the ELSIF statement cannot handle a value of v_final_grade greater than 100. Therefore, any student who should receive a letter grade of A+ will instead receive a letter grade of "F." After the ELSIF statement has terminated, The letter grade is: F is displayed on the screen.
-
How would you change this script so that the message v_final_grade is null is displayed on the screen if v_final_grade is NULL?
ANSWER: The script should look similar to the following. Changes are shown in bold.
-- ch04_3b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := 102; v_section_id NUMBER := 89; v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN SELECT final_grade INTO v_final_grade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; IF v_final_grade IS NULL THEN DBMS_OUTPUT.PUT_LINE('v_final_grade is null'); ELSIF v_final_grade BETWEEN 90 AND 100 THEN v_letter_grade := 'A'; ELSIF v_final_grade BETWEEN 80 AND 89 THEN v_letter_grade := 'B'; ELSIF v_final_grade BETWEEN 70 AND 79 THEN v_letter_grade := 'C'; ELSIF v_final_grade BETWEEN 60 AND 69 THEN v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END IF; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Letter grade is: '|| v_letter_grade); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There is no such student or section'); END;One more condition has been added to the ELSIF statement. The condition
v_final_grade BETWEEN 90 AND 100
becomes the first ELSIF condition. Now, if the value of v_final_grade is NULL, the message v_final_grade is null is displayed on the screen. However, no value is assigned to the variable v_letter_grade. The message Letter grade is: is displayed on the screen as well.
-
How would you change this script so that the user provides the student ID and section ID?
ANSWER: The script should look similar to the following. Changes are shown in bold.
-- ch04_3c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := &sv_student_id; v_section_id NUMBER := &sv_section_id; v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN SELECT final_grade INTO v_final_grade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; IF v_final_grade BETWEEN 90 AND 100 THEN v_letter_grade := 'A'; ELSIF v_final_grade BETWEEN 80 AND 89 THEN v_letter_grade := 'B'; ELSIF v_final_grade BETWEEN 70 AND 79 THEN v_letter_grade := 'C'; ELSIF v_final_grade BETWEEN 60 AND 69 THEN v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END IF; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Letter grade is: '|| v_letter_grade); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There is no such student or section'); END; -
How would you change the script to define a letter grade without specifying the upper limit of the final grade? In the statement v_final_grade BETWEEN 90 and 100, number 100 is the upper limit.
ANSWER: The script should look similar to the following. Changes are shown in bold.
-- ch04_3d.sql, version 4.0 SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := 102; v_section_id NUMBER := 89; v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN SELECT final_grade INTO v_final_grade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; IF v_final_grade >= 90 THEN v_letter_grade := 'A'; ELSIF v_final_grade >= 80 THEN v_letter_grade := 'B'; ELSIF v_final_grade >= 70 THEN v_letter_grade := 'C'; ELSIF v_final_grade >= 60 THEN v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END IF; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Letter grade is: '|| v_letter_grade); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There is no such student or section'); END;In this example, no upper limit is specified for the variable v_final_grade because the BETWEEN operator has been replaced with the >= operator. Thus, this script can handle a value of v_final_grade that is greater than 100. Instead of assigning letter "F" to v_letter_grade (in version 1.0 of the script), the letter "A" is assigned to the variable v_letter_grade. As a result, this script produces more accurate results.