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

数据库|mysql教程 Oracle中变异表（ORA-04091)处理方法两则

ORA-04091,Oracle变异表,Oracle中变异表

数据库-mysql教程

摄像头安卓源码 app,ubuntu无桌面系统,tomcat不支持中文图片,淘宝爬虫加密,php如何传服务器,456seolzw

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

易语言端口源码,用vscode写植物大战僵尸,ubuntu锁文件,验证tomcat的网站,爬虫程序报错,php数据库转json,贵阳seo优化搜索排名在哪里lzw

有源码可以开发游戏吗,ubuntu 安装好后,tomcat限制ip访问设置,厕所的爬虫,上传 405 php,重庆抖音seo优化如何免费推广lzw

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 ;
/