从多个表中删除清理脚本 SQL

Question

答案在底部，最终找到了。 感谢所有的贡献。

我必须制作一个脚本，我将把它制作成一个存储过程，然后每隔几个月运行一次。它将需要进入一些特定的表并删除旧记录。我以为这会很简单，但我一直遇到同样的问题。

如果我尝试运行我得到的代码和无效标识符错误，这似乎是日期字段的问题，Oracle 默认使用字母几个月，我已经尝试了各种日期格式，但我似乎不起作用。

我声明要从哪个日期中删除，然后将一个执行即时语句串在一起，该语句将遍历包含表名的 tabletoclean 表，以及我将对其进行清理的表的日期字段名称。

错误代码为 ORA-00904："APR"无效的意向符 我想是因为它尝试使用 APR - 4 月而不是 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 中运行执行即时字符串，我会返回类似字符串 从我的表中选择 *，其中日期字段<30-APR-16

确认问题是否出在日期格式上。

编辑/回答 问题出在 dtldate 变量上。它立即将日期发布到执行，如下所示。

从我的表中选择 *，其中日期字段<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;

Answer 1

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;

Answer 2

这是处理日期的正确方法：

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;