第7章存儲過程和觸發(fā)器存儲過程存儲過程它存放在數(shù)據(jù)字典中，可以在不同用戶和應(yīng)用程序之間共享，并可實現(xiàn)程序的優(yōu)化和重用。存儲過程的優(yōu)點如下。（1）存儲過程在服務(wù)器端運行，且執(zhí)行速度快。（2）存儲過程執(zhí)行一次后，代碼就駐留在高速緩存中，以后再操作時，只需從高速緩存中調(diào)用已編譯代碼執(zhí)行即可，從而提高了系統(tǒng)性能。（3）確保數(shù)據(jù)庫的安全?？梢圆皇跈?quán)用戶直接訪問應(yīng)用程序中的一些表，而是授權(quán)用戶執(zhí)行訪問這些表的存儲過程。非表的授權(quán)用戶除非通過存儲過程，否則就不能訪問這些表。（4）自動完成需要預(yù)先執(zhí)行的任務(wù)。存儲過程可以在系統(tǒng)啟動時自動執(zhí)行，而不必在系統(tǒng)啟動后再進(jìn)行手工操作，從而大大方便了用戶的使用，可以自動完成一些需要預(yù)先執(zhí)行的任務(wù)。01存儲過程的創(chuàng)建

1．以命令方式創(chuàng)建存儲過程2．以界面方式創(chuàng)建存儲過程存儲過程的創(chuàng)建

1．以命令方式創(chuàng)建存儲過程創(chuàng)建存儲過程使用CREATEPROCEDURE語句，語法格式為：CREATE[ORREPLACE]PROCEDURE過程名 /*定義過程名*/ [(參數(shù)名參數(shù)類型數(shù)據(jù)類型[DEFAULT默認(rèn)值][,…n])] /*定義參數(shù)類型及屬性*/{IS|AS} [變量聲明] /*變量聲明部分*/ BEGIN

過程體 /*PL/SQL過程體*/ END[過程名][;]相關(guān)參數(shù)說明如下。（1）過程名：存儲過程名稱要符合標(biāo)識符規(guī)則，并且在所屬方案中必須是唯一的。（2）參數(shù)名：存儲過程的參數(shù)名也要符合標(biāo)識符規(guī)則，創(chuàng)建過程時，可以聲明一個或多個參數(shù)，執(zhí)行過程時應(yīng)提供相對應(yīng)的參數(shù)。（3）DEFAULT：指定過程中IN參數(shù)的默認(rèn)值，且默認(rèn)值必須是常量。（4）過程體：表示包含PL/SQL語句塊。在存儲過程的定義體中，不能使用下列對象創(chuàng)建語句：CREATEVIEWCREATEDEFAULTCREATERULECREATEPROCEDURECREATETRIGGER存儲過程的創(chuàng)建

【例7.1】

創(chuàng)建一個簡單的存儲過程，輸出helloworld。CREATEPROCEDUREprocASBEGIN DBMS_OUTPUT.PUT_LINE('helloworld');END;【例7.2】

創(chuàng)建存儲過程，計算指定學(xué)生的總學(xué)分。CREATEORREPLACEPROCEDUREtotalcredit (xhINvarchar2)AS xfnumber;BEGIN SELECT總學(xué)分

INTOxf FROMxsb WHERE學(xué)號=xhANDrownum=1; DBMS_OUTPUT.PUT_LINE(xf);END;存儲過程的創(chuàng)建

【例7.3】

計算某專業(yè)總學(xué)分大于50分的人數(shù)，該存儲過程使用了一個輸入（IN）參數(shù)和一個輸出（OUT）參數(shù)。CREATEORREPLACEPROCEDUREcount_grade (zyINchar,person_numOUTnumber)ASBEGIN SELECTCOUNT(學(xué)號) INTOperson_num FROMxsb WHERE專業(yè)=zyAND總學(xué)分>50;END;存儲過程的創(chuàng)建

