PLSQL總結(jié)3異常處理及嵌套塊課件

PL/SQL總結(jié)-3目錄處理異常捕獲OracleServer異常捕獲用戶定義異常識(shí)別變量作用域處理異常本部分將講解：在PL/SQL代碼中包含異常處理代碼的優(yōu)點(diǎn)PL/SQL塊異常處理部分的目的異常處理指南什么是異常?異常是程序的運(yùn)行時(shí)錯(cuò)誤，異常會(huì)中斷程序的正常執(zhí)行.異常的產(chǎn)生有多種原因:用戶輸入錯(cuò)誤;硬件錯(cuò)誤;網(wǎng)頁(yè)不存在;等等.你使用應(yīng)用程序和網(wǎng)站時(shí)遇到過(guò)錯(cuò)誤嗎？PL/SQL中的異常下例運(yùn)行正常.但是如果輸入的是Korea,South而不是RepublicofKorea呢?DECLARE

v_country_namewf_countries.country_name%TYPE:='RepublicofKorea';

v_elevationwf_countries.highest_elevation%TYPE;

BEGIN

SELECThighest_elevation

INTOv_elevation

FROMwf_countries

WHEREcountry_name=v_country_name;

DBMS_OUTPUT.PUT_LINE(v_country_name);

END;PL/SQL中的異常DECLARE

v_country_namewf_countries.country_name%TYPE:='RepublicofKorea';

v_elevationwf_countries.highest_elevation%TYPE;

BEGIN

SELECThighest_elevation

INTOv_elevation

FROMwf_countries

WHEREcountry_name=v_country_name;

DBMS_OUTPUT.PUT_LINE(v_country_name);

END;什么是異常處理代碼?異常處理代碼定義了異常發(fā)生后應(yīng)當(dāng)執(zhí)行的恢復(fù)操作.編寫代碼時(shí),程序員應(yīng)當(dāng)預(yù)見到代碼執(zhí)行時(shí)可能發(fā)生的錯(cuò)誤的類型.然后為每一種錯(cuò)誤編寫異常處理代碼.異常處理代碼是程序員為代碼錯(cuò)誤編制的處理預(yù)案.什么是異常處理代碼?程序員使用異常處理代碼來(lái)處理哪些錯(cuò)誤?系統(tǒng)錯(cuò)誤(比如磁盤空間耗盡)數(shù)據(jù)錯(cuò)誤(比如,主鍵值重復(fù))用戶錯(cuò)誤(比如,數(shù)據(jù)輸入錯(cuò)誤)很多其它的可能性!為什么異常處理重要?你能說(shuō)明為什么異常處理如此重要嗎?可能的原因包括:減輕錯(cuò)誤對(duì)用戶的影響(頻繁的錯(cuò)誤使用戶沮喪甚至拒絕使用程序)保護(hù)數(shù)據(jù)庫(kù)(避免數(shù)據(jù)丟失或被覆蓋)錯(cuò)誤消耗大量系統(tǒng)資源(錯(cuò)誤發(fā)生后,糾正錯(cuò)誤代價(jià)高昂;用戶頻繁打電話請(qǐng)求幫助).代碼更加易讀，因?yàn)殄e(cuò)誤處理代碼可在同一個(gè)塊中的獨(dú)立部分處理錯(cuò)誤.處理PL/SQL異常異常如果被處理,PL/SQL程序就不會(huì)突然中斷.異常拋出后,控制轉(zhuǎn)到異常處理部分，執(zhí)行相應(yīng)的處理代碼.之后，PL/SQL塊正常、成功結(jié)束.一個(gè)時(shí)刻只能發(fā)生一個(gè)異常.異常發(fā)生時(shí),PL/SQL在塊結(jié)束前只執(zhí)行一個(gè)異常處理代碼.處理PL/SQL異常點(diǎn)A的代碼不會(huì)執(zhí)行，因?yàn)镾ELECT語(yǔ)句失敗.處理PL/SQL異常下面是另一示例.塊中select語(yǔ)句用于獲取John的last_name. DECLARE

v_lnameVARCHAR2(15);

BEGIN

SELECTlast_nameINTOv_lnameFROMemployeesWHEREfirst_namelike

'El%';

DBMS_OUTPUT.PUT_LINE('Ellen''slastnameis:'||v_lname);

