mysql存储过程双层循环循环结束条件 mysql存储过程嵌套循环

转载

技术笔耕者 2023-10-07 18:47:59

文章标签 mysql 数据库 database 模版字段 文章分类 MySQL 数据库

文章目录

目录
前言一、模版一
二、模版二
三、模版三

前言

分享主题：我们在工作过程中常常需要用mysql的存储过程造数据，下面将总结分享3个常用的存储过程模版方便大家造数据。

提示：以下是本篇文章正文内容，下面案例可供参考

一、模版一

1.解决问题：如何往表中插入n条数据

代码如下（示例）：

实现往user_profiles表中插入500条数据

#前提：假设需要被插入数据的表的建表语句如下： create table user_profiles ( user_id bigint not null primary key, gender varchar(16) null, age int null, mbr_level varchar(16) null, mbr_type varchar(16) null, register_channel varchar(32) null, lifecycle varchar(16) null, is_qiwei varchar(16) null, is_public_fans varchar(16) null, is_potential varchar(16) null, city_level varchar(16) null, payment_180d double null, shop_time int null, clothing_payment double null, avg_distinct double null, max_payment double null, login_cnt_7d int null, last_shop_day int null, is_tmall_shop varchar(16) null, offline_shop varchar(32) null ) charset = utf8mb4; drop procedure if exists idata1; delimiter ;; create procedure idata1() begin declare i int; set i = 0; while(i <= 499) do INSERT INTO user_profiles (user_id, gender, age, mbr_level, mbr_type, register_channel, lifecycle, is_qiwei, is_public_fans, is_potential, city_level, payment_180d, shop_time, clothing_payment, avg_distinct, max_payment, login_cnt_7d, last_shop_day, is_tmall_shop, offline_shop) VALUES (i, ELT(CEILING(rand() * 2), '男', '女'), FLOOR(1 + (RAND() * 101)), ELT(CEILING(rand() * 4), 'L1', 'L2', 'L3', 'L4'), ELT(CEILING(rand() * 2), '非付费会员', '付费会员'), ELT(CEILING(rand() * 4), '商城', '微信公众号', '微信小程序', '会员app'), ELT(CEILING(rand() * 4), '新增用户', '沉睡用户', '活跃用户', '流失用户'), ELT(CEILING(rand() * 11), '厦门', '无锡', '杭州', '上海', '铁岭', '开封', '德州', '扬州', '芜湖', '丽江', '宁波'), ELT(CEILING(rand() * 2), '是', '否'), ELT(CEILING(rand() * 2), '是', '否'), ELT(CEILING(rand() * 5), '一线城市', '二线城市', '三线城市', '四线城市', '五线城市'), CEILING(0 + (RAND() * 100000)), CEILING(0 + (RAND() * 100)), CEILING(0 + (RAND() * 5000)), CEILING(0 + (RAND() * 9)), CEILING(0 + (RAND() * 5000)), CEILING(0 + (RAND() * 20)), CEILING(0 + (RAND() * 365)), ELT(CEILING(rand() * 2), '是', '否'), ELT(CEILING(rand() * 5), '包', '帽子', '鞋', '打底裤', '衣服')); set i = i + 1; end while; end;; delimiter ; call idata1();

2.说明

模版中CEILING（）、ELT（）等都是造数据常用的一些函数方法，具体每个字段造数据用什么函数需结合具体的业务再自行调整

二、模版二

1.解决问题：往A表中插入数据，要求trade表channel_id字段，必须是channel_basic表的所有channel_id字段的值，且不重复

代码如下（示例）：

