1、第6章 存儲(chǔ)過程和觸發(fā)器,6.1 存儲(chǔ)過程 在Oracle中，可以在數(shù)據(jù)庫中定義子程序，這種程序塊稱為存儲(chǔ)過程 (procedure)。它存放在數(shù)據(jù)字典中，可以在不同用戶和應(yīng)用程序之間共享，并可 實(shí)現(xiàn)程序的優(yōu)化和重用。使用存儲(chǔ)過程的優(yōu)點(diǎn)是： (1)過程在服務(wù)器端運(yùn)行，執(zhí)行速度快。 (2)過程執(zhí)行一次后代碼就駐留在高速緩沖存儲(chǔ)器，在以后的操作中，只需從高 速緩沖存儲(chǔ)器中調(diào)用已編譯代碼執(zhí)行，提高了系統(tǒng)性能。 (3) 確保數(shù)據(jù)庫的安全?？梢圆皇跈?quán)用戶直接訪問應(yīng)用程序中的一些表，而是授 權(quán)用戶執(zhí)行訪問這些表的過程。非表的授權(quán)用戶除非通過過程，否則就不能訪問 這些表。 (4) 自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)

2、。過程可以在系統(tǒng)啟動(dòng)時(shí)自動(dòng)執(zhí)行，而不必在 系統(tǒng)啟動(dòng)后再進(jìn)行手工操作，大大方便了用戶的使用，可以自動(dòng)完成一些需要預(yù) 先執(zhí)行的任務(wù)。,6.1.1存儲(chǔ)過程的創(chuàng)建和執(zhí)行,用戶存儲(chǔ)過程只能定義在當(dāng)前數(shù)據(jù)庫中，可以使用SQL命令語句或OEM創(chuàng)建存 儲(chǔ)過程。缺省情況下，用戶創(chuàng)建的存儲(chǔ)過程歸登錄數(shù)據(jù)庫的用戶所擁有，DBA可 以把許可授權(quán)給其他用戶。在用戶的定義中不能使用下列對象創(chuàng)建語句： CREATE VIEW CREATE DEFAULT CREATE RULE CREATE PROCEDURE CREATE TRIGGER,6.1.1存儲(chǔ)過程的創(chuàng)建和執(zhí)行,1.SQL命令創(chuàng)建存儲(chǔ)過程 語法格式： CREAT

3、E OR REPLACE PROCEDURE cedure_name /*定義過程名*/ (parameter parameter_mode date_type , n) /*定義參數(shù)類型及屬性*/ IS | AS BEGIN sql_statement /*PL/SQL過程體，要執(zhí)行的操作*/ END procedure_name 其中： procedure_name：是過程名，必須符合標(biāo)識符規(guī)則。關(guān)鍵字REPLACE表示在創(chuàng)建過程 時(shí)，如果已存在同名的過程，則重新創(chuàng)建。如果使用CREATE關(guān)鍵字，則需將原有的過程 刪除后才能創(chuàng)建。 schema.：是指定過程屬于的用戶方

4、案。 parameter：是過程的參數(shù)。參數(shù)名必須符合標(biāo)識符規(guī)則，創(chuàng)建過程時(shí)，可以聲明一個(gè)或多 個(gè)參數(shù)，執(zhí)行過程時(shí)應(yīng)提供相對應(yīng)的參數(shù)。Parameter_mode是參數(shù)的類型，過程參數(shù)和 函數(shù)參數(shù)一樣，也有3種類型，分別為IN、OUT和IN OUT。 IN：表示參數(shù)是輸入給過程的； OUT：表示參數(shù)在過程中將被賦值，可以傳給過程體的外部； IN OUT：表示該類型的參數(shù)既可以向過程體傳值，也可以在過程體中賦值。 sql_statement：代表過程體包含的PL/SQL語句。,6.1.1存儲(chǔ)過程的創(chuàng)建和執(zhí)行,2.調(diào)用存儲(chǔ)過程 直接輸入存儲(chǔ)過程的名字就可以執(zhí)行一個(gè)已定義的存儲(chǔ)過程。 語法格式： E