END;然而,因?yàn)橛卸鄠€(gè)John所以會(huì)產(chǎn)生異常.捕獲異常你可通過(guò)在PL/SQL塊的異常處理部分增加相應(yīng)的處理代碼來(lái)處理或捕獲（trap）任何錯(cuò)誤.語(yǔ)法:捕獲異常每個(gè)異常處理代碼包含一個(gè)WHEN子句,其后是異常名,然后是THEN引導(dǎo)的異常處理語(yǔ)句.在EXCEPTION部分可包含任意多個(gè)異常處理代碼來(lái)處理特定的異常.然而,不能為同一個(gè)異常編寫多個(gè)異常處理代碼.捕獲異常語(yǔ)法中:exception是預(yù)定義的異常名或用戶在聲明部分定義的異常名statement是一個(gè)或多個(gè)PL/SQL或SQL語(yǔ)句OTHERS是可選的異常處理子句，用于捕獲前面沒(méi)有明確捕獲的任何異常OTHERS關(guān)鍵字考慮下面的例子:

如果程序拋出NO_DATA_FOUND異常,則執(zhí)行statement1如果程序拋出TOO_MANY_ROWS異常,則執(zhí)行statement2如果程序拋出了其他異常,,則執(zhí)行statement3捕獲異常指南如果有可能發(fā)生錯(cuò)誤，就要添加異常處理代碼.在計(jì)算時(shí)，字符串操作時(shí)，執(zhí)行SQL語(yǔ)句時(shí)都有可能發(fā)生錯(cuò)誤.如果有可能，盡量按名稱處理異常而不是用OTHERS捕獲異常.掌握預(yù)定義異常的名稱及其產(chǎn)生的原因.用不同的壞數(shù)據(jù)及其組合測(cè)試代碼，發(fā)現(xiàn)可能出現(xiàn)的錯(cuò)誤.在異常處理代碼中輸出調(diào)試信息.仔細(xì)考慮每個(gè)異常處理代碼是需要提交事務(wù),還是撤銷事務(wù),或是讓事務(wù)繼續(xù).不管錯(cuò)誤多么嚴(yán)重,我們都要使數(shù)據(jù)庫(kù)處于一致狀態(tài)，避免保存任何壞數(shù)據(jù).處理異常本部分講解了：在PL/SQL代碼中包含異常處理代碼的優(yōu)點(diǎn)PL/SQL塊異常處理部分的目的異常處理指南捕獲OracleServer異常PL/SQL錯(cuò)誤處理非常靈活，允許程序員處理用戶定義異常和Oracle定義異常.本部分學(xué)習(xí)預(yù)定義和非預(yù)定義Oracle服務(wù)錯(cuò)誤.預(yù)定義錯(cuò)誤是常見的Oracle錯(cuò)誤，為了方便，PL/SQL已經(jīng)為其定義了異常名.非預(yù)定義錯(cuò)誤使用ORA錯(cuò)誤代碼和消息.兩種錯(cuò)誤處理語(yǔ)法不同,但你可在EXCEPTION處理部分捕獲所有這兩種錯(cuò)誤.ExceptionTypes捕獲預(yù)定義Oracle服務(wù)錯(cuò)誤在異常處理代碼中引用預(yù)定義的異常名.下面是幾個(gè)預(yù)定義異常的例子:NO_DATA_FOUNDTOO_MANY_ROWSINVALID_CURSORZERO_DIVIDEDUP_VAL_ON_INDEXplsql_s06_l02_predefined_errors.doc文檔中給出了部分預(yù)定義異常.完整異常列表參看PL/SQLUser’sGuideandReference.捕獲預(yù)定義Oracle服務(wù)錯(cuò)誤下例中使用了預(yù)定義Oracle錯(cuò)誤TOO_MANY_ROWS

注意預(yù)定義Oracle錯(cuò)誤無(wú)需聲明，直接使用DECLARE

v_lnameVARCHAR2(15);

BEGIN

SELECTlast_nameINTOv_lnameFROMemployeesWHEREfirst_namelike

'El%';

DBMS_OUTPUT.PUT_LINE('Ellen''slastnameis:'||v_lname);

EXCEPTION

WHENTOO_MANY_ROWSTHEN

DBMS_OUTPUT.PUT_LINE('Yourselectstatementretrievedmultiplerows.Considerusingacursor.');

