【筆記】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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-539650/，如需轉載，請註明出處，否則將追究法律責任。