Lab 6.3 Exercises

6.3.1 Use the NULLIF Function

In this exercise, you will modify the following script. Instead of using the searched CASE expression, you will use the NULLIF function. Note that the SELECT INTO statement uses ANSI 1999 SQL standard.

You will find detailed explanations and examples of the statements using new ANSI 1999 SQL standard in Appendix E and in Oracle help. Throughout this book we try to provide you with examples illustrating both standards; however our main focus is on PL/SQL features rather than SQL.

 -- ch06_4a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    v_final_grade NUMBER; BEGIN    SELECT CASE              WHEN e.final_grade = g.numeric_grade THEN NULL              ELSE g.numeric_grade           END      INTO v_final_grade      FROM enrollment e      JOIN grade g        ON (e.student_id = g.student_id        AND e.section_id = g.section_id)     WHERE e.student_id = 102       AND e.section_id = 86       AND g.grade_type_code = 'FI';    DBMS_OUTPUT.PUT_LINE ('Final grade: 'v_final_grade); END; 

In the preceding script, the value of the final grade is compared to the value of the numeric grade. If these values are equal, the CASE expression returns NULL. In the opposite case, the CASE expression returns the numeric grade. The result of the CASE expression is then displayed on the screen via the DBMS_OUTPUT.PUT_LINE statement.

Answer the following questions:

a)	Modify script ch06_4a.sql. Substitute the CASE expression with the NULLIF function.
b)	Run the modified version of the script and explain the output produced.
c)	Change the order of columns in the NULLIF function. Run the modified version of the script and explain the output produced.

6.3.2 Use the COALESCE Function

In this exercise, you will modify the following script. Instead of using the searched CASE expression, you will use the COALESCE function.

 -- ch06_5a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    v_num1 NUMBER := &sv_num1;    v_num2 NUMBER := &sv_num2;    v_num3 NUMBER := &sv_num3;    v_result NUMBER; BEGIN    v_result := CASE                   WHEN v_num1 IS NOT NULL THEN v_num1                   ELSE                      CASE                         WHEN v_num2 IS NOT NULL THEN v_num2                         ELSE v_num3                      END                   END;    DBMS_OUTPUT.PUT_LINE ('Result: 'v_result); END; 

In the preceding script, the list consisting of three numbers is evaluated as follows : If the value of the first number is not NULL, then the outer CASE expression returns the value of the first number. Otherwise, control is passed to the inner CASE expression, which evaluates the second number. If the value of the second number is not NULL, then the inner CASE expression returns the value of the second number; in the opposite case, it returns the value of the third number.

The preceding CASE expression is equivalent to the following two CASE expressions:

  CASE   WHEN v_num1 IS NOT NULL THEN v_num1   WHEN v_num2 IS NOT NULL THEN v_num2   ELSE v_num3   END   CASE   WHEN v_num1 IS NOT NULL THEN v_num1   ELSE COALESCE(v_num2, v_num3)   END  

Answer the following questions:

a)	Modify script ch06_5a.sql. Substitute the CASE expression with the COALESCE function.
b)	Run the modified version of the script and explain the output produced. Use the following values for the list of numbers: NULL, 1, 2.
c)	What output will be produced by the modified version of the script if NULL is provided for all three numbers? Try to explain your answer before you run the script.