存储过程中游标的一条数据却遍历执行了两次
create or replace procedure P_CPM_Point_Inspection as
v_GUID VARCHAR2(50);
v_LINE_ID VARCHAR2(50);
v_NEXTDATE VARCHAR2(50);
v_currentTime date;
--获取当前需要执行的巡点检工作
CURSOR cs_om_dj IS
select b.guid,b.CYCLE_ID from DJ_LINE a
left join DJ_LINE_CYCLE b on a.line_id=b.line_id
where ison='是' and b.line_id is not null
and ( NextExecuteTime is null or to_char(NextExecuteTime,'YYYY-MM-DD HH24:MI') <=to_char(sysdate,'YYYY-MM-DD HH24:MI'))
and (EndDate is null or to_char(EndDate,'YYYY-MM-DD') >= to_char(sysdate,'YYYY-MM-DD'))
and to_char(BeginDate,'YYYY-MM-DD HH24:MI')<=to_char(sysdate,'YYYY-MM-DD HH24:MI');
BEGIN
--获取当前时间
SELECT to_date(to_char(SYSDATE,'YYYY-MM-DD HH24:MI'),'YYYY-MM-DD HH24:MI') into v_currentTime FROM DUAL;
BEGIN
OPEN cs_om_dj;
LOOP
exit when cs_om_dj%NOTFOUND ;
FETCH cs_om_dj INTO V_GUID,v_LINE_ID;
--生成任务
if (V_GUID is not null and v_LINE_ID is not null) then
insert into EAM_INSPECT_EXEC (EXEC_ID,EXEC_NO,LINE_NAME, EXEC_DESCRIPTION,ORGFID,RESP_PERSON_ID, EXEC_STATUS, guid,CREATE_TIME,opercenter)
select a.Opercenter||to_char(sysdate,'yyyymmdd')||seq_dj.nextval, b.CYCLE_ID, a.LineName_TX, a.Desc_TX, a.Dept_DESC, a.rp_user, '未执行', newid() ,v_currentTime ,a.opercenter
from DJ_LINE a left join DJ_LINE_CYCLE b on a.line_id=b.line_id where b.guid=v_guid;
insert into DJ_EXEC_SPEC(exec_id,CD_ID,Cd_Name,Cd_Type,guid)
select a.LINE_ID,rownum,a.cd_name,a.datagroup_desc,newid() FROM DJ_LINE_SPECLIST A where line_id=v_LINE_ID;
-- nextDate:= F_DJ_NEXTDATE(V_GUID);
-- 计算出下次生成任务的时间。
select case
when a.CycType = '小时' then
to_char(v_currentTime+ 1/24 * CycValue,