存储过程，游标 的一个实例

 

存储过程，游标 的一个实例

begin  

    declare stop_flag int DEFAULT 0;#声明一个标记，当游标状态为最后一条记录时，修改该变量  

declare productName VARCHAR (100);

declare productUrl VARCHAR (100);

declare productImgUrl VARCHAR (100);

declare productCurrentPrice DOUBLE ;

declare productOnTime LONG ;

declare shopId VARCHAR (50);

declare productTbId VARCHAR (50);

declare offerStatus VARCHAR (50);

declare productId VARCHAR(50);

 

 

    declare cur1 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;  

#查询待处理的商品信息

   DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET stop_flag=1;    

    open cur1;#打开游标  

 

productCursorLoop: LOOP

fetch cur1 into productName,productImgUrl,productCurrentPrice,shopId,productOnTime,offerStatus,productUrl,productTbId; #读取数据到游标  

if stop_flag=1 then

leave productCursorLoop;

ELSE

select m.PRODUCT_ID INTO productId from mk_commodity_information m where m.SHOP_ID= shopId and m.PRODUCT_TB_ID= productTbId;(这句有问题，游标中不允许出现select into 的句子，改造方法就是在创建游标的时候，创建两个游标，分别处理不同情况)

IF productId is not NULL THEN

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_ID = productId;

ELSE

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(f_id_get('CARD_PRO_CD'),productName,productImgUrl,productCurrentPrice,shopId,productOnTime,offerStatus,productUrl,productTbId,'0'); 

END IF;

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

END IF;

 

END LOOP;  

close cur1;#关闭游标  

END