Conditional Control: CASE Statements - Oracle PL/SQL by Example, Fifth Edition (2015)

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

Chapter 5. Conditional Control: CASE Statements

In this chapter, you will learn about

Image CASE Statements

Image CASE Expressions

Image NULLIF and COALESCE Functions

In the previous chapter, you explored the concept of conditional control via IF and ELSIF statements. In this chapter, you will continue this exploration by examining different types of CASE statements and expressions. You will also learn how to use NULLIF and COALESCE functions, which are considered extensions of CASE.

Lab 5.1: CASE Statements

After this lab, you will be able to

Image Use CASE Statements

Image Use Searched CASE Statements

Image Use Nested CASE Statements

A CASE statement has two forms: CASE and searched CASE. A CASE statement allows you to specify a selector that determines which group of actions to take. A searched CASE statement does not have a selector; rather, it has search conditions that are evaluated to determine which group of actions to take.

CASE Statements

A CASE statement has structure shown in Listing 5.1.

Listing 5.1 CASE Statement Structure

CASE SELECTOR
WHEN EXPRESSION 1 THEN STATEMENT 1;
WHEN EXPRESSION 2 THEN STATEMENT 2;
...
WHEN EXPRESSION N THEN STATEMENT N;
ELSE STATEMENT N+1;
END CASE;

The reserved word CASE marks the beginning of the CASE statement. A selector is a value that determines which WHEN clause should be executed. Each WHEN clause contains an EXPRESSION and one or more executable statements associated with it. The ELSE clause is optional and works similar to the ELSE clause used in the IF-THEN-ELSE statement. END CASE is a reserved phrase that indicates the end of the CASE statement. This flow of the logic from the preceding structure of the CASE statement is illustrated in Figure 5.1.

Image

Figure 5.1 CASE Statement

Note that the selector is evaluated only once, and the WHEN clauses are evaluated sequentially. The value of an expression is compared to the value of the selector. If they are equal, the statement associated with a particular WHEN clause is executed, and any subsequent WHEN clauses are not evaluated. If no expression matches the value of the selector, the ELSE clause is executed.

Recall the example of the IF-THEN-ELSE statement from Chapter 4, which is listed here for reference.

For Example ch05_1a.sql

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;

Now consider a new version of the same example that uses the CASE statement instead of the IF-THEN-ELSE statement.

For Example ch05_1b.sql

DECLARE
v_num NUMBER := &sv_user_num;
v_num_flag NUMBER;
BEGIN
v_num_flag := MOD(v_num,2);

-- test if the number provided by the user is even
CASE v_num_flag
WHEN 0
THEN
DBMS_OUTPUT.PUT_LINE (v_num||' is even number');
ELSE
DBMS_OUTPUT.PUT_LINE (v_num||' is odd number');
END CASE;
END;

In this example, a new variable, v_num_flag, is used as a selector for the CASE statement. If the MOD function returns 0, then the number is even; otherwise, it is odd. If v_num is assigned the value of 7 at the run time, this example produces the following output:

7 is odd number

Searched CASE Statements

A searched CASE statement has search conditions that yield Boolean values: TRUE, FALSE, or NULL. When a particular search condition evaluates to TRUE, the group of statements associated with this condition is executed. This structure is shown in Listing 5.2.

Listing 5.2 Searched CASE Statement Structure

CASE
WHEN SEARCH CONDITION 1 THEN STATEMENT 1;
WHEN SEARCH CONDITION 2 THEN STATEMENT 2;
...
WHEN SEARCH CONDITION N THEN STATEMENT N;
ELSE STATEMENT N+1;
END CASE;

When a search condition evaluates to TRUE, control passes to the statement associated with it. If no search condition evaluates to TRUE, then statements associated with the ELSE clause are executed. Note that the ELSE clause is optional. This flow of logic from the preceding structure of the searched CASE statement is illustrated in Figure 5.2.

Image

Figure 5.2 Searched CASE Statement

Consider the modified version of the ch05_1b.sql example, which you saw earlier in this lab. The changes are highlighted in bold.

For Example ch05_1c.sql

DECLARE
v_num NUMBER := &sv_user_num;
BEGIN
-- test if the number provided by the user is even
CASE
WHEN 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 CASE;
END;

In the previous example, the variable v_num_flag was used as a selector, and the result of the MOD function was assigned to it. The value of the selector was then compared to the value of the expression.

