oracle 创建job 定时调度存储过程

1、建表

create  table test_job(para_date date); 
commit; 

insert into test_job values(sysdate); 
commit; 

select  * from test_job;

2、建立存储过程

create  or replace  procedure test_jobproce as 
begin 
insert into test_job values(sysdate); 
end test_jobproce; 

3、创建job，job创建之后是默认执行的

declare test_job_really  number; 
begin 
 dbms_job.submit(test_job_really,'test_jobproce;',sysdate,'sysdate+1/1440'); 
commit; 
end; 

4、可手动执行和停止执行job

先获取到job的编号：

select  * from sys.user_jobs 

启动job：

begin 
 dbms_job.run(25); 
commit; 
end; 

停止job：

begin 
 dbms_job.broken(25,true);
 commit; 
end; 

删除job：

begin 
 dbms_job.remove(25); 
commit; 
end; 

 项目中的应用：

场景：定时检查表里边的数据，将上个月老人入住产生的固定费用，录入到费用清单中，已经录入的月份，不再录入

CREATE OR REPLACE PROCEDURE proc_fixed_pay_into_listofcost
 AS
 cursor cursor_checkin is select checkout.sys_uid,checkout.id,checkout.pid,checkout.create_date,checkout.fixPay,checkout.cost_fixed,checkout.thismonth from ( SELECT sys_guid() as sys_uid,c.id,c.pid ,c.create_date,c.out_time,(c.sum_cost-c.sum_discount) as fixPay,round((c.sum_cost-c.sum_discount)/(to_number(to_char(last_day(add_months(trunc(sysdate),-1)),'dd')))*
  (case      
  when to_char(c.Create_Date,'yyyymm') != to_char(add_months(trunc(sysdate),-1),'yyyymm') and to_char(decode(c.out_time,null,sysdate,c.out_time),'yyyymm') = to_char(add_months(trunc(sysdate),-1),'yyyymm') then  to_char(to_date(c.out_time),'dd')
  when to_char(c.Create_Date,'yyyymm') != to_char(add_months(trunc(sysdate),-1),'yyyymm') and to_char(decode(c.out_time,null,sysdate,c.out_time),'yyyymm') != to_char(sysdate,'yyyymm') then  to_char(last_day(to_date(add_months(trunc(sysdate),-1),'yyyymm')),'dd')
  when to_char(c.Create_Date,'yyyymm') = to_char(add_months(trunc(sysdate),-1),'yyyymm') and to_char(decode(c.out_time,null,sysdate,c.out_time),'yyyymm') = to_char(add_months(trunc(sysdate),-1),'yyyymm') then (to_number(to_char(to_date(c.out_time),'dd')) - to_number(to_char(c.Create_Date,'dd'))+1||'')
  when to_char(c.Create_Date,'yyyymm') = to_char(add_months(trunc(sysdate),-1),'yyyymm') and to_char(decode(c.out_time,null,sysdate,c.out_time),'yyyymm') != to_char(add_months(trunc(sysdate),-1),'yyyymm') then  (to_number(to_char(add_months(trunc(sysdate),-1),'dd')) - to_number(to_char(c.Create_Date,'dd'))+1||'')
  end),2) as cost_fixed,to_char(add_months(trunc(sysdate),-1),'yyyymm') as thisMonth,(case      
  when to_char(c.Create_Date,'yyyymm') != to_char(add_months(trunc(sysdate),-1),'yyyymm') and to_char(decode(c.out_time,null,sysdate,c.out_time),'yyyymm') = to_char(add_months(trunc(sysdate),-1),'yyyymm') then  to_char(to_date(c.out_time),'dd')
  when to_char(c.Create_Date,'yyyymm') != to_char(add_months(trunc(sysdate),-1),'yyyymm') and to_char(decode(c.out_time,null,sysdate,c.out_time),'yyyymm') != to_char(sysdate,'yyyymm') then  to_char(last_day(to_date(add_months(trunc(sysdate),-1),'yyyymm')),'dd')
  when to_char(c.Create_Date,'yyyymm') = to_char(add_months(trunc(sysdate),-1),'yyyymm') and to_char(decode(c.out_time,null,sysdate,c.out_time),'yyyymm') = to_char(add_months(trunc(sysdate),-1),'yyyymm') then (to_number(to_char(to_date(c.out_time),'dd')) - to_number(to_char(c.Create_Date,'dd'))+1||'')
  when to_char(c.Create_Date,'yyyymm') = to_char(add_months(trunc(sysdate),-1),'yyyymm') and to_char(decode(c.out_time,null,sysdate,c.out_time),'yyyymm') != to_char(add_months(trunc(sysdate),-1),'yyyymm') then  (to_number(to_char(add_months(trunc(sysdate),-1),'dd')) - to_number(to_char(c.Create_Date,'dd'))+1||'')
  end) as tt 
  FROM OLD_CHECKIN c,OLDPERSON op 
  where c.pid = op.id
  and c.chenk_status != '04'--已结算
  and  (c.out_time is null or to_char(c.out_time,'yyyymm') = to_char(add_months(trunc(sysdate),-1),'yyyymm'))  
  and to_char(c.Create_Date,'yyyymm') <=  to_char(add_months(trunc(sysdate),-1),'yyyymm') ) checkout where checkout.tt is not null;
 var_id VARCHAR2(32);--uuid
 var_pid VARCHAR2(32);--pid
 var_create_date date;--老人入住时间
 var_cost_fixed NUMBER(11,2);--老人上个月应扣固定费用
 var_cost NUMBER(11,2);--每月固定费用
 var_checkin_id VARCHAR2(32);--checkinId
 var_this_month VARCHAR2(10);--当前月份
 var_banlance VARCHAR2(20);--当前余额
 var_banlance_count NUMBER(20);--当前入住老人在费用清单列表中的记录数
 var_cost_count NUMBER(20);--当前入住老人在费用清单列表中的本月固定消费的记录数
 BEGIN
 
 --将上个月及上个月之前的入住老人的固定费用添加进费用清单列表
 
 -- 筛选条件  1、退住时间在当月  2、退住时间也不在当月  3、无退住时间
 -- 公共条件  入住时间不在当月
  open cursor_checkin;
  loop
    fetch cursor_checkin into var_id,var_checkin_id,var_pid,var_create_date,var_cost,var_cost_fixed,var_this_month;
    exit when cursor_checkin%notfound;
    
    --需要根据checkinId查询当前入住老人在费用清单中的余额
     select max(balance),count(balance) into var_banlance,var_banlance_count from 
     (select (to_number(decode(lo.banlance,null,'0',lo.banlance)) - to_number(var_cost_fixed))||'' as balance,rownum from list_of_cost lo left join old_checkin ck
             on lo.checkin_id = ck.id 
              where lo.checkin_id = ''||var_checkin_id
              order by lo.create_date desc) 
     where rownum = 1 ;
    
    --表里边如果没有记录，余额就是0减去本次固定费用
    if var_banlance_count=0 then
      var_banlance := '-'||var_cost_fixed;
    end if;

    --如果本月已经插入郭记录，就不再插入 
     select count(id) into var_cost_count from  list_of_cost 
              where checkin_id = ''||var_checkin_id
              and to_char(create_date,'yyyymm') = to_char(sysdate,'yyyymm')
              and fee_id = ''||var_this_month; 
    
    if var_banlance_count < 1 then
        insert into list_of_cost (id,PID,inex_type,wxpend,amount,banlance,remark,create_by,create_date,checkin_id,fee_id)
        values(var_id,var_pid,'02','01',var_cost_fixed,var_banlance,var_this_month||'月固定费用','admin',sysdate,var_checkin_id,var_this_month);
        dbms_output.put_line('var_id:'||var_id||',var_create_date:'||var_create_date||',var_cost:'||var_cost||',var_cost_fixed:'||var_cost_fixed||',var_banlance:'||var_banlance||',var_this_month:'||var_this_month);
    end if;
    
  end loop;
  close cursor_checkin;
  dbms_output.put_line('---loop end---');

COMMIT;
 EXCEPTION
 WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生错误，同步失败！');
 ROLLBACK;
 END;

定义job，设定每个月执行一次

---创建job 每个月定时执行一次
declare job_fixed_pay_into_listofcost  number; 
begin 
 dbms_job.submit(job_fixed_pay_into_listofcost,'proc_fixed_pay_into_listofcost;',sysdate, 'TRUNC(LAST_DAY(SYSDATE ) + 1)' ); 
commit; 
end; 

select * from dba_jobs;

关于设定定时任务的参数解析：

描述                    INTERVAL参数值
每天午夜12点            'TRUNC(SYSDATE + 1)'
每天早上8点30分         'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点         'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点    'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分    'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'