Oracle Delete语句:级联删除已删除多少行 [英] Oracle Delete Statement: how many rows have been deleted by cascade delete

问题描述

我正在执行类似的语句

DELETE FROM USER WHERE USER_ID=1;

在SQLDeveloper中.

In SQLDeveloper.

由于在许多表中都引用了该用户(例如，该用户具有订单，设置等)，我们激活了ON DELETE CASCADE，因此我们不必手动删除每一行.但是，在开发过程中，我们很想知道通过级联删除自动"删除了多少行和哪些表.

As the user is referenced in many tables (for example the user has an order, settings, ...) we activated ON DELETE CASCADE so that we do not have to delete each and every row manually. However while developing we are interested to know how many rows and and in which tables are "automatically" deleted by cascade delete.

有什么办法可以找到答案.是通过SQL语句直接在sqldeveloper中通过日志文件还是其他任何想法?

Is there any way to find this out. Either by SQL-Statement, directly in sqldeveloper from a logfile or any other idea?

推荐答案

这在sql％rowcount中是不可能的，如果您编写触发器代码是可能的，但这意味着您需要在所有想要的表上使用触发器监视器.触发器也会使操作速度变慢.

whilst this is not possible with sql%rowcount, it is possible if you write trigger code, but this means that you need a trigger on all tables you want to monitor. Also triggers would slow down operations a bit.

例如:

SQL> select * from one;

        ID
----------
         1
         2

SQL> select * from child_of_one;

        ID       O_ID
---------- ----------
         1          1
         2          1
         3          1
         4          2
         5          2
         6          2
         7          2
         8          2

我们希望包装规格中包含表和计数的数组:

we want a package spec to hold an array of tables + counts:

SQL> create or replace package foo
  2  as
  3    type rowcount_tab is table of pls_integer index by varchar2(30);
  4    t_rowcount rowcount_tab;
  5  end foo;
  6  /

Package created.

我们希望顶级表上的触发器将这些计数重置为零:

we want a trigger on the top level table to reset these counts to zero:

SQL> create or replace trigger one_biud
  2  before insert or update or delete
  3  on one
  4  declare
  5  begin
  6    foo.t_rowcount.delete;
  7  end;
  8  /

Trigger created.

这假定您只对从顶层表中删除的数组感兴趣.如果不是，则希望在每个表上使用foo.t_rowcount.delete('TABLE_NAME')来触发.

this assumes that you're only interested in the array with a delete from the top level table. if not, you'd want a trigger on each table with foo.t_rowcount.delete('TABLE_NAME') instead.

现在在每个感兴趣的表上为每行触发器设置一个数组:

now a after for each row trigger on each table of interest to set the arrays:

SQL> create or replace trigger one_aiudfer
  2  after insert or update or delete
  3  on one
  4  for each row
  5  declare
  6  begin
  7    if (foo.t_rowcount.exists('ONE'))
  8    then
  9      foo.t_rowcount('ONE') := nvl(foo.t_rowcount('ONE'), 0)+1;
 10    else
 11      foo.t_rowcount('ONE') := 1;
 12    end if;
 13  end;
 14  /

Trigger created.

SQL> create or replace trigger child_of_one_aiudfer
  2  after insert or update or delete
  3  on child_of_one
  4  for each row
  5  declare
  6  begin
  7    if (foo.t_rowcount.exists('CHILD_OF_ONE'))
  8    then
  9      foo.t_rowcount('CHILD_OF_ONE') := nvl(foo.t_rowcount('CHILD_OF_ONE'), 0)+1;
 10    else
 11      foo.t_rowcount('CHILD_OF_ONE') := 1;
 12    end if;
 13  end;
 14  /

Trigger created.

现在我们何时删除或其他任何内容:

now when we delete or whatever:

SQL> delete from one where id = 1;

1 row deleted.

SQL> declare
  2    v_table varchar2(30);
  3  begin
  4    v_table := foo.t_rowcount.first;
  5    loop
  6       exit when v_table is null;
  7             dbms_output.put_line(v_table || ' ' || foo.t_rowcount(v_table) || ' rows');
  8             v_table := foo.t_rowcount.next(v_table);
  9     end loop;
 10  end;
 11  /
CHILD_OF_ONE 3 rows
ONE 1 rows

PL/SQL procedure successfully completed.

SQL> delete from one where id = 2;

1 row deleted.

SQL> declare
  2    v_table varchar2(30);
  3  begin
  4    v_table := foo.t_rowcount.first;
  5    loop
  6       exit when v_table is null;
  7             dbms_output.put_line(v_table || ' ' || foo.t_rowcount(v_table) || ' rows');
  8             v_table := foo.t_rowcount.next(v_table);
  9     end loop;
 10  end;
 11  /
CHILD_OF_ONE 5 rows
ONE 1 rows

这篇关于Oracle Delete语句:级联删除已删除多少行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！