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

Lab 10.3 Exercises

10.3.1 Understand How Exceptions Propagate

In this exercise, you will use nested PL/SQL blocks to practice exception propagation. You will be asked to experiment with the script via exceptions. Try to answer the questions before you run the script. Once you have answered the questions, run the script and check your answers.

Create the following PL/SQL script:

-- ch10_3a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
v_my_name VARCHAR2(15) := 'ELENA SILVESTROVA';
BEGIN
DBMS_OUTPUT.PUT_LINE ('My name is '||v_my_name);
DECLARE
v_your_name VARCHAR2(15);
BEGIN
v_your_name := '&sv_your_name';
DBMS_OUTPUT.PUT_LINE ('Your name is '||v_your_name);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('Error in the inner block');
DBMS_OUTPUT.PUT_LINE ('This name is too long');
END;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('Error in the outer block');
DBMS_OUTPUT.PUT_LINE ('This name is too long');
END;

Answer the following questions first, and then execute the script:

10.3.2 Re-raise Exceptions

In this exercise, you will check the number of sections for each course. If a course does not have a section associated with it, you will raise an exception, e_no_sections. Again, try to answer the questions before you run the script. Once you have answered the questions, run the script and check your answers.

Create the following PL/SQL script:

-- ch10_4a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
CURSOR course_cur IS
SELECT course_no
FROM course;
v_total NUMBER;
e_no_sections EXCEPTION;
BEGIN
FOR course_rec in course_cur LOOP
BEGIN
SELECT COUNT(*)
INTO v_total
FROM section
WHERE course_no = course_rec.course_no;
IF v_total = 0 THEN
RAISE e_no_sections;
ELSE
DBMS_OUTPUT.PUT_LINE ('Course, '||
course_rec.course_no||' has '||
v_total||' sections');
END IF;
EXCEPTION
WHEN e_no_sections THEN
DBMS_OUTPUT.PUT_LINE ('There are no sections '||
'for course '||course_rec.course_no);
END;
END LOOP;
END;

Answer the following questions first, and then execute the script: