mysql 游标

CREATE DEFINER=`oneboss1`@`192.168.%.%` PROCEDURE `increment_for_ali_ware`(IN `limitSize` INT)

begin  

#声明一个标记，当游标状态为最后一条记录时，修改该变量  

declare done int;

#本次处理成功同步（insert/update的商品数量）

DECLARE INSERT_WARE_COUNT INT;

DECLARE UPDATE_WARE_COUNT INT;

declare productName VARCHAR (300);

declare productUrl VARCHAR (300);

declare productImgUrl VARCHAR (300);

declare productCurrentPrice DOUBLE ;

declare productOnTime LONG ;

declare shopId VARCHAR (50);

declare productTbId VARCHAR (50);

declare offerStatus VARCHAR (50);

#用于保存循环采番所得到的ID值

DECLARE productDbId VARCHAR(50);

 

 

 

#定义游标

DECLARE WAIT_SYNC_DATA_CUR CURSOR FOR (SELECT t1.`subject`, t2.image_uri, t3.price, t1.FN_SHOP_ID, t1.gmt_create, t1.offer_status, t1.details_url, t1.offer_id FROM u_sync_albb_offer t1 LEFT JOIN ( SELECT image_uri, tt.offer_id FROM u_sync_albb_offer_image_info tt GROUP BY tt.offer_id ) t2 ON t1.offer_id = t2.offer_id LEFT JOIN ( SELECT price, ty.offer_id FROM U_SYNC_ALBB_OFFER_PRICE_RANGE_INFO ty GROUP BY ty.offer_id ) t3 ON t1.offer_id = t3.offer_id WHERE t1.SYNC_STATUS = 0 AND NOT EXISTS ( SELECT M.PRODUCT_ID FROM MK_COMMODITY_INFORMATION M WHERE M.SHOP_ID = t1.FN_SHOP_ID AND M.PRODUCT_TB_ID = t1.offer_id ) LIMIT 0,limitSize);

DECLARE WAIT_UPDATE_DATA_CUR CURSOR FOR (SELECT t1.`subject`, t2.image_uri, t3.price, t1.FN_SHOP_ID, t1.gmt_create, t1.offer_status, t1.details_url, t1.offer_id FROM u_sync_albb_offer t1 LEFT JOIN ( SELECT image_uri, tt.offer_id FROM u_sync_albb_offer_image_info tt GROUP BY tt.offer_id ) t2 ON t1.offer_id = t2.offer_id LEFT JOIN ( SELECT price, ty.offer_id FROM U_SYNC_ALBB_OFFER_PRICE_RANGE_INFO ty GROUP BY ty.offer_id ) t3 ON t1.offer_id = t3.offer_id WHERE t1.SYNC_STATUS = 0 AND  EXISTS ( SELECT M.PRODUCT_ID FROM MK_COMMODITY_INFORMATION M WHERE M.SHOP_ID = t1.FN_SHOP_ID AND M.PRODUCT_TB_ID = t1.offer_id ) LIMIT 0,limitSize);

 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

 

#打开游标

OPEN WAIT_SYNC_DATA_CUR;

OPEN WAIT_UPDATE_DATA_CUR;

SET INSERT_WARE_COUNT=0;

SET UPDATE_WARE_COUNT=0;

#循环游标

cursor_loop1:LOOP

#把数据取出

  FETCH WAIT_SYNC_DATA_CUR INTO productName,productImgUrl,productCurrentPrice,shopId,productOnTime,offerStatus,productUrl,productTbId; #读取数据到游标  

  IF done=1 THEN

LEAVE cursor_loop1;

  END IF;

#CALL QUERY_MK_COMMODITY_INFORMATION_EXISTS_ID(shopId,productTbId,productId);

CALL NEXT_ID_4_PROCEDURE('CARD_PRO_CD',NULL,1,productDbId);

INSERT INTO mk_commodity_information( PRODUCT_ID, PRODUCT_NAME, PRODUCT_IMG_URL, PRODUCT_CURRENT_PRICE, SHOP_ID, PRODUCT_ON_TIME, PRODUCT_STATUS, PRODUCT_URL, PRODUCT_TB_ID, TWO_DIMENSION_CODE_STATUS ) VALUES(productDbId,productName,productImgUrl,productCurrentPrice,shopId,productOnTime,offerStatus,productUrl,productTbId,'0'); 

UPDATE u_sync_albb_offer SET SYNC_STATUS=1 WHERE offer_id=productTbId AND FN_SHOP_ID=shopId;

SET INSERT_WARE_COUNT = INSERT_WARE_COUNT+1;

END LOOP cursor_loop1;

CLOSE WAIT_SYNC_DATA_CUR;

SET done=0;

cursor_loop2:LOOP

#把数据取出

  FETCH WAIT_UPDATE_DATA_CUR INTO productName,productImgUrl,productCurrentPrice,shopId,productOnTime,offerStatus,productUrl,productTbId; #读取数据到游标  

  IF done=1 THEN

LEAVE cursor_loop2;

  END IF;

UPDATE mk_commodity_information SET PRODUCT_NAME = productName, PRODUCT_URL = productUrl, PRODUCT_IMG_URL = productImgUrl, PRODUCT_CURRENT_PRICE = productCurrentPrice, PRODUCT_STATUS = offerStatus, TWO_DIMENSION_CODE_STATUS = '0' WHERE PRODUCT_TB_ID=productTbId AND SHOP_ID=shopId;

UPDATE u_sync_albb_offer SET SYNC_STATUS=1 WHERE offer_id=productTbId AND FN_SHOP_ID=shopId;

SET UPDATE_WARE_COUNT = UPDATE_WARE_COUNT+1;

END LOOP cursor_loop2;

CLOSE WAIT_UPDATE_DATA_CUR;

SELECT INSERT_WARE_COUNT,UPDATE_WARE_COUNT;

END