2．以界面方式創(chuàng)建存儲過程如果要通過界面方式定義上面的存儲過程count_grade，其步驟如下。（1）啟動SQLDeveloper，選擇myorcl連接的“過程”節(jié)點，右擊選擇“新建過程”選項進(jìn)入“創(chuàng)建PL/SQL過程”對話框，如圖。存儲過程的創(chuàng)建

（2）在“名稱”文本框中輸入存儲過程的名稱，單擊按鈕添加一個參數(shù)，在“參數(shù)”選項頁的“Name”欄中輸入各參數(shù)名稱，在“Type”欄中選擇參數(shù)的類型，在“Mode”欄中選擇參數(shù)的模式，在“DefaultValue”欄中輸入?yún)?shù)默認(rèn)值（如果有的話）。（3）單擊“確定”按鈕，在出現(xiàn)的“COUNT_GRADE”過程的編輯框中編寫過程語句塊，如圖7.2所示，單擊“編譯以進(jìn)行調(diào)試”按鈕完成過程的創(chuàng)建。02存儲過程的調(diào)用存儲過程的調(diào)用調(diào)用存儲過程一般使用EXEC語句，語法格式為：[{EXEC|EXECUTE}]過程名 [([參數(shù)名=]實參|@實參變量[,…n])][;]說明：EXEC是EXECUTE的縮寫，參數(shù)名為CREATEPROCUDURE定義的參數(shù)名稱?！纠?.4】

調(diào)用【例7.1】中的存儲過程proc。EXECproc;或BEGIN proc;END;輸出結(jié)果均為：“helloworld”。存儲過程的調(diào)用【例7.5】

從XSCJ數(shù)據(jù)庫的XSB表中查詢某人的總學(xué)分，并根據(jù)總學(xué)分寫評語。CREATEORREPLACEPROCEDUREupdate_info(xhinchar)AS xfnumber;BEGIN SELECT總學(xué)分INTOxf FROMxsb WHERE學(xué)號=xhANDROWNUM=1; IFxf>50THEN UPDATExsbSET備注='三好學(xué)生'WHERE學(xué)號=xh; ENDIF; IFxf<42THEN UPDATExsbSET備注='學(xué)分未修滿'WHERE學(xué)號=xh; ENDIF;END;執(zhí)行存儲過程update_info：EXECupdate_info(xh=>'151242');執(zhí)行結(jié)果如圖。存儲過程的調(diào)用【例7.6】

統(tǒng)計XSB表中男女學(xué)生的人數(shù)。CREATEORREPLACEPROCEDUREcount_number (sexINchar,numOUTnumber)ASBEGIN IFsex='男'THEN SELECTCOUNT(性別)INTOnum FROMxsb WHERE性別='男'; ELSE SELECTCOUNT(性別)INTOnum FROMxsb WHERE性別='女'; ENDIF;END;在調(diào)用過程count_number時，需要先定義OUT類型參數(shù)，其代碼如下：DECLARE girl_numnumber;BEGIN count_number('女',girl_num); DBMS_OUTPUT.PUT_LINE(girl_num);END;輸出結(jié)果為：8。03存儲過程的修改存儲過程的修改修改存儲過程和修改視圖一樣，雖然也有ALTERPROCEDURE語句，但它是用于重新編譯或驗證現(xiàn)有過程的。如果要修改過程定義，仍然使用CREATEORREPLACEPROCEDURE命令，語法格式一樣。其實，修改已有過程的本質(zhì)就是使用CREATEORREPLEACEPROCEDURE命令重新創(chuàng)建一個新的過程，只要保持名字與原來的過程相同即可。04存儲過程的刪除存儲過程的刪除當(dāng)某個過程不再需要時，應(yīng)將其刪除，以釋放它占用的內(nèi)存資源。刪除過程的語法格式為：DROPPROCEDURE[用戶方案名.]過程名;【例7.7】

