Oracle中变异表（ORA-04091)处理方法两则

Oracle中经常使用了trigger经常会出现ora-04091变异表问题，这里带来两种处理方法给大家参考。

--**** example  *****

场景描述：

--1. init table

create  table  sales (prod_code  varchar2 (  4 ),amount_sold  number  ) ;

insert  into  sales  values  (  '0100' , 0  );

insert  into  sales  values  (  '0200' , 0  );

insert  into  sales  values  (  '0300' , 0  );

commit  ;

select  *  from  sales  order  by  1  ;

-- 2. 如果插入或者更新子类别，父类别的总销量必须级联更新

-- 比如 如果更新了0111 类别销量加1 那么 父类别0100 的销量必须也加上1

/* Test trigger :

create or replace trigger sales_amount_refresh

  after insert on sales

  for each row

  when (new.amount_sold>0)

declare

 

begin

  update sales

     set amount_sold = amount_sold + :new.amount_sold

   where substr(:new.prod_code, 0, length(rtrim(prod_code, '0'))) =

         rtrim(prod_code, '0') ;

end ;

*/

--会产生变异表的问题

/* Test sql :

SQL> insert into sales values ('0111',1) ;

 

insert into sales values ('0111',1)

 

ORA-04091: table DEXTER.SALES is mutating, trigger/function may not see it

ORA-06512: at "DEXTER.SALES_AMOUNT_REFRESH", line 4

ORA-04088: error during execution of trigger 'DEXTER.SALES_AMOUNT_REFRESH'

*/

--解决办法1：可以使用自制事务来解决，但是有bug，即无法回滚

/*

create or replace trigger sales_amount_refresh

  after insert on sales

  for each row

  when (new.amount_sold>0)

declare

  pragma autonomous_transaction ;

begin

  update sales

     set amount_sold = amount_sold + :new.amount_sold

   where substr(:new.prod_code, 0, length(rtrim(prod_code, '0'))) =

         rtrim(prod_code, '0') ;

  commit ;

end ;

自治事务里面的语句不会回滚

*/

--解决办法2 

使用package的集合变量记录需要变更的信息，然后使用statement级别的trigger 执行update操作，package中的变量只在session中有效

--只需要创建一个package 头即可

create  or  replace  package  pkg_vars  is

   type  c_t  is  table  of  number  index  by  varchar2 ( 4  ) ;

  refresh_sales_list c_t ;

end  ;

/

--修改行级触发器为

create  or  replace  trigger  sales_amount_refresh

   after  insert  on  sales

   for  each  row

   when  (new.amount_sold>  0 )

declare

begin

   if  length ( rtrim  (:new.prod_code,  '0' ))>  2  then

    pkg_vars.refresh_sales_list(:new.prod_code) := :new.amount_sold ;

   end  if  ;

end  ;

/

--新增语句级触发器

create  or  replace  trigger  sales_amount_refresh_statement

   after  insert  on  sales

declare

  ind  varchar2 (  4 ) ;

begin

  ind := pkg_vars.refresh_sales_list.first ;

   WHILE  ind  IS  NOT  NULL  LOOP

     update  sales

      set  amount_sold = amount_sold + pkg_vars.refresh_sales_list(ind)

    where  substr (ind,  0  ,  length (  rtrim (prod_code,  '0'  ))) =

          rtrim (prod_code,  '0'  )  and  length ( rtrim  (prod_code,  '0' ))=  2  ;

    ind := pkg_vars.refresh_sales_list.NEXT(ind);

   END  LOOP ;

  pkg_vars.refresh_sales_list.delete ;

end  ;

/

如需转载，请注明出处：

http://blog.csdn.net/renfengjun/article/details/18601155