注意几点：
- 时间比较：TO_CHAR(EnableTime, 'yyyy-mm-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd')
- 定义表：%rowtype ，例子： dormodel DISABLEOPERATIONRECORD%rowtype;--记录表
- 第一表字段：%Type ，例子： s_ipid INSPECTIONPOINT.ID%Type;--巡查表ID
- 计算天数：TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')- TO_DATE(TO_CHAR('2022-04-19', 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss'))
案例1：
create or replace procedure IP_TASK is
--声明变量
cursor c_list is
select id ipid
from INSPECTIONPOINT i
where (EnableTime is not null
or DisableTime is not null )
and deleted=0
and (TO_CHAR(EnableTime, 'yyyy-mm-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd')
or TO_CHAR(DisableTime, 'yyyy-mm-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd') );
s_ipid INSPECTIONPOINT.ID%Type;--巡查表ID
s_msg VARCHAR(200);--消息
ipmodel INSPECTIONPOINT%rowtype;--巡查表
v_setcount number(10);--需要执行行数
dormodel DISABLEOPERATIONRECORD%rowtype;--记录表
v_dorcount number(10);--记录需要执行行数
s_disableheaven number(10);--停用天数
begin
--查询巡查定时未执行数据
--打开游标
open c_list;
--遍历游标
Loop
--赋值给变量
FETCH c_list
INTO s_ipid;
--退出循环条件 (通过%NOTFOUND判断是否有值，有值执行操作，没有则退出循环)
exit when c_list%NOTFOUND;
select count(1) into v_setcount from INSPECTIONPOINT where DELETED=0 and ID=s_ipid ;
if v_setcount > 0 then
--查询今天需要执行的数据
--看这条数据，启用时间和停用时间是执行那个
select * into ipmodel from INSPECTIONPOINT where DELETED=0 and ID=s_ipid ;
--停用时间不需要计算,1：添加操作记录，2修改巡查停用时间-清空
if TO_CHAR(ipmodel.DisableTime, 'yyyy-mm-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd') then
--添加操作记录
insert into DISABLEOPERATIONRECORD
(CREATEDUSER,CREATETIME, LINKID, TQTYPE, MODELTYPE, DISABLEHEAVEN, ISTIMING)
values
(ipmodel.LastOperateUser,
SYSDATE,
ipmodel.id,
1,
1,
0,
1);
--清空时间
update INSPECTIONPOINT set DisableTime='',Status=2 where id=ipmodel.id;
end if;
--启用时间就需要，计算停用天数,1：添加操作记录，2修改巡查启用时间-清空
if TO_CHAR(ipmodel.EnableTime, 'yyyy-mm-dd') =TO_CHAR(SYSDATE, 'yyyy-mm-dd') then
-- 计算天数
--先看操作记录是否有停用记录
select count(1) into v_dorcount from DISABLEOPERATIONRECORD where deleted=0 and LinkId=ipmodel.id and TqType=1 ;
--没有记录，就拿更新时间，更新时间没有，就拿创建时间
if v_dorcount<=0 then
--更新时间
if ipmodel.UpdatedTime is not null then
select TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')- TO_DATE(TO_CHAR(ipmodel.UpdatedTime, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')) into s_disableheaven from dual;
end if;
--创建时间
if ipmodel.UpdatedTime is null then
select TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')- TO_DATE(TO_CHAR(ipmodel.CreateTime, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')) into s_disableheaven from dual;
end if;
end if;
--存在记录
if v_dorcount>0 then
select * into dormodel from ( select * from DISABLEOPERATIONRECORD where deleted=0 and LinkId=ipmodel.id and TqType=1 order by createtime desc ) where rownum = 1;
select TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')- TO_DATE(TO_CHAR(dormodel.CreateTime, 'yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss')) into s_disableheaven from dual;
end if;
--添加操作记录
insert into DISABLEOPERATIONRECORD
(CREATEDUSER,CREATETIME, LINKID, TQTYPE, MODELTYPE, DISABLEHEAVEN, ISTIMING)
values
(ipmodel.LastOperateUser,
SYSDATE,
ipmodel.id,
0,
1,
s_disableheaven,
1);
--清空时间
update INSPECTIONPOINT set EnableTime='',Status=1 where id=ipmodel.id;
end if;
end if;
end loop;
--关闭游标
close c_list;
COMMIT;
s_msg := '定时执行日常巡查停启任务';
DBMS_OUTPUT.PUT_LINE(s_msg || '成功' ||
to_char(CURRENT_DATE, 'yyyy-mm-dd'));
--异常回滚
exception
when others then
rollback;
dbms_output.put_line(s_msg || '失败' ||
to_char(CURRENT_DATE, 'yyyy-mm-dd'));
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end IP_TASK;
案例2：
create or replace procedure EP_Task(e_tasktype in NUMBER) is
--声明变量
cursor c_list is
select e.id EquipId, p.id projectid
from EQUIP e
join PROJECT p
on e.projectid = p.id
where e.status = 2
and e.DELETED = 0 ;
s_equipid EQUIP.ID%Type;--设备ID
s_projectid PROJECT.Id%Type;--项目ID
s_emid EQUIPMAINTENANCEDETAIL.Emid%Type;--新增维保主表ID
s_escid EQUIPSELFCHECKDETAIL.ESID%Type;--新增自检主表ID
s_msg VARCHAR(200);--消息
v_tcount number(10);--维保次数
v_sentcount number(10);--已发送维保次数
v_escount number(10);--自检行数
v_sentescount number(10);--已发送自检行数
v_setcount number(10);--频率行数
emmodel EQUIPFREQUENCYSET%rowtype;--频率表
s_frequency number(10) ;--执行次数
s_bl number(10) ;--循环
begin
case e_tasktype
when 1 then
--创建维保任务
--打开游标
open c_list;
--遍历游标
Loop
--赋值给变量
FETCH c_list
INTO s_equipid, s_projectid;
--退出循环条件 (通过%NOTFOUND判断是否有值，有值执行操作，没有则退出循环)
exit when c_list%NOTFOUND;
select count(1) into v_setcount from EQUIPFREQUENCYSET eps where DELETED=0 and EQUIPID=s_equipid and TYPE=2 and rownum = 1;
if v_setcount>0 then
select * into emmodel from EQUIPFREQUENCYSET eps where DELETED=0 and EQUIPID=s_equipid and TYPE=2 and rownum = 1;
end if;
--频率任务设置存在
if v_setcount > 0 and emmodel.endtime>=SYSDATE and emmodel.begintime<=SYSDATE then
--查看次数和已发送的次数比较
select count(1) into v_sentcount from EQUIPMAINTENANCE where DELETED=0 and EQUIPID=s_equipid and PROJECTID=s_projectid and CREATETIME>=emmodel.begintime and CREATETIME<=emmodel.endtime;
if v_sentcount<emmodel.frequency then
s_frequency:=emmodel.frequency - v_sentcount;
end if;
end if;
if v_setcount <= 0 or emmodel.endtime<SYSDATE or emmodel.begintime>SYSDATE then
--频率任务没有设置---频率任务设置过期了或者不在范围内-默认1月1次
select count(1) into v_tcount from EQUIPMAINTENANCE where DELETED=0 and EQUIPID=s_equipid and PROJECTID=s_projectid and CREATETIME>=trunc(sysdate, 'mm') ;
if v_tcount<=0 then
s_frequency:=1;
end if;
end if;
s_bl:=0;
while s_bl < s_frequency loop s_bl:=s_bl+1;
--新增主表
insert into EQUIPMAINTENANCE
(CREATEDUSER,CREATETIME, EQUIPID, PROJECTID, BEGINTIME, ENDTIME, STATUS)
values
(1,
SYSDATE,
s_equipid,
s_projectid,
(trunc(sysdate, 'mm')+1/24),
(trunc(LAST_DAY(SYSDATE))+23/24),
1);
select "SEQ_EQUIPMAINTENANCE".currval into s_emid from dual;
for ep_row in ( select ep.id EPId from EquipPoint ep join POINTBASIS pb on ep.PointId=pb.id where ep.equipid=s_equipid and ep.Status=0 and ep.DELETED=0 and pb.PointNo != 20 and pb.PointNo != 21 )loop
--新增详情
insert into EQUIPMAINTENANCEDETAIL
(CREATEDUSER,CREATETIME, EMID, EPID)
values
(1,
to_timestamp(TO_CHAR(sys_extract_utc(systimestamp),'YYYY-MM-DD') || ' 00:00:00','yyyy-mm-dd hh24:mi:ss.ff'),
s_emid,
ep_row.epid
);
end loop;
end loop;
end loop;
--关闭游标
close c_list;
COMMIT;
s_msg := '定时创建维保任务';
DBMS_OUTPUT.PUT_LINE(s_msg || '成功' ||
to_char(CURRENT_DATE, 'yyyy-mm-dd'));
when 2 then
--创建自检任务
--打开游标
open c_list;
--遍历游标
Loop
--赋值给变量
FETCH c_list
INTO s_equipid, s_projectid;
--退出循环条件 (通过%NOTFOUND判断是否有值，有值执行操作，没有则退出循环)
exit when c_list%NOTFOUND;
select count(1) into v_setcount from EQUIPFREQUENCYSET eps where DELETED=0 and EQUIPID=s_equipid and TYPE=1 and rownum = 1;
if v_setcount>0 then
select * into emmodel from EQUIPFREQUENCYSET eps where DELETED=0 and EQUIPID=s_equipid and TYPE=1 and rownum = 1;
end if;
--频率任务设置存在
if v_setcount > 0 and emmodel.endtime>=SYSDATE and emmodel.begintime<=SYSDATE then
--查看次数和已发送的次数比较
select count(1) into v_sentescount from EQUIPSELFCHECK where DELETED=0 and EQUIPID=s_equipid and PROJECTID=s_projectid
and CREATETIME>=emmodel.begintime and CREATETIME<=emmodel.endtime and TYPE=1;
if v_sentescount<emmodel.frequency then
s_frequency:=emmodel.frequency - v_sentescount;
end if;
end if;
if v_setcount <= 0 or emmodel.endtime<SYSDATE or emmodel.begintime>SYSDATE then
--频率任务没有设置---频率任务设置过期了或者不在范围内-默认1月1次
select count(1) into v_escount from EQUIPSELFCHECK where DELETED=0 and EQUIPID=s_equipid and PROJECTID=s_projectid and CREATETIME>=trunc(sysdate, 'mm')and TYPE=1 ;
if v_tcount<=0 then
s_frequency:=1;
end if;
end if;
s_bl:=0;
while s_bl < s_frequency loop s_bl:=s_bl+1;
--新增主表
insert into EQUIPSELFCHECK
(CREATEDUSER,
CREATETIME,
EQUIPID,
PROJECTID,
BEGINTIME,
ENDTIME,
TYPE,
STATUS)
values
(1,
SYSDATE,
s_equipid,
s_projectid,
(trunc(sysdate, 'mm')+1/24),
(trunc(LAST_DAY(SYSDATE))+23/24),
1,
1);
select "SEQ_EQUIPSELFCHECK".currval into s_escid from dual;
for ep_row in ( select ep.id EPId from EquipPoint ep join POINTBASIS pb on ep.PointId=pb.id where ep.equipid=s_equipid and ep.Status=0 and ep.DELETED=0 and pb.ISSELFCHECK=1 )loop
--新增详情
insert into EQUIPSELFCHECKDETAIL
(CREATEDUSER,CREATETIME, ESID, EPID, SEVERITY, ELIGIBILITY)
values
(1,
to_timestamp(TO_CHAR(sys_extract_utc(systimestamp),'YYYY-MM-DD') || ' 00:00:00','yyyy-mm-dd hh24:mi:ss.ff'),
s_escid,
ep_row.epid,
1,
1
);
end loop;
end loop;
end loop;
--关闭游标
close c_list;
COMMIT;
s_msg := '定时创建自检任务';
DBMS_OUTPUT.PUT_LINE(s_msg || '成功' ||
to_char(CURRENT_DATE, 'yyyy-mm-dd'));
else
null;
end case;
--异常回滚
exception
when others then
rollback;
dbms_output.put_line(s_msg || '失败' ||
to_char(CURRENT_DATE, 'yyyy-mm-dd'));
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end EP_Task;
