《Oracle PL/SQL实例精讲》学习笔记10——异常

这一章的的重点是异常传播（exception propagation）:

1. 如果在PL/SQL块的声明部分中发生运行时错误，那么这个块的异常处理部分是不能捕获此错误的。

2. PL/SQL块的异常处理部分中发生运行时错误时，这个块的异常处理部分是不能阻止此错误的。

3. 当内层块的异常处理部分发生运行时错误时，异常会立即传播到封闭它的块（外层块、上层块）。

 

源码如下：

-- *** Chapter Exercises *** --
-- For Example ch09_1a.sql
DECLARE
   v_student_id NUMBER := &sv_student_id;
   v_name       VARCHAR2(30);
BEGIN
   SELECT RTRIM(first_name)||' '||RTRIM(last_name)
     INTO v_name
     FROM student
    WHERE student_id = v_student_id;

   DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name);
EXCEPTION
   WHEN NO_DATA_FOUND 
   THEN
      DBMS_OUTPUT.PUT_LINE ('There is no such student');
END; 

-- For Example ch09_1b.sql
<<outer_block>>
DECLARE
   v_student_id NUMBER := &sv_student_id;
   v_name       VARCHAR2(30);
   v_total      NUMBER(1);

BEGIN
   SELECT RTRIM(first_name)||' '||RTRIM(last_name)
     INTO v_name
     FROM student
    WHERE student_id = v_student_id;
   
   DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name);
   
   <<inner_block>>
   BEGIN
      SELECT COUNT(*)
        INTO v_total
        FROM enrollment
       WHERE student_id = v_student_id;
      
      DBMS_OUTPUT.PUT_LINE ('Student is registered for '||v_total||' course(s)');
   EXCEPTION
      WHEN VALUE_ERROR OR INVALID_NUMBER 
      THEN
         DBMS_OUTPUT.PUT_LINE ('An error has occurred');
   END;

EXCEPTION
   WHEN NO_DATA_FOUND 
   THEN
      DBMS_OUTPUT.PUT_LINE ('There is no such student');
END; 

-- For Example ch09_1c.sql
<<outer_block>>
DECLARE
   v_student_id NUMBER := &sv_student_id;
   v_name       VARCHAR2(30);
   v_registered CHAR;

BEGIN
   SELECT RTRIM(first_name)||' '||RTRIM(last_name)
     INTO v_name
     FROM student
    WHERE student_id = v_student_id;
   
   DBMS_OUTPUT.PUT_LINE ('Student name is '||v_name);

   <<inner_block>>
   BEGIN
      SELECT 'Y'
        INTO v_registered
        FROM enrollment
       WHERE student_id = v_student_id;
      
      DBMS_OUTPUT.PUT_LINE ('Student is registered');
   EXCEPTION
      WHEN VALUE_ERROR OR INVALID_NUMBER 
      THEN
         DBMS_OUTPUT.PUT_LINE ('An error has occurred');
   END;

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('There is no such student');
END; 

-- For Example ch09_2a.sql
DECLARE
   v_student_id    STUDENT.STUDENT_ID%TYPE := &sv_student_id;
   v_total_courses NUMBER;
   e_invalid_id    EXCEPTION;
BEGIN
   IF v_student_id < 0 
   THEN
      RAISE e_invalid_id;
   END IF;

   SELECT COUNT(*)
     INTO v_total_courses
     FROM enrollment
    WHERE student_id = v_student_id;
      
   DBMS_OUTPUT.PUT_LINE ('The student is registered for '||v_total_courses||' courses');
   DBMS_OUTPUT.PUT_LINE ('No exception has been raised');
EXCEPTION
   WHEN e_invalid_id 
   THEN
      DBMS_OUTPUT.PUT_LINE ('An id cannot be negative');
END;

-- For Example ch09_3a.sql
<<outer_block>>
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Outer block');
   
   <<inner_block>>
   DECLARE
      e_my_exception EXCEPTION;
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('Inner block');
   EXCEPTION
      WHEN e_my_exception 
      THEN
         DBMS_OUTPUT.PUT_LINE ('An error has occurred');
   END;
   
   IF 10 > &sv_number 
   THEN
      RAISE e_my_exception;
   END IF;
