MYSQL存储过程遍历结果集游标 从一张表插入数据到另一张表

时间:2021-09-01 21:23:33

从一张表往另一张表插入数据时,有一个字段没有需要手动填入,使用了SUBSTRING方法,代码如下

CREATE DEFINER=`root`@`%` PROCEDURE `insertPresale`()
BEGIN
#Routine body goes here...
DECLARE done INT DEFAULT 0; /*用于判断是否结束循环*/
DECLARE goodsId VARCHAR(255);#标记商品id
DECLARE flag INT DEFAULT 0;#标记数据库是否包含此条商品记录
/*用于存储结果集的记录*/
/*定义游标*/
DECLARE idCur CURSOR FOR SELECT goods_id FROM `sys_goods_publish` WHERE presale= 1 AND presale_time <= NOW();
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/
OPEN idCur; /*打开游标*/
/* 循环开始 */
REPEAT
#/* 如果要fetch多列应该这样写,fetch cur/*对应下面的idCur*/
FETCH idCur INTO goodsId; /*还可以fetch多列(假设结果集的记录不是单列的话)*/
IF NOT done THEN /*数值为非0,MySQL认为是true*/
SELECT COUNT(*) INTO flag FROM `itemsinfonew` WHERE TaoBaoitemId = goodsId;
IF (flag>0) THEN #如果数据库中有爬取此条记录则删除已用户发布的为准
DELETE FROM `itemsinfonew` WHERE TaoBaoitemId = goodsId;
END IF;
INSERT INTO `itemsinfonew` (TaoBaoitemId,CouponID,CreateSourceName)
SELECT goods_id,coupon_id,SUBSTRING("customer_fd",0.5)
FROM `sys_goods_publish`
WHERE presale_time <= NOW() AND goods_id = goodsId;
UPDATE `sys_goods_publish` SET presale = 0 WHERE presale= 1 AND presale_time <= NOW() AND goods_id = goodsId;
END IF;
UNTIL done END REPEAT;
CLOSE idCur; /*关闭游标*/
END