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

Oracle中经常使用了trigger经常会出现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 ;

/

推荐阅读：

ORA-01172、ORA-01151错误处理

ORA-00600 [2662]错误解决

ORA-01078 和 LRM-00109 报错解决方法

ORA-00471 处理方法笔记

ORA-00314,redolog 损坏，或丢失处理方法

ORA-00257 归档日志过大导致无法存储的解决办法

，

本文原创发布php中文网，转载请注明出处，感谢您的尊重！