刪除XSCJ數(shù)據(jù)庫中的count_number存儲過程。

DROPPROCEDUREcount_number;也可以使用界面方式刪除存儲過程，具體操作如圖。第7章存儲過程和觸發(fā)器觸

發(fā)

器01以命令方式創(chuàng)建觸發(fā)器1．創(chuàng)建DML觸發(fā)器2．創(chuàng)建替代觸發(fā)器3．創(chuàng)建系統(tǒng)觸發(fā)器以命令方式創(chuàng)建觸發(fā)器1．創(chuàng)建DML觸發(fā)器語法格式為：CREATE[ORREPLACE]TRIGGER[用戶方案名.]觸發(fā)器名 {BEFORE∣AFTER∣INSTEADOF} /*定義觸發(fā)動作*/ {DELETE|INSERT|UPDATE[OF列名[,…n]]} /*定義觸發(fā)器種類*/ [OR{DELETE|INSERT|UPDATE[OF列名[,…n]]}] ON{表名∣視圖名} /*在指定表或視圖中建立觸發(fā)器*/ [FOREACHROW[WHEN(條件表達(dá)式)]] PL/SQL語句塊相關(guān)參數(shù)說明如下。（1）觸發(fā)器名：觸發(fā)器與過程名和包的名字不一樣，它有單獨的名字空間，因此觸發(fā)器名可以和表名或過程名同名，但在同一個方案中的觸發(fā)器名不能相同。（2）BEFORE：觸發(fā)器在指定操作執(zhí)行前觸發(fā)，如BEFOREINSERT表示在向表中插入數(shù)據(jù)前激活觸發(fā)器。（3）AFTER：觸發(fā)器在指定操作都成功執(zhí)行后觸發(fā)，如AFTERINSERT表示向表中插入數(shù)據(jù)時激活觸發(fā)器。不能在視圖上定義AFTER觸發(fā)器。（4）INSTEADOF：指定創(chuàng)建替代觸發(fā)器，觸發(fā)器指定的事件不執(zhí)行，而執(zhí)行觸發(fā)器本身的操作。（5）DELETE|INSERT|UPDATE：指定一個或多個觸發(fā)事件，多個觸發(fā)事件之間用OR連接。（6）OF：指定在某列上應(yīng)用UPDATE觸發(fā)器，如果為多個列，則需要使用逗號分隔。（7）FOREACHROW：在觸發(fā)器定義中，如果未使用FOREACHROW子句則表示觸發(fā)器為語句級觸發(fā)器，觸發(fā)器在激活后只執(zhí)行一次，而不管這個操作將影響多少行。以命令方式創(chuàng)建觸發(fā)器有關(guān)DML觸發(fā)器，還有以下幾點說明。（1）創(chuàng)建觸發(fā)器的限制。創(chuàng)建觸發(fā)器有以下限制。①代碼大小。觸發(fā)器代碼大小必須小于32KB。②觸發(fā)器中有效語句可以包括DML語句，但不能包括DDL語句。③LONG、LONGRAW和LOB的限制如下。

不能插入數(shù)據(jù)到LONG或LONGRAW中。

來自LONG或LONGRAW的數(shù)據(jù)可以轉(zhuǎn)換成字符型（如char、varchar2），但是不能超過32KB。

使用LONG或LONGRAW不能聲明變量。

在LONG或LONGRAW列中不能使用:NEW和:OLD。

在LOB中的:NEW變量不能被修改。④

引用包變量的限制。（2）觸發(fā)器觸發(fā)次序。Oracle系統(tǒng)對事件的觸發(fā)是按照一定次序執(zhí)行的。①

執(zhí)行BEFORE語句級觸發(fā)器。②

對于受語句影響的每一行，執(zhí)行順序為：BEFORE行級觸發(fā)器→DML語句→AFTER行級觸發(fā)器。③

