存储过程的创建:
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `his_proc`()
BEGIN
start transaction;
set @CurYear = year(curdate());
set @strDate = CURRENT_DATE();
set @PlantTableName = CONCAT('plant',@CurYear);
set @InvTableName = CONCAT('inverter',@CurYear);
set @comboxTableName = CONCAT('combox',@CurYear);
set @meterTableName = CONCAT('meter',@CurYear);
set @sensorTableName = CONCAT('sensor',@CurYear);
#电站
set @PlantSql= concat('insert ignore into ',@PlantTableName,
'(PlantTime,DailyYield,TotalYield,LevelSunAmplitude,SlantSunAmplitude,EnviroTemp,ModuleTemp,PlantPower,DailyCo2,TotalCo2,DCPower,TheoryPower,GridPower) ',
'select PlantTime,DailyYield,TotalYield,LevelSunAmplitude,SlantSunAmplitude,EnviroTemp,ModuleTemp,PlantPower,DailyCo2,TotalCo2,DCPower,TheoryPower,GridPower ',
'from plantpinstance where date(PlantTime)=\'',@strDate,'\'');
#select @PlantSql;
#逆变器
set @inverterSql = concat('insert ignore into ',@InvTableName,
'(InvTime,DevAddr,DailyYield,TotalYield,TotalRuntime,AirTemp,TransferTemp,FanTemp,DCVoltage,DCCurrent,InputPSum,Uab,Ubc,',
'Uca,Ia,Ib,Ic,PA,PB,PC,PSum,QSum,PFSum,Frq,InvEfficiency,RunStatus,Upv2,Upv3,Upv4,Upv5,Upv6,Ipv2,Ipv3,Ipv4,Ipv5,Ipv6)',
'select InvTime,DevAddr,DailyYield,TotalYield,TotalRuntime,AirTemp,TransferTemp,FanTemp,DCVoltage,DCCurrent,InputPSum,Uab,Ubc,',
'Uca,Ia,Ib,Ic,PA,PB,PC,PSum,QSum,PFSum,Frq,InvEfficiency,RunStatus,Upv2,Upv3,Upv4,Upv5,Upv6,Ipv2,Ipv3,Ipv4,Ipv5,Ipv6 ',
'from inverterpinstance where date(InvTime)=\'',@strDate,'\'');
#汇流箱
set @comboxSql = concat('insert ignore into ',@comboxTableName,
'(CBTime,DevAddr,CBTemp,Voltage,I_1,I_2,I_3,I_4,I_5,I_6,I_7,I_8,I_9,I_10,I_11,I_12,I_13,I_14,',
'I_15,I_16,I_17,I_18,I_19,I_20,I_21,I_22,I_23,I_24,I_25,I_26,I_27,I_28,I_29,I_30,I_31,I_32)',
'select CBTime,DevAddr,CBTemp,Voltage,I_1,I_2,I_3,I_4,I_5,I_6,I_7,I_8,I_9,I_10,I_11,I_12,I_13,I_14,',
'I_15,I_16,I_17,I_18,I_19,I_20,I_21,I_22,I_23,I_24,I_25,I_26,I_27,I_28,I_29,I_30,I_31,I_32 ',
'from combinerboxpinstance where date(CBTime)=\'',@strDate,'\'');
#电表
set @meterSql = concat('insert ignore into ',@meterTableName,
'(MDPTime,DevAddr,SystemFrq,Uab,Ubc,Uca,Ia,Ib,Ic,P1,P2,P3,PSum,Q1,Q2,Q3,QSum,S1,S2,S3,SSum,PF1,PF2,PF3,PFSum,ZY,FY,ZW,FW)',
'select MDPTime,DevAddr,SystemFrq,Uab,Ubc,Uca,Ia,Ib,Ic,P1,P2,P3,PSum,Q1,Q2,Q3,QSum,S1,S2,S3,SSum,PF1,PF2,PF3,PFSum,ZY,FY,ZW,FW ',
'from meterpinstance where date(MDPTime)=\'',@strDate,'\'');
#环境检测仪
set @sensorSql = concat('insert ignore into ',@sensorTableName,
'(EMDTime,DevAddr,DailyAmplitude,TotalAmplitude,LevelSunAmplitude,SlantSunAmplitude,EnviroTemp,ModuleTemp,WindSpeed,WindDir)',
'select EMDTime,DevAddr,DailyAmplitude,TotalAmplitude,LevelSunAmplitude,SlantSunAmplitude,EnviroTemp,ModuleTemp,WindSpeed,WindDir ',
'from sensorpinstance where date(EMDTime)=\'',@strDate,'\'');
prepare PlantStmt from @PlantSql;
execute PlantStmt;
prepare inverterStmt from @inverterSql;
execute inverterStmt;
prepare comboxStmt from @comboxSql;
execute comboxStmt;
prepare meterStmt from @meterSql;
execute meterStmt;
prepare sensorStmt from @sensorSql;
execute sensorStmt;
commit;
END
//
DELIMITER;
#查看存储过程
show procedure status;
#查看过程创建代码
show create procedure his;
#删除过程
DROP PROCEDURE his_proc;
定时器的创建:
CREATE DEFINER=`root`@`localhost`
EVENT `his_timer`
ON SCHEDULE EVERY 5 MINUTE STARTS '2016-05-20 20:30:45'
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT '历史数据定时器'
DO
call his();
#查看事件
select * from mysql.event;
#关闭事件
alter event hhis_timer on completion preserve disable;
#删除事件
DROP EVENT IF EXISTS hhis_timer;