MySql存储过程使用,含变量、流程、游标、事务等操作,可以复用。
CREATE DEFINER=`root`@`%` PROCEDURE `p_insertCollect`()
BEGIN
-- 需要定义接收游标数据的变量
-- 自定义变量
declare var_now datetime;
declare var_real_time datetime;
declare var_id int(18);
declare var_stationCode VARCHAR (100);
declare var_itemCode VARCHAR (100) ;
declare var_collectTime datetime;
declare var_collectValue decimal(18,4) ;
declare var_couValue decimal(18,4) ;
declare var_isYaoXin VARCHAR (1) ;
declare var_fTagType VARCHAR (1) ;
declare var_collectTimeNext datetime;
declare var_collectValueNext decimal(18,4) ;
declare var_couValueNext decimal(18,4) ;
declare done int DEFAULT 0 ;-- 游标是否结束
declare t_error int DEFAULT 0; -- 错误变量
-- 声明游标
DECLARE cur CURSOR FOR select t1.Id,t1.StationCode,t1.ItemCode,t1.CollectTime,t1.CollectValue,t1.CouValue,t3.IsYaoxin,t3.FTagType from collectitemdatareal t1 join collectitem t2 on t1.StationCode=t2.StationCode and t1.ItemCode=t2.ItemCode join collectitemtype t3 on t2.TypeCode=t3.TypeCode where t1.CollectValue>0;
-- 设置结束标志
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 游标结束,设置为1
declare continue handler for sqlexception set t_error=1; -- 捕获到sql的错误,就设置t_error为1
set var_now=date_add(now(),interval -15 minute);
START TRANSACTION;
-- 打开游标
OPEN cur ;
-- 使用repeat循环语法
cur:loop
-- 批读取数据到指定变量上
FETCH cur INTO var_id,var_stationCode,var_itemCode,var_collectTime,var_collectValue,var_couValue,var_isYaoXin,var_fTagType ;
if done=1 then
leave cur;
end if;
if var_collectTime<var_now then
-- 开始插入数据
if var_isYaoXin='1' then
set var_collectValueNext=abs(round((rand()*100+45)/100)-1);
set var_couValueNext=var_collectValueNext;
else
if var_fTagType='1' then -- 累计值
set var_couValueNext=round(abs(var_couValue+var_couValue*(rand()*1-0.5)/5),2);
set var_collectValueNext=var_collectValue+var_couValueNext;
elseif var_fTagType='0' then -- 瞬时值
set var_couValueNext=round(var_couValue+(var_couValue*(rand()*1-0.5)/5),2);
set var_collectValueNext=var_couValueNext;
end if;
end if;
set var_collectTimeNext=date_add(var_collectTime,interval 15 minute);
-- 准备插入数据
update collectitemdatareal t set t.CollectTime=var_collectTimeNext,t.CollectValue=var_collectValueNext,t.CouValue=var_couValueNext where t.Id=var_id;
insert into collectitemdatahistory(StationCode,ItemCode,CollectTime,CollectValue,CouValue) values (var_stationCode,var_itemCode,var_collectTimeNext,var_collectValueNext,var_couValueNext);
end if;
-- 循环结束条件
end loop cur;
-- 关闭游标
CLOSE cur ;
if t_error=1 then
rollback;
else
commit;
end if;
select t_error;
END