執(zhí)行AFTER語句級觸發(fā)器。以命令方式創(chuàng)建觸發(fā)器【例7.8】

創(chuàng)建一個表table1，其中只有一列a。在表上創(chuàng)建一個觸發(fā)器，每次插入操作時，將變量str的值設(shè)為“TRIGGERISWORKING”并顯示。創(chuàng)建表table1：CREATETABLEtable1(anumber);創(chuàng)建INSERT觸發(fā)器table1_insert：CREATEORREPLACETRIGGERtable1_insert AFTERINSERTONtable1DECLARE strchar(100):='TRIGGERISWORKING';BEGIN DBMS_OUTPUT.PUT_LINE(str);END;向table1中插入一行數(shù)據(jù)：INSERTINTOtable1VALUES(10);輸出結(jié)果如圖。以命令方式創(chuàng)建觸發(fā)器【例7.9】

在XSCJ數(shù)據(jù)庫中增加一個日志表XSB_HIS，表結(jié)構(gòu)和XSB表相同，用來存放從XSB表中刪除的記錄。創(chuàng)建一個觸發(fā)器，當(dāng)XSB表被刪除一行時，可把刪除的記錄寫到XSB_HIS表中。CREATEORREPLACETRIGGERdel_xs BEFOREDELETEONxsbforEACHROWBEGIN INSERTINTOxsb_his(學(xué)號,姓名,性別,出生時間,專業(yè),總學(xué)分,備注) VALUES(:OLD.學(xué)號,:OLD.姓名,:OLD.性別,:OLD.出生時間,:OLD.專業(yè),:OLD.總學(xué)分,:OLD.備注);END;其中，OLD修飾訪問操作完成前列的值。以命令方式創(chuàng)建觸發(fā)器【例7.10】

利用觸發(fā)器在數(shù)據(jù)庫XSCJ的XSB表中執(zhí)行插入、更新和刪除后，并給出相應(yīng)提示。CREATETRIGGERcue_xs AFTERINSERTORUPDATEORDELETEONxsbFOREACHROWDECLARE Inforchar(10);BEGIN IFINSERTINGTHEN /*INSERT語句激活了觸發(fā)器*/ Infor:='插入'; ELSIFUPDATINGTHEN /*UPDATE語句激活了觸發(fā)器*/ Infor:='更新'; ELSIFDELETINGTHEN /*DELETE語句激活了觸發(fā)器*/ Infor:='刪除'; ENDIF; DBMS_OUTPUT.PUT_LINE(Infor);END;說明：程序中使用條件謂詞IF通過謂詞INSERTING、UPDATING和DELETING分別判斷是否是INSERT、UPDATE和DELETE激活了觸發(fā)器。以命令方式創(chuàng)建觸發(fā)器2．創(chuàng)建替代觸發(fā)器例如，若在一個多表視圖上定義了INSTEADOFINSERT觸發(fā)器，視圖各列的值可能允許為空，也可能不允許。若視圖某列的值不允許為空，則INSERT語句必須為該列提供相應(yīng)的值?！纠?.11】在XSCJ數(shù)據(jù)庫中創(chuàng)建視圖stu_view，包含學(xué)生學(xué)號、專業(yè)、課程號、成績。該視圖依賴的XSB表和CJB表都是不可更新視圖。在視圖上可以創(chuàng)建INSTEADOF觸發(fā)器，當(dāng)向視圖中插入數(shù)據(jù)時分別向XSB表和CJB表插入數(shù)據(jù)，從而實現(xiàn)向視圖插入數(shù)據(jù)的功能。創(chuàng)建視圖：CREATEVIEWstu_viewASSELECTxsb.學(xué)號,專業(yè),課程號,成績 FROMxsb,cjb WHERExsb.學(xué)號=cjb.學(xué)號以命令方式創(chuàng)建觸發(fā)器創(chuàng)建INSTEADOF觸發(fā)器：CREATETRIGGERInsteadTrig INSTEADOFINSERTONstu_viewFOREACHROWDECLARE xmchar(8); xbchar(2); cssjdate;BEGIN xm:='徐鶴'; xb:='男'; cssj:='1997-07-28'; INSERTINTOxsb(學(xué)號,姓名,性別,出生時間,專業(yè)) VALUES(:NEW.學(xué)號,xm,xb,cssj,:NEW.專業(yè)); INSERTINTOcjbVALUES(:NEW.學(xué)號,:NEW.課程號,:NEW.成績);END;向視圖插入一行數(shù)據(jù)：INSERTINTOstu_viewVALUES('151116','計算機(jī)','101',85);以命令方式創(chuàng)建觸發(fā)器查看數(shù)據(jù)是否插入：SELECT*FROMstu_viewWHERE學(xué)號='151116';執(zhí)行結(jié)果如圖。查看與視圖關(guān)聯(lián)的XSB表情況：SELECT*FROMxsbWHERE學(xué)號='151116';執(zhí)行結(jié)果如圖。以命令方式創(chuàng)建觸發(fā)器3．創(chuàng)建系統(tǒng)觸發(fā)器系統(tǒng)觸發(fā)器可以在DDL或數(shù)據(jù)庫系統(tǒng)事件上被觸發(fā)。DDL指的是數(shù)據(jù)定義語句，如CREATE、ALTER和DROP等。數(shù)據(jù)庫系統(tǒng)事件包括數(shù)據(jù)庫服務(wù)器的啟動（STARTUP）、關(guān)閉（SHUTDOWN）、出錯（SERVERERROR）等。創(chuàng)建系統(tǒng)觸發(fā)器的語法格式為：CREATEORREPLACETRIGGER[用戶方案名.]觸發(fā)器名 {BEFORE︱AFTER} {DDL事件︱數(shù)據(jù)庫事件} ON{DATABASE︱[用戶方案名.]SCHEMA}

