Удаление из нескольких таблиц скрипт очистки SQL
Ответ внизу, выяснил со временем. спасибо за все вклады.
Мне нужно создать сценарий, который я превращу в хранимую процедуру, которая будет запускаться каждые несколько месяцев. Потребуется перейти в некоторые конкретные таблицы и удалить старые записи. Я думал, что это будет довольно просто, но я продолжаю сталкиваться с той же проблемой.
если я попытаюсь запустить код, который я получаю, и ошибка неверного идентификатора, похоже, проблема с полем даты, где Oracle по умолчанию использует буквы в течение нескольких месяцев, я пробовал различное форматирование даты, но, похоже, не работает.
Я объявляю, с какой даты я хочу удалить, а затем объединяю оператор выполнения, который будет перебирать таблицу tabletoclean, которая содержит имя таблицы и имя поля даты таблиц, в которых я буду выполнять очистку.
Код ошибки - ORA-00904: недопустимый указатель "APR". Я предполагаю, что это потому, что он пытается использовать APR - APRIL вместо 04. но я не знаю. Насколько я вижу, ни одно из dtl_fields не использует форматирование даты имени месяца.
Declare
dtldate date := to_date(add_months( to_date(sysdate), -24 ), 'dd-mm-yy');
Begin
for tbl IN (Select * from tbltoclean)
loop
execute immediate 'Delete from '||tbl.tbl_name || ' where ' || tbl.dtl_field ||' < ' || dtldate;
DBMS_OUTPUT.PUT_LINE ('Deleted from '|| to_char(tbl.tbl_name));
end loop;
end;
если я запускаю строку для немедленного выполнения в dbms.output, я возвращаю строку, например Выберите * из mytable, где поле даты <30-APR-16
Подтверждаем, что проблема в правильном форматировании даты.
РЕДАКТИРОВАТЬ / ОТВЕТИТЬ Проблема была в переменной dtldate. он разместил даты для немедленного выполнения следующим образом.
Выберите * из mytable, где поле даты <30-APR-16
Это не сработало, но
Выберите * из моей таблицы, где поле даты <'30 -APR-16 '
будет работать, поэтому я отредактировал строку, следующий код работает.
Declare
dtldate date := add_months( to_date(sysdate), -24 );
Begin
for tbl IN (Select * from tbltoclean)
loop
execute immediate 'Select null from '||tbl.tbl_name || ' where ' || tbl.dtl_field ||' < ' || 'to_date('''||dtldate||''')'; --dtldate have escaped ''
DBMS_OUTPUT.PUT_LINE ('Deleted from '|| to_char(tbl.tbl_name));
end loop;
end;
Проблема заключалась в том, что оракул не распознал dtldate как дату. и мне нужно было поставить '' вокруг dtldate. Следующий оператор работает :) немедленно выполнить 'Select null from' || tbl.tbl_name || 'где' || tbl.dtl_field || ' <'|| 'to_date (' '' || dtldate || '' ')';
Извините, что разочаровал вас, @EclecticFish, но вы делаете одну ошибку за другой. '30 -APR-16' - это строка, и вы сравниваете столбец типа данных DATE (по крайней мере, я надеюсь - для вашего же блага - что это DATE) с этой строкой. К счастью, Oracle неявно преобразовал строку на сегодняшний день, основываясь на ваших текущих настройках NLS. Как только он изменится, у вас снова будут проблемы.
Ответы 2
SYSDATE - это функция, которая возвращает тип данных DATE; нет смысла снова преобразовывать его в дату (с TO_DATE).
Итак, это может делать то, что вы хотите:
declare
dtldate date := add_months(trunc(sysdate), -24);
begin
for tbl in (select * from tbltoclean)
loop
execute immediate 'Delete from '||tbl.tbl_name ||
' where ' || tbl.dtl_field ||' < DATE ''' || to_char(dtldate, 'YYYY-MM-DD') || '''';
dbms_output.put_line ('Deleted from '|| to_char(tbl.tbl_name));
end loop;
end;
Поправьте меня, если я ошибаюсь, но разве использование dtldate таким образом неявно преобразовывает его в строку? Я полагаю, что было бы безопаснее использовать ' < DATE''' || TO_CHAR(dtldate, 'YYYY-MM-DD') || ''''
Ага; используя литерал DATE. Я бы сказал, ты прав, @ Дэвид, большое тебе спасибо. (Кстати, браво для меня - проповедовать одно, а делать прямо противоположное ...)
Это правильный способ лечения ДАТЫ:
DECLARE
--
DTLDATE DATE;
--
BEGIN
--
DTLDATE := TRUNC(SYSDATE) -- 30/04/2018 14:36 -> 30/04/2018
--
DTLDATE := ADD_MONTHS(DTLDATE, -24) -- 30/04/2018 -> 30/04/2016
--
FOR TBL IN (SELECT * FROM TBLTOCLEAN) LOOP
--
EXECUTE IMMEDIATE 'DELETE FROM '||TBL.TBL_NAME ||
' WHERE ' || TBL.DTL_FIELD ||' < :DTLDATE'
USING DTLDATE;
DBMS_OUTPUT.PUT_LINE ('DELETED FROM '|| TBL.TBL_NAME);
--
END LOOP;
--
END;
Хм, нет - вы ошиблись. Это неверно: DTLDATE := ADD_MONTHS(DTLDATE), ADD_MONTHS требует еще один параметр. (Кроме того, вы забыли завершать команды точкой с запятой).
Я думаю, вам нужно идентифицировать dtldate как переменную.