Oracle9i数据库的完整性和并发控制

1、.,大型数据库概论 Introduction of Large Database 第9章 Oracle9i数据库的完整性和并发控制(1),.,学习目标 1、理解数据库完整性的概念和使用方法。 2、掌握DML触发器的概念和使用方法。 3、掌握DML触发器的概念和使用方法。 4、掌握系统触发器的概念和使用方法。 5、掌握替代触发器的概念和使用方法。,.,第9章 Oracle9i数据库的完整性和并发控制 9.1 完整性控制：数据库的完整性是指保证数据库中数据及语义的正确、相容和有效，防止任何对数据造成错误的操作。数据库的完整性一般包括实体完整性、参照完整性和用户自定义完整性三种，Oracle9i允许

2、定义和实施这三种类型的完整性规则，这些规则可用完整性约束和触发器来定义。 9.1.1 完整性约束：完整性约束是指数据库中数据所具有的制约和依存规则，用以限定数据库的状态以及状态的变化。实施完整性规则的好处是： 完整性约束可在创建和修改表时定义，对完整性规则的实施优于应用程序中的复杂编码和触发器。 完整性约束存储在数据字典中，任何对表的更新操作都必须遵守该表所定义的完整性约束。 由于可以临时禁用完整性约束，使得装载大量数据时可以避免约束检查的开销。当数据装载完成时，再启用完整性约束，任何破坏完整性约束的数据将在例外表中列出。,.,9.1.2 触发器 触发器的概念 触发器类似于过程和函数，也是一个

3、PL/SQL命名块，但它不需显式调用，而是当相应事件发生时，激发在后台自动运行。使用触发器的好处： n 维护不可能在表创建时通过声明进行的复杂完整性限制。 n 通过记录修改内容和修改者来审计表中的信息。 n当表被修改时，自动给其它需要在该表上执行操作的程序发出信号。 一个触发器一般是由触发事件、发生事件的对象、触发条件和触发器体四部分组成。触发事件可以是DML语句、DDL语句或系统事件（如数据库启动和关闭等），根据触发事件的不同，可将触发器分为DML触发器、DDL触发器、系统触发器和替代触发器四种类型。,.,nDML触发器：定义在表上的触发器，由INSERT、DELETE和UPDATE等DML

4、语句触发。创建DML触发器要明确规定执行SQL语句时机的BEFORE/AFTER选项，同时指定是行级触发器（触发语句每影响表的一行，触发器就被触发一次）还是语句级触发器（触发器只在相应语句被执行时触发一次，并不针对每一行）。 nDDL触发器：定义在用户模式上的触发器，由CREATE、ALTER或DELETE等语句触发。 n替代触发器：定义在视图上的触发器，由INSERT、DELETE和UPDATE等语句触发。因为直接对有些视图的INSERT、DELETE和UPDATE操作是非法的，使用替代触发器则可以将这些操作合法替代为对相应基表的操作。创建替代触发器不能加BEFORE/AFTER选项。,.,

5、n系统触发器：定义在数据库上的触发器，由表9.1所示的系统事件触发。,., 创建触发器： 语法如下： CREATE OR REPLACE TRIGGER 触发器名 触发事件 ON DATABASE|用户模式名|表名|视图名| REFERENCING | | FOR EACH ROW WHEN 触发条件 触发器体; END触发器名;,.,上面语法中的主要参数描述如下： n BEFORE|AFTER|INSTEAD OF：BEFORE表示在执行触发语句之前触发，AFTER表示在执行触发语句之后触发，INSTEAD OF表示创建替代触发器。 n REFERENCING：行级触发器的触发器体可以引用一

6、些特定的条件谓词INSERTING、DELETING和UPDATING。OLD指定在条件谓词执行前引用字段旧值时要使用的名称；NEW指定在条件谓词执行后引用字段新值时要使用的名称；如果触发器定义在参照表上，PARENT指定被参照表的当前行。 n FOR EACH ROW：指定为行触发器。 n WHEN：指定触发条件。但该语句不能在语句级触发器中使用。 n 触发器体可以是PL/SQL块，也是CALL 过程名的形式。,.,例9.1 创建一个DML触发器：用于每次对表SYSTEM.STUDENT进行DML操作（插入、删除和修改）前，首先在屏幕上显示该学生原来的年龄、现在的年龄以及新旧年龄的差值。 C

7、REATE 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_LINE(原来年龄:|:OLD.SAGE); DBMS_OUTPUT.PUT_LINE(现在年龄:|:NEW.SAGE); DBMS_OUTPUT.PUT_LINE(年龄差值:|AGEDIFF); END

8、PRINT_SAGE_TRIGGER;,.,例9.2 创建一个DDL触发器：用于自动记录用户SYSTEM模式下创建建数据库对象时的用户名、对象名、对象类型及创建时间。 首先创建一个表用于保存创建数据库对象时的各类信息： CREATE 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 S

9、YSTEM.CREATE_OBJECT_TRIGGER AFTER CREATE ON SYSTEM.SCHEMA BEGIN INSERT INTO SYSTEM.OBJECT_CREATED VALUES(SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_NAME, SYS.DICTIONARY_OBJ_TYPE, SYSDATE); END CREATE_OBJECT_TRIGGER;,.,例9.3 创建一个系统触发器：用于自动记录用户登录数据库时的用户名、数据库名和登录时间。 首先创建一个表用于保存用户登录数据库时的各类信息： CREATE TA

10、BLE SYSTEM.LOG_DATABASE ( LOGUSER VARCHAR2(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_

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

12、GGER 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 VALUES(:N.SNO,:N.SNAME,:N.SSEX, :N.SAGE,:N.SCLA

13、SS); 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;,.,IF ROWCOUNT=0 THEN INSERT INTO S