oracle?10g:?PL/SQL?User's?Guide?and?Reference
?--->?10?Handling?PL/SQL?Errors
?--->?Summary?of?Predefined?PL/SQL?Exceptions?

系統預定義異常(有名字的錯誤代碼):
TOO_MANY_ROWS?:?SELECT?INTO返回多行
INVALID_CURSOR?:非法指針操作(關閉已經關閉的游標)
ZERO_DIVIDE?:除數等于零
DUP_VAL_ON_INDEX?:違反唯一性約束
ACCESS_INTO_NULL:?未定義對象?
CASE_NOT_FOUND:?CASE?中若未包含相應的?WHEN?，并且沒有設置?ELSE?時?
COLLECTION_IS_NULL:?集合元素未初始化?
CURSER_ALREADY_OPEN:?游標已經打開?
DUP_VAL_ON_INDEX:?唯一索引對應的列上有重復的值?
INVALID_NUMBER:?內嵌的?SQL?語句不能將字符轉換為數字?
NO_DATA_FOUND:?使用?select?into?未返回行，或應用索引表未初始化的元素時?
SUBSCRIPT_BEYOND_COUNT:元素下標超過嵌套表或?VARRAY?的最大值?
SUBSCRIPT_OUTSIDE_LIMIT:?使用嵌套表或?VARRAY?時，將下標指定為負數??
VALUE_ERROR:?賦值時，變量長度不足以容納實際數據?
LOGIN_DENIED:?PL/SQL?應用程序連接到?oracle?數據庫時，提供了不正確的用戶名或密碼?
NOT_LOGGED_ON:?PL/SQL?應用程序在沒有連接?oralce?數據庫的情況下訪問數據?
PROGRAM_ERROR:?PL/SQL?內部問題，可能需要重裝數據字典＆?pl./SQL?系統包?
ROWTYPE_MISMATCH:?宿主游標變量與?PL/SQL?游標變量的返回類型不兼容?
SELF_IS_NULL:?使用對象類型時，在?null?對象上調用對象方法?
STORAGE_ERROR:?運行?PL/SQL?時，超出內存空間?
SYS_INVALID_ID:?無效的?ROWID?字符串?
TIMEOUT_ON_RESOURCE:?Oracle?在等待資源時超時?

oracl?11g:?Pl/SQL?LANGUAGE?REFERENCE
?---->?11??PL/SQL?error?handling
?---->?Predefined?Exceptions

練習?1:捕獲預定義異常
declare
??v1?emp.sal%type;
begin
??select?sal?into?v1?from?emp;
end;
/

declare
??v1?emp.sal%type;
begin
??select?sal?into?v1?from?emp;
exception
??when?TOO_MANY_ROWS?then
???dbms_output.put_line(sqlcode||';'||sqlerrm);
end;
/

declare
???v_ename?employees.last_name%type;
begin
???select?last_name?into?v_ename?from?employees;
exception
???when?too_many_rows?then
????????dbms_output.put_line('too?many?rows,you?should?add?a?condition');
end;
/

練習?2:捕獲預定義異常
declare
??v1?emp.sal%type;
begin
??select?sal?into?v1?from?emp?where?empno=7777;
exception
??when?TOO_MANY_ROWS?then
????dbms_output.put_line('more?person?!');
--??when?NO_DATA_FOUND?then
--????dbms_output.put_line('no?rows?selected!');
??when?others?then?--other執行器
????dbms_output.put_line(sqlcode||';'||sqlerrm);
end;
/

declare
???v_sal?hr.employees.salary%type;
begin
???select?salary?into?v_sal?from?hr.employees?where?department_id=?&ID;
???dbms_output.put_line(v_sal);
exception
???WHEN?TOO_MANY_ROWS?then
????????dbms_output.put_line('there?are?too?many?rows?to?return.');
???when?NO_DATA_FOUND?THEN
????????DBMS_OUTPUT.PUT_LINE('there?is?no?data?to?return.');
end;

declare
???v_ename?employees.last_name%type;
begin
???select?last_name?into?v_ename?from?employees;
exception
???when?too_many_rows?then
????????dbms_output.put_line('sqlcode?:'||sqlcode?||chr(10)||'sqlerrm?:'||sqlerrm);
end;
/
sqlcode?:-1422
sqlerrm?:ORA-01422:?exact?fetch?returns?more?than?requested?number?of?rows

練習?3:捕獲錯誤代碼和錯誤描述,借助預定義函數sqlcode(ERROR代碼),sqlerrm(ERROR文本)
begin
??update?emp?set?deptno=60;
??dbms_output.put_line('ok');
exception
??when?TOO_MANY_ROWS?then
????dbms_output.put_line('more?person?!');
??when?NO_DATA_FOUND?then
????dbms_output.put_line('no?person?!');
??when?others?then?--other執行器
????dbms_output.put_line(sqlcode||';'||sqlerrm);
end;
/

練習?4:捕獲非預定義異常(捕獲oracle錯誤代碼)
declare
??fk_error??exception;--聲明異常
??pragma?exception_init(fk_error,-2292);--使用編譯指示器將異常名稱和oracle的錯誤代碼綁定
begin
??delete?dept;?--oracle自動傳播錯誤（fk_error）
??dbms_output.put_line('ok');
exception
??when?TOO_MANY_ROWS?then
????dbms_output.put_line('more?person?');
??when?NO_DATA_FOUND?then
????dbms_output.put_line('no?person?');
??when?fk_error?then
????dbms_output.put_line('infringe?forign?key?!');??
end;
/

