第9章 Oracle9i数据库的完整性和并发控制(1)

1、第第9章章 Oracle9i数据库的完整性和并发控制数据库的完整性和并发控制 9.1 完整性控制：数据库的完整性是指保证数据库中数据及语义完整性控制：数据库的完整性是指保证数据库中数据及语义的正确、相容和有效，防止任何对数据造成错误的操作。数据的正确、相容和有效，防止任何对数据造成错误的操作。数据库的完整性一般包括实体完整性、参照完整性和用户自定义完库的完整性一般包括实体完整性、参照完整性和用户自定义完整性三种，整性三种，Oracle9i允许定义和实施这三种类型的完整性规则，允许定义和实施这三种类型的完整性规则，这些规则可用完整性约束和触发器来定义。这些规则可用完整性约束和触发器来定义。9.1

2、.1 完整性约束：完整性约束是指数据库中数据所具有的制约完整性约束：完整性约束是指数据库中数据所具有的制约和依存规则，用以限定数据库的状态以及状态的变化。实施完和依存规则，用以限定数据库的状态以及状态的变化。实施完整性规则的好处是：整性规则的好处是： 完整性约束可在创建和修改表时定义，对完整性规则的实完整性约束可在创建和修改表时定义，对完整性规则的实施优于应用程序中的复杂编码和触发器。施优于应用程序中的复杂编码和触发器。 完整性约束存储在数据字典中，任何对表的更新操作都必完整性约束存储在数据字典中，任何对表的更新操作都必须遵守该表所定义的完整性约束。须遵守该表所定义的完整性约束。 由于可以临时

3、禁用完整性约束，使得装载大量数据时可以由于可以临时禁用完整性约束，使得装载大量数据时可以避免约束检查的开销。当数据装载完成时，再启用完整性约束，避免约束检查的开销。当数据装载完成时，再启用完整性约束，任何破坏完整性约束的数据将在例外表中列出。任何破坏完整性约束的数据将在例外表中列出。 9.1.2 触发器触发器 触发器的概念触发器的概念 触发器类似于过程和函数，也是一个触发器类似于过程和函数，也是一个PL/SQL命名块，但它不命名块，但它不需显式调用，而是当相应事件发生时，激发在后台自动运行。需显式调用，而是当相应事件发生时，激发在后台自动运行。使用触发器的好处：使用触发器的好处： n n 维护

4、不可能在表创建时通过声明进行的复杂完整性限制。维护不可能在表创建时通过声明进行的复杂完整性限制。 n n 通过记录修改内容和修改者来审计表中的信息。通过记录修改内容和修改者来审计表中的信息。 n n 当表被修改时，自动给其它需要在该表上执行操作的程序发当表被修改时，自动给其它需要在该表上执行操作的程序发出信号。出信号。 一个触发器一般是由触发事件、发生事件的对象、触发条件和一个触发器一般是由触发事件、发生事件的对象、触发条件和触发器体四部分组成。触发事件可以是触发器体四部分组成。触发事件可以是DML语句、语句、DDL语句语句或系统事件（如数据库启动和关闭等），根据触发事件的不同，或系统事件（如

5、数据库启动和关闭等），根据触发事件的不同，可将触发器分为可将触发器分为DML触发器、触发器、DDL触发器、系统触发器和替触发器、系统触发器和替代触发器四种类型。代触发器四种类型。 n n DML触发器：定义在表上的触发器，由触发器：定义在表上的触发器，由INSERT、DELETE和和UPDATE等等DML语句触发。创建语句触发。创建DML触发器要明确规定执触发器要明确规定执行行SQL语句时机的语句时机的BEFORE/AFTER选项，同时指定是行级触选项，同时指定是行级触发器（触发语句每影响表的一行，触发器就被触发一次）还是发器（触发语句每影响表的一行，触发器就被触发一次）还是语句级触发器（触发

6、器只在相应语句被执行时触发一次，并不语句级触发器（触发器只在相应语句被执行时触发一次，并不针对每一行）。针对每一行）。 n n DDL触发器：定义在用户模式上的触发器，由触发器：定义在用户模式上的触发器，由CREATE、ALTER或或DELETE等语句触发。等语句触发。 n n 替代触发器：定义在视图上的触发器，由替代触发器：定义在视图上的触发器，由INSERT、DELETE和和UPDATE等语句触发。因为直接对有些视图的等语句触发。因为直接对有些视图的INSERT、DELETE和和UPDATE操作是非法的，使用替代触发器则可以将操作是非法的，使用替代触发器则可以将这些操作合法替代为对相应基表

