问题描述:
教材中有这么一段PL/SQL代码块,在测试过程中,我故意为替换变量sv_zip赋值123456,让其溢出,看看代码中的异常处理部分能否捕捉到这一异常。
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 || ' students');
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 occurred');
END;
没想到,代码直接报错。
这个错误属于数据长度不匹配导致的“VALUE_ERROR”,但是这段代码并没有捕捉到该错误,原因可能是PL/SQL声明部分出错,并不在异常处理的作用范围内。
我的直觉告诉我,在代码外围再“包”一层,通过嵌套说不定可以捕捉该错误。
修改后的代码如下:
/* Formatted on 2018/11/3 3:26:30 (QP5 v5.256.13226.35538) */
BEGIN
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 || ' students');
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 occurred');
END;
EXCEPTION
WHEN VALUE_ERROR OR INVALID_NUMBER
THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred and was caught in outter block');
END;
相比之下,这段代码的健壮性得到了提高!