数据库是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;
/
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;
/
as
begin
execute immediate 'delete from pm_time_rule t where t.special_end_time <sysdate and t.activeflag=0';
end;
/
然后what里面改为'test1;'
用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
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;
/
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);