END;

-- For Example ch09_4a.sql
DECLARE
   v_test_var CHAR(3):= 'ABCDE';
BEGIN
   DBMS_OUTPUT.PUT_LINE ('This is a test');
EXCEPTION
   WHEN INVALID_NUMBER OR VALUE_ERROR 
   THEN
      DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

-- For Example ch09_4b.sql
<<outer_block>>
BEGIN
   <<inner_block>>
   DECLARE
      v_test_var CHAR(3):= 'ABCDE';
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('This is a test');
   EXCEPTION
      WHEN INVALID_NUMBER OR VALUE_ERROR 
      THEN
         DBMS_OUTPUT.PUT_LINE ('An error has occurred in the inner block');
   END;
EXCEPTION
   WHEN INVALID_NUMBER OR VALUE_ERROR 
   THEN
      DBMS_OUTPUT.PUT_LINE ('An error has occurred in the program');
END;

-- For Example ch09_5a.sql
DECLARE
   v_test_var CHAR(3) := 'ABC';
BEGIN
   v_test_var := '1234';
   DBMS_OUTPUT.PUT_LINE ('v_test_var: '||v_test_var);
EXCEPTION
   WHEN INVALID_NUMBER OR VALUE_ERROR 
   THEN
      v_test_var := 'ABCD'; 
      DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

-- For Example ch09_5b.sql
<<outer_block>>
BEGIN
   <<inner_block>>
   DECLARE
      v_test_var CHAR(3) := 'ABC';
   BEGIN
      v_test_var := '1234';
      DBMS_OUTPUT.PUT_LINE ('v_test_var: '||v_test_var);
   EXCEPTION
      WHEN INVALID_NUMBER OR VALUE_ERROR 
      THEN
         v_test_var := 'ABCD'; 
         DBMS_OUTPUT.PUT_LINE ('An error has occurred in the inner block');
   END;
EXCEPTION
   WHEN INVALID_NUMBER OR VALUE_ERROR 
   THEN
      DBMS_OUTPUT.PUT_LINE ('An error has occurred in the program');
END;

-- For Example ch09_6a.sql
<<outer_block>>
DECLARE
   e_exception1 EXCEPTION;
   e_exception2 EXCEPTION;
BEGIN
   <<inner_block>>
   BEGIN
      RAISE e_exception1;
   EXCEPTION
      WHEN e_exception1 
      THEN
         RAISE e_exception2;
      WHEN e_exception2 
      THEN
         DBMS_OUTPUT.PUT_LINE ('An error has occurred in the inner block');
   END;
EXCEPTION
   WHEN e_exception2 
   THEN
      DBMS_OUTPUT.PUT_LINE ('An error has occurred in the program');
END;

-- For Example ch09_7a.sql
<<outer_block>>
DECLARE
   e_exception EXCEPTION;
BEGIN
   <<inner_block>>
   BEGIN
      RAISE e_exception;
   EXCEPTION
      WHEN e_exception 
      THEN
         RAISE;
   END;
EXCEPTION
   WHEN e_exception 
   THEN
      DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;

-- *** Web Chapter Exercises *** --
--For Example ch09_8a.sql
<<outer_block>>
DECLARE   
   v_zip   VARCHAR2(5) := '&sv_zip'; 
   v_name  VARCHAR2(50);   

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Check if provided zipcode is valid'); 
   SELECT zip
     INTO v_zip
     FROM zipcode
    WHERE zip = v_zip;

   <<inner_block>>
   BEGIN   
      SELECT first_name||' '||last_name
        INTO v_name
        FROM instructor
       WHERE zip = v_zip;

      DBMS_OUTPUT.PUT_LINE ('Instructor name is '||v_name);
   END;
END;

