《數(shù)據(jù)庫應(yīng)用技術(shù)》第5章 面向過程的SQL擴展

數(shù)據(jù)庫應(yīng)用技術(shù)

第五章面向過程的SQL擴展內(nèi)容概要5.1服務(wù)器端程序5.2PL/SQL的基本語法5.3控制流5.4過程與函數(shù)5.5游標5.6錯誤與異常處理5.7觸發(fā)器5.1服務(wù)器端程序存儲過程：由用戶創(chuàng)建的，使用SQL和其他語言(如PL/SQL)編寫的過程或函數(shù)，存儲在數(shù)據(jù)庫內(nèi)部，用來完成一個特定的任務(wù)。Oracle：PL/SQLMS-SQL：Transaction-SQLPL/SQL：Oracle對SQL進行擴展的過程式語言，具有通用程序設(shè)計語言的絕大部分特性，能夠完成較為復(fù)雜和完整的功能可以用來編寫存儲過程。5.1服務(wù)器端程序存儲過程的優(yōu)點安全：創(chuàng)建者必須擁有存儲過程內(nèi)部指令執(zhí)行需要的所有權(quán)限，而調(diào)用者之需要擁有運行存儲過程的權(quán)限。性能：網(wǎng)絡(luò)開銷小，不傳送中間結(jié)果。節(jié)約SQL代碼分析時間代碼可重用完整性和一致性特性：可以使用變量、游標、控制結(jié)構(gòu)，具有模塊化、數(shù)據(jù)抽象、信息隱藏、錯誤處理等特性。5.2PL/SQL的基本語法1、PL/SQL的基本規(guī)則2、塊結(jié)構(gòu)3、變量的定義與使用4、內(nèi)嵌SQLPL/SQL的基本規(guī)則每條語句可以寫在多行每條語句都以;結(jié)尾語句保留字和變量不區(qū)分大小寫--引導(dǎo)單行注釋/*…*/引導(dǎo)多行注釋塊結(jié)構(gòu)塊結(jié)構(gòu)一個塊的語法地位等價于一條語句塊的整體構(gòu)成[<<name>>] [DECLARE <聲明部分>] BEGIN <執(zhí)行部分> [EXCEPTION <錯誤處理部分>] END;變量的定義與使用變量類型簡單變量類型和字段的變量類型相同記錄變量類型一組具有不同類型的變量的集合，類似C中的STRUCT或PASCAL中的RECORD。集合變量類型變量的定義與使用變量聲明必須先聲明，后使用。在DECLARE段聲明不允許前向引用一行中只能定義一個變量大小寫不敏感變量的定義與使用簡單示例salers_countNUMBER(4);--初始值為NULL/*賦缺省值*/blood_typeCHAR:=‘A’;blood_typeCHARDEFAULT‘A’;/*NOTNULL類型的變量必須有缺省值*/salers_countNUMBER(4)NOTNULL:=0;變量的定義與使用%TYPE使用其它變量或列的數(shù)據(jù)類型，但不繼承NOTNULL屬性

creditNUMBER(7,2)NOTNULL:=0; debitcredit%TYPE;經(jīng)常用于表中字段的數(shù)據(jù)類型my_snamesalers.sname%TYPE;優(yōu)點：不需要知道精確類型；在表定義發(fā)生變化時，不用修改程序。變量的定義與使用%ROWTYPE使用其他表、游標等定義記錄變量 salers_recsalers%ROWTYPE; CURSORc1IS

SELECTsid,sname,lidFROMsalers; salers_recc1%ROWTYPE;變量的定義與使用賦值與計算、比較使用:=作為賦值運算符其他計算、比較方法類似于普通語言作用域外層定義的變量可以在子塊中使用可以在不同塊中定義同名變量子塊可以通過外層的塊名來引用外層變量內(nèi)嵌SQL可以直接在PL/SQL中使用DQL和DML，但不能使用DDL和DCL。在SQL語句中使用變量的值可以出現(xiàn)表達式的地方都可以使用變量直接寫變量名，不需要額外的語法要素。容易和字段混淆在MS-SQL中，使用:作為前綴通常出現(xiàn)在where子句、select子句內(nèi)嵌SQL將查詢結(jié)果賦值給一個變量SELECT…

INTO<變量列表>FROM子句

[…

其他子句]要求SELECT語句必須返回1行結(jié)果，運行時返回多行或0行會報錯。必須在邏輯上保證返回一條記錄，例如使用主鍵或者聚組函數(shù)等。與子查詢不同，返回0行也是錯誤。如何進行錯誤處理，見后面章節(jié)。5.3控制流語句定義標號跳轉(zhuǎn)語句空語句返回語句條件語句循環(huán)語句控制流語句定義標號

