Oracle 12c - сложное обновление и удаление

У меня есть следующий сценарий в таблице аудита (AUDIT_TABLE).

t_id  e_id     detail_log                                                    date_created
01    111      USER_1; Salary: from '25' to '30'; Dept_ID: from '001' to ''; 01/01/2019 
02    111      USER_1; Dept_ID: from '' to '001';                            01/01/2019
03    001      USER_1; Dept_ID: from '012' to '';                            01/01/2019
04    002      USER_1; Dept_ID: from '555' to '666';                         01/01/2019
05    222      USER_1; Dept_ID: from '' to '123';                            01/02/2019
06    333      USER_1; Salary: from '10' to '20'; Dept_ID: from '200' to ''; 01/03/2019
07    444      USER_1; Salary: from '50' to '100'; Dept_ID: from '' to '10'; 01/04/2019

В пакетных процессах есть ошибка, которая инициирует вставку в эту таблицу аудита и создает эти записи «Dept_ID: from .. to», когда значение «Dept_ID» (в другой таблице) не изменяется. Есть миллионы таких записей, которые нужно очистить. Записи, в которых зафиксировано несколько изменений полей, т. е. item_id 01, необходимо обновить, чтобы очистить сообщение аудита Dept_ID:, а записи, содержащие только запись аудита Dept_ID, необходимо удалить (item_id 02). Могут быть и другие пары, которые содержат только контрольное сообщение Dept ID в обеих записях, и в этом случае оба должны быть удалены. Логика триггера была исправлена, поэтому больше не создаются ложные записи, когда нет фактических изменений в Dept ID, однако записи, уже созданные во время ошибки, необходимо очистить. Могут быть строки, в которых есть только одна запись пары, и в этом случае их не нужно обновлять/удалять, поскольку Dept_ID фактически был изменен на нуль или изменен с нуля на значение.

Таким образом, после исправления вышеуказанного набора данных должно существовать следующее:

t_id  e_id     detail_log                                                    date_created
01    111      USER_1; Salary: from '25' to '30';                            01/01/2019 
03    001      USER_1; Dept_ID: from '012' to '';                            01/01/2019
04    002      USER_1; Dept_ID: from '555' to '666';                         01/01/2019
05    222      USER_1; Dept_ID: from '' to '123';                            01/02/2019
06    333      USER_1; Salary: from '10' to '20'; Dept_ID: from '200' to ''; 01/03/2019
07    444      USER_1; Salary: from '50' to '100'; Dept_ID: from '' to '10'; 01/04/2019

У меня есть операторы удаления и обновления, готовые сделать это, но если я удалю одну запись из пары, то обновление не найдет другую запись, поскольку ее поиск зависит от удаленной записи, и наоборот для оператора обновления. Я думал использовать оператор слияния, но не знаю, как это сделать. Любые идеи?


sql oracle sql-delete sql-update oracle12c
person user3224907    schedule 13.01.2019    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Итак, если предположить, что такие тестовые данные

DETAIL_LOG                                                                              
-----------------------------------------------------------------------------------------
USER_1; Salary: from '25' to '30'; Dept_ID: from '001' to '';                             
USER_1; Dept_ID: from '' to '001';                                                        
USER_1; Salary: from '25' to '30'; Dept_ID: from '001' to '002'; Prdeel: from '0' to '1': 
USER_1; Dept_ID: from '' to '';

если я правильно понял, вы хотите удалить запись Dept_ID из первой и второй строки, потому что одно из значений равно NULL. Я добавил четвертую строку с обоими значениями NULL, которые также следует удалить.

Третья строка остается без изменений, потому что оба значения заполнены.

Вам нужно это регулярное выражение для замены данных

 q'[(Dept_ID: from '.*' to '';|Dept_ID: from '' to '.*';)]'

Обратите внимание, что полоса в середине представляет собой ИЛИ. Левая часть соответствует отделам со значением to NULL, правая часть соответствует отделу со значением from NULL.

Совпадающая строка заменяется строкой NULL.

