存储过程、定时任务调用过程 博客分类： oracle

--存储过程
create or replace procedure AUTO_IMPORT_MC_Q_ORDER(rtn_code out number,
                                                   rtn_msg  out varchar2) is
begin
  rtn_code := sqlcode;
  rtn_msg  := sqlerrm;
  insert into tb_hyfx_log
  values
    (sysdate, 'AUTO_IMPORT_MC_Q_ORDER', rtn_code, rtn_msg);
  insert into mc_q_order
    (ord_id, CUST_ID, TOT_ORD_AMT, etr_date, import_date)
    select j1.ord_id, j1.cust_id, j1.tot_ord_amt, j1.etr_date, sysdate
      from tb_aj001@link_to_lndtest j1
     where j1.ORD_STAT_H_CD = '90'
       and j1.ord_id not in (select qo.ord_id from mc_q_order qo);
  commit;
exception
  when others then
    rtn_code := sqlcode;
    rtn_msg  := sqlerrm;
    rollback;
    insert into tb_hyfx_log
    values
      (sysdate, 'AUTO_IMPORT_MC_Q_ORDER', rtn_code, rtn_msg);
    commit;
end;

--定时任务调用过程
declare
  job number;
begin
  sys.dbms_job.submit(job,
                      'declare rtn_code number; rtn_msg varchar2(1000); begin auto_import_mc_q_order(rtn_code, rtn_msg); end;',
                      sysdate,
                      'sysdate + 1/24');
  commit;
end;

描述 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）'