ALTER proc p_alarm_status
as
declare
@HisStatus varchar(10),
@SenEquipNo varchar(50)
begin
--(1)取s_EnvHis表中最新的设备状态 更新设备状态 update dbo.s_senequip set sStatus=c.hisstatus,StatusDate=c.histime from
(
select distinct(a.SenEquipNo),a.histime,b.HisStatus from (select SenEquipNo,max(HisTime) as histime from dbo.s_EnvHis group by SenEquipNo) a
left join (select * from dbo.s_EnvHis) b
on a.SenEquipNo=b.SenEquipNo and a.histime=b.histime
) c ,
dbo.s_senequip d where d.SenEquipNo=c.SenEquipNo and isnull(StatusDate,'2010-01-01')<=c.histime --取s_EnergyHis表中最新的设备状态 更新设备状态
update dbo.s_senequip set sStatus=c.HisStatus,StatusDate=c.histime from
(
select distinct(a.SenEquipNo),a.histime,
case when b.HisStatus='normal' then '0' else '1' end as HisStatus
from (select SenEquipNo,max(HisTime) as histime from dbo.s_EnergyHis group by SenEquipNo) a
left join s_EnergyHis b
on a.SenEquipNo=b.SenEquipNo and a.histime=b.HisTime
) c ,
dbo.s_senequip d where d.SenEquipNo=c.SenEquipNo and isnull(StatusDate,'2010-01-01')<=c.histime --(2) 消警操作
delete from s_Alarm where SenEquipNo in (
select a.SenEquipNo from s_senequip a ,s_Alarm b where sStatus=0 and a.SenEquipNo=b.SenEquipNo and a.StatusDate>=b.AlarmTime
)
--(3) 新增告警信息
insert into s_Alarm
select StatusDate,'1','1','','',--convert(varchar(20),getdate()
SenEquipNo from s_senequip where SenEquipNo not in(select SenEquipNo from s_Alarm) and sStatus=1 --(4)
EXEC p_EnvHis_filter
EXEC p_EnergyHis_filter
end
as
declare
@HisStatus varchar(10),
@SenEquipNo varchar(50)
begin
--(1)取s_EnvHis表中最新的设备状态 更新设备状态 update dbo.s_senequip set sStatus=c.hisstatus,StatusDate=c.histime from
(
select distinct(a.SenEquipNo),a.histime,b.HisStatus from (select SenEquipNo,max(HisTime) as histime from dbo.s_EnvHis group by SenEquipNo) a
left join (select * from dbo.s_EnvHis) b
on a.SenEquipNo=b.SenEquipNo and a.histime=b.histime
) c ,
dbo.s_senequip d where d.SenEquipNo=c.SenEquipNo and isnull(StatusDate,'2010-01-01')<=c.histime --取s_EnergyHis表中最新的设备状态 更新设备状态
update dbo.s_senequip set sStatus=c.HisStatus,StatusDate=c.histime from
(
select distinct(a.SenEquipNo),a.histime,
case when b.HisStatus='normal' then '0' else '1' end as HisStatus
from (select SenEquipNo,max(HisTime) as histime from dbo.s_EnergyHis group by SenEquipNo) a
left join s_EnergyHis b
on a.SenEquipNo=b.SenEquipNo and a.histime=b.HisTime
) c ,
dbo.s_senequip d where d.SenEquipNo=c.SenEquipNo and isnull(StatusDate,'2010-01-01')<=c.histime --(2) 消警操作
delete from s_Alarm where SenEquipNo in (
select a.SenEquipNo from s_senequip a ,s_Alarm b where sStatus=0 and a.SenEquipNo=b.SenEquipNo and a.StatusDate>=b.AlarmTime
)
--(3) 新增告警信息
insert into s_Alarm
select StatusDate,'1','1','','',--convert(varchar(20),getdate()
SenEquipNo from s_senequip where SenEquipNo not in(select SenEquipNo from s_Alarm) and sStatus=1 --(4)
EXEC p_EnvHis_filter
EXEC p_EnergyHis_filter
end
解决方案 »
- 存储过程Insert为什么比PLSQL慢好多
- oracle截取字符串
- Oracle存储过程问题
- 10g怎样做增量备份啊?
- 求助。
- 求助oracle sql语句
- JDBC连接Oracle的问题。唉……
- Oracle 数据导入问题!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- root连接oracle 提示:ORA-12546: TNS:permission denied (AIX环境)
- 急急急!Oracle8.16+Win2000系统,安装后一切正常,重启机后出现ORA-01034错误,大侠帮忙!
- Enterprise Manager Console 连接数据库自动消失了。。。
- 急vc ado oracle锁表问题
我的该法:update S_SENEQUIP set (sStatus,StatusDate)=
(
select distinct(a.SenEquipNo),a.histime,b.HisStatus from (select SenEquipNo,max(HisTime) as histime from dbo.s_EnvHis group by SenEquipNo) a
left join (select * from dbo.s_EnvHis) b
on a.SenEquipNo=b.SenEquipNo and a.histime=b.histime
) where isnull(StatusDate,'2010-01-01')<=a.histime;
另外,你update 的set语句怎么好像有问题?更新两个字段但是后面的查询语句却有3个字段
--oracle中的关联更新,使用b表中的字段col1,col2更改a表中的字段col1,col2,关联字段是ID
UPDATE a
SET (a.col1, a.col2) =
(SELECT b.col1, b.col2 FROM b WHERE b.id = a.id)
WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id)
(
select a."histime",b."HisStatus" from (select max(S_ENVHIS."HisTime") as histime from S_ENVHIS group by S_ENVHIS."SenEquipNo") a
left join (select * from S_ENVHIS) b
on a."SenEquipNo"=b."SenEquipNo" and a."histime"=b."histime" where a."SenEquipNo" = S_SENEQUIP."SenEquipNo" and NVL(StatusDate,'2010-01-01')<=a.histime
) where exists (select 1 from (select max(S_ENVHIS."HisTime") as histime from S_ENVHIS group by S_ENVHIS."SenEquipNo") a
left join (select * from S_ENVHIS) b
on a."SenEquipNo"=b."SenEquipNo" and a."histime"=b."histime" where a."SenEquipNo" = d."SenEquipNo" and NVL(StatusDate,'2010-01-01')<=a.histime
);
换成这样还是搞不定,悲剧
各位老大指点一下