Чтобы ограничить объем обновляемых записей, необходимо определить точную логику ошибочных записей. Вот пример, где я ожидаю, что обе записи должны иметь одинаковые e_id и упорядочены по t_id

Используя LEAD и LAG, вы проверяете следующие и предыдущие записи, чтобы проверить, выполнены ли условия изменения на emtpy и изменения с пустого.

Обратите внимание, что я использую LIKE для фильтрации строк, чтобы повысить производительность.

Запрос на окончательную проверку перед обновлением :)

with al as (
select T_ID, E_ID, DETAIL_LOG, 
lead(DETAIL_LOG) over (partition by e_id  order by t_id) DETAIL_LOG_LEAD,
lag(DETAIL_LOG) over (partition by e_id  order by t_id) DETAIL_LOG_LAG
from AUDIT_TABLE)
select T_ID, E_ID,
/* updated entry */
regexp_replace(detail_log, q'[(Dept_ID: from '.*' to '';|Dept_ID: from '' to '.*';)]', '') DETAIL_LOG
from al
where (DETAIL_LOG      like q'[%Dept_ID: from '_%' to '';%]' and /* first wrong record */
       DETAIL_LOG_LEAD like q'[%Dept_ID: from '' to '_%';%]') OR
      (DETAIL_LOG      like q'[%Dept_ID: from '' to '_%';%]' and /* second wrong record */
       DETAIL_LOG_LAG  like q'[%Dept_ID: from '_%' to '';%]') 
;

возвращается

      T_ID       E_ID DETAIL_LOG                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
---------- ---------- ------------
         1        111 USER_1; Salary: from '25' to '30';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
         2        111 USER_1;

Обновить

Оператор UPDATE представляет собой простую переформулировку приведенного выше запроса с использованием IN (подзапрос) для ограничения области действия.

update AUDIT_TABLE
set DETAIL_LOG = regexp_replace(detail_log, q'[(Dept_ID: from '.*' to '';|Dept_ID: from '' to '.*';)]', '')
where (T_ID, E_ID) in 
-- query from above that limits the updated rows

Удаление пустых записей аудита после этой очистки — тривиальный шаг.

person Marmite Bomber    schedule 13.01.2019
comment
Это сработало бы, ЕСЛИ у меня не было действительных записей, когда идентификатор отдела фактически изменился на NULL или с NULL на NOT NULL. Я не хочу трогать те записи, которые не пострадали от этой ошибки, те, которые не имеют пары, переходя от не нуля к нулю и сразу после другой записи для перехода от нуля к не нулю. - person user3224907; 13.01.2019
comment
Что ж, придется как-то более подробно объяснить, как вы сопрягаете удаляемые записи. Например. обе записи должны иметь один и тот же e_id и упорядочены по t_id. Без этой информации возможны только IF (т.е. спекулятивные) ответы.... - person Marmite Bomber; 13.01.2019
comment
t_id будет последовательным в паре, и e_id будет таким же, однако могут быть другие записи аудита для того же самого e_id. - person user3224907; 13.01.2019
comment
хорошо проверьте обновленный ответ, сейчас должно быть точное решение. - person Marmite Bomber; 13.01.2019
comment
снимаю перед вами шляпу. - person user3224907; 13.01.2019

arrow_upward
0
arrow_downward

Если все строки, которые должны остаться, имеют данные Salary: с соседним двоеточием в конце, как в вашем случае, рассмотрите возможность удаления строк без Salary: ;

delete audit_table where instr(detail_log,'Salary:') = 0;

а затем обновить данные столбца detail_log, обрезав остальные после строки Dept_ID:

update audit_table 
   set detail_log = regexp_replace(detail_log, '(.*)Dept_ID:.*', '\1');

Демонстрация Rextester

person Barbaros Özhan    schedule 13.01.2019
comment
@user3224907 user3224907, пожалуйста, поделитесь этими случаями, добавленными к уже заданным двум строкам, чтобы мы поняли, что вы имеете в виду. - person Barbaros Özhan; 13.01.2019
comment
Пожалуйста, смотрите мое описание сейчас. Добавил и данные. - person user3224907; 13.01.2019