<<標號>>跳轉(zhuǎn)語句GOTO<標號>;EXIT<標號>[WHEN<條件>];空語句NULL;返回語句RETURN;控制流語句-條件語句IF<條件>THEN <語句> ENDIF;IF<條件>THEN <語句1> ELSE <語句2> ENDIF;IF<條件1>THEN <語句1> ELSIF<條件2>THEN <語句2> ELSIF<條件3>THEN <語句3> … ELSE <語句n> ENDIF;控制流語句-循環(huán)語句<<標號1>> LOOP ...

<<標號2>> LOOP ... EXIT<標號1>WHEN<條件>; ENDLOOP; ... ENDLOOP;控制流語句-循環(huán)語句WHILE<條件>LOOP … ENDLOOP;FOR<變量>IN<值1>..<值2>[REVERSE]LOOP … ENDLOOP;5.4過程與函數(shù)過程與函數(shù)的作用維護過程與函數(shù)外部過程與內(nèi)部過程調(diào)用與參數(shù)傳遞過程與函數(shù)-維護過程(procedure)CREATE[ORREPLACE]PROCEDURE<過程名>[(<參數(shù)列表>)]{AS|IS}<語句塊>;<參數(shù)>：<參數(shù)名>[IN|OUT|INOUT]<數(shù)據(jù)類型>[DEFAULT<缺省值>]<數(shù)據(jù)類型>：無長度和精度ALTERPROCEDURE<過程名>COMPILE;DROPPROCEDURE<過程名>;過程與函數(shù)-維護函數(shù)(function)CREATE[ORREPLACE]FUNCTION<函數(shù)名>[(<參數(shù)列表>)]RETURN<返回類型>{AS|IS}<語句塊>;ALTERFUNCTION<函數(shù)名>COMPILE;DROPFUNCTION<函數(shù)名>;創(chuàng)建過程例：計算某顧客在給定時間前一年的總購買金額。CREATEORREPLACEPROCEDUREp_1(in_CidINCHAR,out_SumOUTNUMBER,in_DateINDATEDEFAULTsysdate)

ASBEGINSELECTsum(dollars)INTOout_SumFROMordersWHEREcid=in_CidANDbuy_dateBETWEENin_Date-365ANDin_Date;END;CREATEORREPLACEFUNCTIONf_1(in_CidINCHAR,in_DateINDATEDEFAULTsysdate)RETURN

NUMBER AS

BEGIN

DECLARE v_SumNUMBER; BEGIN SELECTsum(dollars)INTOv_sum FROMorders WHEREcid=in_CidAND buy_dateBETWEENin_Date-365ANDin_Date;

RETURNv_sum;

END; END;過程的執(zhí)行方法SQL*PLUS中，使用EXECUTE命令。在其他PL/SQL程序中，直接寫出過程及參數(shù)。DECLAREv_sumnumber;v_cidchar(4)DEFAULT'C001';v_datedate:=to_date('2013-01-01','yyyy-mm-dd');BEGIN

p_1(v_cid,v_sum,v_date);--全局過程p_1dbms_output.put_line(v_cid||'inthelastyearof'||to_char(v_date)||'hasconsumed'||to_char(v_sum));END;exp參數(shù)傳遞的語法位置表示f_1(‘C001’,sysdate);名稱表示

f_1(in_Cid=>‘C001’,in_Date=>sysdate);

f_1(in_Date=>sysdate,in_Cid=>‘C001’);混和表示f_1(‘C001’,in_Date=>sysdate);如果默認值在前，后面有非默認值，必須使用名稱表示方式。名稱表示法后不能再用位置表示法。外部過程與內(nèi)部過程內(nèi)部（子）過程在過程或函數(shù)的DECLARE部分定義，只供該過程或函數(shù)調(diào)用。不能在外部使用聲明時不使用關(guān)鍵字CREATE放在DECLARE中的最后部分先聲明后使用，可前向聲明。不單獨存放在數(shù)據(jù)庫中內(nèi)部過程舉例CREATEORREPLACEPROCEDUREp_3ASBEGINDECLAREv1NUMBER;

PRODECURElp_1ASBEGIN…END;

PROCEDURElp_2ASBEGIN

lp_1;END;BEGIN

lp_2;