5、XECUTE procedure_name(parameter,n) 其中，procedure_name為要調(diào)用的存儲(chǔ)過程的名字，parameter為參數(shù)值。 【例6.1】計(jì)算指定系總學(xué)分大于40的人數(shù)。 CREATE OR REPLACE PROCEDURE count_grade ( zym in char,person_num out number ) AS BEGIN SELECT COUNT(ZXF) INTO person_num FROM XS WHERE ZYM=zym; END count_grade;,6.1.1存儲(chǔ)過程的創(chuàng)建和執(zhí)行,【例6.2】從XSCJ數(shù)據(jù)庫的XS表中查

6、詢某人的總學(xué)分，根據(jù)總學(xué)分寫評語。 CREATE OR REPLACE PROCEDURE update_info ( xm in char ) AS Xf number; BEGIN SELECT ZXF INTO XF FROM XS WHERE XM=xm; IF XF60 THEN UPDATE XS SET BZ=三好學(xué)生 WHERE XM=xm; END IF; IF XF35 THEN UPDATE XS SET BZ=學(xué)分未修滿 WHERE XM=xm; END IF; END update_info; update_info存儲(chǔ)過程執(zhí)行： EXEC update_info(李

7、明);,6.1.1存儲(chǔ)過程的創(chuàng)建和執(zhí)行,【例6.3】計(jì)算指定學(xué)生的總學(xué)分，存儲(chǔ)過程使用了一個(gè)輸入?yún)?shù)和一個(gè)輸出參數(shù)。 CREATE OR REPLACE PROCEDURE totalcredit ( name IN varchar2, total OUT number ) AS BEGIN SELECT SUM(XF) INTO total FROM XS, XS_KC WHERE XM=name AND XS.XH=XS_KC.XH; END;,6.1.1存儲(chǔ)過程的創(chuàng)建和執(zhí)行,【例6.4】統(tǒng)計(jì)表XS中男女同學(xué)的人數(shù)，存儲(chǔ)過程使用了一個(gè)輸入?yún)?shù)和一個(gè)輸出參數(shù)。 CREATE OR REPLA

8、CE PROCEDURE count_num ( sex IN char, num OUT number ) AS BEGIN IF sex=男 THEN SELECT COUNT(XB) INTO num FROM XS WHERE XB=男; ELSE SELECT COUNT(XB) INTO num FROM XS WHERE XB=女; END IF; END count_num; 在調(diào)用過程count_num時(shí)，需要先定義OUT類型參數(shù)，調(diào)用如下： DECLARE man_num NUMBER; BEGIN count_num(男,man_num); END;,6.1.1存儲(chǔ)過程的

9、創(chuàng)建和執(zhí)行,3.利用OEM創(chuàng)建過程 (1)在OEM界面中，如圖6.1所示，選擇“過程”，單擊鼠標(biāo)左鍵，進(jìn)入“過程搜索”界面，如 圖6.2所示。,圖6.1 Oracle企業(yè)管理器,圖6.2過程搜索界面,6.1.1存儲(chǔ)過程的創(chuàng)建和執(zhí)行,3.利用OEM創(chuàng)建過程 (2)單擊“創(chuàng)建”按鈕，進(jìn)入過程創(chuàng)建界面，如圖6.3所示。在“名稱”文本框中指定過程名稱 count_grade；在“方案”中選擇建立過程的用戶方案ADMIN。然后在“源”代碼區(qū)域，編輯 PL/SQL過程語句塊。 (3)代碼輸入編輯完成后，單擊“確定”按鈕完成過程創(chuàng)建。,圖6.3創(chuàng)建過程界面,6.1.2存儲(chǔ)過程的編輯修改,【例6.5】對存儲(chǔ)過

10、程update_info進(jìn)行修改。 CREATE OR REPLACE PROCEDURE update_info ( xm in char ) AS Xf number; BEGIN SELECT ZXF INTO XF FROM XS WHERE XM=xm; IF XF60 THEN UPDATE XS SET BZ=三好學(xué)生 WHERE XM=xm; END IF; IF XF35 THEN UPDATE XS SET BZ=學(xué)分未修滿 WHERE XM=xm; END IF; END update_info;,6.1.2存儲(chǔ)過程的編輯修改,【例6.6】創(chuàng)建名為select_stude

