MySQL之存储过程、游标、遍历结合使用

drop procedure if exists init_group_val;
delimiter $
create PROCEDURE init_group_val()
BEGIN
    DECLARE  group_id VARCHAR(64);

    -- 遍历数据结束标志
    DECLARE done INT DEFAULT FALSE;
    -- 定义获取所有品牌属性组id 游标
    DECLARE cur_account CURSOR FOR SELECT id as group_id FROM tbl_brand_attr_group;
    -- 将结束标志绑定到游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标
    OPEN  cur_account;     
    -- 遍历
    read_loop: LOOP
            -- 从游标中取出品牌属性组id值
            FETCH  NEXT from cur_account INTO group_id;
            IF done THEN
                LEAVE read_loop;
             END IF;
						  -- 调用存储过程设置值的组id
							call set_attr_val_group_id(group_id);
    END LOOP;
 
 
    CLOSE cur_account;
END $

drop procedure if exists set_attr_val_group_id;
delimiter $
create PROCEDURE set_attr_val_group_id(group_id VARCHAR(64))
BEGIN
    DECLARE  g_id VARCHAR(64);
		DECLARE  val_id VARCHAR(64);

    -- 遍历数据结束标志
    DECLARE done INT DEFAULT FALSE;
    -- 定义根据品牌属性组id将品牌属性组val值转行 游标
    -- basedb表是自己创建的,help_topic是自增字段,若果几千条数据逗号分割,要确保basedb表数据足够多,不然转换的行数是basedb表的行数
    DECLARE cur_account CURSOR FOR select a.id as g_id,substring_index(substring_index(a.val,',',b.help_topic_id+1),',',-1) as val_id
																		from 
																		tbl_brand_attr_group a
																		join
																		basedb.help_topic b
																		on b.help_topic_id < (length(a.val) - length(replace(a.val,',',''))+1)
																		and a.id=group_id
																		order by val_id;
    -- 将结束标志绑定到游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标
    OPEN  cur_account;     
    -- 遍历
    read_loop: LOOP
            -- 取值 取多个字段
            FETCH  NEXT from cur_account INTO g_id, val_id;
            IF done THEN
                LEAVE read_loop;
             END IF;
						 -- 将品牌属性组id更新到品牌属性值中
							update tbl_brand_attr_val set group_id = g_id  WHERE id=val_id;
    END LOOP;
 
 
    CLOSE cur_account;
END $


ALTER TABLE product.tbl_product_attr_val DROP key_id;

-- 调用存储过程
call init_group_val()