异常处理

1.内置异常：

DECLARE
    v_student_name VARCHAR2(50);
BEGIN
    SELECT first_name || ' ' || last_name
      INTO v_student_name
      FROM student
     WHERE student_id = 103;
    dbms_output.put_line('Student name is ' || v_student_name);
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('There is no such student');
END;

DECLARE
    v_instructor_id   NUMBER := &sv_instuctor_id;
    v_instructor_name VARCHAR2(50);
BEGIN
    SELECT first_name || ' ' || last_name
      INTO v_instructor_name
      FROM instructor
     WHERE instructor_id = v_instructor_id;
    dbms_output.put_line('Instructor name is ' || v_instructor_name);
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('An error has occurred');
END;

DECLARE
    v_student_id NUMBER := &sv_student_id;
    v_enrolled   VARCHAR2(3) := 'NO';
BEGIN
    dbms_output.put_line('Check if the student is enrolled');
    SELECT 'YES'
      INTO v_enrolled
      FROM enrollment
     WHERE student_id = v_student_id;
    dbms_output.put_line('The student is enrolled into one course');
EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('The student is not enrolled');
    WHEN too_many_rows THEN
        dbms_output.put_line('The student is enrolled in too many courseed');
END;

DECLARE
    v_exists         NUMBER(1);
    v_total_students NUMBER(1);
    v_zip            CHAR(5) := '&sv_zip';
BEGIN
    SELECT COUNT(*) INTO v_exists FROM zipcode WHERE zip = v_zip;
    IF v_exists != 0
    THEN
        SELECT COUNT(*)
          INTO v_total_students
          FROM student
         WHERE zip = v_zip;
        dbms_output.put_line('There are ' || v_total_students || ' student');
    ELSE
        dbms_output.put_line(v_zip || 'is not a valid zip ');
    END IF;
EXCEPTION
    WHEN value_error
         OR invalid_number THEN
        dbms_output.put_line('An error has occoured');
END;

显示姓名：

DECLARE
    v_exists       NUMBER(1);
    v_student_name VARCHAR2(30);
    v_zip          CHAR(5) := '&sv_zip';
BEGIN
    SELECT COUNT(*) INTO v_exists FROM zipcode WHERE zip = v_zip;
    IF v_exists != 0
    THEN
        SELECT first_name || ' ' || last_name
          INTO v_student_name
          FROM student
         WHERE zip = v_zip
        AND rownum = 1;
        dbms_output.put_line('Student name  is ' || v_student_name);
    ELSE
        dbms_output.put_line(v_zip || 'is not a valid zip ');
    END IF;
EXCEPTION
    WHEN value_error
         OR invalid_number THEN
        dbms_output.put_line('An error has occoured');
    WHEN no_data_found THEN
        dbms_output.put_line('There are no students for this value of zip code');
END;

异常作用范围：

DECLARE
    v_exists       NUMBER(1);
    v_student_name VARCHAR2(30);
    v_zip          CHAR(5) := '&sv_zip';
BEGIN
    SELECT COUNT(*) INTO v_exists FROM zipcode WHERE zip = v_zip;
    IF v_exists != 0
    THEN
        SELECT first_name || ' ' || last_name
          INTO v_student_name
          FROM student
         WHERE zip = v_zip
        AND rownum = 1;
        dbms_output.put_line('Student name  is ' || v_student_name);
    ELSE
        dbms_output.put_line(v_zip || 'is not a valid zip ');
    END IF;
EXCEPTION
    WHEN value_error
         OR invalid_number THEN
        dbms_output.put_line('An error has occoured');
    WHEN no_data_found THEN
        dbms_output.put_line('There are no students for this value of zip code');
END;

DECLARE
    v_zip   VARCHAR2(5) := '&sv_zip';
    v_total NUMBER(1);
    --outer block
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 COUNT(*) INTO v_total FROM student WHERE zip = v_zip;
        dbms_output.put_line('There are ' || v_total ||
                             ' student for zipcode ' || v_zip);
    EXCEPTION
        WHEN value_error
             OR invalid_number THEN
            dbms_output.put_line('An error has occurred');
    END;
    dbms_output.put_line('Done....');
