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