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,
'YYYY-MM-DD hh24:mi')
when a.CycType = '天' then
to_char(v_currentTime + 1 * CycValue,
'YYYY-MM-DD hh24:mi')
when a.CycType = '月' then
to_char(Add_months(v_currentTime , 1 * CycValue),
'YYYY-MM-DD hh24:mi')
when a.CycType = '周' then
to_char(v_currentTime + 7 * CycValue,
'YYYY-MM-DD hh24:mi')
when a.CycType = '年' then
to_char(Add_months(v_currentTime, 12 * CycValue),
'YYYY-MM-DD hh24:mi')
end rk_date
into v_NEXTDATE from DJ_LINE_CYCLE a where guid = v_guid;
--更新下次生成任务的时间
update DJ_LINE_CYCLE set NextExecuteTime = to_date(v_NEXTDATE,'YYYY-MM-DD HH24:MI') where guid=v_guid;
end if;
end loop;
end;
end;
以上部分是我的存储过程。我的游标明明只有一条数据,为什么却把一条数据执行了两次 。就是LOOP中的代码走了两边,是不是我哪边弄错了。小弟对存储过程的模块不熟悉。求指导。希望说的详细点,不胜感激。存储过程游标cursor
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,
'YYYY-MM-DD hh24:mi')
when a.CycType = '天' then
to_char(v_currentTime + 1 * CycValue,
'YYYY-MM-DD hh24:mi')
when a.CycType = '月' then
to_char(Add_months(v_currentTime , 1 * CycValue),
'YYYY-MM-DD hh24:mi')
when a.CycType = '周' then
to_char(v_currentTime + 7 * CycValue,
'YYYY-MM-DD hh24:mi')
when a.CycType = '年' then
to_char(Add_months(v_currentTime, 12 * CycValue),
'YYYY-MM-DD hh24:mi')
end rk_date
into v_NEXTDATE from DJ_LINE_CYCLE a where guid = v_guid;
--更新下次生成任务的时间
update DJ_LINE_CYCLE set NextExecuteTime = to_date(v_NEXTDATE,'YYYY-MM-DD HH24:MI') where guid=v_guid;
end if;
end loop;
end;
end;
以上部分是我的存储过程。我的游标明明只有一条数据,为什么却把一条数据执行了两次 。就是LOOP中的代码走了两边,是不是我哪边弄错了。小弟对存储过程的模块不熟悉。求指导。希望说的详细点,不胜感激。存储过程游标cursor
解决方案 »
- redhat6.3安装oracle10g ins_net_client.mk
- ORACLE的init.ora中关于progresses和sessions参数的关系,请说详细点。
- 怎么样把java类加载到数据库中?
- oracle的这个查询条件 WHERE (HH||BH)=(B.USERID||B.MPADDR) 是什么意思?
- 关于游标的问题 急急急急!!!!
- 请教,如何用trigger实现外健检查
- Oracle数据库同步
- 请教一个查询 语句
- 请问哪儿有p4用的symcjit.dll 下载?谢谢
- 并发交易oracle的sql的执行时间问题
- 存储过程哪里错了。。。
- 表中时间字段的格式为年月日 时分秒,我只想修改年月日的值,而保留时分秒的值
FETCH cs_om_dj INTO V_GUID,v_LINE_ID;
这两行换下位置试试。