BEGIN
--定义变量
--定义done
DECLARE done INT;--定义 ammeter_id_bl
DECLARE ammeter_id_bl DOUBLE;--定义表名(tableName)游标
DECLARE rs_ammeter_id CURSOR FOR
--得到游标集合
SELECT id FROM `res_meter` WHERE id<>1 AND id<>10 AND meter_type=1;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;--初始化done,为0,false
SET done = 0;--打开游标
OPENrs_ammeter_id;--遍历游标(开始循环)
REPEATFETCH rs_ammeter_id intoammeter_id_bl;IF done<>1 then
/*1、处理要插入的数据*/
--给变量赋值
SET @ammeter_id_bl=ammeter_id_bl;SELECT t.*
INTO
@min_meter_count_top,@min_meter_count_max,@min_meter_count_avg,@min_meter_count_min,@add_top,@add_max,@add_avg,@add_min,@collect_time,@meter_status
FROM(SELECTmeter_count_top
,meter_count_max
,meter_count_avg
,meter_count_min
,ROUND(RAND()*10+4,2) add_top
,ROUND(RAND()*8+3,2) add_max
,ROUND(RAND()*7+2,2) add_avg
,ROUND(RAND()*6+1,2) add_min
,DATE_ADD(collect_time, INTERVAL30MINUTE) collect_time
,ROUND(RAND(),0) meter_statusFROMcap_ammeter_201810WHEREcollect_time=(SELECT
MAX(collect_time) collect_timeFROMcap_ammeter_201810WHEREammeter_id= @ammeter_id_bl
GROUP BYammeter_id
)AND ammeter_id = @ammeter_id_bl)t;SET @meter_count_top=ROUND(@min_meter_count_top+@add_top,2)
,@meter_count_max=ROUND(@min_meter_count_max+@add_max,2)
,@meter_count_avg=ROUND(@min_meter_count_avg+@add_avg,2)
,@meter_count_min=ROUND(@min_meter_count_min+@add_min,2);SET @meter_count=ROUND(@meter_count_top+@meter_count_max+@meter_count_avg+@meter_count_min,2);--查看变量的值/*SELECT
@ammeter_id_bl
,@collect_time
,@meter_status
,@min_meter_count_top
,@min_meter_count_max
,@min_meter_count_avg
,@min_meter_count_min
,@add_top
,@add_max
,@add_avg
,@add_min
,@meter_count_top
,@meter_count_max
,@meter_count_avg
,@meter_count_min;*/
/*2、插入数据*/
--1)写sql语句 如果要用到变量,使用CONCAT()拼接
--查询出要插入的数据
SET @queryDataSqlStr=CONCAT("SELECT",ROUND(@meter_count,2)," meter_count
,'",@collect_time,"'collect_time
,",@ammeter_id_bl," ammeter_id
,",@meter_status," meter_status
,",ROUND(@meter_count_top,2)," meter_count_top
,",ROUND(@meter_count_max,2)," meter_count_max
,",ROUND(@meter_count_avg,2)," meter_count_avg
,",ROUND(@meter_count_min,2)," meter_count_min"
);--插入数据
SET @insertSqlStr=CONCAT("INSERT INTOcap_ammeter_201810
(
meter_count
,collect_time
,ammeter_id
,meter_status
,meter_count_top
,meter_count_max
,meter_count_avg
,meter_count_min
) ",@queryDataSqlStr);--4)查看sql语句
--SELECT @insertSqlStr;
--3)执行sql语句
PREPARE insertSqlStr FROM @insertSqlStr;EXECUTEinsertSqlStr;END IF;--直到done变为true结束循环
UNTIL done ENDREPEAT;CLOSErs_ammeter_id;END