This example uses a searched CASE statement, so there is no selector present. The variable v_num is used as part of the search conditions, so there is no need to declare variable v_num_flag. This example produces the same output when the same value is provided for the v_num:

7 is odd number

Differences between CASE and Searched CASE Statements

It is important to note the differences between the CASE and searched CASE statements. You have seen that the searched CASE statement does not have a selector. In addition, its WHEN clauses contain search conditions that yield a Boolean value similar to the IF statement, not expressions that can yield a value of any type except a PL/SQL record, an index-by-table, a nested table, a vararray, BLOB, BFILE, or an object type. You will encounter some of these types in the future chapters.

Consider the two code fragments shown in Table 5.1, which are based on the examples you saw earlier in this chapter.

Image

Table 5.1 CASE Statement versus Searched CASE Statement

In the code fragment on the left (CASE statement), v_num_flag is the selector. It is a PL/SQL variable that has been defined as a NUMBER. Because the value of the expression is compared to the value of the selector, the expression must return a similar data type. The expression ‘0’ contains a number, so its data type is also numeric.

In the code fragment on the right (searched CASE statement), there is no need for the selector, as it has been replaced by the searched expression

MOD(v_num, 2) = 0

This expression evaluates to TRUE or FALSE just like conditions of an IF statement.

Next, consider an example of the CASE statement that generates a syntax error because the data type returned by the expressions does not match the data type assigned to the selector.

For Example ch05_2a.sql

DECLARE
v_num NUMBER := &sv_num;
v_num_flag NUMBER;
BEGIN
CASE v_num_flag
WHEN 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 CASE;
END;

In this example, the variable v_num_flag has been defined as a NUMBER. However, the result of the expression evaluated by the WHEN clause yields a Boolean data type. As a result, this example produces the following syntax error:

ORA-06550: line 5, column 9:
PLS-00615: type mismatch found at 'V_NUM_FLAG' between CASE operand and WHEN operands
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored

Now consider a modified version of this example where v_num_flag variable has been declared as a Boolean (affected statements are shown in bold).

For Example ch05_2b.sql

DECLARE
v_num NUMBER := &sv_num;
v_num_flag Boolean;
BEGIN
CASE v_num_flag
WHEN 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 CASE;
END;

If v_num is assigned the value of 7 again, this example produces the following output:

7 is odd number

At first glance this seems to be the output that you would expect. However, consider the output produced by this example when the value of 4 is assigned to the variable v_num:

4 is odd number

Watch Out!

The second run of the example produced an incorrect output even though it did not generate any syntax errors. When the value of 4 is assigned to the variable v_num, the expression MOD(v_num,2) = 0 yields TRUE, and it is compared to the selector v_num_flag. However, the v_num_flag has not been initialized to any value, so it remains NULL. Because NULL does not equal TRUE, the statement associated with the ELSE clause is executed. To produce the correct output, the v_num_flag variable must be initialized to TRUE.

You learned earlier that the expressions in the CASE statements and searched conditions in the searched CASE statements are evaluated sequentially. Also, as soon as the expression or searched condition evaluates to the desired result, the rest of the expressions and searched conditions are ignored. Essentially, at this point, the executable statements associated with that expression or searched condition are executed. Once this execution completes, control passes to the first executable statement after the END CASE clause. This logic implies that the order in which you list the expressions and searched conditions affects the flow of the execution—a relationship illustrated by the following example.

For Example ch05_3a.sql

DECLARE
v_final_grade NUMBER := &sv_final_grade;
v_letter_grade CHAR(1);
BEGIN
CASE
WHEN v_final_grade >= 60
THEN
v_letter_grade := 'D';
WHEN v_final_grade >= 70
THEN
v_letter_grade := 'C';
WHEN v_final_grade >= 80
THEN
v_letter_grade := 'B';
WHEN v_final_grade >= 90
THEN
v_letter_grade := 'A';
ELSE
v_letter_grade := 'F';
END CASE;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Final grade is: '||v_final_grade);
DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||v_letter_grade);
END;

In this example of a searched CASE statement, the value of the letter grade is assigned based on the numeric grade provided by the user at run time. When a value of 67 is provided at run time for the variable v_final_grade, this example produces the following output:

Final grade is: 67
Letter grade is: D

At first sight, this is behavior that you expect. Next, consider the output produced by this example when 94 is assigned to the variable v_final_grade:

Final grade is: 94
Letter grade is: D

In this run, the example produced incorrect output. This error occurred because the first searched condition

v_final_grade >= 60

