mysql游标实现到了最后一个结束之后结束循环

游标实现到了最后一个结束之后 此时会抛出 not found 异常结合异常的时机设置可以使循环结束的标记值，结束循环

游标是用来存结果集的 

 存储过程设置的输出参数，在过程结束后自然会输出，你给它赋什么值，就输出什么

 

CREATE DEFINER = 'flexitm'@'%'

PROCEDURE financial_sales_debug.PD_CD_CHK(IN INPARA VARCHAR(1000),

   OUT OUTPARA  VARCHAR(1000)) 

 

BEGIN

 DECLARE V_ERRMSG VARCHAR(128);

 DECLARE fid VARCHAR(100);

 DECLARE str VARCHAR(100);

 declare tmpMobile varchar(20) default '' ;

 declare allMobile varchar(255) default '' ;

##获取待分派名单结果集

 declare cur1 CURSOR FOR SELECT MOBILE FROM tb_userinfo WHERE ISASSIGN is NULL  or ISASSIGN='' ;

 declare CONTINUE HANDLER FOR NOT FOUND SET tmpMobile = NULL;--结束循环

 declare CONTINUE HANDLER FOR SQLEXCEPTION SET V_ERRMSG = '002';--异常回滚

   OPEN cur1;

    FETCH cur1 INTO tmpMobile;

##遍历游标

   WHILE ( tmpMobile is not null) DO

    SET INPARA=tmpMobile;

  IF INPARA IS NULL  THEN

   SET V_ERRMSG ='传入参数错误';

     ELSE

   ##  FETCH cur1 INTO tmpMobile;

    ## SET INPARA=tmpMobile;

    ##获取归属关系

SET fid =(

 

 

SELECT

COALESCE (

COALESCE (COALESCE(l2.MOBILE, l4.MOBILE), l6.MOBILE),

l8.MOBILE

) MOBILE

FROM

tb_userinfo a LEFT JOIN tb_tele_userinfo l ON a.MOBILE=l.MOBILE

 

 

LEFT JOIN tb_userinfo b ON a.INVITER_INVITATION_CODE = b.INVITATION_CODE

LEFT JOIN tb_tele_userinfo l2 ON b.MOBILE=l2.MOBILE

 

LEFT JOIN tb_userinfo c ON b.INVITER_INVITATION_CODE = c.INVITATION_CODE

LEFT JOIN tb_tele_userinfo l4 ON c.MOBILE=l4.MOBILE

 

 

LEFT JOIN tb_userinfo d ON c.INVITER_INVITATION_CODE = d.INVITATION_CODE

LEFT JOIN tb_tele_userinfo l6 ON d.MOBILE=l6.MOBILE

 

LEFT JOIN tb_userinfo e ON d.INVITER_INVITATION_CODE = e.INVITATION_CODE

LEFT JOIN tb_tele_userinfo l8 ON e.MOBILE=l8.MOBILE

 

WHERE

a.MOBILE = INPARA  AND (l.ID is   null or l.ID ='')

AND (

(l8.MOBILE IS NOT NULL AND l8.MOBILE !='')

OR (l6.MOBILE IS NOT NULL AND l6.MOBILE !='')

OR (l4.MOBILE IS NOT NULL AND l4.MOBILE !='')

OR (l2.MOBILE IS NOT NULL AND l2.MOBILE !='')

)

);

 

IF fid is not null THEN

SET str = fid;

 

 

##通过归属id获取归属坐席，团队

    SELECT ID,TEMID INTO @empid ,@temid  FROM financial_sales_employment WHERE MOBILE=fid;

 

    IF (@empid IS NOT NULL) OR ( @temid IS NOT NULL) THEN

 

      SET @cnt = (SELECT COUNT(1) FROM financial_sales_order WHERE MOBILE=INPARA);

  ##自动导入的名单之前数据库已经存在，不处理，将这个理财表名单标记为已分派

      IF @cnt > 0 THEN

 

   ##UPDATE tb_userinfo SET RE_ID=str,ISASSIGN='1' WHERE MOBILE=INPARA;报异常

     UPDATE tb_userinfo SET RE_ID=str,ISASSIGN='1' WHERE MOBILE=INPARA;

     ELSE

 

    ##进行分派---还需要增加分派时间 开始事物

     START TRANSACTION;

    INSERT INTO financial_sales_order(MOBILE, REGISTER_TIME, CUSTOMER_NAME, ISINVEST,ORDER_TYPE,EMP_ID, TEM_ID, CREATED_TIME, ASSIGN_TIME) SELECT MOBILE, REG_TIME, CUSTOMER_NAME, ISINVEST, ORDER_TYPE,@empid, @temid,date(now()) ,date(now()) FROM tb_userinfo WHERE MOBILE=INPARA;

 

    ##更新理财电销名单状态

    UPDATE tb_userinfo SET RE_ID=str,ISASSIGN='1' WHERE MOBILE=INPARA;

        IF  V_ERRMSG = '002' THEN

         ROLLBACK;

        ELSE

         COMMIT;

       END IF;

 

    END IF;

 

 

 

 

      END IF;

 

  ELSE

   SET str = fid;

   SET V_ERRMSG ='仓单不存在！';

    END IF;

  END IF;

 

 

 

 

 

  SET OUTPARA = str;

   /*游标向下走一步*/

        FETCH cur1 INTO tmpMobile;

      END WHILE;

 CLOSE cur1;

END