Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

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:

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: