第9章 Oracle9i數(shù)據(jù)庫的完整性和并發(fā)控制(1)

1、第第9章章 Oracle9i數(shù)據(jù)庫的完整性和并發(fā)控制數(shù)據(jù)庫的完整性和并發(fā)控制 9.1 完整性控制：數(shù)據(jù)庫的完整性是指保證數(shù)據(jù)庫中數(shù)據(jù)及語義完整性控制：數(shù)據(jù)庫的完整性是指保證數(shù)據(jù)庫中數(shù)據(jù)及語義的正確、相容和有效，防止任何對數(shù)據(jù)造成錯誤的操作。數(shù)據(jù)的正確、相容和有效，防止任何對數(shù)據(jù)造成錯誤的操作。數(shù)據(jù)庫的完整性一般包括實體完整性、參照完整性和用戶自定義完庫的完整性一般包括實體完整性、參照完整性和用戶自定義完整性三種，整性三種，Oracle9i允許定義和實施這三種類型的完整性規(guī)則，允許定義和實施這三種類型的完整性規(guī)則，這些規(guī)則可用完整性約束和觸發(fā)器來定義。這些規(guī)則可用完整性約束和觸發(fā)器來定義。9.1

2、.1 完整性約束：完整性約束是指數(shù)據(jù)庫中數(shù)據(jù)所具有的制約完整性約束：完整性約束是指數(shù)據(jù)庫中數(shù)據(jù)所具有的制約和依存規(guī)則，用以限定數(shù)據(jù)庫的狀態(tài)以及狀態(tài)的變化。實施完和依存規(guī)則，用以限定數(shù)據(jù)庫的狀態(tài)以及狀態(tài)的變化。實施完整性規(guī)則的好處是：整性規(guī)則的好處是： 完整性約束可在創(chuàng)建和修改表時定義，對完整性規(guī)則的實完整性約束可在創(chuàng)建和修改表時定義，對完整性規(guī)則的實施優(yōu)于應用程序中的復雜編碼和觸發(fā)器。施優(yōu)于應用程序中的復雜編碼和觸發(fā)器。 完整性約束存儲在數(shù)據(jù)字典中，任何對表的更新操作都必完整性約束存儲在數(shù)據(jù)字典中，任何對表的更新操作都必須遵守該表所定義的完整性約束。須遵守該表所定義的完整性約束。 由于可以臨時

3、禁用完整性約束，使得裝載大量數(shù)據(jù)時可以由于可以臨時禁用完整性約束，使得裝載大量數(shù)據(jù)時可以避免約束檢查的開銷。當數(shù)據(jù)裝載完成時，再啟用完整性約束，避免約束檢查的開銷。當數(shù)據(jù)裝載完成時，再啟用完整性約束，任何破壞完整性約束的數(shù)據(jù)將在例外表中列出。任何破壞完整性約束的數(shù)據(jù)將在例外表中列出。 9.1.2 觸發(fā)器觸發(fā)器 觸發(fā)器的概念觸發(fā)器的概念 觸發(fā)器類似于過程和函數(shù)，也是一個觸發(fā)器類似于過程和函數(shù)，也是一個PL/SQL命名塊，但它不命名塊，但它不需顯式調用，而是當相應事件發(fā)生時，激發(fā)在后臺自動運行。需顯式調用，而是當相應事件發(fā)生時，激發(fā)在后臺自動運行。使用觸發(fā)器的好處：使用觸發(fā)器的好處： n n 維護

4、不可能在表創(chuàng)建時通過聲明進行的復雜完整性限制。維護不可能在表創(chuàng)建時通過聲明進行的復雜完整性限制。 n n 通過記錄修改內容和修改者來審計表中的信息。通過記錄修改內容和修改者來審計表中的信息。 n n 當表被修改時，自動給其它需要在該表上執(zhí)行操作的程序發(fā)當表被修改時，自動給其它需要在該表上執(zhí)行操作的程序發(fā)出信號。出信號。 一個觸發(fā)器一般是由觸發(fā)事件、發(fā)生事件的對象、觸發(fā)條件和一個觸發(fā)器一般是由觸發(fā)事件、發(fā)生事件的對象、觸發(fā)條件和觸發(fā)器體四部分組成。觸發(fā)事件可以是觸發(fā)器體四部分組成。觸發(fā)事件可以是DML語句、語句、DDL語句語句或系統(tǒng)事件（如數(shù)據(jù)庫啟動和關閉等），根據(jù)觸發(fā)事件的不同，或系統(tǒng)事件（如

5、數(shù)據(jù)庫啟動和關閉等），根據(jù)觸發(fā)事件的不同，可將觸發(fā)器分為可將觸發(fā)器分為DML觸發(fā)器、觸發(fā)器、DDL觸發(fā)器、系統(tǒng)觸發(fā)器和替觸發(fā)器、系統(tǒng)觸發(fā)器和替代觸發(fā)器四種類型。代觸發(fā)器四種類型。 n n DML觸發(fā)器：定義在表上的觸發(fā)器，由觸發(fā)器：定義在表上的觸發(fā)器，由INSERT、DELETE和和UPDATE等等DML語句觸發(fā)。創(chuàng)建語句觸發(fā)。創(chuàng)建DML觸發(fā)器要明確規(guī)定執(zhí)觸發(fā)器要明確規(guī)定執(zhí)行行SQL語句時機的語句時機的BEFORE/AFTER選項，同時指定是行級觸選項，同時指定是行級觸發(fā)器（觸發(fā)語句每影響表的一行，觸發(fā)器就被觸發(fā)一次）還是發(fā)器（觸發(fā)語句每影響表的一行，觸發(fā)器就被觸發(fā)一次）還是語句級觸發(fā)器（觸發(fā)

6、器只在相應語句被執(zhí)行時觸發(fā)一次，并不語句級觸發(fā)器（觸發(fā)器只在相應語句被執(zhí)行時觸發(fā)一次，并不針對每一行）。針對每一行）。 n n DDL觸發(fā)器：定義在用戶模式上的觸發(fā)器，由觸發(fā)器：定義在用戶模式上的觸發(fā)器，由CREATE、ALTER或或DELETE等語句觸發(fā)。等語句觸發(fā)。 n n 替代觸發(fā)器：定義在視圖上的觸發(fā)器，由替代觸發(fā)器：定義在視圖上的觸發(fā)器，由INSERT、DELETE和和UPDATE等語句觸發(fā)。因為直接對有些視圖的等語句觸發(fā)。因為直接對有些視圖的INSERT、DELETE和和UPDATE操作是非法的，使用替代觸發(fā)器則可以將操作是非法的，使用替代觸發(fā)器則可以將這些操作合法替代為對相應基表

7、的操作。創(chuàng)建替代觸發(fā)器不能這些操作合法替代為對相應基表的操作。創(chuàng)建替代觸發(fā)器不能加加BEFORE/AFTER選項。選項。 n n 系統(tǒng)觸發(fā)器：定義在數(shù)據(jù)庫上的觸發(fā)器，由表系統(tǒng)觸發(fā)器：定義在數(shù)據(jù)庫上的觸發(fā)器，由表9.1所示的系所示的系統(tǒng)事件觸發(fā)。統(tǒng)事件觸發(fā)。 創(chuàng)建觸發(fā)器：創(chuàng)建觸發(fā)器： 語法如下：語法如下： CREATE OR REPLACE TRIGGER 觸發(fā)器名觸發(fā)器名 觸發(fā)事件觸發(fā)事件 ON DATABASE|用戶模式名用戶模式名|表名表名|視圖名視圖名| REFERENCING | | FOR EACH ROW WHEN 觸發(fā)條件觸發(fā)條件 觸發(fā)器體觸發(fā)器體; END觸發(fā)器名觸發(fā)器名; 上

8、面語法中的主要參數(shù)描述如下：上面語法中的主要參數(shù)描述如下： n n BEFORE|AFTER|INSTEAD OF：BEFORE表示在執(zhí)行觸表示在執(zhí)行觸發(fā)語句之前觸發(fā)，發(fā)語句之前觸發(fā)，AFTER表示在執(zhí)行觸發(fā)語句之后觸發(fā)，表示在執(zhí)行觸發(fā)語句之后觸發(fā)，INSTEAD OF表示創(chuàng)建替代觸發(fā)器。表示創(chuàng)建替代觸發(fā)器。 n n REFERENCING：行級觸發(fā)器的觸發(fā)器體可以引用一些特：行級觸發(fā)器的觸發(fā)器體可以引用一些特定的條件謂詞定的條件謂詞INSERTING、DELETING和和UPDATING。OLD指定在條件謂詞執(zhí)行前引用字段舊值時要使用的名稱；指定在條件謂詞執(zhí)行前引用字段舊值時要使用的名稱；N

9、EW指定在條件謂詞執(zhí)行后引用字段新值時要使用的名稱；指定在條件謂詞執(zhí)行后引用字段新值時要使用的名稱；如果觸發(fā)器定義在參照表上，如果觸發(fā)器定義在參照表上，PARENT指定被參照表的當前行。指定被參照表的當前行。 n n FOR EACH ROW：指定為行觸發(fā)器。：指定為行觸發(fā)器。 n n WHEN：指定觸發(fā)條件。但該語句不能在語句級觸發(fā)器中：指定觸發(fā)條件。但該語句不能在語句級觸發(fā)器中使用。使用。 n n 觸發(fā)器體可以是觸發(fā)器體可以是PL/SQL塊，也是塊，也是CALL 過程名的形式。過程名的形式。 例例9.1 創(chuàng)建一個創(chuàng)建一個DML觸發(fā)器：用于每次對表觸發(fā)器：用于每次對表SYSTEM.STUDE

10、NT進行進行DML操作（插入、刪除和修改）前，首先在屏幕上顯操作（插入、刪除和修改）前，首先在屏幕上顯示該學生原來的年齡、現(xiàn)在的年齡以及新舊年齡的差值。示該學生原來的年齡、現(xiàn)在的年齡以及新舊年齡的差值。 CREATE OR REPLACE TRIGGER SYSTEM.PRINT_SAGE_TRIGGER BEFORE INSERT OR DELETE OR UPDATE ON SYSTEM.STUDENT FOR EACH ROW DECLARE AGEDIFF NUMBER(3,0); BEGIN AGEDIFF:=:NEW.SAGE-:OLD.SAGE; DBMS_OUTPUT.PUT_

11、LINE(原來年齡原來年齡:|:OLD.SAGE); DBMS_OUTPUT.PUT_LINE(現(xiàn)在年齡現(xiàn)在年齡:|:NEW.SAGE); DBMS_OUTPUT.PUT_LINE(年齡差值年齡差值:|AGEDIFF); END PRINT_SAGE_TRIGGER; 例例9.2 創(chuàng)建一個創(chuàng)建一個DDL觸發(fā)器：用于自動記錄用戶觸發(fā)器：用于自動記錄用戶SYSTEM模模式下創(chuàng)建建數(shù)據(jù)庫對象時的用戶名、對象名、對象類型及創(chuàng)建式下創(chuàng)建建數(shù)據(jù)庫對象時的用戶名、對象名、對象類型及創(chuàng)建時間。時間。 首先創(chuàng)建一個表用于保存創(chuàng)建數(shù)據(jù)庫對象時的各類信息：首先創(chuàng)建一個表用于保存創(chuàng)建數(shù)據(jù)庫對象時的各類信息： CREA

12、TE TABLE SYSTEM.OBJECT_CREATED ( OBJECT_OWNER VARCHAR2(10), OBJECT_NAME VARCHAR2(30), OBJECT_TYPE VARCHAR2(20), CREATE_TIME DATE); 然后創(chuàng)建一個然后創(chuàng)建一個DDL觸發(fā)器，觸發(fā)事件為觸發(fā)器，觸發(fā)事件為CREATE： CREATE OR REPLACE TRIGGER SYSTEM.CREATE_OBJECT_TRIGGER AFTER CREATE ON SYSTEM.SCHEMA BEGIN INSERT INTO SYSTEM.OBJECT_CREATED VAL

