Lab 10.2 Exercises
10.2.1 Use User-Defined Exceptions
In this exercise, you will define an exception that will allow you to raise an error if an instructor teaches ten or more sections.
Create the following PL/SQL script:
-- ch10_2a.sql, version 1.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
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;
END LOOP;
EXCEPTION
WHEN e_too_many_sections THEN
DBMS_OUTPUT.PUT_LINE
('This instructor teaches too much');
END;
Execute the script, and then answer the following questions:
| a) |
What output was printed on the screen? | | b) |
What is the condition that causes the user-defined exception to be 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. | | d) |
How would you change the script to display an instructor's name in the error message as well? |
|
No comments:
Post a Comment