11、nts的存儲(chǔ)過程，默認(rèn)情況下，該過程可查詢所有學(xué)生的信 息。當(dāng)該過程需要改為能檢索計(jì)算機(jī)專業(yè)的學(xué)生信息時(shí)，用CREATE OR REPLACE PROCEDURE重新定義。 (1) 定義過程 CREATE OR REPLACE PROCEDURE select_students ( CUR OUT select.cur_07 ) AS BEGIN OPEN CUR FOR SELECT xh,xm,zym,xb,cssj,zxf,bz FROM XS ORDER BY XH; END; 注意：使用查詢包頭時(shí)在SELECT語句中不能使用*標(biāo)識符。 (2) 修改過程 CREATE OR REPLA

12、CE PROCEDURE select_students ( CUR OUT select.cur_07 ) AS BEGIN OPEN CUR FOR SELECT xh,xm,zym,xb,cssj,zxf,bz FROM XS WHERE ZYM=計(jì)算機(jī) ORDER BY XH; END;,6.1.3存儲(chǔ)過程的刪除,當(dāng)某個(gè)過程不再需要時(shí)，應(yīng)將其從內(nèi)存中刪除，以釋放它占用的內(nèi)存資源。 語法格式： DROP PROCEDURE schema. procedure_name; 其中，schema是包含過程的用戶；procedure_name是將要?jiǎng)h除的存儲(chǔ)過程名稱。 【例6.7】刪除XSCJ數(shù)

13、據(jù)庫中的count_num存儲(chǔ)過程。 DROP PROCEDURE count_num; 也可在OEM中選擇要?jiǎng)h除的存儲(chǔ)過程，在如圖6.2所示的界面，搜索并選擇要?jiǎng)h 除的存儲(chǔ)過程，單擊“刪除”，出現(xiàn)確認(rèn)刪除界面，單擊“是”即可刪除該存儲(chǔ)過程。,6.2 觸發(fā)器,觸發(fā)器(trigger)是一些過程，與表關(guān)系密切，用于保護(hù)表中的數(shù)據(jù)，當(dāng)一個(gè)基表 被修改(INSERT、UPDATE或DELETE)時(shí)，觸發(fā)器自動(dòng)執(zhí)行，例如通過觸發(fā)器 可實(shí)現(xiàn)多個(gè)表間數(shù)據(jù)的一致性和完整性。觸發(fā)器和應(yīng)用程序無關(guān)。例如，對于 XSCJ數(shù)據(jù)庫有XS表、XS_KC表和KC表，當(dāng)插入某一學(xué)號的學(xué)生某一課程成績 時(shí)，該學(xué)號應(yīng)是XS表中

14、已存在的，課程號應(yīng)是KC表中已存在的，此時(shí)，可通過 定義INSERT觸發(fā)器實(shí)現(xiàn)上述功能。 觸發(fā)器的類型有三種： (1)DML觸發(fā)器。Oracle可以在DML(數(shù)據(jù)操縱語句)語句進(jìn)行觸發(fā)，可以在DML 操作前或操作后進(jìn)行觸發(fā)，并且可以在每個(gè)行或該語句操作上進(jìn)行觸發(fā)。 (2)替代觸發(fā)器。由于在Oracle中不能直接對有兩個(gè)以上的表建立的視圖進(jìn)行操 作，所以給出了替代觸發(fā)器。它是Oracle專門為進(jìn)行視圖操作的一種處理方法。 (3)系統(tǒng)觸發(fā)器。在Oracle8i時(shí)，提供了第三種類型的觸發(fā)器叫系統(tǒng)觸發(fā)器。它 可以在Oracle數(shù)據(jù)庫系統(tǒng)的時(shí)間中進(jìn)行觸發(fā)，如Oracle數(shù)據(jù)庫的關(guān)閉或打開等。,6.2 觸

