mysql存储过程局部变量使用_MySQL存储过程中变量及循环的使用

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