Oracle 下的自定义函数

create or replace function MY_LOSTDAY(

t_taskidinvarchar2)

returnvarchar2as

Resultvarchar2(5);

finishvarchar2(5);--标识预警，黄牌，红牌

   t_taskreducedate date;

    --查询正在进行中的阶段的时间

cursorcur_enddateis

selectr.enddate

fromt_business_task_reducer,t_business_taskt

wherer.taskid=t.id

andt.id=t_taskid

    and r.isfinishstate='1';

  begin

finish:=0;

opencur_enddate;--打开游标

loop

fetchcur_enddate--遍历

intot_taskreducedate;

exitwhencur_enddate%notfound;--当游标为空跳出

--当前时间没有阶段结束的

if(trunc(sysdate-t_taskreducedate)<=0)then

iffinish!=0then

finish:=finish;

endif;

iffinish=0then

finish:=0;

endif;

     end if;

     --预警

if(trunc(sysdate-t_taskreducedate)>0)and(trunc(sysdate-t_taskreducedate)<=3)then

iffinish>=2then

finish:=finish;

endif;

iffinish<2then

finish:=1;

endif;

    end if;

    --黄牌

if(trunc(sysdate-t_taskreducedate)>3)and(trunc(sysdate-t_taskreducedate)<=8)then

iffinish>=3then

finish:=finish;

endif;

iffinish<3then

finish:=2;

endif;

    end if;

    --红牌

if(trunc(sysdate-t_taskreducedate)>8)then

finish:=3;

    end if;

    end loop;    close cur_enddate; --关闭游标

  Result:=finish;

return(Result);

endMY_LOSTDAY;