-- For Example ch09_8b.sql
<<outer_block>>
DECLARE
   v_zip   VARCHAR2(5) := '&sv_zip'; 
   v_name  VARCHAR2(50);   

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Check if provided zipcode is valid'); 
   SELECT zip
     INTO v_zip
     FROM zipcode
    WHERE zip = v_zip;

   <<inner_block>>
   BEGIN   
      SELECT first_name||' '||last_name
        INTO v_name
        FROM instructor
       WHERE zip = v_zip;

      DBMS_OUTPUT.PUT_LINE ('Instructor name is '||v_name);
   EXCEPTION
      WHEN TOO_MANY_ROWS
      THEN
         DBMS_OUTPUT.PUT_LINE 
            ('More than one instructor resides in this zip code');
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.PUT_LINE 
            ('There are no instructors residing in this zip code');
   END;
END;

-- For Example ch09_8c.sql
<<outer_block>>
DECLARE
   v_zip   VARCHAR2(5) := '&sv_zip'; 
   v_name  VARCHAR2(50);   

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Check if provided zipcode is valid'); 
   SELECT zip
     INTO v_zip
     FROM zipcode
    WHERE zip = v_zip;

   <<inner_block>>
   BEGIN   
      SELECT first_name||' '||last_name
        INTO v_name
        FROM instructor
       WHERE zip = v_zip;

      DBMS_OUTPUT.PUT_LINE ('Instructor name is '||v_name);
   END;
EXCEPTION
   WHEN TOO_MANY_ROWS
   THEN
      DBMS_OUTPUT.PUT_LINE 
         ('More than one instructor resides in this zip code');
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE 
         ('There are no instructors residing in this zip code');
END;

-- For Example ch09_9a.sql
DECLARE
   v_instructor_id     NUMBER := &sv_instructor_id;
   v_tot_sections      NUMBER;
   v_name              VARCHAR2(30);
   e_too_many_sections EXCEPTION;
BEGIN
   SELECT RTRIM(first_name)||' '||RTRIM(last_name)
     INTO v_name
     FROM instructor
    WHERE instructor_id = v_instructor_id;

   SELECT COUNT(*) 
     INTO v_tot_sections      
     FROM section
    WHERE instructor_id = v_instructor_id;  

   IF v_tot_sections >= 10 
   THEN
      RAISE e_too_many_sections;
   ELSE
      DBMS_OUTPUT.PUT_LINE 
         ('Instructor, '||v_name||', teaches '||v_tot_sections||' sections');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('This is not a valid instructor');

   WHEN e_too_many_sections 
   THEN
      DBMS_OUTPUT.PUT_LINE ('Instructor, '||v_name||', teaches too much');
END;

-- For Example ch09_10a.sql
DECLARE
   v_my_name VARCHAR2(15) := 'THIS IS A REALLY LONG NAME';
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;

-- For Example ch09_10b.sql
DECLARE
   v_my_name VARCHAR2(15);
BEGIN
   v_my_name := 'THIS IS A REALLY LONG NAME';
   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;

-- For Example ch09_10c.sql
DECLARE
   v_my_name VARCHAR2(15) := 'MY NAME';
BEGIN
   DBMS_OUTPUT.PUT_LINE ('My name is '||v_my_name);
   
   DECLARE
      v_your_name VARCHAR2(15) := '&sv_your_name';
   BEGIN
      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;

-- For Example ch09_10d.sql
DECLARE
   v_my_name VARCHAR2(15) := 'MY NAME';
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
         RAISE;
   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;

-- For Example ch09_11a.sql
DECLARE
   v_course_no   NUMBER := 430;
   v_total       NUMBER;
   e_no_sections EXCEPTION;
BEGIN
   BEGIN
      SELECT COUNT(*)
        INTO v_total
        FROM section
       WHERE course_no = v_course_no;
         
      IF v_total = 0 
      THEN
         RAISE e_no_sections;
      ELSE
         DBMS_OUTPUT.PUT_LINE ('Course, '||v_course_no||' has '||v_total||' sections');
      END IF;
   EXCEPTION
      WHEN e_no_sections 
      THEN
         DBMS_OUTPUT.PUT_LINE ('There are no sections for course '||v_course_no);  
   END;
END;

-- For Example ch09_11b.sql
DECLARE
   v_course_no   NUMBER := 430;
   v_total       NUMBER;
   e_no_sections EXCEPTION;