13、UES(SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_NAME, SYS.DICTIONARY_OBJ_TYPE, SYSDATE); END CREATE_OBJECT_TRIGGER; 例例9.3 創(chuàng)建一個系統(tǒng)觸發(fā)器：用于自動記錄用戶登錄數(shù)據(jù)庫創(chuàng)建一個系統(tǒng)觸發(fā)器：用于自動記錄用戶登錄數(shù)據(jù)庫時的用戶名、數(shù)據(jù)庫名和登錄時間。時的用戶名、數(shù)據(jù)庫名和登錄時間。 首先創(chuàng)建一個表用于保存用戶登錄數(shù)據(jù)庫時的各類信息：首先創(chuàng)建一個表用于保存用戶登錄數(shù)據(jù)庫時的各類信息： CREATE TABLE SYSTEM.LOG_DATABASE ( LOGUSER VARC

14、HAR2(10), DATABASE_NAME VARCHAR2(30),LOG_TIME DATE); 然后創(chuàng)建一個系統(tǒng)觸發(fā)器，觸發(fā)事件為然后創(chuàng)建一個系統(tǒng)觸發(fā)器，觸發(fā)事件為LOGON： CREATE OR REPLACE TRIGGER SYSTEM.LOG_DATABASE_TRIGGER AFTER LOGON ON DATABASE BEGIN INSERT INTO SYSTEM.LOG_DATABASE VALUES(SYS.LOGIN_USER, SYS.DATABASE_NAME, SYSDATE); END LOG_DATABASE_TRIGGER; 例例9.4 創(chuàng)建一個替代

15、觸發(fā)器：用于替代一個針對基于三表連創(chuàng)建一個替代觸發(fā)器：用于替代一個針對基于三表連接查詢的視圖上的更新操作。接查詢的視圖上的更新操作。 首先創(chuàng)建一個基于三表的視圖：首先創(chuàng)建一個基于三表的視圖： CREATE VIEW SYSTEM.S_C_SC_VIEW AS SELECT X.SNO,SNAME,SSEX,SAGE,SCLASS, Y.CNO,CNAME,CCREDIT,GRADE FROM SYSTEM.STUDENT X, SYSTEM.COURSE Y,SYSTEM.SCORE Z WHERE X.SNO=Z.SNO AND Y.CNO=Z.CNO; 然后創(chuàng)建一個替代觸發(fā)器，觸發(fā)事件為然

16、后創(chuàng)建一個替代觸發(fā)器，觸發(fā)事件為INSERT： CREATE OR REPLACE TRIGGER SYSTEM.S_C_SC_VIEW_TRIGGER INSTEAD OF INSERT ON SYSTEM.S_C_SC_VIEW REFERENCING NEW AS N FOR EACH ROW DECLARE ROWCOUNT NUMBER; BEGIN SELECT COUNT(*) INTO ROWCOUNT FROM SYSTEM.STUDENT WHERE SNO=:N.SNO; IF ROWCOUNT=0 THEN INSERT INTO SYSTEM.STUDENT VALU

17、ES(:N.SNO,:N.SNAME,:N.SSEX, :N.SAGE,:N.SCLASS); ELSE UPDATE SYSTEM.STUDENT SET SNAME=:N.SNAME,SSEX=:N.SSEX, SCLASS=:N.SCLASS WHERE SNO=:N.SNO; END IF; SELECT COUNT(*) INTO ROWCOUNT FROM SYSTEM.COURSE WHERE CNO=:N.CNO; IF ROWCOUNT=0 THEN INSERT INTO SYSTEM.COURSE VALUES(:N.CNO,:N.CNAME,:N.CCREDIT); ELSE UPDATE SYSTEM.COURSE SET CNAME=:N.CNAME,CCREDIT=:N.CCREDIT WHERE CNO=:N.CNO; END IF; SELECT COUNT(*) INTO ROWCOUNT FROM SYSTEM.SCORE WHERE SNO=:N.SNO AND CNO=:N.CNO; I