END;捕獲多個(gè)預(yù)定義Oracle服務(wù)錯(cuò)誤下例處理了TOO_MANY_ROWS和NO_DATA_FOUND異常,并使用OTHERS來(lái)處理其它可能的異常.DECLARE

v_lnameVARCHAR2(15);

BEGIN

SELECTlast_nameINTOv_lnameFROMemployeesWHEREfirst_name='John';

DBMS_OUTPUT.PUT_LINE('John''slastnameis:'||v_lname);

EXCEPTION

WHENTOO_MANY_ROWSTHEN

DBMS_OUTPUT.PUT_LINE('Selectstatementfoundmultiplerows');

WHENNO_DATA_FOUNDTHEN

DBMS_OUTPUT.PUT_LINE('Selectstatementfoundnorows');

WHEN

OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('Anothertypeoferroroccurred');

END;捕獲非預(yù)定義Oracle服務(wù)錯(cuò)誤非預(yù)定義Oracle異常和預(yù)定義異常類似;但在PL/SQL中沒(méi)有預(yù)定義的名稱.它們是標(biāo)準(zhǔn)Oracle服務(wù)錯(cuò)誤，有ORA-錯(cuò)誤編號(hào).在DECLARE部分程序員自己為錯(cuò)誤定義一個(gè)異常名，并使用PRAGMAEXCEPTION_INIT函數(shù)將其與ORA-錯(cuò)誤編號(hào)關(guān)聯(lián)在一起.捕獲非預(yù)定義Oracle服務(wù)錯(cuò)誤要捕獲非預(yù)定義Oracle服務(wù)錯(cuò)誤，必須先聲明異常.聲明的異常是自動(dòng)拋出的.在PL/SQL中,PRAGMAEXCEPTION_INIT指示編譯器將異常名和Oracle錯(cuò)誤編號(hào)關(guān)聯(lián).這樣，就可以通過(guò)異常名來(lái)捕獲非預(yù)定義Oracle服務(wù)錯(cuò)誤，并為其提供專門的異常處理代碼.非預(yù)定義Oracle服務(wù)錯(cuò)誤示例 BEGIN

INSERT

INTOdepartments

(department_id,department_name)

VALUES

(280,NULL);

END;INSERT語(yǔ)句試圖為departments表的department_name列插入空值.然而語(yǔ)句失敗，因?yàn)閐epartment_name定義為NOTNULL.違反NOTNULL約束這種錯(cuò)誤沒(méi)有預(yù)定義好的異常名稱.處理這種錯(cuò)誤的方法是自己聲明異常名并將其與ORA-1400錯(cuò)誤編號(hào)關(guān)聯(lián)在一起.非預(yù)定義Oracle服務(wù)錯(cuò)誤1.在聲明部分定義一個(gè)異常名.非預(yù)定義Oracle服務(wù)錯(cuò)誤2.使用PRAGMAEXCEPTION_INIT函數(shù)將異常名和標(biāo)準(zhǔn)Oracle錯(cuò)誤編號(hào)關(guān)聯(lián).非預(yù)定義Oracle服務(wù)錯(cuò)誤3.在異常處理代碼中引用前面定義的異常名異常相關(guān)函數(shù)異常發(fā)生時(shí),可通過(guò)下面兩個(gè)函數(shù)獲取相關(guān)的錯(cuò)誤代碼和錯(cuò)誤消息.

基于錯(cuò)誤代碼和錯(cuò)誤消息可以決定后續(xù)操作.SQLERRM返回文本類型的錯(cuò)誤消息SQLCODE返回?cái)?shù)值類型的錯(cuò)誤代碼(可賦予NUMBER類型變量.)異常相關(guān)函數(shù)不能直接在SQL語(yǔ)句中使用SQLCODE或

