mysql 存储过程中游标遇到的问题

在mysql的存储过程中，我们常根据需要使用游标处理数据。并使用

DECLARE CONTINUE HANDLER FOR NOT FOUND  SET nextdo =1;

来处理游标结束时跳出循环体。但在今天的使用中发现，如果在循环中，存在select xxx into xxx这样的语句时，如果没有查询到值，nextdo会被设置成1，导致游标不能继续循环下去。所以在select xxx into xxx这样的语句后面要使用下面的语句。把变量重置为0。以保证游标可以继续执行。

IF _BottlePackCode IS NOT NULL THEN 
   UPDATE  acexeym_packcode.code SET status=_status  
         WHERE ParentCode=_PackCode ;
ELSE
    SET nextdo=0;#如果上面条件没有找到,程序会的将nextdo设置为1
END IF;

另外，在select xxx into xxx这样的语句之前,一定要把变量重置为null,否则当没有获取到值into时,程序仍会取到上一个已经赋值的变量.

下面是该存储过程的完整代码 

CREATE PROCEDURE temp_async_order_bottlepackcode(_orderid bigint)
    BEGIN 
      DECLARE _PackCode nvarchar(50);
      DECLARE _CompanyId bigint;
      DECLARE _status bigint;
      DECLARE _batchno nvarchar(50);
      DECLARE _ProductId bigint;
      DECLARE _skuid bigint;    
      DECLARE _UpdateTime datetime;
      DECLARE _RelationEvent nvarchar(300);
      DECLARE _BatchNoId bigint;
      DECLARE _ActiveTime datetime;
  
      DECLARE _BottlePackCode nvarchar(50);
      DECLARE nextdo tinyint default 0;

      #获取激活了的瓶码
      DECLARE  mycursor CURSOR FOR   
          SELECT a.PackCode,a.CompanyId,a.status,a.batchno,a.ProductId,a.skuid,a.UpdateTime,a.RelationEvent,a.BatchNoId,a.ActiveTime
            FROM acexeym_packcode.code a 
            WHERE  orderid=_orderid AND a.ActiveTime IS NOT NULL;
      DECLARE CONTINUE HANDLER FOR NOT FOUND  SET nextdo =1;
  

      OPEN mycursor;
      lbl_read:LOOP
        BEGIN 
           FETCH mycursor 
            INTO    _PackCode ,     _CompanyId ,    _status,     _batchno ,     _ProductId ,     _skuid ,    _UpdateTime ,     _RelationEvent ,     _BatchNoId ,     _ActiveTime;
          IF nextdo =1 THEN
            LEAVE lbl_read;
           END IF; 
          SET _BottlePackCode=NULL;
          SELECT PackCode INTO _BottlePackCode FROM acexeym_packcode.code 
              WHERE ParentCode=_PackCode AND CompanyId=_CompanyId AND substring(Packcode,10,1)='0' AND ActiveTime IS NULL  LIMIT 1;
          IF _BottlePackCode IS NOT NULL THEN 
            UPDATE  acexeym_packcode.code SET status=_status,    batchno= _batchno ,    ProductId= _ProductId ,   
              skuid= _skuid ,    UpdateTime=_UpdateTime ,     RelationEvent=_RelationEvent ,     BatchNoId=_BatchNoId ,   
              ActiveTime=_ActiveTime
              WHERE ParentCode=_PackCode AND CompanyId=_CompanyId ;
          ELSE
            SET nextdo=0;#如果上面条件没有找到,程序会的将nextdo设置为1
          END IF;
          END ;
      END LOOP;
    close mycursor;     
  END  //