LAB 6.1 CASE Statements

Lab Objectives

After this Lab, you will be able to:

Use CASE Statements

Use Searched 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; it has search conditions that are evaluated in order to determine which group of actions to take.

CASE Statements

A CASE statement has the following 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 that 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 6.1.

Figure 6.1. CASE Statement

Note that the selector is evaluated only once. 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 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 used in the previous chapter.

FOR 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;    DBMS_OUTPUT.PUT_LINE ('Done'); END; 

Consider the new version of the same example with the CASE statement instead of the IF-THEN-ELSE statement.

FOR EXAMPLE

 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;    DBMS_OUTPUT.PUT_LINE ('Done'); 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, this example produces the following output:

  Enter value for sv_user_num: 7   old   2:    v_num NUMBER := &sv_user_num;   new   2:    v_num NUMBER := 7;   7 is odd number   Done   PL/SQL procedure successfully completed.  

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 is indicated as follows :

 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 is passed to the statement associated with it. If no search condition yields TRUE, then statements associated with the ELSE clause are executed. This flow of logic from the preceding structure of the searched CASE statement is illustrated in Figure 6.2.

Figure 6.2. Searched CASE Statement

Consider the modified version of the example that you have seen previously in this lab.

FOR EXAMPLE

 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;    DBMS_OUTPUT.PUT_LINE ('Done'); END; 

Notice that this example is almost identical to the previous example.

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. In this example, you are using 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 :

  Enter value for sv_user_num: 7   old   2:    v_num NUMBER := &sv_user_num;   new   2:    v_num NUMBER := 7;   7 is odd number   Done   PL/SQL procedure successfully completed.  

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 following two code fragments based on the examples you have seen earlier in this chapter.

FOR EXAMPLE

 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'); ... 

FOR EXAMPLE

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

In the first code fragment, v_num_flag is the selector. It is a PL/SQL variable that has been defined as NUMBER. Because the value of the expression is compared to the value of the selector, the expression must return a similar datatype. The expression '0' contains a number, so its datatype is also numeric. In the second code fragment, each searched 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 datatype returned by the expressions does not match the datatype assigned to the selector.

FOR EXAMPLE

 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;    DBMS_OUTPUT.PUT_LINE ('Done'); END; 

In this example, the variable v_num_flag has been defined as a NUMBER. However, the result of each expression yields Boolean datatype. As a result, this example produces the following syntax error:

  Enter value for sv_num: 7   old   2:    v_num NUMBER := &sv_num;   new   2:    v_num NUMBER := 7;   CASE v_num_flag   *   ERROR at line 5:   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  

Consider a modified version of this example where v_num_flag has been defined as a Boolean variable.

FOR EXAMPLE

 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;    DBMS_OUTPUT.PUT_LINE ('Done'); END; 

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

  Enter value for sv_num: 7   old   2:    v_num NUMBER := &sv_num;   new   2:    v_num NUMBER := 7;   7 is odd number   Done   PL/SQL procedure successfully completed.  

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 :

  Enter value for sv_num: 4   old   2:    v_num NUMBER := &sv_num;   new   2:    v_num NUMBER := 4;   4 is odd number   Done   PL/SQL procedure successfully completed.  

Notice that the second run of the example produced an incorrect output even though it did not generate any syntax errors. When the value 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 is NULL. Because NULL does not equal to TRUE, the statement associated with the ELSE clause is executed.