该过程是对原始采集表中数据(T_OV_MeterOrigValue,View_BuildCircuitTtem_V )进行清理然后逐时统计变化量存入相关表(T_EC_EnergyItemHourResult ) ,在代码标出的地方(标有????????)就跳出来了,原始数据少的时侯(1000)左右没问题大了就
跳出来了,难道是锁超时,是游标的问提,还是其他,咋办呢,帮忙呀,等着解决,不胜感激,
procedure [dbo].[Usp_hourV]
@DateTimeS datetime='2006-1-1 00:00:00',
@DateTimeE datetime='2006-3-1 00:00:00'AS/*--if object_id('tempdb..#epa') is not null
-- TRUNCATE TABLE #epa
-- ELSE
三点过滤原始数据
#OrigV过虑原始表
*/
create TABLE #OrigV
( --OrigValueID
BuildID VARCHAR(16),
MeterParamID VARCHAR(16),
CircuitID VARCHAR(16),
-- CircuitName VARCHAR(48),
-- MeterID
EnergyItemCode VARCHAR(16),
-- ChangeRate Numeric(18,4),
CollectTime datetime,
OrigValue Numeric(18,4),
EquValue Numeric(18,4)
) DECLARE @MeterParamID varchar(16)
DECLARE mc_cursor_ParamID CURSOR SCROLL static FOR
select distinct F_MeterParamID from dbo.T_OV_MeterOrigValue
where F_CollectTime >= @DateTimeS -- F_MeterParamID in('320105E001000601','320105F002003801' , '320102H001002701') AND
AND F_CollectTime <= @DateTimeE
OPEN mc_cursor_ParamID
FETCH NEXT FROM mc_cursor_ParamID INTO @MeterParamID
-----------------------
-----
WHILE @@FETCH_STATUS = 0 BEGIN
-----------------置基数
set @BaseHV = 0
set @BaseQV = 0-----------------
DECLARE mc_cursor_OrigV CURSOR static FOR
select F_BuildID,F_MeterParamID,F_CircuitID, F_CircuitName
,F_EnergyItemCode,F_ChangeRate,F_CollectTime, F_OrigValue
from dbo.View_BuildCircuitTtem_V
where F_MeterParamID=@MeterParamID ORDER BY F_CollectTime OPEN mc_cursor_OrigV
FETCH NEXT FROM mc_cursor_OrigV
INTO @BuildID ,@MeterParamID,@CirID,@CircuitName,
@ItemCode,@ChangeRate,@StartHa,@HourVa
if @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM mc_cursor_OrigV
INTO @BuildID ,@MeterParamID,@CirID,@CircuitName,
@ItemCode,@ChangeRate,@StartHb,@HourVb
end
else return
if @@FETCH_STATUS = 0
begin
FETCH NEXT FROM mc_cursor_OrigV
INTO @BuildID ,@MeterParamID,@CirID,@CircuitName,
@ItemCode,@ChangeRate,@StartHc,@HourVc
end
else return
-----------------------
--对一个采集点数据进行清理并存入临时表
WHILE @@FETCH_STATUS = 0
begin ------??????大致是从该循环中跳出的-------------
--------------------
if (@HourVa <= @HourVb) and (@HourVb <= @HourVc)
begin
set @HourEv = @HourVb * @ChangeRate +@BaseQV
insert into #OrigV ( [BuildID],[MeterParamID], [CircuitID],[EnergyItemCode],[CollectTime], [OrigValue],[EquValue] )
VALUES ( @BuildID ,@MeterParamID,@CirID,@ItemCode ,@StartHb,@HourVb+@BaseHV,@HourEv)
--A=B.B=C,C+NEXT
PRINT 'h>>'+ @BuildID +','+@MeterParamID+','+convert(varchar(16), @StartHb,120 )
set @HourVa = @HourVb
set @HourVb = @HourVC
set @StartHb = @StartHc
FETCH NEXT FROM mc_cursor_OrigV
INTO @BuildID ,@MeterParamID,@CirID,@CircuitName,
@ItemCode,@ChangeRate,@StartHc,@HourVc
PRINT 'q<<'+ @BuildID +','+@MeterParamID+','+convert(varchar(16) ,@StartHb,120 )
---
end --
.............
跳出来了,难道是锁超时,是游标的问提,还是其他,咋办呢,帮忙呀,等着解决,不胜感激,
procedure [dbo].[Usp_hourV]
@DateTimeS datetime='2006-1-1 00:00:00',
@DateTimeE datetime='2006-3-1 00:00:00'AS/*--if object_id('tempdb..#epa') is not null
-- TRUNCATE TABLE #epa
-- ELSE
三点过滤原始数据
#OrigV过虑原始表
*/
create TABLE #OrigV
( --OrigValueID
BuildID VARCHAR(16),
MeterParamID VARCHAR(16),
CircuitID VARCHAR(16),
-- CircuitName VARCHAR(48),
-- MeterID
EnergyItemCode VARCHAR(16),
-- ChangeRate Numeric(18,4),
CollectTime datetime,
OrigValue Numeric(18,4),
EquValue Numeric(18,4)
) DECLARE @MeterParamID varchar(16)
DECLARE mc_cursor_ParamID CURSOR SCROLL static FOR
select distinct F_MeterParamID from dbo.T_OV_MeterOrigValue
where F_CollectTime >= @DateTimeS -- F_MeterParamID in('320105E001000601','320105F002003801' , '320102H001002701') AND
AND F_CollectTime <= @DateTimeE
OPEN mc_cursor_ParamID
FETCH NEXT FROM mc_cursor_ParamID INTO @MeterParamID
-----------------------
-----
WHILE @@FETCH_STATUS = 0 BEGIN
-----------------置基数
set @BaseHV = 0
set @BaseQV = 0-----------------
DECLARE mc_cursor_OrigV CURSOR static FOR
select F_BuildID,F_MeterParamID,F_CircuitID, F_CircuitName
,F_EnergyItemCode,F_ChangeRate,F_CollectTime, F_OrigValue
from dbo.View_BuildCircuitTtem_V
where F_MeterParamID=@MeterParamID ORDER BY F_CollectTime OPEN mc_cursor_OrigV
FETCH NEXT FROM mc_cursor_OrigV
INTO @BuildID ,@MeterParamID,@CirID,@CircuitName,
@ItemCode,@ChangeRate,@StartHa,@HourVa
if @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM mc_cursor_OrigV
INTO @BuildID ,@MeterParamID,@CirID,@CircuitName,
@ItemCode,@ChangeRate,@StartHb,@HourVb
end
else return
if @@FETCH_STATUS = 0
begin
FETCH NEXT FROM mc_cursor_OrigV
INTO @BuildID ,@MeterParamID,@CirID,@CircuitName,
@ItemCode,@ChangeRate,@StartHc,@HourVc
end
else return
-----------------------
--对一个采集点数据进行清理并存入临时表
WHILE @@FETCH_STATUS = 0
begin ------??????大致是从该循环中跳出的-------------
--------------------
if (@HourVa <= @HourVb) and (@HourVb <= @HourVc)
begin
set @HourEv = @HourVb * @ChangeRate +@BaseQV
insert into #OrigV ( [BuildID],[MeterParamID], [CircuitID],[EnergyItemCode],[CollectTime], [OrigValue],[EquValue] )
VALUES ( @BuildID ,@MeterParamID,@CirID,@ItemCode ,@StartHb,@HourVb+@BaseHV,@HourEv)
--A=B.B=C,C+NEXT
PRINT 'h>>'+ @BuildID +','+@MeterParamID+','+convert(varchar(16), @StartHb,120 )
set @HourVa = @HourVb
set @HourVb = @HourVC
set @StartHb = @StartHc
FETCH NEXT FROM mc_cursor_OrigV
INTO @BuildID ,@MeterParamID,@CirID,@CircuitName,
@ItemCode,@ChangeRate,@StartHc,@HourVc
PRINT 'q<<'+ @BuildID +','+@MeterParamID+','+convert(varchar(16) ,@StartHb,120 )
---
end --
.............
只见RETURN,怎么没见你销毁游标