Oracle例外用法实例详解
本文实例讲述了Oracle例外用法。分享给大家供大家参考，具体如下：
一、例外分类
oracle将例外分为预定义例外、非预定义例外和自定义例外三种。
1)、预定义例外用于处理常见的oracle错误。
2)、非预定义例外用于处理预定义例外不能处理的例外。
3)、自定义例外用于处理与oracle错误无关的其它情况。
下面通过一个小案例演示如果不处理例外看会出现什么情况？
编写一个存储过程，可接收雇员的编号，并显示该雇员的姓名。
sql代码如下：
SETSERVEROUTPUTON;
DECLARE
V_ENAMEEMP.ENAME%TYPE;
BEGIN
SELECTENAMEINTOV_ENAMEFROMEMPWHEREEMPNO=&GNO;
DBMS_OUTPUT.PUT_LINE('名字：'||V_ENAME);
END;
/
随便输入不存在的编号，回车，会抛出如下异常：
ORA-01403:未找到数据
ORA-06512:在line6
例外捕获的sql代码如下：
SETSERVEROUTPUTON;
DECLARE
V_ENAMEEMP.ENAME%TYPE;
BEGIN
SELECTENAMEINTOV_ENAMEFROMEMPWHEREEMPNO=&GNO;
DBMS_OUTPUT.PUT_LINE('名字：'||V_ENAME);
EXCEPTION
WHENno_data_foundTHEN
DBMS_OUTPUT.PUT_LINE('编号未找到！');
END;
/
随便输入不存在的编号，回车，会友情提示：编号未找到！
二、处理预定义例外
预定义例外是由pl/sql所提供的系统例外。当pl/sql应用程序违反了oracle规定的限制时，则会隐含的触发一个内部例外。pl/sql为开发人员提供了二十多个预定义例外。我们给大家介绍常用的例外。
1)、case_not_found预定义例外
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found例外：
SETSERVEROUTPUTON;
CREATEORREPLACEPROCEDURESP_PRO6(SPNONUMBER)IS
V_SALEMP.SAL%TYPE;
BEGIN
SELECTSALINTOV_SALFROMEMPWHEREEMPNO=SPNO;
CASE
WHENV_SAL<1000THEN
UPDATEEMPSETSAL=SAL+100WHEREEMPNO=SPNO;
WHENV_SAL<2000THEN
UPDATEEMPSETSAL=SAL+200WHEREEMPNO=SPNO;
ENDCASE;
EXCEPTION
WHENCASE_NOT_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE('case语句没有与'||V_SAL||'相匹配的条件');
END;
/
--调用存储过程
SQL>EXECSP_PRO6(7369);
case语句没有与4444相匹配的条件
2)、cursor_already_open预定义例外
当重新打开已经打开的游标时，会隐含的触发cursor_already_open例外
DECLARE
CURSOREMP_CURSORIS
SELECTENAME,SALFROMEMP;
BEGIN
OPENEMP_CURSOR;--声明时游标已打开，所以没必要再次打开
FOREMP_RECORD1INEMP_CURSORLOOP
DBMS_OUTPUT.PUT_LINE(EMP_RECORD1.ENAME);
ENDLOOP;
EXCEPTION
WHENCURSOR_ALREADY_OPENTHEN
DBMS_OUTPUT.PUT_LINE('游标已经打开');
END;
/
3)、dup_val_on_index预定义例外
在唯一索引所对应的列上插入重复的值时，会隐含的触发例外
BEGIN
INSERTINTODEPTVALUES(10,'公关部','北京');
EXCEPTION
WHENDUP_VAL_ON_INDEXTHEN
DBMS_OUTPUT.PUT_LINE('在deptno列上不能出现重复值');
END;
/
4)、invalid_cursorn预定义例外
当试图在不合法的游标上执行操作时，会触发该例外
例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标。则会触发该例外
DECLARE
CURSOREMP_CURSORIS
SELECTENAME,SALFROMEMP;
EMP_RECORDEMP_CURSOR%ROWTYPE;
BEGIN
--openemp_cursor;--打开游标
FETCHEMP_CURSORINTOEMP_RECORD;
DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);
CLOSEEMP_CURSOR;
EXCEPTION
WHENINVALID_CURSORTHEN
DBMS_OUTPUT.PUT_LINE('请检测游标是否打开');
END;
/
5)、invalid_number预定义例外
当输入的数据有误时，会触发该例外
比如：数字100写成了loo就会触发该例外
SETSERVEROUTPUTON;
BEGIN
UPDATEEMPSETSAL=SAL+'AAA';
EXCEPTION
WHENINVALID_NUMBERTHEN
DBMS_OUTPUT.PUT_LINE('输入的数字不正确');
END;
/
6)、no_data_found预定义例外
下面是一个pl/sql块，当执行selectinto没有返回行，就会触发该例外
SETserveroutputON;
DECLARE
V_SALEMP.SAL%TYPE;
BEGIN
SELECTSALINTOV_SALFROMEMPWHEREENAME='ljq';
EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE('不存在该员工');
END;
/
7)、too_many_rows预定义例外
当执行selectinto语句时，如果返回超过了一行，则会触发该例外。
DECLARE
V_ENAMEEMP.ENAME%TYPE;
BEGIN
SELECTENAMEINTOV_ENAMEFROMEMP;
EXCEPTION
WHENTOO_MANY_ROWSTHEN
DBMS_OUTPUT.PUT_LINE('返回了多行');
END;
/
8)、zero_divide预定义例外
当执行2/0语句时，则会触发该例外
9)、value_error预定义例外
当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外value_error
其它预定义例外(这些例外不是在pl/sql里触发的，而是在用oracle时触发的，所以取名叫其它预定义例外)
1、login_denied
当用户非法登录时，会触发该例外
2、not_logged_on
如果用户没有登录就执行dml操作，就会触发该例外
3、storage_error
如果超过了内存空间或是内存被损坏，就触发该例外
4、timeout_on_resource
如果oracle在等待资源时，出现了超时就触发该例外
三、非预定义例外
非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle错误，而当使用pl/sql开发应用程序时，可能会遇到其它的一些oracle错误。比如在pl/sql块中执行dml语句时，违反了约束规定等等。在这样的情况下，也可以处理oracle的各种例外，因为非预定义例外用的不多，这里我就不举例了。
四、处理自定义例外
预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle错误会隐含的触发相应的例外；而自定义例外与oracle错误没有任何关联，它是由开发人员为特定情况所定义的例外.
问题：请编写一个pl/sql块，接收一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。
CREATEORREPLACEPROCEDUREEX_TEST(SPNONUMBER)IS BEGIN UPDATEEMPSETSAL=SAL+1000WHEREEMPNO=SPNO; END; / --调用存储过程， EXECEX_TEST(56);
这里，编号为56是不存在的，刚才的报异常了，为什么现在不报异常呢？
因为刚才的是select语句
怎么解决这个问题呢？修改代码，如下：
--自定义例外
CREATEORREPLACEPROCEDUREEX_TEST(SPNONUMBER)IS
--定义一个例外
MYEXEXCEPTION;
BEGIN
--更新用户sal
UPDATEEMPSETSAL=SAL+1000WHEREEMPNO=SPNO;
--sql%notfound这是表示没有update
--raisemyex;触发myex
IFSQL%NOTFOUNDTHENRAISEMYEX;
ENDIF;
EXCEPTION
WHENMYEXTHENDBMS_OUTPUT.PUT_LINE('没有更新任何用户');
END;
/
现在再测试一次：
SQL>execex_test(56);
没有更新任何用户
希望本文所述对大家Oracle数据库程序设计有所帮助。