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

a)

What output was printed on the screen?

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.  
b)

What is the condition that causes the user -defined exception to be raised?

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.

c)

How would you change the script so that the cursor FOR loop processes all records returned by the cursor? In other words, once an exception is raised, the cursor FOR loop should not terminate.

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.  
d)

How would you change the script to display an instructor's name in the error message as well?

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.



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net