15、發(fā)器,一般情況下，對表數(shù)據(jù)的操作有插入、修改、刪除，因而維護(hù)數(shù)據(jù)的觸發(fā)器也可 分為INSERT、UPDATE和DELETE。每張基表最多可建立12個(gè)觸發(fā)器，它們是： (1)BEFORE INSERT； (2)BEFORE INSERT FOR EACH ROW； (3)AFTER INSERT； (4)AFTER INSERT FOR EACH ROW； (5)BEFORE UPDATE； (6)BEFORE UPDATE FOR EACH ROW； (7)AFTER UPDATE； (8)AFTER UPDATE FOR EACH ROW； (9)BEFORE DELETE； (10) BE

16、FORE DELETE FOR EACH ROW； (11) AFTER DELETE； (12) AFTER DELETE FOR EACH ROW。,6.2.1利用SQL語句創(chuàng)建觸發(fā)器,1.語法格式 CREATE OR REPLACE TRIGGER schema. trigger_name /*指定觸發(fā)器名稱*/ BEFOREAFTERINSTEAD OF DELETE OR INSERTE OR UPDATE OF column,n /*定義觸發(fā)器種類*/ ON schema. table_nameview_name /*指定操作對象*/ FOR EACH ROW WHEN(condi

17、tion) sql_statementn,6.2.1利用SQL語句創(chuàng)建觸發(fā)器,2.創(chuàng)建觸發(fā)器的限制 創(chuàng)建觸發(fā)器有以下限制： (1)代碼大小。觸發(fā)器代碼大小必須小于32K。 (2)觸發(fā)器中有效語句可以包括DML語句，但不能包括DDL語句。ROLLBACK、 COMMIT、SAVEPOINT也不能使用。但是，對于系統(tǒng)觸發(fā)器(system trigger)可以使用 CREATE、ALTER、DROP TABLE和ALTERCOMPILE語句。 (3)LONG、LONG RAW和LOB的限制： 不能插入數(shù)據(jù)到LONG或LONG RAW； 來自LONG或LONG RAW的數(shù)據(jù)可以轉(zhuǎn)換成字符型(如char

18、、varchar2)，但是不能超 過32K； 使用LONG或LONG RAW不能聲明變量； 在LONG或LONG RAW列中不能使用:NEW和:PARENT； 在LOB中的:NEW變量不能修改。 (4)引用包變量的限制。如果UPDATE或DELETE語句檢測到當(dāng)前的UPDATE沖突，則 Oracle執(zhí)行ROLLBACK到SAVEPOINT上并重新啟動(dòng)更新，這樣可能需要多次才能成功。 3.觸發(fā)器觸發(fā)次序 Oracle對事件的觸發(fā)有16種，它們按照一定次序執(zhí)行： (1) 執(zhí)行BEFORE語句的觸發(fā)器； (2) 對于受語句影響的每一行：執(zhí)行BEFORE語句行級觸發(fā)器執(zhí)行DML語句執(zhí)行 AFTER行級

19、觸發(fā)器。 (3)執(zhí)行AFTER語句級觸發(fā)器。,6.2.1利用SQL語句創(chuàng)建觸發(fā)器,4.創(chuàng)建DML觸發(fā)器 觸發(fā)器與過程名和包的名字不一樣，它有單獨(dú)的名字空間，因而觸發(fā)器名可以和表名或過 程名同名，但在同一個(gè)schema(方案)中的觸發(fā)器名不能相同。DML觸發(fā)器也叫表級觸發(fā) 器，因?yàn)閷δ硞€(gè)表進(jìn)行DML操作時(shí)會(huì)觸發(fā)該觸發(fā)器運(yùn)行而得名。 【例6.8】假設(shè)XSCJ數(shù)據(jù)庫中增加一新表XS_HIS，表結(jié)構(gòu)和表XS相同，用來存放從XS表 中刪除的記錄。創(chuàng)建一個(gè)觸發(fā)器，當(dāng)XS表被刪除一行，把刪除的記錄寫到日志表XS_HIS 中。 CREATE OR REPLACE TRIGGER del_xs BEFORE D

