Lab 6.2 CASE Expressions
In Chapter 3, you encountered various PL/SQL expressions. You will 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 is then 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 previous lab of this chapter: 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; Consider the new version of the same example, with the CASE expression instead of the CASE statement: DECLARE v_num NUMBER := &sv_user_num; v_num_flag NUMBER; v_result VARCHAR2(30); BEGIN v_num_flag := MOD(v_num,2); 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); DBMS_OUTPUT.PUT_LINE ('Done'); END; In this example, a new variable, v_result , is used to hold the value returned by the CASE expression. If v_num is assigned the value of 8, this example produces the following output: Enter value for sv_user_num: 8 old 2: v_num NUMBER := &sv_user_num; new 2: v_num NUMBER := 8; 8 is even number Done PL/SQL procedure successfully completed. It is important to note some syntax differences between a CASE statement and a CASE expression. Consider the following code fragments :
In the CASE statement, the WHEN and ELSE clauses each contain a single executable statement. Each executable statement is terminated by a semicolon. In the CASE expression, the WHEN and ELSE clauses each contain an expression that is not terminated by a semicolon. There is one semicolon present 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: DECLARE v_num NUMBER := &sv_user_num; v_result VARCHAR2(30); BEGIN 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); DBMS_OUTPUT.PUT_LINE ('Done'); END; In this example, there is no need to declare 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 run, this example produces output identical to the previous version: Enter value for sv_user_num: 8 old 2: v_num NUMBER := &sv_user_num; new 2: v_num NUMBER := 8; 8 is even number Done PL/SQL procedure successfully completed. 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 may recall that there is another way to assign a value to a PL/SQL variable, via a SELECT INTO statement. Consider an example of the CASE expression used in a SELECT INTO statement: 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; In this example, you are displaying course number, description, and the number of a prerequisite course on the screen. Furthermore, if a given course does not have a prerequisite course, a message stating so is displayed on the screen. In order 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 Computers Prerequisite: No prerequisite course required PL/SQL procedure successfully completed. 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 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, thus, a single datatype. Therefore, it is important to ensure that regardless of what part of a CASE expression is executed, it always returns the same datatype. In the preceding CASE expression, the WHEN clause returns the VARCHAR2 datatype. The ELSE clause returns the value of the PREREQUISITE column of the COURSE table. This column has been defined as NUMBER, so it is necessary to convert it to the string datatype. When the TO_CHAR function is not used, the CASE expression causes the following syntax error: ELSE prerequisite * ERROR at line 9: ORA-06550: line 9, column 19: PL/SQL: ORA-00932: inconsistent datatypes ORA-06550: line 6, column 4: PL/SQL: SQL Statement ignored  |
Use CASE Expressions 
![Oracle PL[s]SQL by Example](/icons/blank_book.jpg "Oracle PL[s]SQL by Example"){kind=icon}