Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

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: