declare jobno binary_integer;rm_days number;rm_hour number; --传入的hourmy_hour number; --取出当前时间的hourmy_interval_day number;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:=30; rm_hour:=11; my_interval_day:=1; my_sql:='''delete from log_info_bak where currenttime <sysdate-'||rm_days||';'''; dbms_output.put_line('my_sql is: '||my_sql); --判断两个hour select to_number(to_char(sysdate,'hh24')) into my_hour from dual;-- dbms_output.put_line(my_hour); --16 18 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'')'; --20 18 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'')'; -- dbms_output.put_line(rm_hour); end if; dbms_output.put_line('my_next_date is:'||my_next_date); my_interval:='''sysdate+'||to_char(my_interval_day)||''''; --my_interval:='sysdate+1';dbms_output.put_line('my_interval is: '||my_interval);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;';dbms_output.put_line(my_own_sql);execute immediate my_own_sql; /* dbms_job.submit(job => jobno, what => my_sql, next_date => my_next_date, interval => my_interval);*/ commit;end;--测试部分/*系统 time 11:00:00 任务时间 9点 结果 执行时间推迟到 to_date('2016-12-21 9:00:00' ,'yyyy-mm-dd hh24:mi:ss')系统 time 9:00:00 任务时间 11点 结果 执行时间推迟到 to_date('2016-12-20 11:00:00' ,'yyyy-mm-dd hh24:mi:ss') */
转载于:https://www.cnblogs.com/iyoume2008/p/6204384.html
