oracle - 从多个表中删除同一组值

Question

我希望不必对所有表一遍又一遍地重复相同的子查询。

例子:

begin
    -- where subquery is quite complex and returns thousands of records of a single ID column
    delete from t1 where exists ( select 1 from subquery where t1.ID = subquery.ID );
    delete from t2 where exists ( select 1 from subquery where t2.ID = subquery.ID );
    delete from t3 where exists ( select 1 from subquery where t3.ID = subquery.ID );
end;
/

我发现的另一种选择是:

declare
  type id_table_type is table of table.column%type index by PLS_INTEGER
  ids id_table_type;
begin
  select ID
  bulk collect into ids
  from subquery;

  forall indx in 1 .. ids.COUNT
    delete from t1 where ID = ids(indx);

  forall indx in 1 .. ids.COUNT
    delete from t2 where ID = ids(indx);

  forall indx in 1 .. ids.COUNT
    delete from t3 where ID = ids(indx);
end;
/

您对这种替代方案有何看法？有没有更有效的方法来做到这一点？

Answer 1

一次创建一个临时表来保存子查询的结果。

对于每次运行，将子查询的结果插入到临时表中。子查询只运行一次，每次删除都很简单:delete from mytable t where t.id in (select id from tmptable); .

完成后截断表格。