當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

PL/SQL异常处理(原创)

發(fā)布時(shí)間：2025/6/15 数据库 15 豆豆

生活随笔收集整理的這篇文章主要介紹了 PL/SQL异常处理(原创) 小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

Exception概述

Exception是一種PL/SQL標(biāo)識(shí)符，當(dāng)運(yùn)行的PL/SQL塊出現(xiàn)錯(cuò)誤或警告，則會(huì)觸發(fā)異常處理。為了提高程序的健壯性，可以在PL/SQL塊中引入異常處理部分，進(jìn)行捕捉異常，并根據(jù)異常出現(xiàn)的情況進(jìn)行相應(yīng)的處理。
ORACLE異常分為兩種類(lèi)型：系統(tǒng)異常、自定義異常。其中系統(tǒng)異常又分為：預(yù)定義異常和非預(yù)定義異常。
預(yù)定義異常
ORACLE定義了他們的錯(cuò)誤編號(hào)和異常名字，常見(jiàn)的預(yù)定義異常處理如下

錯(cuò)誤號(hào)	異常錯(cuò)誤信息名稱(chēng)	說(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	沒(méi)有連接到ORACLE
ORA-1017	Login-denied	無(wú)效的用戶(hù)名/口令
ORA-1403	No_data_found	SELECT?INTO沒(méi)有找到數(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變量有不兼容行類(lèi)型
ORA-6511	CURSOR-already-OPEN	試圖打開(kāi)一個(gè)已處于打開(kāi)狀態(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ù).

非預(yù)定義異常
ORACLE為它定義了錯(cuò)誤編號(hào)，但沒(méi)有定義異常名字。我們使用的時(shí)候，先聲名一個(gè)異常名，通過(guò)偽過(guò)程PRAGMA EXCEPTION_INIT，將異常名與錯(cuò)誤號(hào)關(guān)聯(lián)起來(lái)。
自定義異常
程序員從業(yè)務(wù)角度出發(fā)，制定的一些規(guī)則和限制。
異常處理
PL/SQL中，異常處理按個(gè)步驟進(jìn)行：
定義異常

exception_name EXCEPTION;
拋出異常

RAISE exception_name
捕獲及處理異常
EXCEPTION
??? WHEN e_name1 [OR e_name2 ... ] THEN
??????? statements;
??? WHEN e_name3 [OR e_name4 ... ] THEN
??????? statements;
??????? ......
??? WHEN OTHERS THEN
??????? statements;
END;

預(yù)定義異常處理示例???
一個(gè)整除的異常
SQL>? declare
? 2??? v_n1 number := 50;
? 3??? v_n2 number := 0 ;
? 4??? v_n3 number;
? 5? begin
? 6??? v_n3 := v_n1/v_n2;
? 7??? dbms_output.put_line('v_n3=' || v_n3);
? 8? exception
? 9??? when zero_divide then
?10????? dbms_output.put_line('v_n2 can' || '''' || 't be 0');
?11* end;

v_n2 can't be 0
PL/SQL procedure successfully completed.

VALUE_ERROR(錯(cuò)誤號(hào)ORA-06502)

SQL> declare
? 2??? v_ename varchar2(3);
? 3? begin
? 4??? select ename into v_ename from emp where empno = &eno;
? 5??? dbms_output.put_line(v_ename);
? 6? exception
? 7??? when value_error then
? 8????? dbms_output.put_line('variable datatype length is small');
? 9? end;
?10? /
Enter value for eno: 7788
old?? 4:?? select ename into v_ename from emp where empno = &eno;
new?? 4:?? select ename into v_ename from emp where empno = 7788;
variable datatype length is small
PL/SQL procedure successfully completed.
TOO_MANY_ROWS(對(duì)應(yīng)Oracle錯(cuò)誤號(hào)ORA-01422)
SQL>declare
? 2??? v_ename emp.ename%type;
? 3? begin
? 4??? select ename into v_ename from emp where deptno = &deptno;
? 5??? dbms_output.put_line(v_ename);
? 6? exception
? 7??? when too_many_rows then
? 8????? dbms_output.put_line('Too many rows are returned');
? 9? end;
?10? /
Enter value for deptno: 30
old?? 4:?? select ename into v_ename from emp where deptno = &deptno;
new?? 4:?? select ename into v_ename from emp where deptno = 30;
Too many rows are returned
PL/SQL procedure successfully completed.
非預(yù)定義異常示例

非預(yù)定義異常使用的基本過(guò)程如下
a.定義一個(gè)異常名
b.將異常名與異常編號(hào)相關(guān)聯(lián)
c.在異常處理部分捕捉并處理異常

SQL> delete from dept where deptno = 10;
delete from dept where deptno = 10
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
SQL> declare
? 2??? e_deptid exception;?????? --定義異常
? 3??? pragma exception_init(e_deptid,-2292);?????? --將異常和錯(cuò)誤號(hào)關(guān)聯(lián)
? 4? begin
? 5??? delete from dept where deptno = 10;
? 6? exception
? 7??? when e_deptid then???????? --捕獲異常
? 8????? dbms_output.put_line('There is record at sub table');
? 9? end;
?10? /
There is record at sub table
PL/SQL procedure successfully completed.
自定義異常示例
自定義異常與Oracle錯(cuò)誤沒(méi)有任何關(guān)系，由開(kāi)發(fā)人員為特定情況所定義的例外。下面的例子中，通過(guò)自定義異常，當(dāng)雇員編號(hào)不存在時(shí)，PL/SQL代碼能夠給出適當(dāng)?shù)奶崾?/span>
對(duì)于自定義的異常處理需要顯示的觸發(fā)，其步驟如下
a.定義異常(在declare部分進(jìn)行定義)
b.顯示觸發(fā)異常(在執(zhí)行BEGIN部分觸發(fā)異常，使用RAISE語(yǔ)句)
c.引用異常(在EXCEPTION部分捕捉并處理異常)
SQL> update emp set deptno=20 where empno=1111;
0 rows updated.
SQL> declare
? 2??? e_integrity exception;
? pragma exception_init(e_integrity,-2291);
? 4??? update emp set deptno = &dno where empno = &eno;
? e_no_employee exception;
begin
? update emp set deptno = &dno where empno = &eno;
? if sql%notfound then
??? raise e_no_employee;
? end if;
?10? exception
?11??? when e_integrity then
??? dbms_output.put_line('The dept does not exists');
? when e_no_employee then
??? dbms_output.put_line('The employess does not exists');
?15? end;
?16? /
Enter value for dno: 20
Enter value for eno: 1111
old?? 6:?? update emp set deptno = &dno where empno = &eno;
new?? 6:?? update emp set deptno = 20 where empno = 1111;
The employess does not exists
PL/SQL procedure successfully completed.
工資如果少于1500，則拋出異常
SQL> declare
? 2??? v_empno emp.empno%type;
? 3??? v_sal?? emp.sal%type;
? 4??? e_sal exception;
? 5? begin
? 6??? v_empno := &empno;
? 7??? v_sal?? := &sal;
? 8??? insert into emp(empno,sal) values(v_empno,v_sal);
? 9??? if v_sal < 1500 then
?10????? raise e_sal;
?11??? end if;
?12? exception
?13??? when e_sal then
?14????? rollback;
?15????? dbms_output.put_line('Salary must be more then 1500');
?16? end;
?17? /
Enter value for empno: 8888
old?? 6:?? v_empno := &empno;
new?? 6:?? v_empno := 8888;
Enter value for sal: 1234
old?? 7:?? v_sal?? := &sal;
new?? 7:?? v_sal?? := 1234;
Salary must be more then 1500
PL/SQL procedure successfully completed.
使用異常函數(shù)處理異常??????????

SQLCODE與SQLERRM

SQLCODE與SQLERRM為異常處理函數(shù)。函數(shù)SQLCODE用于取得Oracle錯(cuò)誤號(hào)，函數(shù)SQLERRM用于取得與錯(cuò)誤號(hào)對(duì)應(yīng)的相關(guān)錯(cuò)誤消息
SQL> declare
? 2??? v_ename emp.ename%type;
? 3? begin
? 4??? select ename into v_ename from emp
? 5??? where sal = &sal;
? 6??? dbms_output.put_line('Employee Name:' || v_ename);
? 7? exception
? 8??? when no_data_found then
? 9????? dbms_output.put_line('The employee does not exists');
?10??? when others then
?11????? dbms_output.put_line('Error No:' || SQLCODE);
?12????? dbms_output.put_line(SQLERRM);
?13? end;
?14? /
Enter value for sal: 1250
old?? 5:?? where sal = &sal;
new?? 5:?? where sal = 1250;
Error No:-1422
ORA-01422: exact fetch returns more than requested number of rows
PL/SQL procedure successfully completed.
RAISE_APPLICATION_ERROR
調(diào)用DBMS_STANDARD(ORACLE提供的包)包所定義的RAISE_APPLICATION_ERROR過(guò)程，可以重新定義異常錯(cuò)誤消息，它為應(yīng)用程序提供了一種與ORACLE交互的方法。該函數(shù)用于在PL/SQL中定義錯(cuò)誤消息，且只能在數(shù)據(jù)庫(kù)端的子程序中使用(存儲(chǔ)過(guò)程、函數(shù)、包、觸發(fā)器)，不能在匿名塊和客戶(hù)端的子程序中使用
使用方法
RAISE_APPLICATION_ERROR(error_number,message[,{true | false}]);
該函數(shù)內(nèi)的錯(cuò)誤代碼和內(nèi)容，都是用用戶(hù)自定義
error_number：用于定義錯(cuò)誤號(hào)，且錯(cuò)誤號(hào)從-20000 到-20999 之間，以避免與ORACLE 的任何錯(cuò)誤代碼發(fā)生沖突。
message：用于指定錯(cuò)誤消息，且消息長(zhǎng)度不能超過(guò)k，超出部分將被截取
可選參數(shù)true,false:默認(rèn)值為false，會(huì)替換先前的所有錯(cuò)誤。當(dāng)設(shè)置為true，則該錯(cuò)誤會(huì)被放在先前錯(cuò)誤堆棧中。
SQL> create or replace procedure raise_comm
? 2? (v_no emp.empno%type,v_comm out emp.comm%type)
? 3? as
? 4? begin
? 5??? select comm into v_comm from emp where empno = v_no;
? 6??? if v_comm is null then
? 7????? raise_application_error(-20001,'This employee has no comm');
? 8??? end if;
? 9? exception
?10??? when no_data_found then
??? dbms_output.put_line('The employee does not exists');
?12? end;
?13? /
Procedure created.
SQL> var g_sal number;
SQL> exec raise_comm(7788,:g_sal);
BEGIN raise_comm(7788,:g_sal); END;
*
ERROR at line 1:
ORA-20001: This employee has no comm
ORA-06512: at "SCOTT.RAISE_COMM", line 7
ORA-06512: at line 1
SQL> exec raise_comm(7499,:g_sal);
PL/SQL procedure successfully completed.
SQL> print g_sal;
???? G_SAL
----------
?????? 300
PL/SQL編譯警告
PL/SQL警告的分類(lèi)
SEVERE: 用于檢查可能出現(xiàn)的不可預(yù)料結(jié)果或錯(cuò)誤結(jié)果,例如參數(shù)的別名問(wèn)題.
PERFORMANCE: 用于檢查可能引起性能問(wèn)題,如在INSERT操作是為NUMBER列提供了VARCHAR2類(lèi)型數(shù)據(jù).
INFORMATIONAL: 用于檢查程序中的死代碼.
ALL: 用于檢查所有警告.
控制PL/SQL警告消息
通過(guò)設(shè)置初始化參數(shù)PLSQL_WARNINGS來(lái)啟用在編譯PL/SQL子程序時(shí)發(fā)出警告消息，缺省為DISABLE:ALL
設(shè)置警告消息時(shí)有如下不同的范圍
系統(tǒng)級(jí)別
會(huì)話(huà)級(jí)別
ALTER PROCEDURE????? ---只針對(duì)設(shè)置的過(guò)程有效
將特定的消息號(hào)設(shè)置為錯(cuò)誤,也可以激活或禁止特定消息號(hào)
SQL> show parameter plsql%ings;
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
plsql_warnings?????????????????????? string????? DISABLE:ALL???????
SQL> ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL' |'DISABLE:ALL'
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE' |'DISABLE:ALL'
SQL> ALTER PROCEDURE usp COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';
SQL> alter session set plsql_warnings='enable:(5001,5002)';
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE','DISABLE:PERFORMANCE','ERROR:06002';
PL/SQL編譯告警示例
檢測(cè)死代碼
在下面的代碼中，ELSE子句永遠(yuǎn)不會(huì)執(zhí)行,應(yīng)該避免出現(xiàn)類(lèi)似的死代碼.從Oracle 10g開(kāi)始,在編寫(xiě)PL/SQL子程序之前開(kāi)發(fā)人員可以激活警告檢查.

SQL> alter session set plsql_warnings='enable:informational';
Session altered.

SQL> show parameter plsql_warnings
NAME???????????????????????????????? TYPE????????????????????????????? VALUE
------------------------------------ --------------------------------- ------------------------------
plsql_warnings?????????????????????? string??????????????????????????? ENABLE:INFORMATIONAL, DISABLE:
?????????????????????????????????????????????????????????????????????? PERFORMANCE, DISABLE:SEVERE
SQL> create or replace procedure dead_code?
? 2? as
? 3? x number := 10;
? 4? begin
? 5??? if x > 10 then
? 6????? x := 1;
? 7??? else
? 8????? x := 2;????? --死代碼
? 9??? end if;
?10? end ;
?11? /
SP2-0804: Procedure created with compilation warnings
檢測(cè)引起性能問(wèn)題的代碼
SQL>? alter session set plsql_warnings='enable:performance';
Session altered.
SQL>? create or replace procedure update_sal
? 2?? (no number,salary varchar2)
? 3?? as
? 4?? begin
? 5???? update emp set sal=salary where empno=no;
? 6?? end;
? 7?? /
SP2-0804: Procedure created with compilation warnings
SQL> show errors
Errors for PROCEDURE UPDATE_SAL:
LINE/COL ERROR
-------- ---------------------------------------------------------------------------------------------------
5/23???? PLW-07202: bind type would result in conversion away from column? type

參考至:http://blog.csdn.net/robinson_0612/article/details/6080119
????????????? http://www.cnblogs.com/soundcode/archive/2012/01/10/2318385.html

??????????????http://blog.csdn.net/endless_horizen/article/details/6563034

??????????????http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10755/initparams165.htm

本文原創(chuàng)，轉(zhuǎn)載請(qǐng)注明出處、作者

如有錯(cuò)誤，歡迎指正

郵箱:czmcj@163.com

作者：czmmiao ?文章出處：http://czmmiao.iteye.com/blog/1836709

總結(jié)

以上是生活随笔為你收集整理的PL/SQL异常处理(原创)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： JQuery全选/反选设置
下一篇： SQLAlchemy之SQL Expre