SQLERRM.必須先將其值賦予局部變量,然后在SQL語(yǔ)句中使用變量,如下例所示:捕獲OracleServer異常本部分講解了：Oracle服務(wù)異常用戶自定義異常顯式和隱式拋出的異常捕獲預(yù)定義的OracleServer異常捕獲非預(yù)定義的OracleServer錯(cuò)誤通過(guò)錯(cuò)誤代碼和錯(cuò)誤消息識(shí)別異常捕獲用戶定義異常此部分將講解：編寫PL/SQL代碼定義用戶定義異常編寫PL/SQL代碼拋出異常處理拋出的異常使用RAISE_APPLICATION_ERROR捕獲用戶定義異常PL/SQL處理的另一類錯(cuò)誤是用戶定義異常.此類錯(cuò)誤不是由Oracle服務(wù)自動(dòng)拋出,而是程序員在自己的代碼中定義并拋出的.自定義錯(cuò)誤的一個(gè)例子是非法經(jīng)理ID：INVALID_MANAGER_ID.也可以為自定義錯(cuò)誤定義錯(cuò)誤編號(hào)和錯(cuò)誤消息.異常類型本部分學(xué)習(xí)用戶定義異常.捕獲用戶定義異常PL/SQL允許自定義異常.根據(jù)應(yīng)用程序需要定義自己的異常.捕獲用戶定義異常需要自定義異常的一個(gè)例子是，你需要處理和強(qiáng)調(diào)輸入數(shù)據(jù)的錯(cuò)誤.

例如,假設(shè)程序需要提示用戶輸入部門編號(hào)和姓名，用于修改部門數(shù)據(jù).

DECLARE

v_nameVARCHAR2(20):='Accounting';

v_deptnoNUMBER:=27;

BEGIN

UPDATEdepartments

SETdepartment_name=v_name

WHEREdepartment_id=v_deptno;

END;如果用戶輸入不存在的部門編號(hào)會(huì)怎樣?上面代碼不會(huì)出現(xiàn)Oracle服務(wù)錯(cuò)誤.你需要自定義異常來(lái)提示數(shù)據(jù)輸入的錯(cuò)誤.捕獲用戶定義異常自定義異常的方法是:1.在聲明部分定義異常的名稱.2.使用RAISE語(yǔ)句在執(zhí)行部分顯式拋出異常.3.在異常處理部分通過(guò)自定義異常名捕獲異常.捕獲用戶定義異常下面是完整的代碼.捕獲用戶定義異常1.在聲明部分定義異常的名稱.

語(yǔ)法:exceptionEXCEPTION;其中:exception是異常的名稱捕獲用戶定義異常2.使用RAISE語(yǔ)句在執(zhí)行部分顯式拋出異常.

語(yǔ)法:RAISEexception;其中:exception是前面定義的異常名捕獲用戶定義異常3.在異常處理部分通過(guò)自定義異常名捕獲異常.RAISE語(yǔ)句RAISE語(yǔ)句用于拋出已命名的異常.可以拋出:自定義異常Oracle服務(wù)異常RAISE_APPLICATION_ERROR可用RAISE_APPLICATION_ERROR過(guò)程從存儲(chǔ)程序中返回給用戶自定義的錯(cuò)誤消息.使用RAISE_APPLICATION_ERROR的主要優(yōu)點(diǎn)是，和RAISE相比，RAISE_APPLICATION_ERROR允許為自定義的異常指定錯(cuò)誤編號(hào)和錯(cuò)誤消息.錯(cuò)誤編號(hào)必須在-20000和-20999之間.語(yǔ)法:RAISE_APPLICATION_ERRORerror_number是自定義錯(cuò)誤編號(hào)，自定義的異常錯(cuò)誤編號(hào)必須在–20000和–20999之間Message是為錯(cuò)誤自定義的錯(cuò)誤消息.是可長(zhǎng)達(dá)2,048字節(jié)的字符串.TRUE|FALSE是可選的Boolean參數(shù)(TRUE,此錯(cuò)誤在前面出現(xiàn)的所有錯(cuò)誤之前顯示.FALSE,此錯(cuò)誤取代前面出現(xiàn)的所有錯(cuò)誤，前面的其它錯(cuò)誤不再有提示信息.)范圍-20000到-20999的錯(cuò)誤編號(hào)預(yù)留給程序員使用,預(yù)定義的Oracle服務(wù)錯(cuò)誤不會(huì)使用.RAISE_APPLICATION_ERRORRAISE_APPLICATION_ERROR可用于:執(zhí)行部分異常處理部分執(zhí)行部分的

RAISE_APPLICATION_ERRORRAISE_APPLICATION_ERROR過(guò)程為用戶顯示錯(cuò)誤編號(hào)和消息.

這和Oracle服務(wù)錯(cuò)誤的處理方式是一致的.DECLARE

v_mgrPLS_INTEGER:=123;

BEGIN

DELETE

FROMemployeesWHEREmanager_id=v_mgr;