lp_1;END;END;參數(shù)類型比較INOUTINOUT指定缺省類型作用傳入?yún)?shù)傳出返回值傳遞參數(shù)和返回值形式參數(shù)如同一個常數(shù)未初始化變量已初始化變量不能賦值不能用于表達式、必須賦值應(yīng)該賦值實際參數(shù)可以是常量、變量、表達式變量變量傳遞方式值傳送引用傳送引用傳送包（Package）將一組相關(guān)過程、函數(shù)、變量、常量和游標等PL/SQL程序設(shè)計元素組合在一起的數(shù)據(jù)庫對象。分為包定義和包體兩部分。包定義和包主體分開編譯，并作為兩部分分別存在數(shù)據(jù)字典中。包內(nèi)的元素分為公有和私有兩種。包定義包定義是包和應(yīng)用程序之間的接口用于定義包的公用組件，包括常量、變量、游標、過程和函數(shù)等。公用組件不僅可以在包內(nèi)引用，而且可以由其他子程序引用。變量salers_num過程add_saler過程fire_saler函數(shù)get_salary全局變量公用過程公用過程公用函數(shù)包定義CREATE[ORREPLACE]PACKAGE<包名>{AS|IS}<數(shù)據(jù)類型定義，游標、變量、常量和子程序聲明>

END[<包名>];包定義中的元素都是公有的。包定義例如：

CREATEORREPLACEPACKAGEsalers_packageIS

salers_numNUMBER(3):=30;

PROCEDUREadd_salers(…);

PROCEDUREfire_salers(…);

FUNCTIONget_salary(…)RETURNsalers.salary%TYPE;

…

ENDsalers_package;包體包體用于實現(xiàn)包定義中的過程和函數(shù)。在包體中，可以定義私有組件，包括數(shù)據(jù)類型、變量、常量、過程和函數(shù)等。這些私有組件只能在包體中使用。包體中的公有子程序定義必須與包定義中該子程序的聲明格式完全一致。CREATE[ORREPLACE]PACKAGEBODY<包名>{AS|IS}<數(shù)據(jù)類型定義，變量、常量、子定義和聲明>

BEGIN <PL/SQL語句>END<包名>;調(diào)用包組件私有組件，只能在包內(nèi)調(diào)用，并且直接調(diào)用；公用組件，即可以在包內(nèi)調(diào)用，也可以在其他程序中調(diào)用；在其他程序中調(diào)用包的組件時，必須加上包名作為前綴。例如：dbms_output.put_line(‘…’);dbms_output：包名put_line：過程名5.5游標游標的作用使用游標和游標屬性游標FOR循環(huán)使用游標更新或者刪除數(shù)據(jù)游標的作用用來處理從數(shù)據(jù)庫中查詢出來的一組數(shù)據(jù)的機制。游標查詢返回的數(shù)據(jù)稱為結(jié)果集(resultset)。游標的使用與文件的操作類似，包括OPEN、FETCH和CLOSE等步驟。游標的類型顯式游標專門用于處理SELECT語句返回的多行數(shù)據(jù)隱式游標PL/SQL為每一個SELECT…INTO和DML語句隱含地定義了一個游標包游標如何使用游標聲明在DECLARE段中打開循環(huán)讀取讀取當(dāng)前行的數(shù)據(jù)到變量中判斷是否讀到末尾關(guān)閉游標關(guān)閉后才可以重新打開DECLAREOPENFETCHCLOSE空？聲明游標聲明CURSOR<游標名>[(<參數(shù)列表>)][RETURN<返回類型>]IS<SELECT查詢語句>;<參數(shù)>：<游標參數(shù)名>[IN]<數(shù)據(jù)類型>[{:=|DEFAULT}<表達式>]參數(shù)只能是基本類型，必須是IN類型，可以有缺省值。DECLARECURSORc1IS

SELECTsid,sname,salary

FROMsalers

WHEREsalary>2000;CURSORc2RETURNsalers%ROWTYPE

IS

SELECT*

FROMsalersWHEREsalary>2000;CURSORc3(in_salaryNUMBER)IS

SELECTsid,sname,salary

FROMsalers

