Create the following script. Modify the script created in this section in Chapter 5 (Question 1 of the Test Your Thinking section). You can use either the CASE statement or the searched CASE statement. Your output should look similar to the output produced by the example created in Chapter 5.
Answer: Consider the script created in the section in Chapter 5:
SET SERVEROUTPUT ON
DECLARE
v_day VARCHAR2(15);
v_time VARCHAR(8);
BEGIN
v_day := TO_CHAR(SYSDATE, 'fmDAY');
v_time := TO_CHAR(SYSDATE, 'HH24:MI');
IF v_day IN ('SATURDAY', 'SUNDAY') THEN
DBMS_OUTPUT.PUT_LINE (v_day||', '||v_time);
IF v_time BETWEEN '12:01' AND '24:00' THEN
DBMS_OUTPUT.PUT_LINE ('It''s afternoon');
ELSE
DBMS_OUTPUT.PUT_LINE ('It''s morning');
END IF;
END IF;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Done…');
END;
Next, consider modified version of the script with nested CASE statements. For illustrative purposes, this script uses both, CASE and searched CASE statement. All changes are shown bold letters.
SET SERVEROUTPUT ON
DECLARE
v_day VARCHAR2(15);
v_time VARCHAR(8);
BEGIN
v_day := TO_CHAR(SYSDATE, 'fmDay');
v_time := TO_CHAR(SYSDATE, 'HH24:MI');
-- CASE statement
CASE SUBSTR(v_day, 1, 1)
WHEN 'S' THEN
DBMS_OUTPUT.PUT_LINE (v_day||', '||v_time);
-- searched CASE statement
CASE
WHEN v_time BETWEEN '12:01' AND '24:00' THEN
DBMS_OUTPUT.PUT_LINE ('It''s afternoon');
ELSE
DBMS_OUTPUT.PUT_LINE ('It''s morning');
END CASE;
END CASE;
-- control resumes here
DBMS_OUTPUT.PUT_LINE('Done…');
END;
In this exercise, you substitute nested IF statements with nested CASE statements. Consider the outer CASE statement. It uses a selector expression
SUBSTR(v_day, 1, 1)
to check if a current day falls on the weekend. Notice, it derives only first letter of the day. This is a good solution when using CASE statement because only Saturday and Sunday start with letter 'S'. Furthermore, without using the SUBSTR function, you would need to use searched CASE statement. You will recall that the value of the WHEN expression is compared to the value of the selector. As a result, the WHEN expression must return similar datatype. In this example, the selector the expression returns a string datatype, so the WHEN expression must also return a string datatype.
Next, you use searched CASE statement is used to validate the time of the day. You will recall that similarly to the IF statement, the WHEN conditions of the searched CASE statement yield Boolean values.
When run, this exercise produces the output shown below:
Saturday, 19:49 It's afternoon Done… PLSQL procedure successfully completed.
Create the following script. Modify the script created in this section in Chapter 5 (Question 2 of the Test Your Thinking section). You can use either the CASE statement or the searched CASE statement. Your output should look similar to the output produced by the example created in Chapter 5.
Answer: Consider the script created in the section in Chapter 5:
SET SERVEROUTPUT ON
DECLARE
v_instructor_id NUMBER := &sv_instructor_id;
v_total NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_total
FROM section
WHERE instructor_id = v_instructor_id;
-- check if instructor teaches 3 or more sections
IF v_total >= 3 THEN
DBMS_OUTPUT.PUT_LINE ('This instructor needs a vacation');
ELSE
DBMS_OUTPUT.PUT_LINE ('This instructor teaches '||v_total||
' sections');
END IF;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Done…');
END;
Next, consider modified version of the script with the searched CASE statement instead of IF-THEN-ELSE statement. All changes are shown bold letters.
SET SERVEROUTPUT ON
DECLARE
v_instructor_id NUMBER := &sv_instructor_id;
v_total NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_total
FROM section
WHERE instructor_id = v_instructor_id;
-- check if instructor teaches 3 or more sections
CASE
WHEN v_total &= 3 THEN
DBMS_OUTPUT.PUT_LINE ('This instructor needs a vacation');
ELSE
DBMS_OUTPUT.PUT_LINE ('This instructor teaches '||v_total||
' sections');
END CASE;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Done…');
END;
Assume that value 109 was provided at the run-time. Then, the script produces the output shown below:
Enter value for sv_instructor_id: 109 old 2: v_instructor_id NUMBER := &sv_instructor_id; new 2: v_instructor_id NUMBER := 109; This instructor teaches 1 sections Done… PLSQL procedure successfully completed.
In order to use CASE statement, the searched CASE statement could be modified as follows:
CASE SIGN(v_total - 3)
WHEN -1 THEN
DBMS_OUTPUT.PUT_LINE ('This instructor teaches '||v_total||
' sections');
ELSE
DBMS_OUTPUT.PUT_LINE ('This instructor needs a vacation');
END CASE;
Notice, the SIGN function is used to determine if an instructor teaches 3 or more sections. You will recall that the SIGN function returns -1 if v_total is less than 3, 0 if v_total equals to 3, and 1 if v_total is greater than 3. In this case, as long as the SIGN function returns -1, the message 'This instructor teaches…' is displayed on the screen. In all other cases, the message 'This instructor needs a vacation' is displayed on the screen.
Execute the two SELECT statements below and explain why they produce different output:
SELECT e.student_id, e.section_id, e.final_grade, g.numeric_grade,
COALESCE(g.numeric_grade, e.final_grade) 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';
SELECT e.student_id, e.section_id, e.final_grade, g.numeric_grade,
NULLIF(g.numeric_grade, e.final_grade) 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';
Answer: Consider outputs produced by the SELECT statements above:
STUDENT_ID SECTION_ID FINAL_GRADE NUMERIC_GRADE GRADE
---------- ---------- ----------- ------------- ----------
102 86 85 85
102 89 92 92 92
STUDENT_ID SECTION_ID FINAL_GRADE NUMERIC_GRADE GRADE
---------- ---------- ----------- ------------- ----------
102 86 85 85
102 89 92 92
Consider the output returned by the first SELECT statement. This statement uses the COALESCE function to derive the value of GRADE. It equals to the value of the NUMERIC_GRADE in the first row, and to the value of FINAL_GRADE in the second row.
The COALESCE function compares the value of the FINAL_GRADE to NULL. If it is NULL, then the value of the NUMERIC_GRADE is compared to NULL. Because the value of the NUMERIC_GRADE is not NULL, the COALESCE function returns the value of the NUMERIC_GRADE in the first row. In the second row, the COALESCE function returns the value of FINAL_GRADE because it is not NULL.
Next. Consider the output returned by the second SELECT statement. This statement uses the NULLIF function to derive the value of GRADE. It equals to the value of the NUMERIC_GRADE in the first row, and it is NULL in the second row.
The NULLIF function compares NUMERIC_GRADE value to the FINAL_GRADE value. If these values are equal, the NULLIF function returns NULL. In opposite case, it returns the value of the NUMERIC_GRADE.