declare
jobno binary_integer ;
rm_days number := 15; --保留多少天的数据，单位天数
rm_hour number := 23; --每天几点运行，单位24小时制
my_hour number;
my_interval_day number := 100; --执行周期，单位天数
my_interval varchar2(20);
my_sql varchar2(200);
my_next_date varchar2(200);
my_yymmdd varchar2(200);
my_own_sql varchar2(2000);
begin
-- rm_days := 15;--保留多少天的数据，单位天数
-- rm_hour := 23;--每天几点运行，单位24小时制
-- my_interval_day := 100;--执行周期，单位天数
my_sql := '''delete from log_info where currenttime <sysdate-' ||
rm_days || ';''';
select to_number(to_char(sysdate, 'hh24')) into my_hour from dual;
if rm_hour <= my_hour then
rm_hour := rm_hour;
select to_char(sysdate + 1, 'yyyy-mm-dd') into my_yymmdd from dual;
my_next_date := 'to_date(''' || my_yymmdd || ' ' || rm_hour ||
':00:00'' ,''yyyy-mm-dd hh24:mi:ss'')';
elsif rm_hour > my_hour then
select to_char(sysdate, 'yyyy-mm-dd') into my_yymmdd from dual;
my_next_date := 'to_date(''' || my_yymmdd || ' ' || rm_hour ||
':00:00'' ,''yyyy-mm-dd hh24:mi:ss'')';
end if;
my_interval := '''sysdate+' || to_char(my_interval_day) || '''';
my_own_sql := ' declare
jobno binary_integer;
begin
dbms_job.submit(job => jobno,what => ' || my_sql || ',
next_date => ' || my_next_date || ',
interval =>' || my_interval || ');
commit;
end;';
execute immediate my_own_sql;
end;