MYSQL存储过程循环遍历插入数据

125 阅读 0 评论 83 点赞

我是靠谱客的博主时尚冷风，最近开发中收集的这篇文章主要介绍MYSQL存储过程循环遍历插入数据，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

本示例通过 while...end while 循环控制游标来实现插入表记录。

DROP PROCEDURE IF exists pro_initCategoryForTradingEntity;
create procedure pro_initCategoryForTradingEntity(tradingEntityId int)
begin
    declare f_parent,entityId int;
    declare b int default 0;    /*是否达到记录的末尾控制变量*/
    declare f_name varchar(100);
    DECLARE cur_1 CURSOR FOR select FName,FParent,tradingEntityId from t_category_tag;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    OPEN cur_1;
    FETCH cur_1 INTO f_name, f_parent, entityId; /*获取第一条记录*/
    while b<>1 do
        insert into t_category(FName,FParent,FTradingEntity) values(f_name, f_parent, entityId);
        FETCH cur_1 INTO f_name, f_parent, entityId; /*取下一条记录*/
    end while;
    close cur_1;
end;

call pro_initCategoryForTradingEntity(2);

本实例通过 repeat...end repeat 来循环控制。

CREATE DEFINER=`root`@`localhost` PROCEDURE `smt_update_goods_price`()
BEGIN
/*局部变量的定义 declare*/
DECLARE goodsName varchar(50);
DECLARE goodsCode varchar(50);
DECLARE avgPrice varchar(50);
DECLARE maxPrice varchar(50);
DECLARE minPrice varchar(50);
DECLARE Done INT DEFAULT 0;
DECLARE rs cursor for(
select goods_name, goods_code, avg(goods_price), max(goods_price), min(goods_price) from smt_temp_goods group by goods_code );
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
OPEN rs;
FETCH NEXT FROM rs INTO goodsName, goodsCode, avgPrice, maxPrice, minPrice;
REPEAT
IF NOT Done THEN
insert into smt_goods_price(id,goods_name,goods_code,avg_price,max_price,min_price) values(REPLACE(UUID(), '-', ''),goodsName,goodsCode,avgPrice,maxPrice,minPrice);
END IF;
FETCH NEXT FROM rs INTO goodsName, goodsCode, avgPrice, maxPrice, minPrice;
UNTIL Done END REPEAT;
CLOSE rs;
END