先写一个存储过程,然后添加到job,脚本如下: PL/SQL Developer Test script 3.013 -- Created on 2002-7-2 by ADMINISTRATOR declare -- Local variables here v_jobnum number; begin -- Test statements here dbms_job.submit(v_jobnum,'sp_delete_czrz;',sysdate,'last_day(sysdate)+1'); --dbms_output.put_line('job '||v_jobnum||' has been submitted.'); --dbms_job.run(v_jobnum,TRUE); --dbms_job.run(29,TRUE); --DBMS_JOB.REMOVE(6); COMMIT; end;
SQL> create table aa(a number,b date);Table createdSQL> SQL> create or replace procedure pro 2 is 3 begin 4 insert into aa values(1,sysdate); 5 end; 6 /Procedure createdQL> declare 2 jobno number; 3 begin 4 dbms_job.submit(jobno,'pro;',sysdate,'sysdate+1/(24*60)'); 5 commit; 6 end; 7 /
10小时后开始执行,每小时一次 var ret number exec dbms_job.submit(:ret,'<存储过程名>;',sysdate+10/24,'sysdate + 1/1'); commit;
PL/SQL Developer Test script 3.013
-- Created on 2002-7-2 by ADMINISTRATOR
declare
-- Local variables here
v_jobnum number;
begin
-- Test statements here
dbms_job.submit(v_jobnum,'sp_delete_czrz;',sysdate,'last_day(sysdate)+1');
--dbms_output.put_line('job '||v_jobnum||' has been submitted.');
--dbms_job.run(v_jobnum,TRUE);
--dbms_job.run(29,TRUE);
--DBMS_JOB.REMOVE(6);
COMMIT;
end;
SQL> create or replace procedure pro
2 is
3 begin
4 insert into aa values(1,sysdate);
5 end;
6 /Procedure createdQL> declare
2 jobno number;
3 begin
4 dbms_job.submit(jobno,'pro;',sysdate,'sysdate+1/(24*60)');
5 commit;
6 end;
7 /
var ret number
exec dbms_job.submit(:ret,'<存储过程名>;',sysdate+10/24,'sysdate + 1/1');
commit;