Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources]

Benjamin Rosenzweig

نسخه متنی -صفحه : 289/ 70
نمايش فراداده

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:

a)

If the value of v_date equals '15-JAN-2002', what output is printed on the screen?

b)

How many times is the CASE selector v_day evaluated?

c)

Rewrite this script using the ELSE clause in the CASE statement.

d)

Rewrite this script using the searched CASE statement.

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:

a)

What letter grade will be displayed on the screen:

  • if the value of v_final_grade is equal to 60?

  • if the value of v_final_grade is greater than 60 and less than 70?

  • if the value of v_final_grade is NULL?

    • b)

    How would you change this script so that a message "There is no final grade" is displayed if v_final_grade is null? In addition, make sure that the message "Letter grade is: " is not displayed on the screen.

    c)

    Rewrite this script, changing the order of the searched conditions as follows:

    CASE WHEN v_final_grade >= 60 THEN v_letter_grade := 'D'; WHEN v_final_grade >= 70 THEN v_letter_grade := 'C'; WHEN v_final_grade >= 80 THEN ... WHEN v_final_grade >= 90 THEN ... ELSE ...

    Execute the script and explain the output produced.