create or replace function fn_get_myjob_interval return date is Result date; v_nextdate date; v_nexthours numbe

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;