WHEREsalary>in_salary;exp打開游標打開OPEN<游標名>[(<傳入的參數(shù)>)]；可以使用位置表示法和名稱表示法不能打開已經(jīng)打開的游標打開游標時帶入的參數(shù)已經(jīng)在打開時被固定Oracle執(zhí)行所對應(yīng)的SELECT語句，并將結(jié)果暫存在結(jié)果集中。讀取游標中的數(shù)據(jù)讀取數(shù)據(jù)FETCH<游標名>INTO{<變量列表>|<記錄變量列表>};循環(huán)讀LOOP FETCH<游標名>INTO…; EXITWHEN<游標名>%NOTFOUND; <處理游標變量語句>ENDLOOP;關(guān)閉游標關(guān)閉CLOSE<游標名>;關(guān)閉游標釋放結(jié)果集游標屬性顯式隱式（SQL%）%ISOPEN游標打開時為TRUE，否則為FALSE。語句執(zhí)行完畢后，自動關(guān)閉游標，因此該屬性永遠為FALSE。%FOUND游標打開后，F(xiàn)ETCH前為NULL，F(xiàn)ETCH到數(shù)據(jù)時為TRUE，沒有FETCH到數(shù)據(jù)時為FALSE。TRUE表示INSERT、UPDATE和DELETE語句成功地影響了表中的某些數(shù)據(jù)，SELECTINTO成功地返回了數(shù)據(jù)。%NOTFOUND與%FOUND相反與%FOUND相反%ROWCOUNT游標剛打開時為0，每成功FETCH一行數(shù)據(jù)后加1。被INSERT、UPDATE和DELETE語句影響的行數(shù)，SELECTINTO有返回時為1。對于顯式游標，只有%ISOPEN可以在游標未打開時使用。否則產(chǎn)生錯誤。隱式游標表示了最近的一條DML或SELECTINTO語句執(zhí)行的信息。在Oracle打開隱式游標前，所有屬性都返回NULL。如果想在后續(xù)語句中使用游標屬性值，必須及時賦值給其他變量。CREATEORREPLACEFUNCTIONf_var1(in_lidCHAR)RETURNNUMBERISDECLAREv_avgNUMBER;v_sumNUMBERDEFAULT0;CURSORc1(v_lidCHAR)ISSELECTsalaryFROMsalersWHERElid=in_lid;viNUMBER;v_countnumber:=0;BEGINSELECTAVG(salary)INTOv_avgFROMsalersWHERElid=in_lid;

OPENc1(in_lid);

LOOPFETCHc1INTOvi;EXITWHENc1%NOTFOUND;v_count:=v_count+1;v_sum:=v_sum+(vi-v_avg)*(vi-v_avg);

ENDLOOP;CLOSEc1;v_sum:=v_sum/v_count;RETURNv_sum;END;例1：計算在某工作地點（Lid）工作的銷售員工資的方差。游標FOR循環(huán)自動聲明一個和游標返回值相同類型的循環(huán)變量，自動打開游標，在每一次循環(huán)中讀取一行數(shù)據(jù)賦給循環(huán)變量，在出錯或沒有數(shù)據(jù)時結(jié)束循環(huán)，關(guān)閉游標。同時，當(dāng)在循環(huán)中使用EXIT、GOTO語句或發(fā)生錯誤而跳出循環(huán)時，自動關(guān)閉游標。語法FOR<循環(huán)變量>IN<游標名>[(<參數(shù)列表>)]LOOP <游標處理語句>ENDLOOP;CREATEORREPLACEFUNCTIONf_var2(in_lidCHAR)RETURNNUMBERISDECLAREv_avgNUMBER;v_sumNUMBERDEFAULT0;CURSORc1(v_lidCHAR)ISSELECTsalaryFROMsalersWHERElid=in_lid;

vic1%ROWTYPE;v_countnumber:=0;BEGINSELECTAVG(salary)INTOv_avgFROMsalersWHERElid=in_lid;FORviINc1(in_lid)LOOPv_sum:=v_sum+(vi.salary

–v_avg)*(vi.salary

–v_avg);

v_count:=v_count+1;ENDLOOP;v_sum:=v_sum/v_count;RETURNv_sum;END;exp例1：使用游標FOR循環(huán)CREATETABLElocations_tempASSELECT*FROMlocations;DECLAREv_lidCHAR(4):='L001';BEGIN

DELETE

