Lab 10.2 Exercise Answers
This section gives you some suggested answers to the questions in Lab 10.2, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with. 10.2.1 Answers
A1:
| Answer: Your output should look like the following: Instructor, Fernand Hanks, teaches 9 sections
This instructor teaches too much
PL/SQL procedure successfully completed.
| A2:
| Answer: The user-defined exception is raised if the condition instruct_rec.tot_sec >= 10
evaluates to TRUE. In other words, if an instructor teaches ten or more sections, the exception e_too_many_sections is raised. | A3:
| Answer: Your script should look similar to the script shown. All changes are shown in bold letters. -- ch10_2b.sql, version 2.0
SET SERVEROUTPUT ON
DECLARE
CURSOR instruct_cur IS
SELECT instructor_id, COUNT(*) tot_sec
FROM section
GROUP BY instructor_id;
v_name VARCHAR2(30);
e_too_many_sections EXCEPTION;
BEGIN
FOR instruct_rec IN instruct_cur LOOP
-- inner block
BEGIN
IF instruct_rec.tot_sec >= 10 THEN
RAISE e_too_many_sections;
ELSE
SELECT RTRIM(first_name)||' '||RTRIM(last_name)
INTO v_name
FROM instructor
WHERE instructor_id = instruct_rec.
instructor_id;
DBMS_OUTPUT.PUT_LINE ('Instructor, '||v_name||
', teaches '||instruct_rec.tot_sec||
' sections');
END IF;
EXCEPTION
WHEN e_too_many_sections THEN
DBMS_OUTPUT.PUT_LINE
('This instructor teaches too much');
END; -- end inner block
END LOOP;
END;
|
There are several changes in the new version of this script. First, the inner block has been created inside the body of the cursor FOR loop. Next, the exception-handling section has been moved from the outer block to the inner block.In this script, the exception has been declared in the outer block, but it is raised in the inner block. This does not cause any errors because the exception, e_too_many_sections, is global to the inner block. Hence, it can be raised anywhere in the inner block.The new version of this script produces the output shown:Instructor, Fernand Hanks, teaches 9 sections
This instructor teaches too much
This instructor teaches too much
This instructor teaches too much
This instructor teaches too much
This instructor teaches too much
This instructor teaches too much
Instructor, Charles Lowry, teaches 9 sections
PL/SQL procedure successfully completed.
A4:
| Answer: Your script should look similar to the script shown. All changes are shown in bold letters. -- ch10_2c.sql, version 3.0
SET SERVEROUTPUT ON
DECLARE
CURSOR instruct_cur IS
SELECT instructor_id, COUNT(*) tot_sec
FROM section
GROUP BY instructor_id;
v_name VARCHAR2(30);
e_too_many_sections EXCEPTION;
BEGIN
FOR instruct_rec IN instruct_cur LOOP
BEGIN
SELECT RTRIM(first_name)||' '||RTRIM(last_name)
INTO v_name
FROM instructor
WHERE instructor_id = instruct_rec.instructor_id;
IF instruct_rec.tot_sec >= 10 THEN
RAISE e_too_many_sections;
ELSE
DBMS_OUTPUT.PUT_LINE ('Instructor, '||v_name||
', teaches '||instruct_rec.tot_sec||
' sections');
END IF;
EXCEPTION
WHEN e_too_many_sections THEN
DBMS_OUTPUT.PUT_LINE ('Instructor, '||v_name||
', teaches too much');
END;
END LOOP;
END;
|
In order to achieve the desired result, the SELECT INTO statement has been moved outside the IF-THEN-ELSE statement. This change allows you to get an instructor's name regardless of the number of sections he or she teaches. As a result, you are able to include an instructor's name in the error message, thus improving the error message itself.The new version of the output is shown:Instructor, Fernand Hanks, teaches 9 sections
Instructor, Tom Wojick, teaches too much
Instructor, Nina Schorin, teaches too much
Instructor, Gary Pertez, teaches too much
Instructor, Anita Morris, teaches too much
Instructor, Todd Smythe, teaches too much
Instructor, Marilyn Frantzen, teaches too much
Instructor, Charles Lowry, teaches 9 sections
PL/SQL procedure successfully completed.
This version of the output is oriented more toward a user than the previous versions because it displays the name of the instructor in every message. The previous versions of the output were confusing because it was not clear which instructor caused this error. For example, consider the output produced by the first version of this script:Instructor, Fernand Hanks, teaches 9 sections
This instructor teaches too much
It is not clear to a user whether the message "This instructor teaches too much" is caused by the fact that Fernand Hanks teaches nine sections, or whether another instructor teaches more than nine sections.Remember, you have created this script, and you know the exception that you have defined. However, as mentioned earlier, most of the time, a user does not have access to your program. Therefore, it is important for you to provide clear error messages in your programs. |
لطفا منتظر باشید ...
