存储过程游标循环赋值返回为NULL

存储过程的整体思路是查询dwd_cms_c_cons，得到cons_no，再根据cons_no查询dwd_cms_arc_e_cons_snap，得到calc_id结果集，循环calc_id结果集拼接成逗号隔开的字符串，再用结果去查询dwd_cms_arc_e_consprc_tactic_snap，同样的方法处理结果集，再同样去查 dwd_cms_arc_e_mp_para_snap


CREATE  PROCEDURE `test1`(IN time VARCHAR(6))
BEGIN
DECLARE calc_ids VARCHAR(64) DEFAULT(0); 
DECLARE prc_tactic_snap_ids VARCHAR(64) DEFAULT(0); 
DECLARE mp_para_snap_ids VARCHAR(64) DEFAULT(0); 
 -- 定义一个局部变量接受客户编号数据集
DECLARE s_region VARCHAR(16);
DECLARE done INT DEFAULT(0); 

-- 定义一个动态游标
DECLARE cur REF CURSOR; 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 操作器
/**查询客户号**/
-- 	OPEN cur FOR SELECT DISTINCT cons_no FROM dwd_cms_c_cons WHERE VOLT_CODE IN ('AC00101','AC01101','AC00201','AC02201', 'AC02751','AC03301','AC00351','AC05001','AC10001','AC07501')ORDER BY cons_no ;
	OPEN cur FOR SELECT DISTINCT cons_no FROM dwd_cms_c_cons WHERE CONS_NO = '1500496788';

-- 	循环
	 REPEAT
-- 	 拿到游标中的值
     FETCH cur  INTO s_region;
-- 		 判断游标是否循环是否结束
     IF NOT done THEN
					-- 查询中间表  dwd_cms_arc_e_cons_snap
				BEGIN
				-- 定义一个局部变量接受返回数据集
				DECLARE s_region1 bigint(20); 
				DECLARE done1 INT DEFAULT(0); 
				-- 定义一个动态游标
				DECLARE cur1 REF CURSOR;
				DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING, SQLSTATE '02000' SET done1 = 1; -- 操作器
					/**查询 dwd_cms_arc_e_cons_snap**/
				OPEN cur1 FOR SELECT t3.calc_id from  dwd_cms_arc_e_cons_snap t3 where t3.cons_no = s_region and t3.YM=time;
				REPEAT
				 FETCH cur1  INTO s_region1;
					 IF NOT done1 THEN
					set calc_ids = CONCAT(calc_ids,',',s_region1);
					 END IF; 
				UNTIL done1 END REPEAT; 
				CLOSE cur1; -- 关闭游标
				END;
				SELECT calc_ids 'calc_ids';
				
					-- 查询中间表  dwd_cms_arc_e_consprc_tactic_snap
				BEGIN
				DECLARE s_region2 bigint(20); -- 定义一个局部变量
				DECLARE done2 INT DEFAULT(0); 
				DECLARE cur2 REF CURSOR; -- 定义一个动态游标
				DECLARE CONTINUE HANDLER FOR SQLWARNING,SQLEXCEPTION, SQLSTATE '02000' SET done2 = 1; -- 操作器
					/**查询dwd_cms_arc_e_consprc_tactic_snap**/
				OPEN cur2 FOR select t2.prc_tactic_snap_id from dwd_cms_arc_e_consprc_tactic_snap t2 where FIND_IN_SET(t2.calc_id,calc_ids);
				REPEAT 
				FETCH cur2 INTO s_region2; 
					 IF NOT done2 THEN 
									 set prc_tactic_snap_ids = CONCAT(prc_tactic_snap_ids,',',s_region2);
					END IF; 
				UNTIL done2 END REPEAT; 
				CLOSE cur2; -- 关闭游标
				END;	
				SELECT prc_tactic_snap_ids 'prc_tactic_snap_ids';
				
				-- 查询中间表  dwd_cms_arc_e_mp_para_snap
				BEGIN
					DECLARE mp_para_region bigint(20); -- 定义一个局部变量
					DECLARE mp_para_done INT DEFAULT(0); 
					DECLARE mp_para_cur REF CURSOR; -- 定义一个动态游标
					DECLARE CONTINUE HANDLER FOR SQLWARNING,SQLEXCEPTION, SQLSTATE '02000' SET mp_para_done = 1; -- 操作器
					/**查询 E_MP_PARA_SNAP**/
					OPEN mp_para_cur FOR  select t1.mp_para_snap_id from dwd_cms_arc_e_mp_para_snap t1 where FIND_IN_SET(t1.prc_tactic_snap_id,prc_tactic_snap_ids);
					REPEAT 
					FETCH mp_para_cur INTO mp_para_region; 
						IF NOT mp_para_done THEN 
						 SELECT mp_para_snap_ids 'mp_para_snap_ids';
                                                 set mp_para_snap_ids = CONCAT(mp_para_snap_ids,',',mp_para_region);
									
						END IF; 
					UNTIL mp_para_done END REPEAT; 
					CLOSE mp_para_cur; -- 关闭游标
				END;	
	SELECT mp_para_snap_ids 'mp_para_snap_ids';
				
		
				
	

		 END IF; 
   UNTIL done END REPEAT; 
CLOSE cur; -- 关闭游标

END

打印结果为存储过程游标循环赋值返回为NULL

在游标内的打印是有结果的，但是结果返回的事NULL，

然后百度看帖子的时看到这样一句话：

也不能是你定义游标时用过的别名（如本例中的count），只要一个条件不符合，FETCH INTO就把全部的变量赋NULL值

仔细检查发现问题：

DECLARE calc_ids VARCHAR(64) DEFAULT(0); 
DECLARE prc_tactic_snap_ids VARCHAR(64) DEFAULT(0); 
DECLARE mp_para_snap_ids VARCHAR(64) DEFAULT(0);

游标内存拼接的字符创超过了变量声明时指定的长度

DECLARE calc_ids VARCHAR(225) DEFAULT(0); 
DECLARE prc_tactic_snap_ids VARCHAR(225) DEFAULT(0); 
DECLARE mp_para_snap_ids VARCHAR(225) DEFAULT(0);

问题解决

参考：MySQL游标循环取出空值的BUG

存储过程游标循环赋值返回为NULL

相关推荐