mysql 存储过程变量及循环的使用
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql 存储过程变量及循环的使用，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6367字，纯文字阅读大概需要10分钟。
内容图文
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<>1 AND id<>10 AND 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;
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
(
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
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)
,@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 INTO cap_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;
EXECUTE insertSqlStr;
END IF;
-- 直到done变为true结束循环
UNTIL done END
REPEAT;
CLOSE rs_ammeter_id;
END
2、while循环
BEGIN
-- 定义变量
DECLARE i INT DEFAULT 10;
-- 开始循环
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=19576 DEFAULT CHARSET=utf8;");
-- 查看sql
-- SELECT @createSqlStr;
-- 运行sql
PREPARE createSqlStr FROM @createSqlStr;
EXECUTE createSqlStr;
SET i=i+1;
END WHILE;
COMMIT;
END
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;-- 打开游标 OPEN rs_ammeter_id; -- 遍历游标（开始循环）REPEAT FETCH rs_ammeter_id into ammeter_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_statusFROM (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_statusFROMcap_ammeter_201810WHEREcollect_time = (SELECTMAX(collect_time) collect_timeFROMcap_ammeter_201810WHEREammeter_id = @ammeter_id_blGROUP 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 INTO cap_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; EXECUTE insertSqlStr;
END IF;-- 直到done变为true结束循环UNTIL done END REPEAT; CLOSE rs_ammeter_id; END
内容总结
以上是互联网集市为您收集整理的mysql 存储过程变量及循环的使用全部内容，希望文章能够帮你解决mysql 存储过程变量及循环的使用所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。