FROMlocations_tempWHERElid=v_lid;IFSQL%FOUNDTHENINSERTINTOlocations_tempVALUES(v_lid,‘Delete',null,null);ENDIF;END;例2CREATETABLEsalers_tempASSELECT*FROMsalers;DECLAREv_managerCHAR(4):=‘S001’;BEGINDELETEFROMsalers_tempWHEREmanager=v_manager;dbms_output.put_line('Numberofsalersdeleted:'||TO_CHAR(SQL%ROWCOUNT));END;例3DECLARECURSORc1ISSELECTsnameFROMsalersWHEREROWNUM<5;v_snamesalers.sname%TYPE;BEGINOPENc1;LOOPFETCHc1INTOv_sname;EXITWHENc1%NOTFOUND;dbms_output.put_line(c1%ROWCOUNT||'.'||v_sname);IFc1%ROWCOUNT=2THENdbms_output.put_line('---Fetched2threcord---');ENDIF;ENDLOOP;CLOSEc1;END;exp例4使用游標更新或者刪除數(shù)據(jù)在UPDATE和DELETE中使用CURRENTOF<游標名>子句，直接修改當(dāng)前行的數(shù)據(jù)。FORUPDATE鎖FORUPDATE[OF<字段列表>][NOWAIT];在游標定義時使用FORUPDATE子句，鎖定該游標查詢出來的所有行，避免其他用戶對相關(guān)內(nèi)容的修改。NOWAIT：當(dāng)請求的行被其他用戶鎖定時，Oracle不等待，在重新回來請求前做其他的事情。如果游標將在UPDATE或DELETE語句中通過CURRENTOF子句使用，必須在定義時使用FORUPDATE子句。FORUPDATE子句特別在一個長時間的事務(wù)過程中，避免前后出現(xiàn)不一致的情況。exp例5：給工資低于4500的銷售員加200元DECLARECURSORc1ISSELECTsname,salaryFROMsalersFORUPDATE;v_namesalers.sname%TYPE;v_salarysalers.salary%TYPE;BEGINOPENc1;LOOPFETCHc1INTOv_name,v_salary;EXITWHENc1%NOTFOUND;IFv_salary<4500THENv_salary:=v_salary+200;UPDATEsalersSETsalary=v_salaryWHERECURRENTOFc1;dbms_output.put_line(v_name||'ssalaryisincreasedto‘||

to_char(v_salary));ENDIF;ENDLOOP;CLOSEc1;END;5.6錯誤與異常處理錯誤與異常兩類處理機制的對比異常的定義與分類結(jié)構(gòu)化異常處理錯誤與異常各種非正常的狀態(tài)稱為錯誤。不能夠處理非正常情況的程序是不可靠的，不夠魯棒。異常是對錯誤情況的包裝，在錯誤傳播、處理、返回等操作過程中標明錯誤內(nèi)容。異常也可以指進行錯誤處理的機制。兩種處理機制處理錯誤的方式通常有兩類，一類是C語言等的實現(xiàn)方式，包括：返回值和全局狀態(tài)標志發(fā)生錯誤的程序段不一定能夠解決問題，它可以通過返回值和全局狀態(tài)標志將錯誤的狀態(tài)傳遞給調(diào)用者。但是，調(diào)用者完全可能忽視錯誤。錯誤處理的代碼混雜在正常的程序處理流程中，使得程序結(jié)構(gòu)顯得比較混亂。兩種處理機制另外一種方式，是在PL/1語言中提出的，并在PL/SQL中應(yīng)用，在C++和Java語言中被采用的“結(jié)構(gòu)化異常處理”的方法。try{ /*正常流程*/ } catch(){ /*根據(jù)不同錯誤原因進行處理*/ }優(yōu)點發(fā)生錯誤的程序段只負責(zé)把錯誤拋出，而由有能力的高層調(diào)用者（包括最終用戶）來解決問題。程序流程清晰程序員必須處理錯誤缺點需要對錯誤進行分類，有一個明確的分類體系。錯誤分類和定義在Oracle中，錯誤分為:系統(tǒng)預(yù)定義（內(nèi)部）錯誤，用于處理Oracle中常見的錯誤。用戶自定義錯誤，用于處理與Oracle錯誤無關(guān)的其他情況。所有錯誤均有一個特定的錯誤代碼一些錯誤有預(yù)定義的名字，也可以為沒有名字的錯誤指定名字。系統(tǒng)預(yù)定義用戶定義通用無名名字編號觸發(fā)方式自動自動RAISERAISE_APPLICATION_ERROR定義方式名稱/代碼代碼<異常名>EXCEPTION消息文本，代碼：[-20000,-20999]定義域全局全局塊局部整個程序一些常見的預(yù)定義錯誤預(yù)定義異常名字

描述

Oracle異常

異常代碼CURSOR_ALREADY_OPEN試圖打開一個已經(jīng)打開的游標，一個游標在它重新打開前必須被關(guān)閉。一個游標FOR循環(huán)會自動地打開所涉及的游標，所以在游標循環(huán)里不能打開游標。ORA-06511-6511DUL_VAL_ON_INDEX試圖在一個有唯一性約束的數(shù)據(jù)庫列中存儲重復(fù)的值ORA-00001-1INVALID_CURSOR試圖執(zhí)行一個無效的游標操作ORA-01001-1001INVALID_NUMBER試圖將一個看起來不像是一個有效的數(shù)字的字符串轉(zhuǎn)換成數(shù)字失敗。在過程性語句中，將會引發(fā)VALUE_ERROR錯誤，代替INVALID_NUMBER錯誤。ORA-01722-1722LOGIN_DENIED用一個無效的用戶名或口令去登陸OracleORA-01017-1017一些預(yù)定義錯誤預(yù)定義異常名字

描述Oracle異常

異常代碼NO_DATA_FOUND一個SELECTINTO語句沒有返回數(shù)據(jù)，或者程序引用一個嵌套表中被刪除的元素，或者索引表中一個沒有被初始化的元素。

