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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值