小议Oracle11g有哪些自治事务？

小议Oracle外键约束修改行为

Oracle的外键用来限制子表中参考的字段的值，必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时，定义了一系列的动作。 前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE。 至于SQL标准中定义的其他操作，Oracle只能通过触发器来实现，这里给出一个简单的RESTRICT操作的例子。 SQL> DROP TABLE T_C; 表已删除。 SQL> DROP TABLE T_P; 表已删除。 SQL> CREATE TABLE T_P (ID NUMBER, NAME VARC...全部

  Oracle的外键用来限制子表中参考的字段的值，必须在主表中存在。而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时，定义了一系列的动作。 前面几篇文章介绍了Oracle所支持的3种约束行为NO ACTION、DELETE SET NULL和DELETE CASCADE。
  至于SQL标准中定义的其他操作，Oracle只能通过触发器来实现，这里给出一个简单的RESTRICT操作的例子。 SQL> DROP TABLE T_C; 表已删除。 SQL> DROP TABLE T_P; 表已删除。
   SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30)); 表已创建。 SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID); 表已更改。
   SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30)); 表已创建。 SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C 2 FOREIGN KEY (FID) 3 REFERENCES T_P (ID); 表已更改。
   SQL> INSERT INTO T_P VALUES (1, ’A’); 已创建 1 行。 SQL> INSERT INTO T_P VALUES (2, ’B’); 已创建 1 行。 SQL> INSERT INTO T_C VALUES (1, 1, ’A’); 已创建 1 行。
   SQL> COMMIT; 提交完成。 SQL标准定义的RESTRICT操作其实和NO ACTION操作十分类似。不同之处在于，RESTRICT的检查在语句执行之前，一旦发现主键被引用，就会报错，阻止更新或删除操作的执行。
   SQL> SELECT * FROM T_P; ID NAME ---------- ------------------------------ 1 A 2 B SQL> SELECT * FROM T_C; ID FID NAME ---------- ---------- ------------------------------ 1 1 A SQL> UPDATE T_P SET ID = 3 WHERE ID = 1; UPDATE T_P SET ID = 3 WHERE ID = 1 *第 1 行出现错误: ORA-02292: 违反完整约束条件 (YANGTK。
  FK_T_C) - 已找到子记录日志 对于上面这种情况，无论是NO ACTION还是RESTRICT，效果没有什么差别，而对于下面的情况就不一样了： SQL> UPDATE T_P SET ID = ID - 1; 已更新2行。
   SQL> SELECT * FROM T_P; ID NAME ---------- ------------------------------ 0 A 1 B SQL> UPDATE T_P SET ID = ID + 1; 已更新2行。
   SQL> SELECT * FROM T_P; ID NAME ---------- ------------------------------ 1 A 2 B 对于NO ACTION来说，约束的检查发生在语句之后，所有上面的语句可以顺利执行，而对于RESTRICT而言，这个操作会直接报错。
   下面构建一个RESTRICT操作的触发器： SQL> CREATE OR REPLACE TRIGGER T_P_RESTRICT 2 BEFORE DELETE OR UPDATE OF ID ON T_P 3 FOR EACH ROW 4 WHEN (NVL(NEW。
  ID, -1) != NVL(OLD。ID, -1)) 5 DECLARE 6 V_STR VARCHAR2(32767); 7 V_COUNT NUMBER; 8 BEGIN 9 FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME 10 FROM ALL_CONSTRAINTS 11 WHERE CONSTRAINT_TYPE = ’R’ 12 AND R_OWNER = ’YANGTK’ 13 AND R_CONSTRAINT_NAME IN 14 (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ’T_P’)) 15 LOOP 16 V_STR := ’SELECT COUNT(*) FROM ’ || I。
  TABLE_NAME || ’ WHERE ’; 17 FOR J IN (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS 18 WHERE OWNER = I。
  OWNER 19 AND TABLE_NAME = I。TABLE_NAME 20 AND CONSTRAINT_NAME = I。CONSTRAINT_NAME) 21 LOOP 22 V_STR := V_STR || J。
  COLUMN_NAME || ’ = ’ || :OLD。ID; 23 END LOOP; 24 EXECUTE IMMEDIATE V_STR INTO V_COUNT; 25 IF V_COUNT > 0 THEN 26 RAISE_APPLICATION_ERROR(-20001, ’违反完整约束条件限制操作 - 已找到子记录日志’); 27 END IF; 28 END LOOP; 29 END; 30 / 触发器已创建 SQL> SELECT * FROM T_P; ID NAME ---------- ------------------------------ 1 A 2 B SQL> SELECT * FROM T_C; ID FID NAME ---------- ---------- ------------------------------ 1 1 A SQL> UPDATE T_P SET ID = ID - 1; UPDATE T_P SET ID = ID - 1 *第 1 行出现错误: ORA-20001: 违反完整约束条件限制操作 - 已找到子记录日志 ORA-06512: 在"YANGTK。
  T_P_RESTRICT", line 22 ORA-04088: 触发器 ’YANGTK。T_P_RESTRICT’ 执行过程中出错 这种方式可以实现RESTRICT操作，但是对于其他会话的外键的引用，RESTRICT操作是看不到的： SQL> CONN YANGTK/YANGTK@YTK92已连接。
   SQL> SET SQLP ’SQL2> ’ SQL2> INSERT INTO T_C VALUES (2, 2, ’B’); 已创建 1 行。 在另一个会话插入一条参考主表ID为2的记录，且不提交，这时在主表更新或删除ID为2的记录时，会被锁住，RESTRICT操作不会起作用，因为这时看不到其他用户未提交的修改： SQL> DELETE T_P WHERE ID = 2; 而如果在第二个会话中提交事务： SQL2> COMMIT; 提交完成。
   则RESTRICT操作会起作用，因为这时已经提交，而当前的会话是可以看到其他会话中已提交数据的： DELETE T_P WHERE ID = 2 *第 1 行出现错误: ORA-20001: 违反完整约束条件限制操作 - 已找到子记录日志 ORA-06512: 在"YANGTK。
  T_P_RESTRICT", line 22 ORA-04088: 触发器 ’YANGTK。T_P_RESTRICT’ 执行过程中出错 SQL>。收起