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;
相关推荐
Oracle中round() 函数与trunc()函数的比较,非常实用。x,y,数字型表达式,如果y不为整数则截取y整数部分,如果y>0则四舍五入为y位小数,如果y小于0则四舍五入到小数点向左第y位。