Oracle中變異表（ORA-04091)處理方法兩則

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

ORA-00600 [2662]錯誤解決

ORA-01078 和 LRM-00109 報錯解決方案

ORA-00471 處理方法筆記

ORA-00314,redolog 損壞，或丟失處理方法

ORA-00257 歸檔日誌過大導致無法儲存的解決辦法