ORA-01403100TIMEOUT_ON_RESOURCEOracle在等待資源時發(fā)生超時現(xiàn)象ORA-00051-51TOO_MANY_ROWSSELECTINTO語句返回了多行數(shù)據(jù)ORA-01422-1422VALUE_ERROR一個算法、轉(zhuǎn)換、截斷或者大小約束錯誤發(fā)生。如果在SQL語句中發(fā)生錯誤則會引發(fā)INVALID_ERROR錯誤，替代了VALUE_ERROR錯誤。ORA-06502-6502ZERO_DIVIDE發(fā)生被零除ORA-01476-1476聲明用戶自定義異常<異常名>EXCEPTION;說明1、在DECLARE段聲明；2、作用域方面與變量類似；3、定義的異常名不能出現(xiàn)在賦值和SQL語句中。通過名字拋出異常RAISE<異常名>;說明必須是名字，而不是錯誤代碼；可拋出用戶自定義的與系統(tǒng)預(yù)定義的異常名；使用時應(yīng)注意異常名的作用域。異常的捕獲與處理在EXCEPTION段中捕獲和處理異常{WHEN<異常名>[{OR<異常名>}]THEN<異常處理語句>}[WHENOTHERSTHEN<異常處理語句>]只通過異常名進行處理對于系統(tǒng)內(nèi)部的無名異常和用戶自定義編號異常，

可以在OTHERS里面通過檢查錯誤代碼來判斷，或者，可以使用用戶定義的方式為其起名。異常處理過程執(zhí)行體內(nèi)出現(xiàn)異常，轉(zhuǎn)到本塊的異常處理程序。在聲明段和錯誤處理段發(fā)生異常時，轉(zhuǎn)到外面一層的異常處理程序。如果在這個位置沒有找到異常處理程序，和處理程序中沒有找到對應(yīng)的錯誤號，且沒有OTHERS段，則繼續(xù)向外傳播。如直到最外層都沒有找到，則結(jié)束當(dāng)前程序，返回用戶。如果找到異常處理程序，在執(zhí)行完異常處理程序后，控制權(quán)轉(zhuǎn)移到異常處理程序的外層塊的下一條語句執(zhí)行。如異常處理在最外層，則結(jié)束程序。expDECLAREpast_dueEXCEPTION;BEGIN

DECLAREpast_dueEXCEPTION;

BEGINRAISEpast_due;

END;EXCEPTIONWHENpast_dueTHENdbms_output.put_line('Handlingpast_dueEXCEPTION.');WHENOTHERSTHENdbms_output.put_line('Couldnotrecognizepast_dueEXCEPTIONinthisscope.');END;覆蓋了外層past_due內(nèi)層沒有對past_due的處理外層也無法對內(nèi)層的past_due進行捕獲BEGIN<<b1>>DECLAREpast_dueEXCEPTION;BEGINDECLAREpast_dueEXCEPTION;BEGIN

RAISEb1.past_due;END;EXCEPTIONWHENpast_dueTHENdbms_output.put_line('Handlingpast_dueexception.');WHENOTHERSTHENdbms_output.put_line('CouldnotrecognizePAST_DUE_EXCEPTIONinthisscope.');END;END;為語句塊命名拋出外層b1.past_due異常能夠捕獲本層定義的past_due異常expBEGIN

DECLAREcredit_limitCONSTANTNUMBER(3):=5000;

BEGINNULL;

EXCEPTIONWHENOTHERSTHENdbms_output.put_line('Handletheexceptioninthesameblock.');

END;EXCEPTIONWHENOTHERSTHENdbms_output.put_line('Theexceptionishandledbytheouterblock.');END;DECLARE段的異常不會在本層捕獲，而是傳遞至外層。DECLARE段發(fā)生異常同層EXCEPTION段無法處理向外層傳遞，可由外層EXCEPTION段處理。BEGIN

BEGIN

RAISEINVALID_NUMBER;

EXCEPTION

WHENINVALID_NUMBERTHEN

dbms_output.put_line('INVALID_NUMBERexceptionishandledinthesameblock.');INSERTINTOsalersVALUES('S001','dup_val_on_index','L001',1,1,null);

WHENDUP_VAL_ON_INDEXTHENdbms_output.put_line('DUP_VAL_ON_INDEXexceptionishandledinthesameblock.');

END;EXCEPTION

WHENDUP_VAL_ON_INDEXTHEN

