主页 / dba / 问题 / 49634
Accepted
jpmc26
Asked: 2013-09-11 18:57:37 +0800 CST

根据子表中的条件删除父表中的行 - Oracle

  • 772

我有一个B带有外键的表 table A。我想DELETE在表中的一些行B，我也想DELETE在表中他们的父行A。但是，删除条件基于 table B。A不能先删除表中的行，因为表中的引用B限制了删除，但我还必须A从行中获取要删除的键B。

这是一个带有示例表结构的 SQLFiddle： http ://sqlfiddle.com/#!4/f156c/4/0 。

我的第一个倾向是尝试通过将键SELECT从 from中保存B到变量中，然后将其用于DELETEfrom A。

DECLARE
  A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
  SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
  FROM (SELECT A_ID
        FROM B
        WHERE LENGTH(B_DATA) > 4
       );

  DELETE FROM B
  WHERE LENGTH(B_DATA) > 4;

  DELETE FROM A
  WHERE A_ID IN A_ID_TO_DELETE;
END;
/

但这只会给出一个PLS-00382: expression is of wrong type错误。错误本身来自DELETEon A; 我知道这一点，因为如果我将其注释掉，该块就会运行。

我怎样才能绕过这个expression is of wrong type错误，或者有什么方法可以解决这个问题？

Oracle 版本：Oracle 数据库 10g 企业版版本 10.2.0.1.0 - 产品

（是的，我很清楚那有多大。客户选择的数据库，而不是我们的。）

oracle oracle-10g

2 个回答

  1. Best Answer

    像这样试试

    DECLARE
     A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
     SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
     FROM (SELECT A_ID
           FROM   B
           WHERE  LENGTH(B_DATA) > 4
          );

     DELETE FROM B
     WHERE LENGTH(B_DATA) > 4;

     FORALL i IN A_ID_TO_DELETE.first..A_ID_TO_DELETE.last
          DELETE FROM A
          WHERE A_ID = A_ID_TO_DELETE(i);
END;
/
    • 2

  2. Oracle 中的外键约束被定义为（默认情况下），NON DEFERRABLE这意味着始终在语句末尾检查约束。还有另外两种定义约束的方法：

    • DEFERRABLE INITIALLY DEFERRED

    如果您将约束定义（或现在修改）为DEFERRABLE INITIALLY DEFERRED，那么约束的检查将在事务结束时进行。所以，这对你有用（但它也会修改约束在所有其他事务中的检查方式。）

    • DEFERRABLE INITIALLY IMMEDIATE

    如果您将其定义为DEFERRABLE（它获得默认值INITIALLY IMMEDIATE），那么它仍会在语句末尾进行检查，但可以推迟。因此，您可以选择在事务中设置所有约束SET CONSTRAINTS ALL DEFERRED;，这样可以正常工作：

    SET CONSTRAINTS ALL DEFERRED; 

DELETE FROM A
WHERE A_ID IN  
     ( SELECT A_ID
       FROM B
       WHERE LENGTH(B_DATA) > 4
     );

DELETE FROM B
WHERE LENGTH(B_DATA) > 4;

    在SQL-Fiddle进行测试

    有关 Oracle 文档中的约束的更多详细信息：约束和管理完整性约束

    • 2

相关问题