evaluated to TRUE and ‘D’ was assigned to the variable v_letter_grade. To correct this mistake, the order of the searched conditions could be changed as follows (all changes are highlighted in bold):

For Example ch05_3b.sql

DECLARE
v_final_grade NUMBER := &sv_final_grade;
v_letter_grade CHAR(1);
BEGIN
CASE
WHEN v_final_grade >= 90
THEN
v_letter_grade := 'A';
WHEN v_final_grade >= 80
THEN
v_letter_grade := 'B';
WHEN v_final_grade >= 70
THEN
v_letter_grade := 'C';
WHEN v_final_grade >= 60
THEN
v_letter_grade := 'D';
ELSE
v_letter_grade := 'F';
END CASE;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Final grade is: '||v_final_grade);
DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||v_letter_grade);
END;

In this version of the example, the order of the searched conditions has been reversed. As a result, it produces the correct output in both cases.

Final grade is: 67
Letter grade is: D

Final grade is: 94
Letter grade is: A

Lab 5.2: CASE Expressions

After this lab, you will be able to

Image Use CASE Expressions

In Chapter 2, you encountered various PL/SQL expressions. Recall that the result of an expression yields a single value that is assigned to a variable. In a similar manner, a CASE expression evaluates to a single value that may be assigned to a variable.

A CASE expression has a structure almost identical to a CASE statement. Thus, it also has two forms: CASE and searched CASE. Consider an example of a CASE statement used in the first lab in this chapter:

For Example ch05_1d.sql

DECLARE
v_num NUMBER := &sv_user_num;
v_num_flag NUMBER;
BEGIN
v_num_flag := MOD(v_num,2);

-- test if the number provided by the user is even
CASE v_num_flag
WHEN 0
THEN
DBMS_OUTPUT.PUT_LINE (v_num||' is even number');
ELSE
DBMS_OUTPUT.PUT_LINE (v_num||' is odd number');
END CASE;
END;

Now consider the new version of the same example, which uses a CASE expression instead of a CASE statement. Changes are shown in bold.

For Example ch05_1e.sql

DECLARE
v_num NUMBER := &sv_user_num;
v_num_flag NUMBER;
v_result VARCHAR2(30);
BEGIN
v_num_flag := MOD(v_num,2);

-- test if the number provided by the user is even
v_result := CASE v_num_flag
WHEN 0
THEN
v_num||' is even number'
ELSE
v_num||' is odd number'
END;
DBMS_OUTPUT.PUT_LINE (v_result);
END;

In this example, a new variable, v_result, is used to hold the value returned by the CASE expression. If the variable v_num is assigned the value of 8, this example produces the following output:

8 is even number

It is important to note some syntax differences between a CASE statement and a CASE expression. Consider the code fragments shown in Table 5.2.

Image

Table 5.2 CASE Statement versus CASE Expression

In the CASE statement, the WHEN and ELSE clauses both contain a single executable statement. Each executable statement is terminated by a semicolon. In the CASE expression, the WHEN and ELSE clauses both contain an expression that is not terminated by a semicolon. One semicolon appears after the reserved word END, which terminates the CASE expression. Finally, the CASE statement is terminated by the reserved phrase END CASE.

Next, consider another version of the previous example, with the searched CASE expression (affected statements are shown in bold):

For Example ch05_1f.sql

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

In this example, there is no need to declare the variable v_num_flag because the searched CASE expression does not need a selector value, and the result of the MOD function is incorporated into the search condition. When this example is run, it produces output identical to the previous version:

8 is even number

You learned earlier that a CASE expression returns a single value that is then assigned to a variable. In the examples that you saw earlier, this assignment operation was accomplished via the assignment operator, :=. You might recall that there is another way to assign a value to a PL/SQL variable—that is, via a SELECT INTO statement. Consider an example of the CASE expression used in a SELECT INTO statement:

For Example ch05_4a.sql

DECLARE
v_course_no NUMBER;
v_description VARCHAR2(50);
v_prereq VARCHAR2(35);
BEGIN
SELECT course_no
,description
,CASE
WHEN prerequisite IS NULL
THEN
'No prerequisite course required'
ELSE
TO_CHAR(prerequisite)
END prerequisite
INTO v_course_no
,v_description
,v_prereq
FROM course
WHERE course_no = 20;

DBMS_OUTPUT.PUT_LINE ('Course: '||v_course_no);
DBMS_OUTPUT.PUT_LINE ('Description: '||v_description);
DBMS_OUTPUT.PUT_LINE ('Prerequisite: '||v_prereq);
END;