BEGIN
   BEGIN
      SELECT COUNT(*)
        INTO v_total
        FROM section
       WHERE course_no = v_course_no;
         
      IF v_total = 0 
      THEN
         RAISE e_no_sections;
      ELSE
         DBMS_OUTPUT.PUT_LINE ('Course, '||v_course_no||' has '||v_total||' sections');
      END IF;
   EXCEPTION
      WHEN e_no_sections 
      THEN
         RAISE;
   END;

EXCEPTION
   WHEN e_no_sections 
   THEN
      DBMS_OUTPUT.PUT_LINE ('There are no sections for course '||v_course_no);  
END;

-- For Example ch09_12a.sql
DECLARE
   v_section_id        NUMBER := &sv_section_id;
   v_total_students    NUMBER;
   e_too_many_students EXCEPTION;
BEGIN
   -- Calculate number of students enrolled
   SELECT COUNT(*)
     INTO v_total_students
     FROM enrollment
    WHERE section_id = v_section_id;
         
   IF v_total_students >= 10 
   THEN
      RAISE e_too_many_students;
   ELSE
      DBMS_OUTPUT.PUT_LINE 
         ('There are '||v_total_students||' students in section '||v_section_id);
   END IF;
EXCEPTION
   WHEN e_too_many_students 
   THEN
      DBMS_OUTPUT.PUT_LINE 
         ('There are too many students in section '||v_section_id);  
END;

-- For Example ch09_12b.sql
DECLARE
   v_section_id        NUMBER := &sv_section_id;
   v_total_students    NUMBER;
   e_too_many_students EXCEPTION;
BEGIN
   -- Add inner block
   BEGIN
      -- Calculate number of students enrolled
      SELECT COUNT(*)
        INTO v_total_students
        FROM enrollment
       WHERE section_id = v_section_id;
         
      IF v_total_students >= 10 
      THEN
         RAISE e_too_many_students;
      ELSE
         DBMS_OUTPUT.PUT_LINE 
            ('There are '||v_total_students||' students in section '||
             v_section_id);
      END IF;
   -- Re-raise exception
   EXCEPTION
      WHEN e_too_many_students 
      THEN
         RAISE;
   END;
EXCEPTION
   WHEN e_too_many_students 
   THEN
      DBMS_OUTPUT.PUT_LINE 
         ('There are too many students in section '||v_section_id);  
END;

-- For Example ch09_12c.sql
-- Outer PL/SQL block
BEGIN
   -- This block became inner PL/SQL block
   DECLARE
      v_section_id        NUMBER := &sv_section_id;
      v_total_students    NUMBER;
      e_too_many_students EXCEPTION;
   BEGIN
      -- Calculate number of students enrolled
      SELECT COUNT(*)
        INTO v_total_students
        FROM enrollment
       WHERE section_id = v_section_id;
         
      IF v_total_students >= 10 
      THEN
         RAISE e_too_many_students;
      ELSE
         DBMS_OUTPUT.PUT_LINE 
            ('There are '||v_total_students||' students in section '||
             v_section_id);
      END IF;
   EXCEPTION
      WHEN e_too_many_students 
      THEN
         RAISE;
   END;

EXCEPTION
   WHEN e_too_many_students 
   THEN
      DBMS_OUTPUT.PUT_LINE 
         ('There are too many students in section '||v_section_id);  
END;

-- For Example ch09_12d.sql
-- Outer PL/SQL block
DECLARE
   v_section_id        NUMBER := &sv_section_id;
   e_too_many_students EXCEPTION;
BEGIN
   -- This block became inner PL/SQL block
   DECLARE
      v_total_students NUMBER;
   BEGIN
      -- Calculate number of students enrolled
      SELECT COUNT(*)
        INTO v_total_students
        FROM enrollment
       WHERE section_id = v_section_id;
         
      IF v_total_students >= 10 
      THEN
         RAISE e_too_many_students;
      ELSE
         DBMS_OUTPUT.PUT_LINE 
            ('There are '||v_total_students||' students in section '||
             v_section_id);
      END IF;
   EXCEPTION
      WHEN e_too_many_students 
      THEN
         RAISE;
   END;

EXCEPTION
   WHEN e_too_many_students 
   THEN
      DBMS_OUTPUT.PUT_LINE 
         ('There are too many students for section '||v_section_id);  
END;