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

素颜马尾好姑娘i 2024-04-19 00:46 69阅读 0赞

在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  //