首先建一个procedure!
然后建一个job来定期执行她!
(1)CREATE OR REPLACE PROCEDURE DROPTABLE as
tmpname varchar2(50); cursor ftmp is
select table_name
from all_tables
where table_name<='to_date(to_char(sysdate,
''YYYYMMDD''))-7'
and table_name>='TMP'; begin open ftmp; Loop fetch ftmp
into tmpname; exit when ftmp%notfound; if ftmp%found then
Execute immediate 'drop
table '||tmpname; end if; end loop; close ftmp; end;
(2)
declare v_jobnum
begin
dbms_job.submit(v_jobnum,proname,sysdate,interval);
end;
第一段代码是建procedure
第二断代吗是建job
调用启动job的包proname:要执行的procedure的名字
sysdate:job执行的开始时间
interval:job执行的间隔时间
**********************************************
DECLARE v_jobnum NUMBER;
BEGIN
dbms_job.submit(v_jobnum,'LQSH.GetDataFromTemp;',SYSDATE, 'SYSDATE+1');
END;
***********************************************