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?