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

敏敏张

2018-06-14

关注关注

游标实现到了最后一个结束之后此时会抛出 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;

ELSE

SET str = fid;

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

END IF;

SET OUTPARA = str;