oracle存储过程带游标,Oracle 带游标的存储过程-CSDN博客

该存储过程用于监控任务和阶段的完成情况。它检查当前时间与阶段结束日期的关系，根据时间差调整任务完成度。如果阶段超时，会给予不同级别的警告（如黄牌、红牌）。同时，对于没有阶段的任务，也会进行预警和黄牌警告，根据登记后的时间判断是否超过规定期限。整个过程旨在确保任务管理的有效性和及时性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace procedure TASK_REDUCE_SSPG

(t_deptid in varchar2,

finish out varchar2) as

t_taskreducedate date; --阶段的结束日期

t_taskend date; --任务的结束日期

t_logindate date; --登记任务的时间

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

cursor cur_enddate is

select r.enddate

from t_business_task_reduce r, t_business_task t

where r.taskid = t.id

and t.deptid = t_deptid

and r.isfinishstate='1';

--查询任务下没有阶段的

cursor cur_nottaskreduce is

select t.taskend,to_date(to_char(t.logindate,'yyyy-MM-dd'),'yyyy-MM-dd') as logindate from t_business_task t where t.id not in(

select r.taskid from t_business_task_reduce r where r.taskid is not null)and t.deptid=t_deptid;

begin

finish:=80;

open cur_enddate;

loop

fetch cur_enddate

into t_taskreducedate;

exit when cur_enddate%notfound; --当游标为空跳出

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

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

finish:=finish;

end if;

--正常

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

finish := finish;

end if;

--黄牌

if(cur_enddate%rowcount=1) and(trunc(sysdate-t_taskreducedate)>3)

and (trunc(sysdate-t_taskreducedate)<=8) then

if finish=20 then

finish:=20;

end if;

if finish=50 then

finish:=50;

end if;

if finish=80 then

finish:=finish-30;

end if;

if(cur_enddate%rowcount>1) and (trunc(sysdate-t_taskreducedate)>3)

and (trunc(sysdate-t_taskreducedate)<=8) then

if finish=20 then

finish:=20;

end if;

if finish=50 then

finish:=50;

end if;

if finish=80 then

finish:=finish-30;

end if;

--红牌

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

finish := finish-60;

end if;

--判断红色线的最低

if finish <=20 then

finish := 20;

end if;

end loop;

close cur_enddate;

--没有阶段任务的

open cur_nottaskreduce;

loop

fetch cur_nottaskreduce

into t_taskend,t_logindate;

exit when cur_nottaskreduce%notfound; --当游标为空跳出

dbms_output.put_line(trunc(sysdate-t_logindate));

--登记后3天没有划分阶段的给予预警提醒

if(trunc(sysdate-t_logindate)>=3

and (trunc(sysdate-t_logindate)<6))then

finish:=finish;

end if;

--登记后6天没有划分阶段的给予黄牌

if(cur_nottaskreduce%rowcount=1 and (trunc(sysdate-t_logindate)>=6))then

if finish=20 then

finish:=20;

end if;

if finish=50 then

finish:=50;

end if;

if finish=80 then

finish:=finish-30;

end if;

if(cur_nottaskreduce%rowcount>1 and (trunc(sysdate-t_logindate)>=6))then

if finish=20 then

finish:=20;

end if;

if finish=50 then

finish:=50;

end if;

if finish=80 then

finish:=finish-30;

end if;

--任务截止时间的亮红牌

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

finish := finish-60;

end if;

--判断红色线的最低

if finish <=20 then

finish := 20;

end if;

end loop;

close cur_nottaskreduce;

commit;

end;