demo1
if exists (select * from dbo.sysobjects where id = object_id('update_BuildGroupBaseInfo') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
-- 删除存储过程
drop procedure update_BuildGroupBaseInfo
GO
CREATE PROCEDURE update_BuildGroupBaseInfo
--输入参数
--@targetID int,
--输出参数
--@text varchar(500) output
AS
BEGIN
--初始化
DECLARE @n INT=1
--定义游标
declare cursor_test cursor
for
--查询出一个结果集,注意,这里的*有几个变量,在下面就要declare几个变量,并且顺序要一一对应
select G_ID,F_BuildGroupName from T_BD_BuildGroupBaseInfo;
--与上面对应
declare @gid int, @name varchar(50);
--打开游标
open cursor_test
fetch next from cursor_test into @gid, @name
--fetch到数据时进行的操作
WHILE @@FETCH_STATUS = 0
begin
--这里面可以写具体的操作逻辑,例如我这里就是拼接结果赋值给@text
UPDATE T_BD_BuildGroupBaseInfo set F_BuildGroupName = '测试单位'+ CONVERT(VARCHAR(100),@n) where G_ID=@gid;
set @n = @n +1;
fetch next from cursor_test into @gid,@name
end
--关闭游标
close cursor_test
--删除游标
deallocate cursor_test
END
GO
demo2 能耗报警:
-- 能耗报警存储过程
if exists (select * from dbo.sysobjects where id = object_id('proc_warning') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
-- 删除存储过程
drop procedure proc_warning
GO
CREATE PROCEDURE proc_warning
--输入参数 yyyy-MM
@yearMonth VARCHAR(10)
--输出参数
--@text varchar(500) output
AS
BEGIN
--定义 查询T_EnergyColumn 表的年月参数
DECLARE @_year VARCHAR(4);-- 年
DECLARE @_month VARCHAR(2);-- 月
DECLARE @flag CHAR;-- @yearMonth 的第六位 是否为'0'
IF (@yearMonth = '') BEGIN
set @yearMonth = CONVERT(VARCHAR(7),DATEADD(MONTH, -1, GETDATE()),120);
END
set @_year = SUBSTRING(@yearMonth, 1, 4);
PRINT @_year
set @flag = SUBSTRING(@yearMonth, 6, 1);
PRINT @flag
IF @flag ='0' BEGIN
SET @_month = SUBSTRING(@yearMonth, 7, 1) ;
END
ELSE BEGIN
SET @_month = SUBSTRING(@yearMonth, 6, 2) ;
END
PRINT @_month
--定义单位游标
declare cursor_group cursor for select DISTINCT F_BuildGroupID from T_BD_BuildGroupBaseInfo;
--定义单位编码与上面对应
declare @GroupID varchar(50);
--打开单位游标
open cursor_group
fetch next from cursor_group into @GroupID
--fetch到数据时进行的操作
WHILE @@FETCH_STATUS = 0
begin
PRINT 'GroupID:'+@GroupID;
PRINT 'yearMonth:'+@yearMonth;
--这里面可以写具体的操作逻辑
-- 输入参数
-- 年月:yearMonth 格式 yyyy-MM
-- 能耗编码: F_EnergyItemCode 在常规用能电耗范围(01A00、01B00、01C00)
-- 查询表: T_EC_OrganMonthResult
-- 返回:sum(F_DayValue)
DECLARE @eValue DECIMAL(18,4); -- 总能耗
DECLARE @area DECIMAL(18,4); -- 总面积
DECLARE @eBase DECIMAL(18,4); -- 能耗基准值
DECLARE @eOver DECIMAL(18,4);-- 能耗约束值
DECLARE @eValueAvg DECIMAL(18,4); -- 当月单位面积耗电量
-- 查询总能耗
set @eValue = (SELECT CONVERT(DECIMAL(18,4),ISNULL(sum(F_MonthValue),0)) FROM T_EC_OrganMonthResult WHERE F_OrganID = @GroupID
AND F_EnergyItemCode IN('01A00','01B00','01C00')
AND CONVERT(varchar(7), F_startMonth, 120)= @yearMonth) ;
PRINT 'eValue:'+CONVERT(VARCHAR(100),@eValue);
-- 查询总面积
set @area = (SELECT CONVERT(DECIMAL(18,4),ISNULL(F_TotalArea, 0)) FROM T_BD_BuildGroupBaseInfo WHERE F_BuildGroupID = @GroupID ) ;
PRINT 'area:'+CONVERT(VARCHAR(100),@area);
-- 查询某单位月能耗定额
set @eBase= (SELECT CONVERT(DECIMAL(18,4),ISNULL(F_Value2, 0)) FROM T_EnergyColumn where F_GroupID = @GroupID AND F_Year =@_year AND F_DATE = @_month);
set @eOver= (SELECT CONVERT(DECIMAL(18,4),ISNULL(F_Value1, 0)) FROM T_EnergyColumn where F_GroupID = @GroupID AND F_Year =@_year AND F_DATE = @_month);
PRINT 'eBase:'+CONVERT(VARCHAR(100),@eBase);
PRINT 'eOver:'+CONVERT(VARCHAR(100),@eOver);
-- 判断是否超定额
set @eValueAvg = Round(@eValue/@area,2);
PRINT 'eValueAvg:'+CONVERT(VARCHAR(100),@eValueAvg);
PRINT '===========================================';
IF (@eValueAvg>@eOver) BEGIN -- 超过约束值
PRINT '超过约束值';
-- 执行插入 T_AlarmInfo 表 ,预警信息
MERGE INTO T_AlarmInfo A
USING (SELECT @GroupID as group_id,'overrate' as alarm_type,CONVERT(datetime,@yearMonth+'-02',120) as alarm_time )B
ON (A.F_OrganID = B.group_id AND A.F_AlarmType = B.alarm_type AND A.F_AlarmTime = B.alarm_time )
WHEN MATCHED THEN
UPDATE SET A.F_AlarmInfo = '当月单位面积耗电量['+CONVERT(VARCHAR(100),@eValueAvg)+']超出月定额标准约束值['+CONVERT(VARCHAR(100),@eOver)+']'
WHEN NOT MATCHED THEN
INSERT (F_OrganID,F_AlarmType,F_AlarmInfo,F_AlarmTime,F_AlarmStatus,F_OperTime,F_OperUser)
VALUES (@GroupID,B.alarm_type,'当月单位面积耗电量['+CONVERT(VARCHAR(100),@eValueAvg)+']超出月定额标准约束值['+CONVERT(VARCHAR(100),@eOver)+']',B.alarm_time,0,GETDATE(),'proc');
END
ELSE BEGIN
IF (@eValueAvg>@eBase) BEGIN -- 超过基准值
PRINT '超过基准值';
-- 执行插入 T_AlarmInfo 表 ,预警信息
MERGE INTO T_AlarmInfo A
USING (SELECT @GroupID as group_id,'overrate' as alarm_type,CONVERT(datetime,@yearMonth+'-02',120) as alarm_time )B
ON (A.F_OrganID = B.group_id AND A.F_AlarmType = B.alarm_type AND A.F_AlarmTime = B.alarm_time )
WHEN MATCHED THEN
UPDATE SET A.F_AlarmInfo = '当月单位面积耗电量['+CONVERT(VARCHAR(100),@eValueAvg)+']超出月定额标准基准值['+CONVERT(VARCHAR(100),@eOver)+']'
WHEN NOT MATCHED THEN
INSERT (F_OrganID,F_AlarmType,F_AlarmInfo,F_AlarmTime,F_AlarmStatus,F_OperTime,F_OperUser)
VALUES (@GroupID,B.alarm_type,'当月单位面积耗电量['+CONVERT(VARCHAR(100),@eValueAvg)+']超出月定额标准基准值['+CONVERT(VARCHAR(100),@eOver)+']',B.alarm_time,0,GETDATE(),'proc');
END
END
fetch next from cursor_group into @GroupID
end
--关闭游标
close cursor_group
--删除游标
deallocate cursor_group
END
GO
demo3: 能耗预警
-- 能耗预警存储过程
-- 算法逻辑:
-- 当@dateType = 0 时
-- 每月指定日期(默认月中) 格式yyyy-MM-dd查询截止到当月指定日期累计日能耗(查询能耗日表)判断 是否超出月定额,如果超出则给予预警,并求出超出月定额百分比。
-- 当@dateType = 1 时
-- 每月月初(默认2号) 格式yyyy-MM-dd 查询截止到当前指定日期累计月能耗(查询月能耗表) 判断 是否超出累计月定额值,如果超出则给予预警,并求出超出累计定额百分比。
if exists (select * from dbo.sysobjects where id = object_id('proc_prewarning') and OBJECTPROPERTY(id, 'IsProcedure') = 1)
-- 删除存储过程
drop procedure proc_prewarning
GO
CREATE PROCEDURE proc_prewarning
-- 日期类型:yyyy-MM-dd
@dateType CHAR(1),
--输入参数 yyyy-MM-dd
@inputDate VARCHAR(10)
--输出参数
--@text varchar(500) output
AS
BEGIN
--定义 查询T_EnergyColumn 表的年月参数
DECLARE @_year VARCHAR(4);-- 年
DECLARE @_month VARCHAR(2);-- 月
DECLARE @flag CHAR;-- @inputDate 的第六位 是否为'0'
DECLARE @currentDate VARCHAR(20) = GETDATE();-- 当前日期
--定义单位编码
DECLARE @GroupID varchar(50);
--定义单位游标
DECLARE cursor_group cursor for select DISTINCT F_BuildGroupID from T_BD_BuildGroupBaseInfo;
DECLARE @eValue DECIMAL(18,2); -- 总能耗
DECLARE @area DECIMAL(18,2); -- 总面积
DECLARE @eBase DECIMAL(18,2); -- 能耗基准值
DECLARE @eOver DECIMAL(18,2);-- 能耗约束值
DECLARE @eValueAvg DECIMAL(18,2); -- 当月单位面积耗电量
DECLARE @overRate VARCHAR(10); -- 超出月定额百分比
-- @dateType = 0
IF @dateType = 0 BEGIN
IF (@inputDate = '') BEGIN
set @inputDate = DATEFROMPARTS(year(@currentDate),month(@currentDate),15) ; -- 默认月中
END
set @_year = SUBSTRING(@inputDate, 1, 4);
PRINT @_year
set @flag = SUBSTRING(@inputDate, 6, 1);
PRINT @flag
IF @flag ='0' BEGIN
SET @_month = SUBSTRING(@inputDate, 7, 1) ;
END
ELSE BEGIN
SET @_month = SUBSTRING(@inputDate, 6, 2) ;
END
PRINT @_month
--打开单位游标
open cursor_group
fetch next from cursor_group into @GroupID
--fetch到数据时进行的操作
WHILE @@FETCH_STATUS = 0
begin
PRINT 'GroupID:'+@GroupID;
PRINT 'inputDate:'+@inputDate;
--这里面可以写具体的操作逻辑
-- 输入参数
-- 年月日:inputDate 格式 yyyy-MM-dd
-- 能耗编码: F_EnergyItemCode 在常规用能电耗范围(01A00、01B00、01C00)
-- 查询表: T_EC_OrganDayResult
-- 返回:sum(F_DayValue)
-- 查询总能耗
set @eValue = (SELECT CONVERT(DECIMAL(18,2),ISNULL(sum(F_DayValue),0)) FROM T_EC_OrganDayResult WHERE F_OrganID = @GroupID
AND F_EnergyItemCode IN('01A00','01B00','01C00')
AND CONVERT(varchar(10), F_StartDay, 120)>= SUBSTRING(@inputDate, 1, 7)+'-01'
AND CONVERT(varchar(10), F_StartDay, 120)<= @inputDate );
PRINT 'eValue:'+CONVERT(VARCHAR(100),@eValue);
-- 查询总面积
set @area = (SELECT CONVERT(DECIMAL(18,2),ISNULL(F_TotalArea, 0)) FROM T_BD_BuildGroupBaseInfo WHERE F_BuildGroupID = @GroupID );
PRINT 'area:'+CONVERT(VARCHAR(100),@area);
-- 查询某单位月能耗定额
set @eBase= (SELECT CONVERT(DECIMAL(18,2),ISNULL(F_Value2, 0)) FROM T_EnergyColumn where F_GroupID = @GroupID AND F_Year =@_year AND F_DATE = @_month);
set @eOver= (SELECT CONVERT(DECIMAL(18,2),ISNULL(F_Value1, 0)) FROM T_EnergyColumn where F_GroupID = @GroupID AND F_Year =@_year AND F_DATE = @_month);
PRINT 'eBase:'+CONVERT(VARCHAR(100),@eBase);
PRINT 'eOver:'+CONVERT(VARCHAR(100),@eOver);
-- 判断是否超定额
set @eValueAvg = Round(@eValue/@area,2);
PRINT 'eValueAvg:'+CONVERT(VARCHAR(100),@eValueAvg);
PRINT '===========================================';
IF (@eValueAvg>@eOver) BEGIN -- 超过约束值
PRINT '超过约束值';
SET @overRate = CONVERT(VARCHAR(100),CONVERT(DECIMAL(18,2),(@eValueAvg-@eOver)/@eOver)*100)+'%';
PRINT '超出约束值:'+@overRate
-- 执行插入 T_DE_WarnInfo 表 ,预警信息
MERGE INTO T_DE_WarnInfo A
USING (SELECT @GroupID as group_id,'1' as warn_type,@currentDate as warn_time )B
ON (A.F_EprID = B.group_id AND A.F_WarnType = B.warn_type AND A.F_WarnTime = B.warn_time )
WHEN MATCHED THEN
UPDATE SET A.F_WarnContent = '截至'+@inputDate+'本月单位面积用电量['+CONVERT(VARCHAR(10),@eValueAvg)+'kWh/㎡],超出定额标准约束值['+CONVERT(VARCHAR(10),@eOver)+'kWh/㎡]的'+@overRate
WHEN NOT MATCHED THEN
INSERT (F_EprID,F_WarnTime,F_WarnDate,F_WarnType,F_WarnContent,F_WarnStatus)
VALUES (@GroupID,B.warn_time,@inputDate,'1','截至'+@inputDate+'本月单位面积常规用电量['+CONVERT(VARCHAR(10),@eValueAvg)+'kWh/㎡],超出定额标准约束值['+CONVERT(VARCHAR(10),@eOver)+'kWh/㎡]的'+@overRate,'0');
END
--
ELSE BEGIN
IF (@eValueAvg>@eBase) BEGIN -- 超过基准值
PRINT '超过基准值';
SET @overRate = CONVERT(VARCHAR(100),CONVERT(DECIMAL(18,2),(@eValueAvg-@eBase)/@eBase)*100)+'%';
PRINT '超出基准值:'+@overRate
-- 执行插入 T_DE_WarnInfo 表 ,预警信息
MERGE INTO T_DE_WarnInfo A
USING (SELECT @GroupID as group_id,'1' as warn_type,@currentDate as warn_time )B
ON (A.F_EprID = B.group_id AND A.F_WarnType = B.warn_type AND A.F_WarnTime = B.warn_time )
WHEN MATCHED THEN
UPDATE SET A.F_WarnContent = '截至'+@inputDate+'本月单位面积用电量['+CONVERT(VARCHAR(10),@eValueAvg)+'kWh/㎡],超出定额标准基准值['+CONVERT(VARCHAR(10),@eBase)+'kWh/㎡]的'+@overRate
WHEN NOT MATCHED THEN
INSERT (F_EprID,F_WarnTime,F_WarnDate,F_WarnType,F_WarnContent,F_WarnStatus)
VALUES (@GroupID,B.warn_time,@inputDate,'1','截至'+@inputDate+'本月单位面积常规用电量['+CONVERT(VARCHAR(10),@eValueAvg)+'kWh/㎡],超出定额标准基准值['+CONVERT(VARCHAR(10),@eBase)+'kWh/㎡]的'+@overRate,'0');
END
END
fetch next from cursor_group into @GroupID
end
--关闭游标
close cursor_group
--删除游标
deallocate cursor_group
END
-- @dateType = 1
ELSE BEGIN
--定义 查询T_EnergyColumn 表的年月参数
-- DECLARE @_year VARCHAR(4);-- 年
-- DECLARE @_month VARCHAR(2);-- 月
-- DECLARE @flag CHAR;-- @inputDate 的第六位 是否为'0'
-- DECLARE @currentDate VARCHAR(20) = GETDATE();-- 当前日期
IF (@inputDate = '') BEGIN
set @inputDate = DATEFROMPARTS(year(@currentDate),month(@currentDate),2) ; -- 默认月初2号
END
set @_year = SUBSTRING(@inputDate, 1, 4);
PRINT @_year
set @flag = SUBSTRING(@inputDate, 6, 1);
PRINT @flag
IF @flag ='0' BEGIN
SET @_month = SUBSTRING(@inputDate, 7, 1) ;
END
ELSE BEGIN
SET @_month = SUBSTRING(@inputDate, 6, 2) ;
END
PRINT @_month
--打开单位游标
open cursor_group
fetch next from cursor_group into @GroupID
--fetch到数据时进行的操作
WHILE @@FETCH_STATUS = 0
begin
PRINT 'GroupID:'+@GroupID;
PRINT 'inputDate:'+@inputDate;
--这里面可以写具体的操作逻辑
-- 输入参数
-- 年月日:inputDate 格式 yyyy-MM-dd
-- 能耗编码: F_EnergyItemCode 在常规用能电耗范围(01A00、01B00、01C00)
-- 查询表: T_EC_OrganMonthResult
-- 返回:sum(F_DayValue)
-- 查询总能耗
set @eValue = (SELECT CONVERT(DECIMAL(18,2),ISNULL(sum(F_MonthValue),0)) FROM T_EC_OrganMonthResult WHERE F_OrganID = @GroupID
AND F_EnergyItemCode IN('01A00','01B00','01C00')
AND CONVERT(varchar(7), F_startMonth, 120)>= substring(@inputDate,1,4)+'-01'
AND CONVERT(varchar(7), F_startMonth, 120)< substring(@inputDate,1,7)) ;
PRINT 'eValue:'+CONVERT(VARCHAR(100),@eValue);
-- 查询总面积
set @area = (SELECT CONVERT(DECIMAL(18,2),ISNULL(F_TotalArea, 0)) FROM T_BD_BuildGroupBaseInfo WHERE F_BuildGroupID = @GroupID ) ;
PRINT 'area:'+CONVERT(VARCHAR(100),@area);
-- 查询某单位截止到上月累计能耗定额
set @eBase= (SELECT CONVERT(DECIMAL(18,2),ISNULL(SUM(F_Value2), 0)) FROM T_EnergyColumn where F_GroupID = @GroupID AND F_Year =@_year AND F_DATE>=1 AND F_DATE < @_month);
set @eOver= (SELECT CONVERT(DECIMAL(18,2),ISNULL(SUM(F_Value3), 0)) FROM T_EnergyColumn where F_GroupID = @GroupID AND F_Year =@_year AND F_DATE>=1 AND F_DATE < @_month);
PRINT 'eBase:'+CONVERT(VARCHAR(100),@eBase);
PRINT 'eOver:'+CONVERT(VARCHAR(100),@eOver);
-- 判断是否超定额
set @eValueAvg = Round(@eValue/@area,2);
PRINT 'eValueAvg:'+CONVERT(VARCHAR(100),@eValueAvg);
PRINT '===========================================';
IF (@eValueAvg>@eOver) BEGIN -- 超过约束值
PRINT '超过约束值';
SET @overRate = CONVERT(VARCHAR(100),CONVERT(DECIMAL(18,2),(@eValueAvg-@eOver)/@eOver)*100)+'%';
PRINT '超出约束值:'+@overRate
-- 执行插入 T_DE_WarnInfo 表 ,预警信息
MERGE INTO T_DE_WarnInfo A
USING (SELECT @GroupID as group_id,'1' as warn_type,@currentDate as warn_time )B
ON (A.F_EprID = B.group_id AND A.F_WarnType = B.warn_type AND A.F_WarnTime = B.warn_time )
WHEN MATCHED THEN
UPDATE SET A.F_WarnContent = '截至'+@inputDate+'本年单位面积用电量['+CONVERT(VARCHAR(10),@eValueAvg)+'kWh/㎡],超出定额标准约束值['+CONVERT(VARCHAR(10),@eOver)+'kWh/㎡]的'+@overRate
WHEN NOT MATCHED THEN
INSERT (F_EprID,F_WarnTime,F_WarnDate,F_WarnType,F_WarnContent,F_WarnStatus)
VALUES (@GroupID,B.warn_time,@inputDate,'1','截至'+@inputDate+'本年单位面积常规用电量['+CONVERT(VARCHAR(10),@eValueAvg)+'kWh/㎡],超出定额标准约束值['+CONVERT(VARCHAR(10),@eOver)+'kWh/㎡]的'+@overRate,'0');
END
ELSE BEGIN
IF (@eValueAvg>@eBase) BEGIN -- 超过基准值
PRINT '超过基准值';
SET @overRate = CONVERT(VARCHAR(100),CONVERT(DECIMAL(18,2),(@eValueAvg-@eBase)/@eBase)*100)+'%';
PRINT '超出基准值:'+@overRate
-- 执行插入 T_DE_WarnInfo 表 ,预警信息
MERGE INTO T_DE_WarnInfo A
USING (SELECT @GroupID as group_id,'1' as warn_type,@currentDate as warn_time )B
ON (A.F_EprID = B.group_id AND A.F_WarnType = B.warn_type AND A.F_WarnTime = B.warn_time )
WHEN MATCHED THEN
UPDATE SET A.F_WarnContent = '截至'+@inputDate+'本年单位面积用电量['+CONVERT(VARCHAR(10),@eValueAvg)+'kWh/㎡],超出定额标准基准值['+CONVERT(VARCHAR(10),@eBase)+'kWh/㎡]的'+@overRate
WHEN NOT MATCHED THEN
INSERT (F_EprID,F_WarnTime,F_WarnDate,F_WarnType,F_WarnContent,F_WarnStatus)
VALUES (@GroupID,B.warn_time,@inputDate,'1','截至'+@inputDate+'本年单位面积常规用电量['+CONVERT(VARCHAR(10),@eValueAvg)+'kWh/㎡],超出定额标准基准值['+CONVERT(VARCHAR(10),@eBase)+'kWh/㎡]的'+@overRate,'0');
END
END
fetch next from cursor_group into @GroupID
end
--关闭游标
close cursor_group
--删除游标
deallocate cursor_group
END
END
GO