ORACLE中的异常处理

1、ORALCE自带一些命名的系统异常，如下：

OracleExceptionName	OracleError	Explanation	中文注释
DUP_VAL_ON_INDEX	ORA-00001	YoutriedtoexecuteanINSERTorUPDATEstatementthathascreatedaduplicatevalueinafieldrestrictedbyauniqueindex.	插入或者更新语句，与唯一索引相冲突。
TIMEOUT_ON_RESOURCE	ORA-00051	Youwerewaitingforaresourceandyoutimedout.	等待资源超时
TRANSACTION_BACKED_OUT	ORA-00061	Theremoteportionofatransactionhasrolledback.	远程交易的部份交易已经回滚
INVALID_CURSOR	ORA-01001	Youtriedtoreferenceacursorthatdoesnotyetexist.Thismayhavehappenedbecauseyou'veexecutedaFETCHcursororCLOSEcursorbeforeOPENingthecursor.	引用一个不存在的游标，如FETCH或者是CLOSE在其OPEN之前等。
NOT_LOGGED_ON	ORA-01012	YoutriedtoexecuteacalltoOraclebeforeloggingin.	在登陆ORACLE之前执行调用错误
LOGIN_DENIED	ORA-01017	YoutriedtologintoOraclewithaninvalidusername/passwordcombination.	登陆时用户名或者密码非法。
NO_DATA_FOUND	ORA-01403	Youtriedoneofthefollowing: 1.YouexecutedaSELECTINTOstatementandnorowswerereturned. 2.Youreferencedanuninitializedrowinatable. 3.YoureadpasttheendoffilewiththeUTL_FILEpackage.	执行查询无数据、引用一个末初使化的表、通过UTL_FILE包调用到尾的文件
TOO_MANY_ROWS	ORA-01422	YoutriedtoexecuteaSELECTINTOstatementandmorethanonerowwasreturned.	采用SELECTINTO语句，但返回的记录超过了1条
ZERO_DIVIDE	ORA-01476	Youtriedtodivideanumberbyzero.	0为除数
INVALID_NUMBER	ORA-01722	YoutriedtoexecuteanSQLstatementthattriedtoconvertastringtoanumber,butitwasunsuccessful.	将字符串转换成数字，但是转换失败
STORAGE_ERROR	ORA-06500	Youranoutofmemoryormemorywascorrupted.	内存不足
PROGRAM_ERROR	ORA-06501	Thisisageneric"ContactOraclesupport"messagebecauseaninternalproblemwasencountered.	系统自身程序错误
VALUE_ERROR	ORA-06502	Youtriedtoperformanoperationandtherewasaerroronaconversion,truncation,orinvalidconstrainingofnumericorcharacterdata.	在执行转换、截断、非法转换数据到文本出错
CURSOR_ALREADY_OPEN	ORA-06511	Youtriedtoopenacursorthatisalreadyopen.	打开一个已经打开的游标

<!--EndFragment-->

2、也可以采用自定义的异常的名字：

declare

myexception exception;

begin

if 1<>2 then

raise myexception;

end if;

exception

when myexception then

/*注：raise_application_error的语法为raise_application_error(erorcd in int,erortx in varchar 2)，其中erorcd的值为20001到20999*/

raise_application_error(20001,'my exception happens');

when others then

raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

end

3、存储过程可能要涉及到层层调用，因此在每一次都需要写异常处理，这让程序会更健壮。