This script displays the course number, description, and number of a prerequisite course on the screen. Furthermore, if a given course does not have a prerequisite course, a message stating that fact is displayed on the screen. To achieve the desired results, a CASE expression is used as one of the columns in the SELECT INTO statement. Its value is assigned to the variable v_prereq. Notice that there is no semicolon after the reserved word END of the CASE expression.

This example produces the following output:

Course: 20
Description: Intro to Information Systems
Prerequisite: No prerequisite course required

Course 20 does not have a prerequisite course. As a result, the searched condition

WHEN prerequisite IS NULL
THEN

evaluates to TRUE, and the value “No prerequisite course required” is assigned to the variable v_prereq.

It is important to note why the function TO_CHAR is used in the ELSE clause of the CASE expression:

CASE
WHEN prerequisite IS NULL
THEN
'No prerequisite course required'
ELSE
TO_CHAR(prerequisite)
END

A CASE expression returns a single value and, therefore, a single data type. For this reason, it is important to ensure that regardless of which part of a CASE expression executes, it always returns the same data type. In the preceding CASE expression, the WHEN clause returns the VARCHAR2data type. The ELSE clause returns the value of the PREREQUISITE column of the COURSE table. This column has been defined as a NUMBER, so it is necessary to convert it to the string data type.

When the TO_CHAR function is not used, the CASE expression causes the following syntax error:

ORA-06550: line 13, column 17:
PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
ORA-06550: line 6, column 4:
PL/SQL: SQL Statement ignored

Lab 5.3: NULLIF and COALESCE Functions

After this lab, you will be able to

Image Use the NULLIF Function

Image Use the COALESCE Function

The NULLIF and COALESCE functions are defined by the ANSI 1999 standard to be “CASE abbreviations.” Both functions can be used as variations on the CASE expression.

NULLIF Function

The NULLIF function compares two expressions. If they are equal, then the function returns NULL; otherwise, it returns the value of the first expression. The NULLIF function has the structure shown in Listing 5.3.

Listing 5.3 NULLIF Function

NULLIF (EXPRESSION 1, EXPRESSION 2)

If EXPRESSION 1 is equal to EXPRESSION 2, the NULLIF function returns NULL. If EXPRESSION 1 does not equal EXPRESSION 2, the NULLIF function returns EXPRESSION 1. Note that the NULLIF function does the opposite of the NVL function. If the first expression is NULL, then NVL function returns the second expression. If the first expression is not NULL, then the NVL function returns the first expression.

The NULLIF function is equivalent to the following CASE expression:

CASE
WHEN EXPRESSION 1 = EXPRESSION 2 THEN NULL
ELSE EXPRESSION 1
END

Consider the following example of a NULLIF function:

For Example ch05_5a.sql

DECLARE
v_num NUMBER := &sv_user_num;
v_remainder NUMBER;
BEGIN
-- calculate the remainder and if it is zero return NULL
v_remainder := NULLIF(MOD(v_num, 2),0);
DBMS_OUTPUT.PUT_LINE ('v_remainder: '||v_remainder);
END;

This example is somewhat similar to an example that you saw earlier in this chapter. A value is assigned to the variable v_num at run time. Next, this value is divided by 2, and its remainder is compared to 0 via the NULLIF function. If the remainder equals 0, the NULLIF function returns NULL; otherwise, it returns the remainder. The value returned by the NULLIF function is stored in the variable v_remainder and displayed on the screen via the DBMS_OUTPUT.PUT_LINE statement.

Suppose that for the first run, 5 is assigned to the variable v_num. The example produces the following output:

v_remainder: 1

Now suppose that for the second run, 4 is assigned to the variable v_num. The example produces the following output:

v_remainder:

In the first run, 5 is not divisible by 2, and the NULLIF function returns the value of the remainder. In the second run, 4 is divisible by 2, and the NULLIF function returns NULL as the value of the remainder.

The NULLIF function has a restriction: You cannot assign a literal NULL to EXPRESSION 1. You learned about literals in Chapter 2. Consider another run of the preceding example, in which the variable v_num is assigned a value of NULL, as shown in Figure 5.3. In this instance, the example produces the following output:

v_remainder:

Image

Figure 5.3 Assigning Literal NULL in SQL Developer

