/*功能:每个月的到日,提醒客户填写计划
涉及到least()函数的巧妙用法
*/
begin
sys.dbms_job.submit(job => :job,
what => 'insert_yctx(1);',
next_date => to_date('02-09-2017', 'dd-mm-yyyy'),
interval => 'GET_LEAST_5DAYS_EVERYMONTH(sysdate+1)');
commit;
end;
/
其中ET_LEAST_5DAYS_EVERYMONTH(p_date) 函数的写法:
create or replace function "GET_LEAST_5DAYS_EVERYMONTH" (p_date date)
return date
is
mydate date;
begin
select
trunc(LEAST(p_date,
to_date(
to_char(sysdate,'yyyy')||
'/'||to_char(sysdate,'mm')||
'/'||5,
'yyyy/mm/dd'
)
)
) as mydate into mydate
from dual;
return mydate;
end;