dbms_output.put_line('DUP_VAL_ON_INDEXexceptionishandledbytheouterblock.');END;EXCEPTION段的異常不會在本層捕獲，而是傳遞至外層。EXCEPTION段發(fā)生異常同層EXCEPTION段無法處理向外層傳遞，可由外層EXCEPTION段處理。可拋出系統(tǒng)預(yù)定義的異常exp通過代碼拋出異常RAISE_APPLICATION_ERROR(<錯誤代碼>,<錯誤消息>,[TRUE|FALSE]);錯誤代碼：區(qū)間是[-20000,-20999]錯誤消息：CHAR(2048)-20000應(yīng)用于一般性錯誤如果第三個參數(shù)為FALSE，替換現(xiàn)有的錯誤。如果為TRUE，加在現(xiàn)有錯誤之上（新舊異常的MESSAGE都保留，但新異常的代碼將覆蓋舊異常的代碼）。SQLCODE和SQLERRMsqlcode和sqlerrm是Oracle內(nèi)置函數(shù)。sqlcode返回當(dāng)前異常代碼。sqlerrm返回當(dāng)前異常的消息，其中第一部分是ORA-開頭的異常編號，總長度最大512字節(jié)。多用于代碼最外層的others異常處理段，獲取所有程序未處理的異常的信息。系統(tǒng)預(yù)定義異常sqlcode返回的異常代碼是負數(shù)。特殊情況是，“未找到數(shù)據(jù)”錯誤，sqlerrm返回”ora-01403：nodatafound”，而sqlcode為+100。用戶自定義異常sqlcode返回的異常代碼是+1，sqlerrm返回的是”user-definedexception”。如果沒有異常被觸發(fā)sqlcode返回0，sqlerrm返回”ora-0000:normal,successfulcompletion.”CREATETABLEerrors(codeNUMBER,messageVARCHAR2(64),happenedTIMESTAMP);------------------------------------------------------------------------------------------------------------------------------------------------DECLAREv_namesalers.sname%TYPE;v_codeerrors.code%TYPE;v_errmerrors.message%TYPE;BEGINSELECTsnameINTOv_nameFROMsalersWHEREsid=‘0000’;EXCEPTIONWHENOTHERSTHEN

v_code:=sqlcode;v_errm:=substr(sqlerrm,1,64);dbms_output.put_line('Errorcode'||v_code||':'||v_errm);INSERTINTOerrorsVALUES(v_code,v_errm,systimestamp);END;expBEGIN

BEGINRAISE_APPLICATION_ERROR(-20000,'-20000error');