IF

SQL%NOTFOUND

THEN

RAISE_APPLICATION_ERROR(-20202,'Thisisnotavalidmanager');

END

IF;

END;異常部分的

RAISE_APPLICATION_ERRORDECLARE

v_mgrPLS_INTEGER:=27;

v_employee_idemployees.employee_id%TYPE;

BEGIN

SELECTemployee_id

intov_employee_id

FROMemployees

WHEREmanager_id=v_mgr;

DBMS_OUTPUT.PUT_LINE('Theemployeewhoworksformanager_id'||v_mgr||

'is:'||v_employee_id);

EXCEPTION

WHENNO_DATA_FOUNDTHEN

RAISE_APPLICATION_ERROR(-20201,'Thismanagerhasnoemployees');

WHENTOO_MANY_ROWSTHEN

RAISE_APPLICATION_ERROR(-20202,'Toomanyemployeeswerefound.');

END;保留前面的錯(cuò)誤DECLARE

v_mgrPLS_INTEGER:=27;

v_employee_idemployees.employee_id%TYPE;

BEGIN

SELECTemployee_id

intov_employee_id

FROMemployees

WHEREmanager_id=v_mgr;

EXCEPTION

WHENNO_DATA_FOUNDTHEN

RAISE_APPLICATION_ERROR(-20201,'Thismanagerhasnoemployees',true);

END;RAISE_APPLICATION_ERROR

與自定義異常合用捕獲用戶定義異常此部分講解了：編寫PL/SQL代碼定義用戶定義異常編寫PL/SQL代碼拋出異常處理拋出的異常使用RAISE_APPLICATION_ERROR識(shí)別變量作用域此部分將講解：描述在嵌套塊中變量的作用域.使用標(biāo)簽限定嵌套塊中的變量描述異常的作用域識(shí)別嵌套塊中異常的作用域問(wèn)題描述異常在嵌套塊中的傳播方式及其影響識(shí)別變量作用域前面已學(xué)習(xí)過(guò)嵌套塊,變量的作用域和異常的傳播.理解了如何正確處理異常,本部分先復(fù)習(xí)前面知識(shí),然后再講述新的內(nèi)容.命名異常是一種PL/SQL變量.

要正確處理異常,需要理解異常變量的作用域和可見性.這在使用嵌套塊時(shí)尤其重要.復(fù)習(xí)嵌套塊下例有外層(父)塊(藍(lán)色顯示)和嵌套(子)塊(紅色顯示).變量v_outer_variable在父塊中定義，變量v_inner_variable在子塊中定義.復(fù)習(xí)變量作用域變量作用域是變量在其中可被訪問(wèn)和使用的一個(gè)塊或多個(gè)塊.PL/SQL中,變量作用域是其定義所在塊及其所有子塊.下例兩個(gè)變量的作用域是什么?復(fù)習(xí)變量作用域閱讀下面代碼.每個(gè)變量的作用域是什么?復(fù)習(xí)變量作用域下面代碼為什么出錯(cuò)?復(fù)習(xí)變量作用域下面代碼正確嗎?為什么?PL/SQL怎樣尋找變量?在塊中使用變量時(shí),

PL/SQL先在當(dāng)前塊中尋找該變量(局部變量).如果未找到,PL/SQL繼續(xù)在父塊中尋找.如果還未找到,PL/SQL在父塊的父塊中尋找(嵌套可有三層或更多層).依此類推.下例中有三層嵌套.三層嵌套示例每個(gè)變量的作用域是什么?復(fù)習(xí)變量命名下面變量聲明對(duì)不對(duì)?以上聲明合法，但在子塊中無(wú)法訪問(wèn)父塊定義的變量v_myvar.復(fù)習(xí)變量命名變量v_date_of_birth聲明了兩次.DBMS_OUTPUT.PUT_LINE語(yǔ)句中引用的是哪個(gè)v_date_of_birth?復(fù)習(xí)變量可見范圍變量可見范圍是變量作用域中，不加限定符就可使用變量的部分.下面每個(gè)變量的可見范圍是什么?復(fù)習(xí)變量可見范圍父塊的v_date_of_birth變量作用域包括子塊.此變量在父塊中可見.然而,該變量在子塊中不可見（被隱藏），因?yàn)樽訅K中有一個(gè)同名的局部變量.變量v_father_name在父子塊中都可