oracle 定时作业查询,【笔记】oracle 定时作业

create table t_test_2

(

CARNO     VARCHAR2(30),

CARINFOID NUMBER

);

create sequence s_CarInfoID

start with 1

increment by 1

cache 10;

create or replace procedure pro_test

AS

carinfo_id number;

BEGIN

select s_CarInfoID.nextval into carinfo_id

from dual;

insert into t_test_2(carno,carinfoid) values(carinfo_id,'123');

commit;

end pro_test;

declare

jobno number;

begin

jobno := 1;

DBMS_JOB.SUBMIT(jobno,'pro_test;',SYSDATE,'sysdate+1/24/360');

commit;

end;

begin

dbms_job.remove(1);

commit;

end;

declare

v_job number:=1;

begin

sys.dbms_job.submit(job => v_job,

what => 'pro_test;',

next_date => to_date('21-02-2008 17:37:26', 'dd-mm-yyyy hh24:mi:ss'),

interval => 'sysdate+1/24/12');

commit;

end;

/

--创建一个任务的完整的格式是：

declare

v_job number:=1;

begin

sys.dbms_job.submit(job => v_job,

what => 'pro_test;',

next_date => to_date('21-02-2008 17:37:26', 'dd-mm-yyyy hh24:mi:ss'),

interval => 'sysdate+1/24/12');

commit;

end;

/

--系统会自动分配一个任务号jobno。

--查看作业

select * from user_jobs;

select job,next_date,next_sec,failures,broken from user_jobs;