EXCEPTION WHEN exception1 [OR exception2...] THEN statement1; statement2; ... [WHEN exception3 [OR exception4...] THEN statement1; statement2; ... [when OTHERS THEN statement1; statement2; ...示例:
DECLARE v_ename emp.ename%TYPE; BEGIN SELECT ename INTO v_ename FROM emp WHERE empno = &NO; dbms_output.put_line(‘雇员名:‘ || v_ename); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line(‘雇员号不存在,请核实雇员名‘); END;
CREATE TYPE emp_type AS object( NAME VARCHAR2(10), sal NUMBER(6,2) );捕捉并处理该例外的示例如下:
DECLARE v_emp emp_type; --v_emp emp_type := emp_type(‘‘,0); BEGIN v_emp.name := ‘张三‘; v_emp.sal := 1000; EXCEPTION WHEN ACCESS_INTO_NULL THEN dbms_output.put_line(‘首先初始化emp‘); END;(2)CASE_NOT_FOUND
DECLARE v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = &NO; CASE WHEN v_sal < 1000 THEN UPDATE emp SET sal = sal + 100 WHERE empno = &NO; WHEN v_sal < 2000 THEN UPDATE emp SET sal = sal + 150 WHERE empno = &NO; WHEN v_sal < 3000 THEN UPDATE emp SET sal = sal + 200 WHERE empno = &NO; END CASE; EXCEPTION WHEN CASE_NOT_FOUND THEN dbms_output.put_line(‘在CASE语句中缺少与‘ || v_sal || ‘相关的条件‘); END;(3)COLLECTION_IS_NULL
DECLARE TYPE ename_table_type IS TABLE OF emp.ename%TYPE; ename_table ename_table_type; BEGIN SELECT ename INTO ename_table(1) FROM emp WHERE empno = &NO; dbms_output.put_line(‘雇员名:‘ || ename_table(1)); EXCEPTION WHEN COLLECTION_IS_NULL THEN dbms_output.put_line(‘必须使用构造方法初始化集合元素‘); END;(4)CURSOR_ALREADY_OPEN
DECLARE CURSOR cur_emp IS SELECT ename,sal FROM emp; BEGIN --打开游标 OPEN cur_emp; FOR emp_record IN cur_emp LOOP dbms_output.put_line(emp_record.ename); END LOOP; EXCEPTION WHEN CURSOR_ALREADY_OPEN THEN dbms_output.put_line(‘游标已经打开‘); END;(5)DUP_VAL_ON_INDEX
BEGIN UPDATE dept SET deptno = 40 WHERE deptno = &NO; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line(‘在deptno列上不允许重复值‘); END;(6)INVALID_CURSOR
DECLARE CURSOR cur_emp IS SELECT ename,sal FROM emp; TYPE emp_record_type IS RECORD( ename emp.ename%TYPE, sal emp.sal%TYPE ); emp_record emp_record_type; BEGIN --未打开游标 FETCH cur_emp INTO emp_record; --关闭游标 CLOSE cur_emp; EXCEPTION WHEN INVALID_CURSOR THEN dbms_output.put_line(‘请检查游标是否已经打开‘); END;(7)INVALID_NUMBER
BEGIN UPDATE emp SET sal = sal + ‘1OO‘; EXCEPTION WHEN INVALID_NUMBER THEN dbms_output.put_line(‘输入的数字值不正确‘); END;(8)NO_DATA_FOUND
DECLARE v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE LOWER(ename) = LOWER(‘&ename‘); dbms_output.put_line(‘工资:‘ || v_sal); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line(‘不存在该雇员‘); END;(9)TOO_MANY_ROWS
DECLARE v_ename emp.ename%TYPE; BEGIN SELECT ename INTO v_ename FROM emp WHERE sal = 3600; EXCEPTION WHEN TOO_MANY_ROWS THEN dbms_output.put_line(‘返回多行‘); END;(10)ZERO_DIVIDE
DECLARE num1 INT := 100; num2 INT := 0; num3 NUMBER(6,2); BEGIN num3 := num1/num2; EXCEPTION WHEN ZERO_DIVIDE THEN dbms_output.put_line(‘除数不能为0‘); END;(11)SUBSCRIPT_BEYOND_COUNT
DECLARE TYPE emp_array_type IS VARRAY(20) OF VARCHAR2(20); emp_array emp_array_type := emp_array_type(‘‘,‘‘); BEGIN dbms_output.put_line(emp_array(3)); EXCEPTION WHEN SUBSCRIPT_BEYOND_COUNT THEN dbms_output.put_line(‘超出下标范围‘); END;(12)SUBSCRIPT_OUTSIDE_LIMIT
DECLARE TYPE emp_array_type IS VARRAY(20) OF VARCHAR2(20); emp_array emp_array_type := emp_array_type(‘‘,‘‘); BEGIN dbms_output.put_line(emp_array(-1)); EXCEPTION WHEN SUBSCRIPT_OUTSIDE_LIMIT THEN dbms_output.put_line(‘嵌套表或VARRAY下标不能为负‘); END;(13)VALUE_ERROR
DECLARE v_ename VARCHAR2(2); BEGIN SELECT ename INTO v_ename FROM emp WHERE empno = &NO; dbms_output.put_line(v_ename); EXCEPTION WHEN VALUE_ERROR THEN dbms_output.put_line(‘变量尺寸不足‘); END;
PL/SQL_处理例外1(预定义例外),布布扣,bubuko.com
原文:http://blog.csdn.net/com185272358/article/details/21785269