oracle - 删除时外键发生变异表错误

Question

在我们的数据库中，我们有一个表，其中的记录由来自大约 4 个其他表的 id 引用。这些“子”表具有“主”表的外键，“删除时设置为空”。所有表都有一个 mutating-tables 系统(即:与 plsql-table 一起打包，当过程被调用后语句触发器时处理记录)。
但是，在删除主表中的记录时，子记录会给出“表正在变异”错误。我觉得这有点奇怪，因为外键似乎触发了一些隐式更新语句，这些语句位于 plsql 表中。

我所追求的只是试图找出为什么会这样，我似乎无法挖掘出一些相关信息!
当然，我们确实通过简单地将关联表中引用的 id 字段设置为 null 来解决这个问题，从主的语句后触发器，但我仍然想知道为什么会发生这种情况。

重现错误的代码:

CREATE TABLE master_table (ID NUMBER(5) NOT NULL);
CREATE TABLE child_table (ID NUMBER(5) NOT NULL, master_id NUMBER(5));

alter table master_table add constraint master_pk primary key (ID);

alter table child_table add constraint child_pk primary key (ID);

ALTER TABLE child_table
  add constraint on_delete_master foreign key (master_id)
  references master_table (ID) on delete set null;

CREATE OR REPLACE PACKAGE pkg_child
IS
PROCEDURE init_temp;
PROCEDURE add_temp(i_action IN VARCHAR2, 
                   i_master_old IN child_table.master_id%TYPE, 
                   i_master_new IN child_table.master_id%TYPE);
PROCEDURE process_temp;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_child IS
   TYPE temp_record IS RECORD(
      action        VARCHAR2(1),
      old_master_id child_table.master_id%TYPE,
      new_master_id child_table.master_id%TYPE);

   TYPE type_temp IS TABLE OF temp_record INDEX BY BINARY_INTEGER;

   tab_temp type_temp;

   PROCEDURE init_temp IS
   BEGIN
      tab_temp.delete;
   END;

   PROCEDURE add_temp(i_action     IN VARCHAR2,
                      i_master_old IN child_table.master_id%TYPE,
                      i_master_new IN child_table.master_id%TYPE) IS
      v_id BINARY_INTEGER;
   BEGIN
      v_id := nvl(tab_temp.last, 0) + 1;
      tab_temp(v_id).action := i_action;
      tab_temp(v_id).old_master_id := i_master_old;
      tab_temp(v_id).new_master_id := i_master_new;
   END;

   PROCEDURE process_temp IS
      v_id    BINARY_INTEGER;
      v_total NUMBER;
   BEGIN
      v_id := tab_temp.first;
      WHILE v_id IS NOT NULL LOOP
         IF tab_temp(v_id).action = 'U' THEN
            SELECT COUNT(1)
              INTO v_total
              FROM child_table;
         END IF;
         v_id := tab_temp.next(v_id);
      END LOOP;
   END;
END;
/
CREATE OR REPLACE TRIGGER child_table_bs
 BEFORE 
 INSERT OR UPDATE OR DELETE
 ON child_table
 REFERENCING OLD AS OLD NEW AS NEW
BEGIN
  pkg_child.init_temp;
END;
/
CREATE OR REPLACE TRIGGER child_table_ar
 AFTER 
 INSERT OR DELETE OR UPDATE
 ON child_table
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW 
DECLARE
   v_action VARCHAR2(1);
BEGIN
   IF inserting THEN
      v_action := 'I';
   ELSIF updating THEN
      v_action := 'U';
   ELSIF deleting THEN
      v_action := 'D';
   END IF;
   pkg_child.add_temp(v_action, :old.id, :new.id);
END;
/
CREATE OR REPLACE TRIGGER child_table_as
 AFTER 
 INSERT OR UPDATE OR DELETE
 ON child_table
 REFERENCING OLD AS OLD NEW AS NEW
BEGIN
 pkg_child.process_temp;
END;
/

INSERT ALL 
   INTO master_table (id) VALUES (1) 
   INTO master_table (id) VALUES (2) 
   INTO master_table (id) VALUES (3) 
   INTO master_table (id) VALUES (4)
SELECT * FROM dual;

INSERT ALL
   INTO child_table (id, master_id) VALUES (1, NULL) 
   INTO child_table (id, master_id) VALUES (2, 1) 
   INTO child_table (id, master_id) VALUES (3, 2) 
   INTO child_table (id, master_id) VALUES (4, NULL)
SELECT * FROM dual;

-- error on this delete: mutating tables
-- why?
DELETE FROM master_table
 WHERE id = 2;

清理代码:

DROP TRIGGER child_table_bs;
DROP TRIGGER child_table_ar;
DROP TRIGGER child_table_as;
DROP PACKAGE pkg_child;
DROP TABLE child_table;
DROP TABLE master_table;

谢谢

Answer 1

您有一个语句，即主表中的 DELETE，它可以影响多行。
由于 CASCADE 约束，每个删除的行都会触发 CHILD 表的隐式/递归 UPDATE 语句。也就是说，理论上您可以对子表进行多次更新。

假设你做了一个 DELETE FROM master_table WHERE id in (1, 2)
这将生成两个 UPDATE child_table 语句。每个都将尝试执行 AFTER UPDATE 触发器，因此您将获得两次执行

SELECT COUNT(1)
INTO v_total
FROM child_table

单个 DELETE 语句下的任何 SELECT 的结果在特定时间点应保持一致。但是 SELECT 不是在 DELETE 结束时发生，而是在删除期间执行多次，每次都可能有不同的结果。 Oracle 可以计算出您想要/期望的结果，因此会引发变异表错误。

在不了解业务需求的情况下，很难推荐解决方案。像 Oracle 一样，我们不知道您要做什么。可能这种情况可以通过在事务结束时执行的 ON-COMMIT MV 或 DBMS_JOB 来解决。