ORACLE 异常错误处理
本篇主要內(nèi)容如下：
5.1?異常處理概念
5.1.1?預(yù)定義的異常處理
5.1.2?非預(yù)定義的異常處理
5.1.3?用戶自定義的異常處理
5.1.4??用戶定義的異常處理
5.2?異常錯(cuò)誤傳播
5.2.1?在執(zhí)行部分引發(fā)異常錯(cuò)誤
5.2.2?在聲明部分引發(fā)異常錯(cuò)誤
5.3?異常錯(cuò)誤處理編程
5.4??在?PL/SQL?中使用?SQLCODE,?SQLERRM異常處理函數(shù)
?即使是寫得最好的PL/SQL程序也會(huì)遇到錯(cuò)誤或未預(yù)料到的事件。一個(gè)優(yōu)秀的程序都應(yīng)該能夠正確處理各種出錯(cuò)情況，并盡可能從錯(cuò)誤中恢復(fù)。任何ORACLE錯(cuò)誤(報(bào)告為ORA-xxxxx形式的Oracle錯(cuò)誤號(hào))、PL/SQL運(yùn)行錯(cuò)誤或用戶定義條件(不一寫是錯(cuò)誤)，都可以。當(dāng)然了，PL/SQL編譯錯(cuò)誤不能通過PL/SQL異常處理來(lái)處理，因?yàn)檫@些錯(cuò)誤發(fā)生在PL/SQL程序執(zhí)行之前。
ORACLE?提供異常情況(EXCEPTION)和異常處理(EXCEPTION?HANDLER)來(lái)實(shí)現(xiàn)錯(cuò)誤處理。
5.1?異常處理概念
異常情況處理(EXCEPTION)是用來(lái)處理正常執(zhí)行過程中未預(yù)料的事件,程序塊的異常處理預(yù)定義的錯(cuò)誤和自定義錯(cuò)誤,由于PL/SQL程序塊一旦產(chǎn)生異常而沒有指出如何處理時(shí),程序就會(huì)自動(dòng)終止整個(gè)程序運(yùn)行.
有三種類型的異常錯(cuò)誤：
??? 1．?預(yù)定義?(?Predefined?)錯(cuò)誤
??ORACLE預(yù)定義的異常情況大約有24個(gè)。對(duì)這種異常情況的處理，無(wú)需在程序中定義，由ORACLE自動(dòng)將其引發(fā)。
??? 2．?非預(yù)定義?(?Predefined?)錯(cuò)誤
?? 即其他標(biāo)準(zhǔn)的ORACLE錯(cuò)誤。對(duì)這種異常情況的處理，需要用戶在程序中定義，然后由ORACLE自動(dòng)將其引發(fā)。
??? 3．?用戶定義(User_define)?錯(cuò)誤
?程序執(zhí)行過程中，出現(xiàn)編程人員認(rèn)為的非正常情況。對(duì)這種異常情況的處理，需要用戶在程序中定義，然后顯式地在程序中將其引發(fā)。
異常處理部分一般放在?PL/SQL?程序體的后半部,結(jié)構(gòu)為:
EXCEPTION???WHEN?first_exception?THEN??<code?to?handle?first?exception?>
???WHEN?second_exception?THEN??<code?to?handle?second?exception?>
???WHEN?OTHERS?THEN??<code?to?handle?others?exception?>
END;
異常處理可以按任意次序排列,但?OTHERS?必須放在最后.
5.1.1?預(yù)定義的異常處理
?? 預(yù)定義說(shuō)明的部分?ORACLE?異常錯(cuò)誤
| 錯(cuò)誤號(hào) | 異常錯(cuò)誤信息名稱 | 說(shuō)明 |
| ORA-0001 | Dup_val_on_index | 違反了唯一性限制 |
| ORA-0051 | Timeout-on-resource | 在等待資源時(shí)發(fā)生超時(shí) |
| ORA-0061 | Transaction-backed-out | 由于發(fā)生死鎖事務(wù)被撤消 |
| ORA-1001 | Invalid-CURSOR | 試圖使用一個(gè)無(wú)效的游標(biāo) |
| ORA-1012 | Not-logged-on | 沒有連接到ORACLE |
| ORA-1017 | Login-denied | 無(wú)效的用戶名/口令 |
| ORA-1403 | No_data_found | SELECT?INTO沒有找到數(shù)據(jù) |
| ORA-1422 | Too_many_rows | SELECT?INTO?返回多行 |
| ORA-1476 | Zero-divide | 試圖被零除 |
| ORA-1722 | Invalid-NUMBER | 轉(zhuǎn)換一個(gè)數(shù)字失敗 |
| ORA-6500 | Storage-error | 內(nèi)存不夠引發(fā)的內(nèi)部錯(cuò)誤 |
| ORA-6501 | Program-error | 內(nèi)部錯(cuò)誤 |
| ORA-6502 | Value-error | 轉(zhuǎn)換或截?cái)噱e(cuò)誤 |
| ORA-6504 | Rowtype-mismatch | 宿主游標(biāo)變量與?PL/SQL變量有不兼容行類型 |
| ORA-6511 | CURSOR-already-OPEN | 試圖打開一個(gè)已處于打開狀態(tài)的游標(biāo) |
| ORA-6530 | Access-INTO-null | 試圖為null?對(duì)象的屬性賦值 |
| ORA-6531 | Collection-is-null | 試圖將Exists?以外的集合(?collection)方法應(yīng)用于一個(gè)null?pl/sql?表上或varray上 |
| ORA-6532 | Subscript-outside-limit | 對(duì)嵌套或varray索引得引用超出聲明范圍以外 |
| ORA-6533 | Subscript-beyond-count | 對(duì)嵌套或varray?索引得引用大于集合中元素的個(gè)數(shù). |
對(duì)這種異常情況的處理，只需在PL/SQL塊的異常處理部分，直接引用相應(yīng)的異常情況名，并對(duì)其完成相應(yīng)的異常錯(cuò)誤處理即可。
例1：更新指定員工工資，如工資小于1500，則加100；
?
DECLARE???v_empno?employees.employee_id%TYPE?:=?&empno;
???v_sal???employees.salary%TYPE;
BEGIN
???SELECT?salary?INTO?v_sal?FROM?employees?WHERE?employee_id?=?v_empno;
???IF?v_sal<=1500?THEN?
????????UPDATE?employees?SET?salary?=?salary?+?100?WHERE?employee_id=v_empno;?
????????DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'員工工資已更新!');?????
???ELSE
????????DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'員工工資已經(jīng)超過規(guī)定值!');
???END?IF;
EXCEPTION
???WHEN?NO_DATA_FOUND?THEN??
??????DBMS_OUTPUT.PUT_LINE('數(shù)據(jù)庫(kù)中沒有編碼為'||v_empno||'的員工');
???WHEN?TOO_MANY_ROWS?THEN
??????DBMS_OUTPUT.PUT_LINE('程序運(yùn)行錯(cuò)誤!請(qǐng)使用游標(biāo)');
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;?
?5.1.2?非預(yù)定義的異常處理
對(duì)于這類異常情況的處理，首先必須對(duì)非定義的ORACLE錯(cuò)誤進(jìn)行定義。步驟如下：
1.?在PL/SQL?塊的定義部分定義異常情況：
<異常情況>??EXCEPTION;2.?將其定義好的異常情況，與標(biāo)準(zhǔn)的ORACLE錯(cuò)誤聯(lián)系起來(lái)，使用EXCEPTION_INIT語(yǔ)句：
PRAGMA?EXCEPTION_INIT(<異常情況>,?<錯(cuò)誤代碼>);3.?在PL/SQL?塊的異常情況處理部分對(duì)異常情況做出相應(yīng)的處理。
例2：刪除指定部門的記錄信息，以確保該部門沒有員工。
INSERT?INTO?departments?VALUES(50,?'FINANCE',?'CHICAGO');DECLARE
???v_deptno?departments.department_id%TYPE?:=?&deptno;
???deptno_remaining?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(deptno_remaining,?-2292);
???/*?-2292?是違反一致性約束的錯(cuò)誤代碼?*/
BEGIN
???DELETE?FROM?departments?WHERE?department_id?=?v_deptno;
EXCEPTION
???WHEN?deptno_remaining?THEN?
??????DBMS_OUTPUT.PUT_LINE('違反數(shù)據(jù)完整性約束!');
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
5.1.3?用戶自定義的異常處理
當(dāng)與一個(gè)異常錯(cuò)誤相關(guān)的錯(cuò)誤出現(xiàn)時(shí)，就會(huì)隱含觸發(fā)該異常錯(cuò)誤。用戶定義的異常錯(cuò)誤是通過顯式使用?RAISE?語(yǔ)句來(lái)觸發(fā)。當(dāng)引發(fā)一個(gè)異常錯(cuò)誤時(shí)，控制就轉(zhuǎn)向到?EXCEPTION塊異常錯(cuò)誤部分，執(zhí)行錯(cuò)誤處理代碼。
對(duì)于這類異常情況的處理，步驟如下：
1．?在PL/SQL?塊的定義部分定義異常情況：
<異常情況>??EXCEPTION;2．?RAISE?<異常情況>；
3．?在PL/SQL?塊的異常情況處理部分對(duì)異常情況做出相應(yīng)的處理。
例3：更新指定員工工資，增加100；
DECLARE???v_empno?employees.employee_id%TYPE?:=&empno;
???no_result??EXCEPTION;
BEGIN
???UPDATE?employees?SET?salary?=?salary+100?WHERE?employee_id?=?v_empno;
???IF?SQL%NOTFOUND?THEN
??????RAISE?no_result;
???END?IF;
EXCEPTION
???WHEN?no_result?THEN?
??????DBMS_OUTPUT.PUT_LINE('你的數(shù)據(jù)更新語(yǔ)句失敗了!');
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
5.1.4??用戶定義的異常處理
調(diào)用DBMS_STANDARD(ORACLE提供的包)包所定義的RAISE_APPLICATION_ERROR過程，可以重新定義異常錯(cuò)誤消息，它為應(yīng)用程序提供了一種與ORACLE交互的方法。
RAISE_APPLICATION_ERROR?的語(yǔ)法如下：
RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]?);??? 這里的error_number?是從?–20,000?到?–20,999?之間的參數(shù)，
????error_message?是相應(yīng)的提示信息(<?2048?字節(jié))，
??keep_errors?為可選，如果keep_errors?=TRUE?,則新錯(cuò)誤將被添加到已經(jīng)引發(fā)的錯(cuò)誤列表中。如果keep_errors=FALSE(缺省),則新錯(cuò)誤將替換當(dāng)前的錯(cuò)誤列表。
例4：創(chuàng)建一個(gè)函數(shù)get_salary,?該函數(shù)檢索指定部門的工資總和，其中定義了-20991和-20992號(hào)錯(cuò)誤，分別處理參數(shù)為空和非法部門代碼兩種錯(cuò)誤：
CREATE?TABLE?errlog(??Errcode?NUMBER,
??Errtext?CHAR(40));
CREATE?OR?REPLACE?FUNCTION?get_salary(p_deptno?NUMBER)
RETURN?NUMBER?
AS
??v_sal?NUMBER;
BEGIN
??IF?p_deptno?IS?NULL?THEN
????RAISE_APPLICATION_ERROR(-20991,?’部門代碼為空’);
??ELSIF?p_deptno<0?THEN
????RAISE_APPLICATION_ERROR(-20992,?’無(wú)效的部門代碼’);
??ELSE
????SELECT?SUM(employees.salary)?INTO?v_sal?FROM?employees?
????WHERE?employees.department_id=p_deptno;
????RETURN?v_sal;
??END?IF;
END;
DECLARE?
??V_salary?NUMBER(7,2);
??V_sqlcode?NUMBER;
??V_sqlerr?VARCHAR2(512);
??Null_deptno?EXCEPTION;
??Invalid_deptno?EXCEPTION;
??PRAGMA?EXCEPTION_INIT(null_deptno,-20991);
??PRAGMA?EXCEPTION_INIT(invalid_deptno,?-20992);
BEGIN
??V_salary?:=get_salary(10);
??DBMS_OUTPUT.PUT_LINE('10號(hào)部門工資：'?||?TO_CHAR(V_salary));
??BEGIN
????V_salary?:=get_salary(-10);
??EXCEPTION
????WHEN?invalid_deptno?THEN
??????V_sqlcode?:=SQLCODE;
??????V_sqlerr??:=SQLERRM;
??????INSERT?INTO?errlog(errcode,?errtext)?
??????VALUES(v_sqlcode,?v_sqlerr);
??????COMMIT;
??END?inner1;
??V_salary?:=get_salary(20);
??DBMS_OUTPUT.PUT_LINE('部門號(hào)為20的工資為：'||TO_CHAR(V_salary));
??BEGIN
????V_salary?:=get_salary(NULL);
??END?inner2;
??V_salary?:=?get_salary(30);
??DBMS_OUTPUT.PUT_LINE('部門號(hào)為30的工資為：'||TO_CHAR(V_salary));
??EXCEPTION
????WHEN?null_deptno?THEN
??????V_sqlcode?:=SQLCODE;
??????V_sqlerr??:=SQLERRM;
??????INSERT?INTO?errlog(errcode,?errtext)?VALUES(v_sqlcode,?v_sqlerr);
??????COMMIT;
????WHEN?OTHERS?THEN
?????????DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END?outer;
例5：定義觸發(fā)器，使用RAISE_APPLICATION_ERROR阻止沒有員工姓名的新員式記錄插入：
CREATE?OR?REPLACE?TRIGGER?tr_insert_empBEFORE?INSERT?ON?employees
FOR?EACH?ROW
BEGIN
??IF?:new.first_name?IS?NULL?OR?:new.last_name?is?null?THEN
????RAISE_APPLICATION_ERROR(-20000,'Employee?must?have?a?name.');
??END?IF;
END;
5.2?異常錯(cuò)誤傳播
????由于異常錯(cuò)誤可以在聲明部分和執(zhí)行部分以及異常錯(cuò)誤部分出現(xiàn)，因而在不同部分引發(fā)的異常錯(cuò)誤也不一樣。
5.2.1?在執(zhí)行部分引發(fā)異常錯(cuò)誤
????當(dāng)一個(gè)異常錯(cuò)誤在執(zhí)行部分引發(fā)時(shí)，有下列情況：
l?如果當(dāng)前塊對(duì)該異常錯(cuò)誤設(shè)置了處理，則執(zhí)行它并成功完成該塊的執(zhí)行，然后控制轉(zhuǎn)給包含塊。
l?如果沒有對(duì)當(dāng)前塊異常錯(cuò)誤設(shè)置定義處理器，則通過在包含塊中引發(fā)它來(lái)傳播異常錯(cuò)誤。然后對(duì)該包含塊執(zhí)行步驟1)。
5.2.2?在聲明部分引發(fā)異常錯(cuò)誤
????如果在聲明部分引起異常情況，即在聲明部分出現(xiàn)錯(cuò)誤，那么該錯(cuò)誤就能影響到其它的塊。比如在有如下的PL/SQL程序：
DECLARE????name?varchar2(12):='EricHu';
????其它語(yǔ)句
BEGIN
????其它語(yǔ)句
EXCEPTION
????WHEN?OTHERS?THEN?
????其它語(yǔ)句
END;
???? 例子中，由于Abc?number(3)=’abc’;?出錯(cuò)，盡管在EXCEPTION中說(shuō)明了WHEN?OTHERS?THEN語(yǔ)句，但WHEN?OTHERS?THEN也不會(huì)被執(zhí)行。?但是如果在該錯(cuò)誤語(yǔ)句塊的外部有一個(gè)異常錯(cuò)誤，則該錯(cuò)誤能被抓住，如：
BEGIN????DECLARE
????name?varchar2(12):='EricHu';
????其它語(yǔ)句
???BEGIN
????其它語(yǔ)句
???EXCEPTION
????WHEN?OTHERS?THEN?
????其它語(yǔ)句
????END;
EXCEPTION
WHEN?OTHERS?THEN?
????其它語(yǔ)句
END;
5.3?異常錯(cuò)誤處理編程
????在一般的應(yīng)用處理中，建議程序人員要用異常處理，因?yàn)槿绻绦蛑胁宦暶魅魏萎惓Ｌ幚?#xff0c;則在程序運(yùn)行出錯(cuò)時(shí)，程序就被終止，并且也不提示任何信息。下面是使用系統(tǒng)提供的異常來(lái)編程的例子。
5.4??在?PL/SQL?中使用?SQLCODE,?SQLERRM異常處理函數(shù)
????由于ORACLE?的錯(cuò)信息最大長(zhǎng)度是512字節(jié)，為了得到完整的錯(cuò)誤提示信息，我們可用?SQLERRM和?SUBSTR?函數(shù)一起得到錯(cuò)誤提示信息，方便進(jìn)行錯(cuò)誤，特別是如果WHEN?OTHERS異常處理器時(shí)更為方便。
SQLCODE??返回遇到的Oracle錯(cuò)誤號(hào),
SQLERRM??返回遇到的Oracle錯(cuò)誤信息.
如:??SQLCODE=-100???è?SQLERRM=’no_data_found?‘
?SQLCODE=0??????è?SQLERRM=’normal,?successfual?completion’
例6.?將ORACLE錯(cuò)誤代碼及其信息存入錯(cuò)誤代碼表
CREATE?TABLE?errors?(errnum?NUMBER(4),?errmsg?VARCHAR2(100));DECLARE
???err_msg??VARCHAR2(100);
BEGIN
???/*??得到所有?ORACLE?錯(cuò)誤信息??*/
???FOR?err_num?IN?-100?..?0?LOOP
??????err_msg?:=?SQLERRM(err_num);
??????INSERT?INTO?errors?VALUES(err_num,?err_msg);
???END?LOOP;
END;
DROP?TABLE?errors;
例7.?查詢ORACLE錯(cuò)誤代碼；
BEGIN???INSERT?INTO?employees(employee_id,?first_name,last_name,hire_date,department_id)
???VALUES(2222,?'Eric','Hu',?SYSDATE,?20);
???DBMS_OUTPUT.PUT_LINE('插入數(shù)據(jù)記錄成功!');
???INSERT?INTO?employees(employee_id,?first_name,last_name,hire_date,department_id)
???VALUES(2222,?'胡','勇',?SYSDATE,?20);
???DBMS_OUTPUT.PUT_LINE('插入數(shù)據(jù)記錄成功!');
EXCEPTION
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
例8.?利用ORACLE錯(cuò)誤代碼，編寫異常錯(cuò)誤處理代碼；
DECLARE???empno_remaining?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(empno_remaining,?-1);
???/*?-1?是違反唯一約束條件的錯(cuò)誤代碼?*/
BEGIN
???INSERT?INTO?employees(employee_id,?first_name,last_name,hire_date,department_id)
???VALUES(3333,?'Eric','Hu',?SYSDATE,?20);
???DBMS_OUTPUT.PUT_LINE('插入數(shù)據(jù)記錄成功!');
???INSERT?INTO?employees(employee_id,?first_name,last_name,hire_date,department_id)
???VALUES(3333,?'胡','勇',SYSDATE,?20);
???DBMS_OUTPUT.PUT_LINE('插入數(shù)據(jù)記錄成功!');
EXCEPTION
???WHEN?empno_remaining?THEN?
??????DBMS_OUTPUT.PUT_LINE('違反數(shù)據(jù)完整性約束!');
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;
??2011??EricHu
原創(chuàng)作品，轉(zhuǎn)貼請(qǐng)注明作者和出處，留此信息。
------------------------------------------------
cnBlobs:http://www.cnblogs.com/huyong/CSDN：http://blog.csdn.net/chinahuyong?
作者：EricHu(DB、C\S、B\S、WebService、WCF、PM等)
出處：http://www.cnblogs.com/huyong/
Q?Q：80368704???E-Mail:?80368704@qq.com
本博文歡迎大家瀏覽和轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁(yè)面明顯位置給出原文連接，在『參考』的文章中，我會(huì)表明參考的文章來(lái)源，尊重他人版權(quán)。若您發(fā)現(xiàn)我侵犯了您的版權(quán)，請(qǐng)及時(shí)與我聯(lián)系。
更多文章請(qǐng)看?[置頂]索引貼——(不斷更新中)
總結(jié)
以上是生活随笔為你收集整理的ORACLE 异常错误处理的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 无卡存款去哪查记录
- 下一篇： 右键删除选中的行总提示rowIndex