mysql存儲(chǔ)過程游標(biāo)結(jié)果集時(shí)，里面調(diào)用其他存儲(chǔ)過程，如果調(diào)用的存儲(chǔ)過程查詢?yōu)榭?，游?biāo)循環(huán)就結(jié)束了，怎么解決
1 回答
慕婉清6462132
TA貢獻(xiàn)1804條經(jīng)驗(yàn) 獲得超2個(gè)贊
CREATE DEFINER=`root`@`%` PROCEDURE `insertPresale`()
BEGIN
#Routine body goes here...
DECLARE done INT DEFAULT 0; /*用于判斷是否結(jié)束循環(huán)*/
DECLARE goodsId VARCHAR(255);#標(biāo)記商品id
DECLARE flag INT DEFAULT 0;#標(biāo)記數(shù)據(jù)庫是否包含此條商品記錄
/*用于存儲(chǔ)結(jié)果集的記錄*/
/*定義游標(biāo)*/
DECLARE idCur CURSOR FOR SELECT goods_id FROM `sys_goods_publish` WHERE presale= 1 AND presale_time <= NOW();
/*定義 設(shè)置循環(huán)結(jié)束標(biāo)識(shí)done值怎么改變 的邏輯*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/
OPEN idCur; /*打開游標(biāo)*/
/* 循環(huán)開始 */
REPEAT
#/* 如果要fetch多列應(yīng)該這樣寫，fetch cur/*對(duì)應(yīng)下面的idCur*/
FETCH idCur INTO goodsId; /*還可以fetch多列（假設(shè)結(jié)果集的記錄不是單列的話）*/
IF NOT done THEN /*數(shù)值為非0，MySQL認(rèn)為是true*/
SELECT COUNT(*) INTO flag FROM `itemsinfonew` WHERE TaoBaoitemId = goodsId;
IF (flag>0) THEN #如果數(shù)據(jù)庫中有爬取此條記錄則刪除已用戶發(fā)布的為準(zhǔn)
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; /*關(guān)閉游標(biāo)*/
添加回答
舉報(bào)
0/150
提交
取消