Борьба с мутантами в Oracle

Странно, что никто из читающих мой блог этого не заметил (а некоторые, я знаю точно, должны были). Дело в том что метод описанный мной в посте Оптимизация “исторического” отчета в Oracle вызывает мутации таблиц (ORA-4091). Далее я, в кратце опишу как с этим бороться (интересующиеся могут посмотреть решение у Тома Кайта тут, суть моего решения такая же, но немного отличается, в частности, я инициализирую пакетную переменную явно, а не присваиваю ей пустую переменную).

Триггер на котором происходит мутация:
create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
begin
  —MAIN_ID — ID из MAIN_TABLE в дочерних таблицах
  update main_table
    set rest=CalcRest(:new.main_id, trunc(sysdate)),  —Тут предполагается, что функция CalcRest ходит не только по дочерним таблицам, но и по MAIN_TABLE, отсюда и мутации
        rest_date=sysdate
    where id=:new.main_id;
end;

Пример функции CalcRest (очень сильно упрощенный вариант):
create or replace function CalcRest(pid in number, ReportDate in date) return number is
  Result number;
begin
  —Изначальная сумма
  select summa
    into vSumma
    from main_table
    where id=pid;
  —Собираем суммы потраченного
  select sum(summa)
    into vSumma2
    from detail_table
    where main_id=pid;
  Result:=vSumma-vSumma2;
  return(Result);
end CalcRest;

Добавляем пакет (только спецификацию, без тела) с публичной переменной:
create or replace package pck_rest is
  type rest_tbl is table of
number;
 
rests rest_tbl; –Табличная переменная в которую будем сохранять id записей в main_table для которых надо пересчитать
end pck_rest;

Добавляем statement-level триггер в котором инициализируем переменную:
create or replace trigger detail_table1_rest_bfr
  before insert or update or delete on detail_table1
begin
  pck_rest.rests:=pck_rest.rest_tbl(); –Явная инициализация переменной через конструктор
end detail_table1_rest_bfr;

Изменяем row-level триггер:
create or replace trigger detail_table1_rest
after insert or delete or update on detail_table1
for each row
declare
  vID number;
  vInitID number;
  function CheckID (pId in number) return number is –функция для проверки дубликатов в табличной переменной, нужно это для того, чтобы потом не пересчитывать и не обновлять одну и туже запись несколько раз
    Result number:=0;
  begin
     for i in 1..pck_rest.rests.count loop
      if pck_rest.rests(i)=pId then
        Result:=pId;
      end if;
    end loop;
    return Result;
  end;

begin
  if inserting then
    vInitID:=:new.main_id;
  else
    vInitID:=:old.main_id;
  end if;
  —Проверяем на дубли

  vID:=CheckID(vInitID);
  —Если дублей не найдено добавляем новое значение

  if vID=0 then
    pck_rest.rests.extend;
    pck_rest.rests(pcg_rest.rests.last):=vInitID;
  end if;
end;

Добавляем сохраняющий statement-level триггер:
create or replace trigger detail_table1_rest_aft
  after insert or update or delete on detail_table1
declare
  vRest number;
begin
  for i in 1..pck_rest.rests.count loop
    —Пересчитываем
    vRest:=CalcRest(pck_rest.rests(i),trunc(sysdate));
    —Сохраняем
    update main_table
        set rest=vRest,
             rest_date=trunc(sysdate)
        where id=pck_rest.rests(i);
  end loop;
end;

Мутанты побеждены!

Реклама
Запись опубликована в рубрике Oracle. Добавьте в закладки постоянную ссылку.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

w

Connecting to %s