oracle追踪触发器语句,oracle – 如何在该语句的触发器内获取受语句影响的行数...

一种方法是使用全局变量来跟踪行数,因为没有其他方法可以从语句级触发器获取行计数.然后,您需要三个触发器…一个语句级别在语句运行之前初始化变量,一个级别级别为每行添加一个变量,一个语句级别根据您的意愿使用行计数.首先,设置变量和一些程序来帮助它：

create or replace package PKG_ROWCOUNT is

NUMROWS number;

procedure INIT_ROWCOUNT;

procedure ADD_ONE;

function GET_ROWCOUNT

return number;

end PKG_ROWCOUNT;

/

create or replace package body PKG_ROWCOUNT as

procedure INIT_ROWCOUNT is

begin

NUMROWS := 0;

end;

procedure ADD_ONE is

begin

NUMROWS := Nvl(NUMROWS, 0) + 1;

end;

function GET_ROWCOUNT

return number is

begin

return NUMROWS;

end;

end PKG_ROWCOUNT;

/

初始化变量的第一个触发器：

create or replace trigger CUSTOMER_CHANGES_TRIGGER_1

before insert or update or delete

on CUSTOMERS

begin

PKG_ROWCOUNT.INIT_ROWCOUNT;

end;

第二行每行更新：

create or replace trigger CUSTOMER_CHANGES_TRIGGER_2

after insert or update or delete

on CUSTOMERS

for each row

begin

PKG_ROWCOUNT.ADD_ONE;

end;

/

第三个显示总数：

create or replace trigger CUSTOMER_CHANGES_TRIGGER_3

after insert or update or delete

on CUSTOMERS

begin

Dbms_output.

PUT_LINE(PKG_ROWCOUNT.GET_ROWCOUNT || ' rows were affected.');

end;