7、的操作。创建替代触发器不能这些操作合法替代为对相应基表的操作。创建替代触发器不能加加BEFORE/AFTER选项。选项。 n n 系统触发器：定义在数据库上的触发器，由表系统触发器：定义在数据库上的触发器，由表9.1所示的系所示的系统事件触发。统事件触发。 创建触发器：创建触发器： 语法如下：语法如下： CREATE OR REPLACE TRIGGER 触发器名触发器名 触发事件触发事件 ON DATABASE|用户模式名用户模式名|表名表名|视图名视图名| REFERENCING | | FOR EACH ROW WHEN 触发条件触发条件 触发器体触发器体; END触发器名触发器名; 上

8、面语法中的主要参数描述如下：上面语法中的主要参数描述如下： n n BEFORE|AFTER|INSTEAD OF：BEFORE表示在执行触表示在执行触发语句之前触发，发语句之前触发，AFTER表示在执行触发语句之后触发，表示在执行触发语句之后触发，INSTEAD OF表示创建替代触发器。表示创建替代触发器。 n n REFERENCING：行级触发器的触发器体可以引用一些特：行级触发器的触发器体可以引用一些特定的条件谓词定的条件谓词INSERTING、DELETING和和UPDATING。OLD指定在条件谓词执行前引用字段旧值时要使用的名称；指定在条件谓词执行前引用字段旧值时要使用的名称；N

9、EW指定在条件谓词执行后引用字段新值时要使用的名称；指定在条件谓词执行后引用字段新值时要使用的名称；如果触发器定义在参照表上，如果触发器定义在参照表上，PARENT指定被参照表的当前行。指定被参照表的当前行。 n n FOR EACH ROW：指定为行触发器。：指定为行触发器。 n n WHEN：指定触发条件。但该语句不能在语句级触发器中：指定触发条件。但该语句不能在语句级触发器中使用。使用。 n n 触发器体可以是触发器体可以是PL/SQL块，也是块，也是CALL 过程名的形式。过程名的形式。 例例9.1 创建一个创建一个DML触发器：用于每次对表触发器：用于每次对表SYSTEM.STUDE

10、NT进行进行DML操作（插入、删除和修改）前，首先在屏幕上显操作（插入、删除和修改）前，首先在屏幕上显示该学生原来的年龄、现在的年龄以及新旧年龄的差值。示该学生原来的年龄、现在的年龄以及新旧年龄的差值。 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(现在年龄现在年龄:|:NEW.SAGE); DBMS_OUTPUT.PUT_LINE(年龄差值年龄差值:|AGEDIFF); END PRINT_SAGE_TRIGGER; 例例9.2 创建一个创建一个DDL触发器：用于自动记录用户触发器：用于自动记录用户SYSTEM模模式下创建建数据库对象时的用户名、对象名、对象类型及创建式下创建建数据库对象时的用户名、对象名、对象类型及创建时间。时间。 首先创建一个表用于保存创建数据库对象时的各类信息：首先创建一个表用于保存创建数据库对象时的各类信息： CREA

12、TE TABLE SYSTEM.OBJECT_CREATED ( OBJECT_OWNER VARCHAR2(10), OBJECT_NAME VARCHAR2(30), OBJECT_TYPE VARCHAR2(20), CREATE_TIME DATE); 然后创建一个然后创建一个DDL触发器，触发事件为触发器，触发事件为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 创建一个系统触发器：用于自动记录用户登录数据库创建一个系统触发器：用于自动记录用户登录数据库时的用户名、数据库名和登录时间。时的用户名、数据库名和登录时间。 首先创建一个表用于保存用户登录数据库时的各类信息：首先创建一个表用于保存用户登录数据库时的各类信息： CREATE TABLE SYSTEM.LOG_DATABASE ( LOGUSER VARC

14、HAR2(10), DATABASE_NAME VARCHAR2(30),LOG_TIME DATE); 然后创建一个系统触发器，触发事件为然后创建一个系统触发器，触发事件为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 创建一个替代

15、触发器：用于替代一个针对基于三表连创建一个替代触发器：用于替代一个针对基于三表连接查询的视图上的更新操作。接查询的视图上的更新操作。 首先创建一个基于三表的视图：首先创建一个基于三表的视图： 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; 然后创建一个替代触发器，触发事件为然

16、后创建一个替代触发器，触发事件为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