循环计算”时、日、月、年“数据的方法思路
1、使用游标实现跨数据库导数据
参考存储过程：
ALTER proc [dbo].[Nsp_SiPingElectricityData]
as
declare @DANo Char(32)
declare @DATime Datetime
declare @LogTime Datetime
declare @MeterType Char(4)
declare @MeterNo Char(20)
declare @Qty Decimal(18,6)
declare @tagname nvarchar(50)
declare @sql nvarchar(800)
declare convertCursor cursor for
select replace(replace(replace(replace(convert(nvarchar(25),getdate(),121),'-',''),' ',''),':',''),'.',''),
b.tagname,b.PValue ,b.date_time a,b.date_time ,'EM' ,bb.meterno
from openrowset('SQLOLEDB','10.30.16.182';'sa';'tjdx',tj_siping2.dbo.Substation_siping) b,SiPingElectricityMeter bb
where
b.PValue>0
and
b.tagname=bb.TagNameNew
and
(b.label is null or b.label=0)
and b.Date_time is not null
order by b.Date_time asc
open convertCursor
fetch next from convertCursor into @DANo,@tagname,@QTY,@DATime,@LogTime,@MeterType,@meterno
while @@FETCH_STATUS=0
begin
begin
insert into SiPingElectricityData(DANo,DATime,LogTime,MeterType,Qty,Qty01,meterno,tagname) values(@DANo,@DATime,@LogTime,@MeterType,@Qty,@Qty,@meterno,@tagname)
begin
set @sql='Update openrowset(''sqloledb'',''10.30.16.182'';''sa'';''tjdx'',tj_siping2.dbo.Substation_siping) set label=1 where tagname='''+@tagname+''' and date_time='''+convert(nvarchar(25),@DATime,21)+''''
exec (@sql)
end
end
fetch next from convertCursor into @DANo,@tagname,@QTY,@DATime,@LogTime,@MeterType,@meterno
end
close convertCursor
deallocate convertCursor
set nocount on
2、使用游标从表中循环读取数据，然后进行逐条处理
参考存储过程：
USE [CloudDatasCenter]
GO
/****** Object: StoredProcedure [dbo].[Usp_GetTanHouse_GSHP_Solar_DatasCompute] Script Date: 07/14/2014 15:18:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Usp_GetTanHouse_GSHP_Solar_DatasCompute]
as
declare @DANo nvarchar(50)
declare @DATime datetime
declare @LogTime datetime
declare @MeterNo char(20)
declare @MeterType char(4)
declare @Unit char(10)
declare @RTQty decimal(18, 6)
declare @RTQtyOlder decimal(18, 6)
declare @ErrorFlag int
Declare @Collect int
Declare @IncreaseQty Decimal(18,6)
Declare @IncreaseQtyOlder Decimal(18,6)
declare @Qty decimal(18, 6)
declare @sql nvarchar(800)
declare @j int
declare convertCursor cursor for
select top 10000 replace(replace(replace(replace(convert(nvarchar(25),getdate(),121),'-',''),' ',''),':',''),'.','') DANo,thgsd.DATime, thgsd.LogTime,
thgsd.MeterNo,m.MeterType, thgsd.Qty,m.Unit
from TanHouse_GSHP_Solar_Datas thgsd left join meter m on thgsd.MeterNo=m.MeterNo
where m.Usable=1 and m.ProjectNo='TanHouse'
and thgsd.DATime is not null
and thgsd.lable=0
--暂时排除为0数据 2011-12-08
--and qty>='0'
and thgsd.Qty>0
order by thgsd.DATime asc
open convertCursor
fetch next from convertCursor into @DANo,@DATime,@LogTime,@MeterNo,@MeterType,@Qty,@Unit
while @@FETCH_STATUS=0
begin
begin
print CONVERT(varchar(100), @DATime, 121)
print @MeterNo
print @Qty
select @RTQty=RTQty,@RTQtyOlder=RTQtyOlder,@ErrorFlag=ErrorFlag,@Collect=Collect from Meter where MeterNo=@MeterNo
if(@Collect=1)
begin
If IsNull(@Qty,0) = 0
Begin
Set @Qty = 0;
End
If IsNull(@RTQty,0) = 0
Begin
Set @RTQty = 0;
End
If IsNull(@RTQtyOlder,0) = 0
Begin
Set @RTQtyOlder = 0;
End
if @ErrorFlag=0
begin
--print '@ErrorFlag 0';
--print @ErrorFlag
--print '@Qty @RTQty';
--print @Qty
--print @RTQty
set @IncreaseQty=@Qty-@RTQty;
end
else
begin
--print '@ErrorFlag 1';
--print @ErrorFlag
--print '@Qty @RTQty @RTQtyOlder';
-- print @Qty
--print @RTQty
--print @RTQtyOlder
set @IncreaseQty=@Qty-@RTQty;
set @IncreaseQtyOlder=@Qty-@RTQtyOlder;
if @IncreaseQtyOlder>=0
begin
set @IncreaseQty=@IncreaseQtyOlder
end
end
if @IncreaseQty>=0 ---如果增量不为负数
begin
--print '@IncreaseQty>=0';
--print @IncreaseQty
update Meter set RTQty=@Qty,RTQtyOlder=@Qty,ErrorFlag=0 where MeterNo=@MeterNo
exec sp_DataComputer_TanHouse_GSHP_Solar_Datas @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@IncreaseQty,@Unit;
--print CONVERT(varchar(100), GETDATE(), 121)+'计算时间'
--print 'RTQty ,RTQtyOlder,ErrorFlag';
select @RTQty=RTQty, @RTQtyOlder=RTQtyOlder,@ErrorFlag=ErrorFlag from meter where MeterNo=@MeterNo
--print @RTQty
--print @RTQtyOlder
--print @ErrorFlag
end
else
begin
--print '@IncreaseQty<0';
--print @IncreaseQty
update Meter set RTQty=@Qty,ErrorFlag=1 where MeterNo=@MeterNo
--print 'RTQty ,RTQtyOlder,ErrorFlag';
select @RTQty=RTQty, @RTQtyOlder=RTQtyOlder,@ErrorFlag=ErrorFlag from meter where MeterNo=@MeterNo
--print @RTQty
-- print @RTQtyOlder
-- print @ErrorFlag
end
end --IfEnd
else
begin
Declare @DAYear Char(4);
Declare @DAMonth Char(2);
Declare @DADay Char(2);
Declare @DAHour Char(2);
Set @DAYear = Substring(Ltrim(Rtrim(Convert(varchar(10),@DATime,120))),1,4);
Set @DAMonth = Substring(Ltrim(Rtrim(Convert(varchar(10),@DATime,120))),6,2);
Set @DADay = Substring(Ltrim(Rtrim(Convert(varchar(10),@DATime,120))),9,2);
--Set @DADay = Ltrim(Rtrim(Convert(varchar(10),@DATime,112)));
Set @DAHour = Substring(Ltrim(Rtrim(Convert(varchar(10),@DATime,114))),1,2);
--print @DADay
Insert TanHouseDatasByRealTime(DANo,DATime,LogTime,MeterType,MeterNo,Qty,DADay,DAHour)
Select @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty,(@DAYear+@DAMonth+@DADay),@DAHour;
--print CONVERT(varchar(100), GETDATE(), 121)+'计算时间'
end --ElseEnd
update TanHouse_GSHP_Solar_Datas set lable=1 where DATime=@DATime and meterno=@meterno;
-- print '--------汇总成功----------'
--print 'DATime='+convert(nvarchar(25),@DATime,120)
end
fetch next from convertCursor into @DANo,@DATime,@LogTime,@MeterNo,@MeterType,@Qty,@Unit
end
close convertCursor
deallocate convertCursor
set nocount on
3、使用事务，进行“时，日、月、年”的计算
参考存储过程：
USE [CloudDatasCenter]
GO
/****** Object: StoredProcedure [dbo].[sp_DataComputer_TanHouse_GSHP_Solar_Datas] Script Date: 07/14/2014 15:18:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_DataComputer_TanHouse_GSHP_Solar_Datas]
@DANo Char(32),--20110112182345207插入时间
@DATime Datetime,--采集时间
@LogTime Datetime,--更新时间
@MeterType Char(4),--表具类型
@MeterNo Char(20),--表具号码
@Qty Decimal(18,6),--数值
@Unit Char(4),--单位
@outputpar int = 0 output--输出返回值
as
--带事务存储过程模板
begin
-- --print @MeterNo
-- Declare @Collect int;
-- select @Collect=Collect from Meter where MeterNo=@MeterNo
-- --判断是否进行汇总计算
-- --print @Collect
----如果需要计算
--if(@Collect=1)
--begin
--开始事务，开始数据计算-------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION
SAVE TRANSACTION sp_Datacomputer_TRANS
--事务内容,计算时日月数据.
Declare @DAYear Char(4);
Declare @DAMonth Char(2);
Declare @DADay Char(2);
Declare @DAHour Char(2);
Declare @RowCnt_Year Int;
Declare @RowCnt_Month Int;
Declare @RowCnt_Day Int;
Declare @RowCnt_Hour Int;
Set @DAYear = Substring(Ltrim(Rtrim(Convert(varchar(10),@DATime,120))),1,4);
Set @DAMonth = Substring(Ltrim(Rtrim(Convert(varchar(10),@DATime,120))),6,2);
Set @DADay = Substring(Ltrim(Rtrim(Convert(varchar(10),@DATime,120))),9,2);
Set @DAHour = Substring(Ltrim(Rtrim(Convert(varchar(10),@DATime,114))),1,2);
--print @DAHour
Set @RowCnt_Year = (Select Count(*) From TanHouseEnergyDataSumByYear Where MeterNo = @MeterNo
And MeterType = @MeterType And DAYear = @DAYear);
If IsNull(@RowCnt_Year,0) = 0
Begin
Set @RowCnt_Year = 0;
End
Set @RowCnt_Month = (Select Count(*) From TanHouseEnergyDataSumByMonth Where MeterNo = @MeterNo
And MeterType = @MeterType And DAYear = @DAYear And DAMonth = @DAMonth);
If IsNull(@RowCnt_Month,0) = 0
Begin
Set @RowCnt_Month = 0;
End
Set @RowCnt_Day = (Select Count(*) From TanHouseEnergyDataSumByDay Where MeterNo = @MeterNo
And MeterType = @MeterType And DAYear = @DAYear And DAMonth = @DAMonth And DADay = @DADay );
If IsNull(@RowCnt_Day,0) = 0
Begin
Set @RowCnt_Day = 0;
End
Set @RowCnt_Hour = (Select Count(*) From TanHouseEnergyDataSumByHour Where MeterNo = @MeterNo
And MeterType = @MeterType And DADay = IsNull(@DAYear,'') + IsNull(@DAMonth,'') + IsNull(@DADay,'')
And DAHour = @DAHour);
If IsNull(@RowCnt_Hour,0) = 0
Begin
Set @RowCnt_Hour = 0;
End
if (@@error <> 0)
begin
goto LABROLLBACK
end
--begin try
--begin tran
If @RowCnt_Year > 0
Begin
Update TanHouseEnergyDataSumByYear Set SumQty = SumQty + @Qty
Where MeterNo = @MeterNo And MeterType = @MeterType And DAYear = @DAYear ;
End
Else
Begin
Insert TanHouseEnergyDataSumByYear(MeterNo,MeterType,DAYear,SumQty,Unit)
Select @MeterNo,@MeterType,@DAYear,@Qty,@Unit;
End
if (@@error <> 0)
begin
goto LABROLLBACK
end
If @RowCnt_Month > 0
Begin
Update TanHouseEnergyDataSumByMonth Set SumQty = SumQty + @Qty
Where MeterNo = @MeterNo And MeterType = @MeterType And DAYear = @DAYear And DAMonth = @DAMonth;
End
Else
Begin
Insert TanHouseEnergyDataSumByMonth(MeterNo,MeterType,DAYear,DAMonth,SumQty,Unit)
Select @MeterNo,@MeterType,@DAYear,@DAMonth,@Qty,@Unit;
End
if (@@error <> 0)
begin
goto LABROLLBACK
end
If @RowCnt_Day > 0
Begin
Update TanHouseEnergyDataSumByDay Set SumQty = SumQty + @Qty
Where MeterNo = @MeterNo And MeterType = @MeterType And DAYear = @DAYear And DAMonth = @DAMonth And DADay = @DADay;
End
Else
Begin
Insert TanHouseEnergyDataSumByDay(MeterNo,MeterType,DAYear,DAMonth,DADay,SumQty,Unit)
Select @MeterNo,@MeterType,@DAYear,@DAMonth,@DADay,@Qty,@Unit;
End
if (@@error <> 0)
begin
goto LABROLLBACK
end
If @RowCnt_Hour > 0
Begin
Update TanHouseEnergyDataSumByHour Set SumQty = SumQty + @Qty
Where MeterNo = @MeterNo And MeterType = @MeterType And DADay = IsNull(@DAYear,'') + IsNull(@DAMonth,'') + IsNull(@DADay,'')
And DAHour = @DAHour ;
End
Else
Begin
Insert TanHouseEnergyDataSumByHour(MeterNo,MeterType,DADay,DAHour,SumQty,Unit)
Select @MeterNo,@MeterType,IsNull(@DAYear,'') + IsNull(@DAMonth,'') + IsNull(@DADay,''),@DAHour,@Qty,@Unit;
End
----------
--发生错误回滚事务
if (@@error <> 0)
begin
goto LABROLLBACK
end
--提交事务
LABCOMMIT:
if (@@error = 0)
begin
COMMIT TRANSACTION
return(0)
end
--以下回滚事务
LABROLLBACK:
begin
ROLLBACK TRANSACTION sp_Datacomputer_TRANS
return @@error
end
--进行数据计算的事务结束------------------------------------------------------------------------------------------------------
--end --If End
--else
--begin
-- Insert TanHouseDatasByRealTime(DANo,DATime,LogTime,MeterType,MeterNo,Qty)
-- Select @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty;
--end --Else End
end --存储过程结束End
带存储过程的事务模板：
ALTER procedure [dbo].[sp_TRANSACTION_Templater]
@tblname nvarchar(776),
@flagc varchar(10)=null,
@indname sysname=null
as
--带事务存储过程模板
begin
--开始事务
BEGIN TRANSACTION
SAVE TRANSACTION sp_Datacomputer_TRANS
--事务内容
----------
--发生错误回滚事务
if (@@error <> 0)
begin
goto LABROLLBACK
end
--提交事务
LABCOMMIT:
if (@@error = 0)
begin
COMMIT TRANSACTION
return(0)
end
--以下回滚事务
LABROLLBACK:
begin
ROLLBACK TRANSACTION sp_Datacomputer_TRANS
return @@error
end
end
