小议Oracle外键约束修转业为（七）-PLSQL[Oracle防范]

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>