[
推薦
]
ORACLE?PL/SQL
編程之
五
:
異常錯誤處理 ( 知已知彼、百戰不殆 )
????繼上
三
篇:
ORACLE?PL/SQL
編程之八:把觸發器說透
?
????????? ??
ORACLE?PL/SQL
編程之六:把過程與函數說透
(
窮追猛打,把根兒都拔起
!)?
????????????
[
推薦
]ORACLE?PL/SQL
編程之四:把游標說透
(
不怕做不到,只怕想不到
)
? 得到了大家的強力支持 與建議 , 萬分 感謝。接下來介紹下一篇 : oracle?pl/sql異常處理部分 , 還望 大家一定要支持與推薦呀~!
?
本篇主要內容如下:
5.1?
異常處理概念
5.1.1?
預定義的異常處理
5.1.2?
非預定義的異常處理
5.1.3?
用戶自定義的異常處理
5.1.4??
用戶定義的異常處理
5.2?
異常錯誤傳播
5.2.1?
在執行部分引發異常錯誤
5.2.2?
在聲明部分引發異常錯誤
5.3?
異常錯誤處理編程
5.4?? 在? PL/SQL? 中使用? SQLCODE,?SQLERRM 異常處理函數
?
?
?即使是寫得最好的
PL/SQL
程序也會遇到錯誤或未預料到的事件
。
一個優秀的程序都應該能夠正確處理各種出錯情況,并盡可能從錯誤中恢復。
任何
ORACLE
錯誤(報告為
ORA-xxxxx
形式的
Oracle
錯誤號)、
PL/SQL
運行錯誤或用戶定義條件(不一寫是錯誤),都可以。當然了,
PL/SQL
編譯錯誤不能通過
PL/SQL
異常處理來處理,因為這些錯誤發生在
PL/SQL
程序執行之前。
ORACLE? 提供異常情況 (EXCEPTION) 和異常處理 (EXCEPTION?HANDLER) 來實現錯誤處理。
?
異常情況處理
(EXCEPTION)
是用來處理正常執行過程中未預料的事件
,
程序塊的異常處理預定義的錯誤和自定義錯誤
,
由于
PL/SQL
程序塊一旦產生異常而沒有指出如何處理時
,
程序就會自動終止整個程序運行
.
有三種類型的異常錯誤:
??? 1.?
預定義?
(?Predefined?)
錯誤
??ORACLE
預定義的異常情況大約有
24
個。對這種異常情況的處理,無需在程序中定義,由
ORACLE
自動將其引發。
??? 2.?
非預定義?
(?Predefined?)
錯誤
?? 即其他標準的
ORACLE
錯誤。對這種異常情況的處理,需要用戶在程序中定義,然后由
ORACLE
自動將其引發。
??? 3.?
用戶定義
(User_define)?
錯誤
?程序執行過程中,出現編程人員認為的非正常情況。對這種異常情況的處理,需要用戶在程序中定義,然后顯式地在程序中將其引發。
異常處理部分一般放在?
PL/SQL?
程序體的后半部
,
結構為
:
?
??? 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?
預定義的異常處理
?? 預定義說明的部分?
ORACLE?
異常錯誤
錯誤號
|
異常錯誤信息名稱
|
說明
|
ORA-0001
|
Dup_val_on_index
|
違反了唯一性限制
|
ORA-0051
|
Timeout-on-resource
|
在等待資源時發生超時
|
ORA-0061
|
Transaction-backed-out
|
由于發生死鎖事務被撤消
|
ORA-1001
|
Invalid-CURSOR
|
試圖使用一個無效的游標
|
ORA-1012
|
Not-logged-on
|
沒有連接到
ORACLE
|
ORA-1017
|
Login-denied
|
無效的用戶名
/
口令
|
ORA-1403
|
No_data_found
|
SELECT?INTO
沒有找到數據
|
ORA-1422
|
Too_many_rows
|
SELECT?INTO?
返回多行
|
ORA-1476
|
Zero-divide
|
試圖被零除
|
ORA-1722
|
Invalid-NUMBER
|
轉換一個數字失敗
|
ORA-6500
|
Storage-error
|
內存不夠引發的內部錯誤
|
ORA-6501
|
Program-error
|
內部錯誤
|
ORA-6502
|
Value-error
|
轉換或截斷錯誤
|
ORA-6504
|
Rowtype-mismatch
|
宿主
游標變量與?
PL/SQL
變量有不兼容行類型
|
ORA-6511
|
CURSOR-already-OPEN
|
試圖打開一個已
處于打開狀態
的游標
|
ORA-6530
|
Access-INTO-null
|
試圖為
null?
對象的屬性賦值
|
ORA-6531
|
Collection-is-null
|
試圖將
Exists?
以外的集合
(?collection)
方法應用于一個
null?pl/sql?
表上或
varray
上
|
ORA-6532
|
Subscript-outside-limit
|
對嵌套或
varray
索引得引用超出聲明范圍以外
|
ORA-6533
|
Subscript-beyond-count
|
對嵌套或
varray?
索引得引用大于集合中元素的個數
.
|
????
對這種異常情況的處理,只需在
PL/SQL
塊的異常處理部分,直接引用相應的異常情況名,并對其完成相應的異常錯誤處理即可。
例
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
||
'
員工工資已經超過規定值!
'
);
???
END
?
IF
;
EXCEPTION
???
WHEN
?NO_DATA_FOUND?
THEN
??
??????DBMS_OUTPUT.PUT_LINE(
'
數據庫中沒有編碼為
'
||
v_empno
||
'
的員工
'
);
???
WHEN
?TOO_MANY_ROWS?
THEN
??????DBMS_OUTPUT.PUT_LINE(
'
程序運行錯誤!請使用游標
'
);
???
WHEN
?OTHERS?
THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE
||
'
---
'
||
SQLERRM);
END
;?
???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 || ' 員工工資已經超過規定值! ' );
??? END ? IF ;
EXCEPTION
??? WHEN ?NO_DATA_FOUND? THEN ??
??????DBMS_OUTPUT.PUT_LINE( ' 數據庫中沒有編碼為 ' || v_empno || ' 的員工 ' );
??? WHEN ?TOO_MANY_ROWS? THEN
??????DBMS_OUTPUT.PUT_LINE( ' 程序運行錯誤!請使用游標 ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ;?
?
5.1.2?
非預定義的異常處理
對于這類異常情況的處理,首先必須對非定義的
ORACLE
錯誤進行定義。步驟如下:
1.?
在
PL/SQL?
塊的定義部分定義異常情況:
?
?
2.? 將其定義好的異常情況,與標準的 ORACLE 錯誤聯系起來,使用 EXCEPTION_INIT 語句:
?
3.? 在 PL/SQL? 塊的異常情況處理部分對異常情況做出相應的處理。
例
2
:
刪除指定部門的記錄信息,以確保該部門沒有員工。
?
DECLARE
???v_deptno?departments.department_id % TYPE?: = ? & deptno;
???deptno_remaining?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(deptno_remaining,? - 2292 );
??? /* ?-2292?是違反一致性約束的錯誤代碼? */
BEGIN
??? DELETE ? FROM ?departments? WHERE ?department_id? = ?v_deptno;
EXCEPTION
??? WHEN ?deptno_remaining? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 違反數據完整性約束! ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ;
?
?
當與一個異常錯誤相關的錯誤出現時,就會隱含觸發該異常錯誤。用戶定義的異常錯誤是通過顯式使用?
RAISE?
語句來觸發。當引發一個異常錯誤時,控制就轉向到?
EXCEPTION
塊異常錯誤部分,執行錯誤處理代碼。
對于這類異常情況的處理,步驟如下:
1.?
在
PL/SQL?
塊的定義部分定義異常情況:
?
?
2.? RAISE?< 異常情況 > ;
3.? 在 PL/SQL? 塊的異常情況處理部分對異常情況做出相應的處理。
例
3
:
更新指定員工工資,增加
100
;
?
???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( ' 你的數據更新語句失敗了! ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ;
?
?
5.1.4?? 用 戶定義的異常處理
調用
DBMS_STANDARD(ORACLE
提供的包
)
包所定義的
RAISE_APPLICATION_ERROR
過程,可以重新定義異常錯誤消息,它為應用程序提供了一種與
ORACLE
交互的方法。
RAISE_APPLICATION_ERROR?
的語法如下:
?
?
??? 這里的
error_number?
是從?
–20,000?
到?
–20,999?
之間的參數,
????error_message? 是相應的提示信息 (<?2048? 字節 ) ,
??
keep_errors?
為可選,如果
keep_errors?=TRUE?,
則新錯誤將被添加到已經引發的錯誤列表中。如果
keep_errors=FALSE(
缺省
),
則新錯誤將替換當前的錯誤列表。
例
4
:
創建一個函數
get_salary,?
該函數檢索指定部門的工資總和,其中定義了
-20991
和
-20992
號錯誤,分別處理參數為空和非法部門代碼兩種錯誤:
?
??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 ,?’無效的部門代碼’);
?? 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號部門工資: ' ? || ?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( ' 部門號為20的工資為: ' || TO_CHAR(V_salary));
?? BEGIN
????V_salary?: = get_salary( NULL );
?? END ?inner2;
??V_salary?: = ?get_salary( 30 );
??DBMS_OUTPUT.PUT_LINE( ' 部門號為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
:
定義觸發器,使用
RAISE_APPLICATION_ERROR
阻止沒有員工姓名的新員式記錄插入
:
?
BEFORE? 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.1?
在執行部分引發異常錯誤
????當一個異常錯誤在執行部分引發時,有下列情況:
l?
如果當前塊對該異常錯誤設置了處理,則執行它并成功完成該塊的執行,然后控制轉給包含塊。
l? 如果沒有對當前塊異常錯誤設置定義處理器,則通過在包含塊中引發它來傳播異常錯誤。然后對該包含塊執行步驟 1) 。
5.2.2?
在聲明部分引發異常錯誤
????如果在聲明部分引起異常情況,即在聲明部分出現錯誤,那么該錯誤就能影響到其它的塊。比如在有如下的 PL/SQL 程序:
????name? varchar2 ( 12 ): = ' EricHu ' ;
????其它語句
BEGIN
????其它語句
EXCEPTION
???? WHEN ?OTHERS? THEN ?
????其它語句
END ;
?
???? 例子中,由于
Abc?number(3)=’abc’;?
出錯,盡管在
EXCEPTION
中說明了
WHEN?OTHERS?THEN
語句,但
WHEN?OTHERS?THEN
也不會被執行。?但是如果在該錯誤語句塊的外部有一個異常錯誤,則該錯誤能被抓住,如:
?
???? DECLARE
????name? varchar2 ( 12 ): = ' EricHu ' ;
????其它語句
??? BEGIN
????其它語句
???EXCEPTION
???? WHEN ?OTHERS? THEN ?
????其它語句
???? END ;
EXCEPTION
WHEN ?OTHERS? THEN ?
????其它語句
END ;
?
?
????在一般的應用處理中,建議程序人員要用異常處理,因為如果程序中不聲明任何異常處理,則在程序運行出錯時,程序就被終止,并且也不提示任何信息。下面是使用系統提供的異常來編程的例子。
?
5.4??
在?
PL/SQL?
中使用?
SQLCODE,?SQLERRM
異常處理函數
????由于
ORACLE?
的錯信息最大長度是
512
字節,為了得到完整的錯誤提示信息,我們可用?
SQLERRM
和?
SUBSTR?
函數一起得到錯誤提示信息
,方便進行錯誤,特別是如果
WHEN?OTHERS
異常處理器時更為方便。
SQLCODE
?
?返回
遇到的
Oracle
錯誤號
,
SQLERRM?
?
返回
遇到的
Oracle
錯誤信息
.
如
:
??SQLCODE=-100??
?
è
?SQLERRM=’no_data_found?‘
?
SQLCODE=0??????
è
?SQLERRM=’normal,?successfual?completion’
例
6
.?
將
ORACLE
錯誤代碼及其信息存入錯誤代碼表
?
DECLARE
???err_msg?? VARCHAR2 ( 100 );
BEGIN
??? /* ??得到所有?ORACLE?錯誤信息?? */
??? 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
錯誤代碼;
?
??? INSERT ? INTO ?employees(employee_id,?first_name,last_name,hire_date,department_id)
??? VALUES ( 2222 ,? ' Eric ' , ' Hu ' ,?SYSDATE,? 20 );
???DBMS_OUTPUT.PUT_LINE( ' 插入數據記錄成功! ' );
???
??? INSERT ? INTO ?employees(employee_id,?first_name,last_name,hire_date,department_id)
??? VALUES ( 2222 ,? ' 胡 ' , ' 勇 ' ,?SYSDATE,? 20 );
???DBMS_OUTPUT.PUT_LINE( ' 插入數據記錄成功! ' );
EXCEPTION
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ;
?
?
例
8
.?
利用
ORACLE
錯誤代碼,編寫異常錯誤處理代碼;
?
???empno_remaining?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(empno_remaining,? - 1 );
??? /* ?-1?是違反唯一約束條件的錯誤代碼? */
BEGIN
??? INSERT ? INTO ?employees(employee_id,?first_name,last_name,hire_date,department_id)
??? VALUES ( 3333 ,? ' Eric ' , ' Hu ' ,?SYSDATE,? 20 );
???DBMS_OUTPUT.PUT_LINE( ' 插入數據記錄成功! ' );
???
??? INSERT ? INTO ?employees(employee_id,?first_name,last_name,hire_date,department_id)
??? VALUES ( 3333 ,? ' 胡 ' , ' 勇 ' ,SYSDATE,? 20 );
???DBMS_OUTPUT.PUT_LINE( ' 插入數據記錄成功! ' );
EXCEPTION
??? WHEN ?empno_remaining? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 違反數據完整性約束! ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ;
?
?
??2011??
EricHu
原創作品,轉貼請注明作者和出處,留此信息。
------------------------------------------------
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
本博文歡迎大家瀏覽和轉載,但未經作者同意必須保留此段聲明,且在文章頁面明顯位置給出原文連接,在『參考』的文章中,我會表明參考的文章來源,尊重他人版權。若您發現我侵犯了您的版權,請及時與我聯系。
更多文章請看
?
[
置頂
]
索引貼
——
(不斷更新中)
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