drop procedure if exists new;
delimiter ;;
create procedure new()
begin
##定义判断变量
DECLARE _flag1 varchar(50);
## 定义查询变量
DECLARE _cur1 CURSOR FOR
SELECT channel_id FROM channel_basic;
### 循环赋初始值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _flag1 = NULL;
### 打开
OPEN _cur1;
## 赋值
FETCH _cur1 INTO _flag1;
while(_flag1 is not null)
do
INSERT INTO trade (channel_id, channel_name, brand_id, brand_name,
sale_amt, sale_cnt, refund_amt, refund_cnt,
sale_retail_amt, sale_target_amt, order_cnt,
new_sale_amt, user_cnt)
VALUES (_flag1, (SELECT CONCAT(first_menu, second_menu, IFNULL(third_menu,''))
FROM channel_basic
where channel_id = _flag1), null, null, (round(50 + (RAND() * 9000),2)), (CEILING(10 + (RAND() * 500))), (round(50 + (RAND() * 1000), 2)), ((CEILING(10 + (RAND() * 100)))), (round(50 + (RAND() * 9000),2)), ((CEILING(5000 + (RAND() * 10000)))), ((CEILING(100 + (RAND() * 1000)))), ((CEILING(300 + (RAND() * 1000)))),
(CEILING(10 + (RAND() * 500))));
FETCH _cur1 INTO _flag1;
end while;
CLOSE _cur1;
end;;
delimiter ;
call new();

2.说明

模版中CEILING（）、ELT（）等都是造数据常用的一些函数方法，具体每个字段造数据用什么函数需结合
具体的业务再自行调整

三、模版三

1.解决问题：往表A中需要插入多条数据，要求表trade中的channel_id和brand_id字段，分别需要取channel_basic表和brand_basic表，且表trade的primary key(channel_id,brand_id)

2.解决问题的思路：写个嵌套循环，遍历channel_basic表和brand_basic表，分别取出对应值给trade表的channel_id,brand_id值赋值。

下面就讲一下，mysql如何使用存储方式实现嵌套循环。

代码如下（示例）：

drop procedure if exists new1;
delimiter ;;
create procedure new1()
BEGIN
DECLARE _flag11 varchar(50);
DECLARE _flag22 varchar(50);
-- 定义结束标识并绑定游标
DECLARE done INT DEFAULT FALSE;
DECLARE edone INT DEFAULT FALSE;
-- 定义游标01
DECLARE _outerForEach CURSOR FOR
select distinct brand_id from brand_basic;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 结束标识
# 打开游标1
OPEN _outerForEach;
read_loop:
LOOP
-- 循环游标开始,
FETCH _outerForEach INTO _flag11;
IF done THEN
LEAVE read_loop;#跳出循环
END IF;
BEGIN
# 定义游标02
DECLARE _innerForEach CURSOR FOR
SELECT distinct channel_id FROM channel_basic;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = 1;
#结束标识
# 打开游标2
OPEN _innerForEach;
inner_loop:
LOOP
FETCH _innerForEach INTO _flag22;
IF edone THEN
LEAVE inner_loop;
ELSE
channel_id, channel_name, brand_id, brand_name,
sale_amt, sale_cnt, refund_amt, refund_cnt,
sale_retail_amt, sale_target_amt, order_cnt,
new_sale_amt, user_cnt)
VALUES (_flag22, (SELECT CONCAT(first_menu, second_menu, IFNULL(third_menu,''))
FROM channel_basic
where channel_id = _flag22), _flag11, (select brand from brand_basic where brand_id = _flag11 ), (round(50 + (RAND() * 9000),2)), (CEILING(10 + (RAND() * 500))), (round(50 + (RAND() * 1000), 2)), ((CEILING(10 + (RAND() * 100)))), (round(50 + (RAND() * 9000),2)), ((CEILING(5000 + (RAND() * 10000)))), ((CEILING(100 + (RAND() * 1000)))), ((CEILING(300 + (RAND() * 1000)))),
(CEILING(10 + (RAND() * 500))));
END IF;
END LOOP;
CLOSE _innerForEach;-- 关闭内层游标
SET edone = FALSE;-- 内循环复位以便再次循环
END;
END LOOP; -- 结束循环
CLOSE _outerForEach;-- 关闭游标
COMMIT;
end;;
delimiter ;
call new1();