When NULL is assigned to the variable v_num, both the MOD and NULLIF functions return NULL. The preceding example does not produce any errors because the literal NULL is assigned to the variable v_num, and it is not used as the first expression of the NULLIF function.

Now consider a modified version of the preceding example (changes are shown in bold):

For Example ch05_5b.sql

DECLARE
v_remainder NUMBER;
BEGIN
-- calculate the remainder and if it is zero return NULL
v_remainder := NULLIF(NULL,0);
DBMS_OUTPUT.PUT_LINE ('v_remainder: '||v_remainder);
END;

In the previous version of this example, the MOD function was used as EXPRESSION 1. In this version, the literal NULL is used in place of the MOD function, so that the example produces the following syntax error:

v_remainder := NULLIF(NULL,0);
*
ERROR at line 5:
ORA-06550: line 5, column 26:
PLS-00619: the first operand in the NULLIF expression must not be NULL
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored

COALESCE Function

The COALESCE function compares each expression to NULL from the list of expressions and returns the value of the first non-NULL expression. The COALESCE function has the structure shown in Listing 5.4.

Listing 5.4 COALESCE Function

COALESCE (EXPRESSION 1, EXPRESSION 2, ..., EXPRESSION N)

If EXPRESSION 1 evaluates to NULL, then EXPRESSION 2 is evaluated. If EXPRESSION 2 does not evaluate to NULL, then the function returns EXPRESSION 2. If EXPRESSION 2 also evaluates to NULL, then the next expression is evaluated. If all expressions evaluate to NULL, the function returns NULL.

Note that the COALESCE function is like a nested NVL function:

NVL(EXPRESSION 1
,NVL(EXPRESSION 2
,NVL(EXPRESSION 3,...)
)
)

The COALESCE function can also be used as an alternative to a CASE expression. For example,

COALESCE (EXPRESSION 1, EXPRESSION 2)

is equivalent to

CASE
WHEN EXPRESSION 1 IS NOT NULL
THEN
EXPRESSION 1
ELSE
EXPRESSION 2
END

If there are more than two expressions to evaluate, then

COALESCE (EXPRESSION 1, EXPRESSION 2, ..., EXPRESSION N)

is equivalent to

CASE
WHEN EXPRESSION 1 IS NOT NULL
THEN
EXPRESSION 1
ELSE
COALESCE (EXPRESSION 2, ..., EXPRESSION N)
END

which in turn is equivalent to

CASE
WHEN EXPRESSION 1 IS NOT NULL
THEN
EXPRESSION 1
WHEN EXPRESSION 2 IS NOT NULL
THEN
EXPRESSION 2
...
ELSE
EXPRESSION N
END

Consider the following example of the COALESCE function:

For Example ch05_6a.sql

SELECT e.student_id
,e.section_id
,e.final_grade
,g.numeric_grade
,COALESCE(e.final_grade, g.numeric_grade, 0) grade
FROM enrollment e
,grade g
WHERE e.student_id = g.student_id
AND e.section_id = g.section_id
AND e.student_id = 102
AND g.grade_type_code = 'FI';

This SELECT statement returns the following output:

STUDENT_ID SECTION_ID FINAL_GRADE NUMERIC_GRADE GRADE
---------- ---------- ----------- ------------- -----
102 86 (null) 85 85
102 89 92 92 92

The value of GRADE equals the value of NUMERIC_GRADE in the first row. The COALESCE function compares the value of FINAL_GRADE to NULL. If it is NULL, then the value of NUMERIC_GRADE is compared to NULL. Because the value of NUMERIC_GRADE is not NULL, theCOALESCE function returns the value of NUMERIC_GRADE. The value of GRADE equals the value of FINAL_GRADE in the second row. The COALESCE function returns the value of FINAL_GRADE because it is not NULL.

The COALESCE function shown in the previous example is equivalent to the following NVL statement and CASE expressions:

NVL(e.final_grade, NVL(g.numeric_grade, 0))

CASE
WHEN e.final_grade IS NOT NULL
THEN
e.final_grade
ELSE
COALESCE(g.numeric_grade, 0)
END

and

CASE
WHEN e.final_grade IS NOT NULL
THEN
e.final_grade
WHEN g.numeric_grade IS NOT NULL
THEN
g.numeric_grade
ELSE
0
END

Summary

In Chapter 4, you began exploring conditional control structures supported by Oracle’s PL/SQL language. In this chapter, you continued this exploration by learning about CASE statements and expressions and COALESCE and NULLIF functions. You have learned how to employ CASEstructures in both 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.