做个job定时执行一条sql语句，请问该怎么写？

[manzu_zhang_2008]

数据库是oracle 10的，在plsql中执行。本人想做个job定时执行一条sql语句，清除数据库里的时间规则，要求每天夜里12点执行下面语句：
delete from pm_time_rule t where t.special_end_time<sysdate and t.activeflag=0
本人是数据库新手，下面是我刚才写的脚本，但是报错，很多错误信息，又不知哪儿出问题了，请高手帮忙指点：
begin
sys.dbms_job.submit(job => :job,
what => 'delete from pm_time_rule t where t.special_end_time<sysdate and t.activeflag=0',
next_date => to_date('16-01-2010 16:40:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'trunc(sysdate)+1+1/24');
commit;
end;
/

[manzu_zhang_2008]

或者是做个任务计划，定时执行一个批处理脚本，但是这个批处理又该如何写呢？

[crazylaa]

create or replace procedure test1
as
begin
execute immediate 'delete from pm_time_rule t where t.special_end_time <sysdate and t.activeflag=0';
end;
/
然后what里面改为'test1;'

[Dave]

[Quote=引用 2 楼 crazylaa 的回复:]
create or replace procedure test1
as
begin
execute immediate 'delete from pm_time_rule t where t.special_end_time <sysdate and t.activeflag=0';
end;
/
然后what里面改为'test1;'
[/Quote]

用2楼的方法建存储过程，在job里调用它。

declare
x number;
begin
sys.dbms_job.submit(job => x,
what => 'test1;',
next_date => to_date('16-01-2010 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'trunc(sysdate)+1+1/2');
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
commit;
end;


Oracle dbms_job package 用法小结
http://blog.csdn.net/tianlesoftware/archive/2009/10/21/4703133.aspx



------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716

[duqiangcise]

create or replace procedure delete_proc
as
begin 
execute immediate 'delete from pm_time_rule t where t.special_end_time < sysdate and t.activeflag=0';
end;
/

declare
varable job number;
begin
dbms_job.submit(:job,'delete_proc;',sysdate,'trunc(sysdate+1)');
commit;
end;
/

[LuoTongHua]

学习

[zhangwonderful]

同意采用存储过程来实现，在job中调用此存储过程。

[anly917]

定时删除三分钟前的数据

###   1.创建存储过程
SQL> create or replace procedure pro_delete_a as
2  begin
3  delete from a t where t.a < (sysdate-3/1440);  ###一天1440分钟。即一分钟是 1/1440
4  end;
5  /

Procedure created
###  2创建job
SQL> variable job_pro_delete_a number;
SQL> begin
2  dbms_job.submit(:job_pro_delete_a,'pro_delete_a;',sysdate,'sysdate+3/1440');
3  end;
4  /

PL/SQL procedure successfully completed
job_pro_delete_a
---------
127

###  3.调用job
SQL> begin
2  dbms_job.run(:job_pro_delete_a);
3  end;
4  /

PL/SQL procedure successfully completed
job_pro_delete_a
---------
127

###  4.删除job
SQL> begin 
2 dbms_job.remove(:job_pro_delete_a); 
3 end; 
4 /
Ps：select * from user_jobs;
dbms_job.remove(ID);

[zywd]

先如2楼一样写个存储过程，然后再创建job，调用。

[yuxinglian]

plsql里可以图形化的创建job啊， 然后点查看sql就可以看到源码