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

Lab 6.1 Exercises

6.1.1 Use the CASE Statement

In this exercise, you will use the CASE statement to display the name of a day on the screen based on the number of the day in a week. In other words, if the number of a day of the week is 3, then it is Tuesday.

Create the following PL/SQL script:

-- ch06_1a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');
v_day VARCHAR2(1);
BEGIN
v_day := TO_CHAR(v_date, 'D');
CASE v_day
WHEN '1' THEN
DBMS_OUTPUT.PUT_LINE ('Today is Sunday');
WHEN '2' THEN
DBMS_OUTPUT.PUT_LINE ('Today is Monday');
WHEN '3' THEN
DBMS_OUTPUT.PUT_LINE ('Today is Tuesday');
WHEN '4' THEN
DBMS_OUTPUT.PUT_LINE ('Today is Wednesday');
WHEN '5' THEN
DBMS_OUTPUT.PUT_LINE ('Today is Thursday');
WHEN '6' THEN
DBMS_OUTPUT.PUT_LINE ('Today is Friday');
WHEN '7' THEN
DBMS_OUTPUT.PUT_LINE ('Today is Saturday');
END CASE;
END;

Execute the script, and then answer the following questions:

6.1.2 Use the Searched CASE Statement

In this exercise, you will modify the script ch05_3d.sql used in the previous chapter. The original script uses the ELSIF statement to display a letter grade for a student registered for a specific section of course number 25. The new version will use a searched CASE statement to achieve the same result. Try to answer the questions before you run the script. Once you have answered the questions, run the script and check your answers.

Note that you may need to change the values for the variables v_student_id

and v_section_id

as you see fit in order to test some of your answers.

Create the following PL/SQL script:

-- ch06_2a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
v_student_id NUMBER := 102;
v_section_id NUMBER := 89;
v_final_grade NUMBER;
v_letter_grade CHAR(1);
BEGIN
SELECT final_grade
INTO v_final_grade
FROM enrollment
WHERE student_id = v_student_id
AND section_id = v_section_id;
CASE
WHEN v_final_grade >= 90 THEN v_letter_grade := 'A';
WHEN v_final_grade >= 80 THEN v_letter_grade := 'B';
WHEN v_final_grade >= 70 THEN v_letter_grade := 'C';
WHEN v_final_grade >= 60 THEN v_letter_grade := 'D';
ELSE v_letter_grade := 'F';
END CASE;
-- control resumes here
DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||
v_letter_grade);
END;

Try to answer the following questions first, and then execute the script:

Execute the script and explain the output produced.