Получить свойство переменной Oracle PL/SQl по имени (отражение в PL/SQL)

Мне нужно провести аудит по обновлению строки.

Итак, у меня есть функция, которая получает параметр типа some_table%ROWTYPE, содержащий новые значения, которые будут сохранены для этой строки.

Мне также нужно сохранить некоторую информацию в таблице истории о том, какие значения столбцов были изменены. Я думал о том, чтобы получить имена столбцов для some_table из all_tab_columns, а затем перебрать их, чтобы сравнить старые и новые значения и посмотреть, изменились ли они. Проблема в том, что когда у меня есть имя столбца, я не знаю, как получить доступ к значению в моей переменной ROWTYPE. Что-то вроде var.getProperty(columnName).

Я хотел сделать это таким образом, чтобы избежать набора IF, по одному для каждого поля, и это также работало бы непосредственно на добавление нового столбца в таблицу.

Также я не могу использовать триггеры, потому что старшие взлеты сказали "Нет триггеров!". (Если это действительно единственный способ, я мог бы попытаться поговорить с ними еще раз об этом).

Источник

2 ответа

Решение

Возможно, стоит выяснить, почему существует правило "без триггеров".

Есть много хороших аргументов против триггеров - особенно о включении бизнес-правил в триггеры - но ведение журнала обычно считается хорошим примером для их использования.

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

Единственный способ сделать что-то похожее на то, что вы хотите - динамический доступ к свойству%ROWTYPE, - это поместить переменную в заголовок пакета (чтобы он был общедоступен), а затем выполнить динамический PL/SQL. Вы можете инкапсулировать переменную строки, если ваш динамический блок pl/sql содержит локальную копию перед каждой проверкой. т.е. представьте это как ваш шаблон для немедленного выполнения.

DECLARE
     lNew myTab%ROWTYPE;
     lOld myTab%ROWTYPE;
     lReturn PLS_INTEGER := 0;
BEGIN
    lNew := pStatefulPackage.NewRow;
    lOld := pStatefulPackage.OldRow;
    IF NVL(lNew.<variable>,'~') != NVL(lOld..<variable>,'~') THEN
        :lReturn := 1;
    END IF;
END;

Это очень хлопотно для того, чтобы обойти тот факт, что вы не можете связывать переменные записей или логические значения в динамическом SQL.

Это также добавляет много накладных расходов для каждого столбца.

Наконец, я обнаружил, что ALL_TAB_COLUMNS слишком медленный, чтобы использовать его для такого рода вещей - вам нужно будет кэшировать метаданные в локальной памяти pl/sql.

Источник

Возможно, проще всего сгенерировать тело функции, прочитав имена столбцов из USER_TAB_COLUMNS, например (упрощенно, не проверяет наличие нулей):

select 'if :old.'||column_name||' <> :new.'||column_name||' then log('''||column_name||''',:old.'||column_name||',:new.'||column_name||'); end if;'
  from user_tab_columns
  where table_name='MYTABLE';
Источник
Другие вопросы по тегам