declare
???pk_fk_error?exception;
???pragma?exception_init(pk_fk_error,-2292);
begin
???delete?hr.departments;
???dbms_output.put_line('It?had?been?deleted.');
exception
???when?pk_fk_error?then
????????dbms_output.put_line('It?can?not?delete.');
end;
/
It?can?not?delete.

用戶自定義異常的sqlcode代碼段：-20001?~?-20999
練習?5:捕獲用戶自定義的異常:
declare
??my_error?EXCEPTION;
??PRAGMA?EXCEPTION_INIT(my_error,?-20001);--編譯指示,將命名的異常與ORACLE?ERROR關聯
BEGIN
??raise_application_error(-20001,'工資不能被改動!');--將異常傳送到環境
??UPDATE?e?SET?SAL=1000;
EXCEPTION
??WHEN?NO_DATA_FOUND?THEN
????DBMS_OUTPUT.PUT_LINE('未檢索到數據!');
??WHEN?TOO_MANY_ROWS?THEN
????DBMS_OUTPUT.PUT_LINE('SELECT返回多行數據!');
??WHEN?MY_ERROR?THEN
????DBMS_OUTPUT.PUT_LINE('E表工資不可以被修改!');
end;
/

declare
???my_excep?EXCEPTION;
???PRAGMA?EXCEPTION_init(my_excep,-20001);
???v_did?hr.employees.department_id%type?:=?&id;
begin
???if?v_did?!=?10?then
????????raise_application_error(-20001,'my?own?exception.');
???else
???????update?hr.employees?set?salary=salary+1?where?department_id=v_did;
???????dbms_output.put_line('ok');
???end?if;
end;

練習?6:捕獲用戶自定義的異常
declare
??my_error?EXCEPTION;
??PRAGMA?EXCEPTION_INIT(my_error,?-20001);
??v_empno?number(4):=&p_empno;
begin
??IF?TO_CHAR?(SYSDATE,?'HH24')?NOT?BETWEEN?'08'?AND?'14'?OR?TO_CHAR?(SYSDATE,?'DY')?IN?('星期六',?'星期日')?THEN
????RAISE?my_error;
??else
????insert?into?e(empno)?values?(v_empno);
????dbms_output.put_line('insert?成功!');
??END?IF;
exception
??when?my_error?then
????dbms_output.put_line('該時間段不能向E表插入數據!');
end;
/

declare
???v_raise_sal?number?:=?&raise_sal;
???my_error?EXCEPTION;
???PRAGMA?exception_init(my_error,-20001);
begin
???if?v_raise_sal?>?1000?then
??????raise_application_error(-20001,'can?not?raise?so?much?money.');
???else
??????update?employees?set?salary=salary+v_raise_sal;
???end?if;
--exception
--???when?my_error?then
--???????dbms_output.put_line('you?can?not?raise.');
end;
/
錯誤報告:
ORA-20001:?can?not?raise?so?much?money.
ORA-06512:?at?line?7
****************************************************************************************************
練習?7:打印?ORA-#####?錯誤編號和描述:
SPOOL?D:\ORACLE_ERROR.TXT
SET?SERVEROUTPUT?ON
DECLARE
??ERR_MSG?VARCHAR2(4000);
??ERR_CODE?NUMBER(10);
BEGIN
??DBMS_OUTPUT.ENABLE(1000000);
??FOR?ERR_NUM?IN?20000..20999
??LOOP
????ERR_CODE:=sqlcode;
????ERR_MSG?:=?SQLERRM(-ERR_NUM);
?????IF?ERR_MSG?NOT?LIKE?'%Message?'||ERR_NUM||'?not?found%'?then
???????dbms_output.put_line(ERR_MSG);
?????END?IF;
??END?LOOP;
END;
/
SPOOL?OFF;

--嵌套的PL/SQL代碼段的異常處理
declare
??v_ename?varchar2(10);
begin
??select?ename?into?v_ename?from?emp?where?empno=7839;
??dbms_output.put_line(v_ename);
????declare
??????v1?emp.sal%type;
????begin
??????select?sal?into?v1?from?emp;
????exception
??????when?TOO_MANY_ROWS?then
???????dbms_output.put_line(sqlcode||';'||sqlerrm);
????end;
??dbms_output.put_line('ok');
end;
/

--內部pl/sql代碼段，進行了異常處理，是所有外部程序可以正常執行；
declare
???v_name?employees.last_name%type;
begin
???select?last_name?into?v_name?from?hr.employees
???where?employee_id=100;
???declare
???????v_no?departments.department_id%type;
???begin
???????select?department_id?into?v_no?from?departments;
???exception
??????when??OTHERS?then
??????????dbms_output.put_line('too?may?rows');
???end;
???dbms_output.put_line(v_name);
end;
/

too?may?rows
King

--內部pl/sql代碼段異常
declare
???v_name?hr.employees.last_name%type;
begin
???select?last_name?into?v_name?from?hr.employees?where?employee_id=100;
???dbms_output.put_line(v_name);
???declare
???????v_sal?hr.employees.salary%type;
???begin
???????select?salary?into?v_sal?from?employees;
???????dbms_output.put_line('inner?pl/sql?block');
--???exception
--???????when?too_many_rows?then
--???????????dbms_output.put_line('inner?exception?capured.');
???end;
???dbms_output.put_line('outer?block');
exception
???when?too_many_rows?then
???????dbms_output.put_line('outer?exception?captured.');
end;

?

轉載于:https://blog.51cto.com/plking/1337345

總結

以上是生活随笔為你收集整理的oralce 异常处理 exception的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。