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 ;
/更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12推荐阅读：ORA-01172、ORA-01151错误处理 http://www.linuxidc.com/Linux/2013-06/86529.htmORA-00600 [2662]错误解决 http://www.linuxidc.com/Linux/2013-06/86528.htmORA-01078 和 LRM-00109 报错解决方法 http://www.linuxidc.com/Linux/2012-07/66044.htmORA-00471 处理方法笔记 http://www.linuxidc.com/Linux/2013-09/90017.htmORA-00314,redolog 损坏，或丢失处理方法 http://www.linuxidc.com/Linux/2013-09/90646.htmORA-00257 归档日志过大导致无法存储的解决办法 http://www.linuxidc.com/Linux/2013-09/90594.htmOracle 数据库迁移数据的建议方法（ASM 到本地硬盘，或者本地硬盘到 ASM）Linux 下如何 sql*plus 实现上下键历史记录显示（安装rlwrap 以及 readline）相关资讯 Oracle变异表 ORA-04091

ORA-04091和Compound Trigger（（12/14/2013 19:29:56）
关于ORA-04091异常的出现原因，以（07/08/2013 14:04:40）
ORA-04091:触发器/函数不能读（02/25/2013 08:28:13）

Oracle变异表触发器中ORA-04091错（11/25/2013 10:26:56）
Oracle触发器给表自身的字段重新赋（07/08/2013 13:57:10）
Oracle中变异表处理办法（01/12/2013 11:12:58）

本文评论查看全部评论（0）