END;

用户自定义异常：

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;
    ELSE
        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');
    END IF;
    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;

RAISE 语句应该与IF语句一起使用，这一点非常重要，否则的话每次执行时，执行权会转到该语句的异常处理部分。

DECLARE
    v_instructor_id NUMBER := &sv_instuctor_id;
    v_tot_sections  NUMBER;
    v_name          VARCHAR2(30);
    e_too_many_sections EXCEPTION;
BEGIN
    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
        SELECT rtrim(first_name) || ' ' || rtrim(last_name)
          INTO v_name
          FROM instructor
         WHERE instructor_id = v_instructor_id;
        dbms_output.put_line('Instructor , ' || v_name || ', teachers ' ||
                             v_tot_sections || ' sections');
    END IF;
EXCEPTION
    WHEN e_too_many_sections THEN
        dbms_output.put_line('This instructor teaches too much');
    WHEN no_data_found THEN
        dbms_output.put_line('No data found');
END;

显示教师的名字在错误信息中：

DECLARE
    v_instructor_id NUMBER := &sv_instuctor_id;
    v_tot_sections  NUMBER;
    v_name          VARCHAR2(30);
    e_too_many_sections EXCEPTION;
BEGIN
    SELECT COUNT(*)
      INTO v_tot_sections
      FROM section
     WHERE instructor_id = v_instructor_id;
    SELECT rtrim(first_name) || ' ' || rtrim(last_name)
      INTO v_name
      FROM instructor
     WHERE instructor_id = v_instructor_id;
    IF v_tot_sections >= 10
    THEN
        RAISE e_too_many_sections;
    ELSE
        SELECT rtrim(first_name) || ' ' || rtrim(last_name)
          INTO v_name
          FROM instructor
         WHERE instructor_id = v_instructor_id;
        dbms_output.put_line('Instructor , ' || v_name || ', teachers ' ||
                             v_tot_sections || ' sections');
    END IF;
EXCEPTION
    WHEN e_too_many_sections THEN
        dbms_output.put_line('Instructor, ' || v_name ||
                             ', teaches too much');
    WHEN no_data_found THEN
        dbms_output.put_line('No data found');
END;

异常传播：

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 ' || 'program');
END;

当内部语句块的声明部分发生运行时错误时，该异常会立即传播到外包 语句块.

--outer block
DECLARE
    e_exception1 EXCEPTION;
    e_excetpion2 EXCEPTION;
BEGIN
    --inner block
    BEGIN
        RAISE e_exception1;
    EXCEPTION
        WHEN e_exception1 THEN
            RAISE e_excetpion2;
        WHEN e_excetpion2 THEN
            dbms_output.put_line('An error has occurred in ' ||
                                 'the inner block');
    END;
EXCEPTION
    WHEN e_excetpion2 THEN
        dbms_output.put_line('An error has occurred in ' || 'the program');
END;

在此声明了两个异常：e_exception1 和e_exception2.在内部语句块中，使用raise语句抛出e_exception1 异常。在该语句块的异常处理部分，异常e_exception1 会尝试抛出e_exception2异常。尽管内部语句块中存在e_exception2处理程序，但是执行权会转到外部语句块。之所以发生这样的情况，原因在于该语句块的异常处理部分只抛出一个异常，只有当一个异常抛出后，才能处理另一个异常，不能同时抛出多个异常。

再次抛出异常：

--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;

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;
    dbms_output.put_line('Done...');
END;

把异常抛到外部语句块：

DECLARE
    v_course_no NUMBER;
    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;
    dbms_output.put_line('Done...');
EXCEPTION
    WHEN e_no_sections THEN
        dbms_output.put_line('There are no sections for course' ||
                             v_course_no);
END;

RAISE_APPLICATION_ERROR：

DECLARE
    v_student_id    student.student_id%TYPE := &sv_student_id;
    v_total_courses NUMBER;
BEGIN
    IF v_student_id < 0
    THEN
        raise_application_error(-20000, 'An id cannot be negative');
    ELSE
        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');
    END IF;
END;