MySQL存储过程中变量及循环的使⽤1、⽤游标循环
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<>1AND id<>10AND meter_type=1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET done=1;
-- 初始化done，为0，false
SET done =0;
-- 打开游标
OPEN rs_ammeter_id;
-- 遍历游标（开始循环）
REPEAT
FETCH  rs_ammeter_id into ammeter_id_bl;
docker+k8sIF done<>1then
/*
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
(
SELECT
meter_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, INTERVAL 30 MINUTE) collect_time
,ROUND(RAND(),0) meter_status
FROM
cap_ammeter_201810
WHERE
collect_time = (
SELECT
MAX(collect_time) collect_time
FROM
cap_ammeter_201810
WHERE
ammeter_id =@ammeter_id_bl
小白学python买什么书
GROUP BY
ammeter_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)
deamon
,@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简约风ppt模板免费下载
,@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 INTO cap_ammeter_201810
(
meter_count
,collect_time
,ammeter_id
,
meter_statusmysql查看所有存储过程
,meter_count_top
,meter_count_max
,meter_count_avg
,meter_count_min
) ",@queryDataSqlStr
乌克兰危机);
-- 4）查看sql语句
-- SELECT @insertSqlStr;
-- 3）执⾏sql语句
PREPARE insertSqlStr FROM@insertSqlStr;
EXECUTE insertSqlStr;
END IF;
-- 直到done变为true结束循环
UNTIL done END
REPEAT;
CLOSE rs_ammeter_id;
END
2、while循环
BEGIN
-- 定义变量
DECLARE i INT DEFAULT10;
-
- 开始循环
WHILE i<13
DO
SET@day_bl=i;
SET@createSqlStr=CONCAT("CREATE TABLE cap_ammeter_2017",@day_bl,"
                      (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`meter_count` double(14,2) DEFAULT NULL COMMENT '电表读数',
`collect_time` datetime DEFAULT NULL COMMENT '采集时间',
`ammeter_id` bigint(20) NOT NULL COMMENT '电表id，对应res_meter的id',                                    `meter_status` int(1) NOT NULL COMMENT '电表状态 0：正常；1：异常',                                    `meter_count_top` double(14,2) DEFAULT NULL COMMENT '尖值电量',
`meter_count_max` double(14,2) DEFAULT NULL COMMENT '峰值电量',                                    `meter_count_avg` double(14,2) DEFAULT NULL COMMENT '平值电量',
`meter_count_min` double(14,2) DEFAULT NULL COMMENT '⾕值电量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19576DEFAULT CHARSET=utf8;");
-- 查看sql
-- SELECT @createSqlStr;
-- 运⾏sql
PREPARE createSqlStr FROM@createSqlStr;
EXECUTE createSqlStr;
SET i=i+1;
END WHILE;
COMMIT;
END

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。