20、ELETE ON XS FOR EACH ROW BEGIN INSERT INTO XS_HIS (XH,XM,ZYM,XB,CSSJ,ZXF,BZ) VALUES(:OLD.XH,:OLD.XM, :OLD.ZYM, :OLD.XB, :OLD.CSSJ, :OLD.ZXF, :OLD.BZ); END del_xs; OLD修飾訪問操作完成前列的值，NEW修飾訪問操作完成后列的值。,6.2.1利用SQL語句創(chuàng)建觸發(fā)器,【例6.9】利用觸發(fā)器在數(shù)據(jù)庫XSCJ的XS表執(zhí)行插入、更新和刪除3種操作后給 出相應(yīng)提示。 CREATE TRIGGER cue_xs AFTER INSERT OR U

21、PDATE OR DELETE ON XS FOR EACH ROW DECLARE Infor char(10); BEGIN IF INSERTING THEN Infor:=插入; ELSIF UPDATING THEN Infor:=更新; ELSE Infor:=刪除; END IF; INSERT INTO SQL_INFO VALUES(infor); END cue_xs;,6.2.1利用SQL語句創(chuàng)建觸發(fā)器,5.創(chuàng)建替代(Instead_of)觸發(fā)器 Instead_of用于對視圖的DML觸發(fā)。由于視圖有可能由多個(gè)表進(jìn)行關(guān)聯(lián)(Join)而 成，因而并非所有的關(guān)聯(lián)都是可更新的。

22、但是可以按如下例子來創(chuàng)建觸發(fā)器。 【例6.10】在XSCJ數(shù)據(jù)庫中創(chuàng)建視圖和觸發(fā)器，以說明替代觸發(fā)器。 CREATE OR REPLACE VIEW cs_kc_avg AS SELECT XH,AVG(CJ) AS AVG_CJ FROM XS_KC GROUP BY XH; 創(chuàng)建替代觸發(fā)器： CREATE TRIGGER cs_kc_avg_del INSTEAD OF DELETE ON cs_kc_avg FOR EACH ROW BEGIN DELETE FROM XS_KC WHERE XH=:OLD.XH; END cs_kc_avg_del;,6.2.1利用SQL語句創(chuàng)建觸發(fā)器

23、,6.創(chuàng)建系統(tǒng)觸發(fā)器 Oracle8i開始提供的系統(tǒng)觸發(fā)器可以在DDL或數(shù)據(jù)庫系統(tǒng)上被觸發(fā)。DDL指的是 數(shù)據(jù)定義語句，如CREATE、ALTER和DROP等。而數(shù)據(jù)庫系統(tǒng)事件包括數(shù)據(jù)庫 服務(wù)器的啟動(dòng)或關(guān)閉，用戶登錄與退出等。 語法格式： CREATE OR REPLACE TRIGGER scache. trigger_name BEFOREAFTER ddl_event_listdatabse_event_list ON DATABASEschema. SCHEMA when_clause tigger_body 其中： ddl_event_list：表示一個(gè)或多個(gè)DDL事件，事件間用OR

24、分開。 database_event_list：表示一個(gè)或多個(gè)數(shù)據(jù)庫事件，事件間用OR分開。 DATABASE：表示是數(shù)據(jù)庫級觸發(fā)器，而scache表示是用戶級觸發(fā)器。Schema 表示用戶方案。 Trigger_body：觸發(fā)器的PL/SQL語句。,6.2.1利用SQL語句創(chuàng)建觸發(fā)器,【例6.11】創(chuàng)建當(dāng)一個(gè)用戶userA登錄時(shí)自動(dòng)記錄一些信息的觸發(fā)器。 CREATE TRIGGER loguserAconnects AFTER LOGON ON SCHEMA BEGIN INSERT INTO LOGIN VALUES(userA,loguserAconnects fired); END loguserAconnects;,6.2.2利用OEM創(chuàng)建觸發(fā)器,(1)如圖6.1所示的界面中選擇觸發(fā)器，鼠標(biāo)單擊左鍵，打開“觸發(fā)器搜索”界面，如圖6.5 所示。 (2)單擊“創(chuàng)建”按鈕，進(jìn)入“觸發(fā)器創(chuàng)建”界面，如圖6.6所示。