觸發(fā)器的PL/SQL語句塊相關(guān)參數(shù)說明如下。（1）DDL事件：可以是一個或多個DDL事件，事件間用OR分開。（2）數(shù)據(jù)庫事件：可以是一個或多個數(shù)據(jù)庫事件，事件間用OR分開，其中包括STARTUP、SHUTDOWN、SERVERERROR等事件。（3）DATABASE：表示是數(shù)據(jù)庫級觸發(fā)器，對應(yīng)數(shù)據(jù)庫事件，而SCHEMA表示是用戶級觸發(fā)器，對應(yīng)DDL事件。以命令方式創(chuàng)建觸發(fā)器【例7.12】

創(chuàng)建一個用戶事件觸發(fā)器，記錄用戶SYSTEM所刪除的所有對象。以用戶SYSTEM身份連接數(shù)據(jù)庫，創(chuàng)建一個存儲用戶信息的表：CREATETABLEdropped_objects( object_namevarchar2(30), object_typevarchar(20), dropped_datedate);創(chuàng)建BEFOREDROP觸發(fā)器，在用戶刪除對象之前記錄到信息表dropped_objects中：CREATEORREPLACETRIGGERdropped_obj_trigger BEFOREDROPONSYSTEM.SCHEMABEGIN INSERTINTOdropped_objects VALUES(ora_dict_obj_name,ora_dict_obj_type,SYSDATE);END;以命令方式創(chuàng)建觸發(fā)器現(xiàn)在刪除SYSTEM模式下的一些對象，并查詢表dropped_objects：DROPTABLEtable1;DROPTABLEtable2;SELECT*FROMdropped_objects;執(zhí)行結(jié)果如圖。02以界面方式創(chuàng)建觸發(fā)器

以界面方式創(chuàng)建觸發(fā)器

觸發(fā)器也可以利用SQLDeveloper的界面方式創(chuàng)建。（1）選擇myorc