Oracle 用 Function 设定JOB时间

Oracle 用 Function 设定JOB时间

create or replace function fn_get_myjob_interval return date is
  Result      date;
  v_nextdate  date;
  v_nexthours number;
begin
  if to_number(to_char(sysdate, 'dd')) >= 25 then
    begin
      select max(t.activation_date)
        into v_nextdate
        from emesp.tp_production_arrange_kt t
       where t.activation_flag = 0; /*獲取指派最大的時間*/
    exception
      when no_data_found then
        v_nextdate := sysdate + 1; /*如果沒有隔天檢測*/
    end;
    if v_nextdate is null then
      v_nextdate  := sysdate;
      v_nexthours := to_number(to_char(v_nextdate, 'hh24')) + 1;
    else
      if v_nextdate <= sysdate then
        /*判斷獲取時間是否小於當前時間*/
        update emesp.tp_production_arrange_kt t
           set t.activation_date = sysdate + 60 / 1440
         where t.activation_flag = 0
           and t.activation_date = v_nextdate;
        v_nextdate  := sysdate + 60 / 1440;
        v_nexthours := to_number(to_char(v_nextdate, 'hh24')) + 1;
        commit;
      else
        v_nexthours := to_number(to_char(v_nextdate, 'hh24'));
      end if;
    end if;
  else
    v_nextdate  := to_date(to_char(sysdate, 'yyyymm') || '2008',
                           'yyyymmddhh24');
    v_nexthours := to_number(to_char(v_nextdate, 'hh24'));
    /*設定每個月20號以後才啟動JOB*/
  end if;
  result := trunc(v_nextdate) + (v_nexthours * 60) / 1440;
  /*轉化JOB可用時間*/
  return(Result);
end fn_get_myjob_interval;