EXCEPTIONWHENothersTHENdbms_output.put_line('Errorcode'''||sqlcode||''':'''||substr(sqlerrm,1,200)||'''ishandled.');RAISE_APPLICATION_ERROR(-20001,'-20001error',true);

END;EXCEPTIONWHENothersTHENdbms_output.put_line('Errorcode'''||sqlcode||''':'''||substr(sqlerrm,1,200)||'''ishandled.');END;RAISE_APPLICATION_ERROR，參數(shù)TRUE時，新舊異常的MESSAGE都保留，新異常的CODE覆蓋舊的。exp通過EXCEPTION_INIT預(yù)編譯指令可以對用戶自定義異常的名字與異常代碼進行關(guān)聯(lián)。可以為系統(tǒng)預(yù)定義異常命名。PRAGMAEXCEPTION_INIT(<異常名>,<異常代碼>);DECLARE

deadlock_detectedEXCEPTION;

PRAGMAEXCEPTION_INIT(deadlock_detected,-60);v_codeerrors.code%TYPE;v_errmerrors.message%TYPE;BEGINRAISEdeadlock_detected;EXCEPTIONWHENdeadlock_detectedTHENv_code:=sqlcode;v_errm:=substr(sqlerrm,1,64);dbms_output.put_line('Errorcode'||v_code||':'||v_errm);INSERTINTOerrorsVALUES(v_code,v_errm,systimestamp);END;將-60號系統(tǒng)異常與deadlock_detected關(guān)聯(lián)expDECLARE

salary_too_highEXCEPTION;current_salaryNUMBER:=20000;max_salaryNUMBER:=10000;erroneous_salaryNUMBER;BEGINBEGINIFcurrent_salary>max_salaryTHENRAISEsalary_too_high;ENDIF;EXCEPTIONWHENsalary_too_highTHENdbms_output.put_line(‘Currentsalary'||current_salary||'isoutofrange.');

dbms_output.put_line('Maximumsalaryis'||max_salary||'.');

RAISE;END;EXCEPTION

WHENsalary_too_highTHENerroneous_salary:=current_salary;current_salary:=max_salary;

dbms_output.put_line('Revisingsalaryfrom'||erroneous_salary||'to'||current_salary||'.');END;拋出異常適當(dāng)處理后，再次拋出異常再次捕獲并進行處理expCREATETABLEt_temp(id

NUMBER,salaryNUMBER,pctNUMBER);INSERTINTOt_tempVALUES(301,2500,0);------------------------------------------------------------------------------------------------------------------------------------------------DECLAREsal_calcNUMBER;BEGINSELECTsalary/pctINTOsal_calcFROMt_tempWHEREid=301;

INSERTINTOt_tempVALUES(302,sal_calc,0.1);EXCEPTIONWHENZERO_DIVIDETHENdbms_output.put_line('ZERO_DIVIDEexceptionishandled.');END;當(dāng)SELECT語句發(fā)生ZERO_DIVIDE異常并被捕獲后，后續(xù)INSERT語句代碼無法繼續(xù)執(zhí)行。DECLAREsal_calcNUMBER(8,2);BEGINBEGINSELECTsalary/pctINTOsal_calcFROMt_tempWHEREid=301;EXCEPTIONWHENZERO_DIVIDETHENsal_calc:=2500;dbms_output.put_line('ZERO_DIVIDEexceptionishandled.');END;INSERTINTOt_tempVALUES(303,sal_calc,0.1);END;為可能出現(xiàn)異常的代碼增加子塊并對異常單獨捕獲，可以使代碼正常執(zhí)行。expDECLAREv_stmt_noNUMBER;v_nameVARCHAR2(20);BEGINv_stmt_no:=1;SELECTsnameINTOv_nameFROMsalersWHEREsid='S001';v_stmt_no:=2;SELECTsnameINTOv_nameFROMsalersWHEREsid='L001';EXCEPTIONWHENNO_DATA_FOUNDTHENdbms_output.put_line('The'||v_stmt_no||'thstatement!');END;當(dāng)有多條語句可能發(fā)生相同的錯誤時，可以使用變量對錯誤發(fā)生的位置進行定位。exp如何保證代碼被執(zhí)行？循環(huán)執(zhí)行，成功時提交并退出循環(huán)，失敗時在異常處理塊中將數(shù)據(jù)狀態(tài)回滾至保存點，重新開始循環(huán)。CREATETABLEt(idCHAR(10),nameVARCHAR2(10));

CREATEUNIQUEINDEXt_idxONt(id);INSERTINTOtVALUES(‘system',‘AAA');DECLAREv_idCHAR(10):='system';v_nameVARCHAR2(10):='AAA';v_suffixNUMBER:=0;BEGINFORiIN1..5LOOPBEGINSAVEPOINTsp;INSERTINTOtVALUES(v_id,v_name);COMMIT;EXIT;EXCEPTIONWHENDUP_VAL_ON_INDEXTHENROLLBACKTOsp;v_suffix:=v_suffix+1;v_id:=v_id||TO_CHAR(v_suffix);END;ENDLOOP;END;exp5.7觸發(fā)器觸發(fā)器的基本概念創(chuàng)建和維護觸發(fā)器舉例視圖觸發(fā)器觸發(fā)器的基本概念觸發(fā)器是一種特殊的存儲過程。由用戶定義后，卻不是由用戶顯式調(diào)用的，而是當(dāng)滿足某個觸發(fā)事件時由系統(tǒng)自動執(zhí)行的。觸發(fā)器包括：觸發(fā)事件、觸發(fā)器約束和觸發(fā)器動作。觸發(fā)事件：DML語句、DDL語句、數(shù)據(jù)庫系統(tǒng)事件和用戶事件。觸發(fā)約束：在何種條件下觸發(fā)。動作：一段PL/SQL程序。用途與限制觸發(fā)器的用途高級的存取限制（例如在特定時間修改）復(fù)雜的數(shù)據(jù)一致性檢查自動產(chǎn)生關(guān)聯(lián)的數(shù)據(jù)自動建立事件日志觸發(fā)器的限制盡量使用完整性約束盡量不使用多重觸發(fā)器、不要產(chǎn)生遞歸不要過長（<60行）觸發(fā)器代碼不能包括DDL和事務(wù)控制語句（如COMMIT、ROLLBACK和

SAVEPOINT）。創(chuàng)建和維護觸發(fā)器CREATE[ORREPLACE]TRIGGER<觸發(fā)器名> BEFORE|AFTER|INSTEADOF DELETE|INSERT||UPDATE[OF<列名>] ON<表名>|<視圖名> [REFERENCINGOLDAS<舊名>|NEWAS<新名>] [FOREACHROW|STATEMENT] [WHEN(<條件表達式>)] <PL/SQL語句塊>;創(chuàng)建和維護觸發(fā)器BEFORE多用于檢查語句是否恰當(dāng)，進行一致性檢查等。AFTER多用來做日志，記錄語句進行的操作。條件謂詞：INSERTING，DELETING，UPDATING，UPDATING(<字段名>):new和:old?？梢栽贐EFORE觸發(fā)器中向:new記錄中賦值，但不能在AFTER觸發(fā)器中賦值。如果表名與new和old沖突，可以使用REFERENCING子句指定其他名稱。維護觸發(fā)器ALTERTRIGGER<觸發(fā)器名>[ENABLE|DISABLE|COMPILE];DROPTR