mysql 存储过程使用游标时 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE 会提前执行的坑...

10/14 15:05
阅读数 41

在MySQL的存储过程中经常会看到这句话:DECLARE CONTINUE HANDLER FOR NOT FOUND。

它的含义是:若没有数据返回,程序继续,并将变量 IS_FOUND 设为 0 ,这种情况是出现在类似:select XX into XXX from tablename 的时候发生的。mysql游标循环的使用

-- 抓取FETCH的时候,如果cur为null(SELECT i FROM test.t),则会触发SET done = TRUE

CREATE PROCEDURE `test`.`new_procedure` ()
BEGIN
-- 需要定义接收游标数据的变量 
  DECLARE a CHAR(16);
  -- 游标
  DECLARE cur CURSOR FOR SELECT i FROM test.t;
  -- 遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;
  -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  -- 打开游标
  OPEN cur;
  
  -- 开始循环
  read_loop: LOOP
    -- 提取游标里的数据,这里只有一个,多个的话也一样;
    FETCH cur INTO a;-- 抓取FETCH的时候,如果cur为null(SELECT i FROM test.t),则会触发SET done = TRUE
    -- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- 这里做你想做的循环的事件

    INSERT INTO test.t VALUES (a);

  END LOOP;
  -- 关闭游标
  CLOSE cur;

END

我们在使用储存过程中使用游标遍历数据的时候的基本写法如下:

create procedure proc_test() sql security invoker
begin
  declare p_id varchar(32);
  declare done tinyint default false;
  declare c_cur cursor for select id from user;
  declare continue handler for not found set done = true;
  
  open c_cur;
    fetch c_cur into p_id;
    while !done do
    ... #程序逻辑
    fetch c_cur into p_id;
    end while;
  close c_cur;
end;

正常情况这么写是没问题的,可是如果你在while里面的要是有select语句的话就有问题了。如果说你的处理逻辑是这样的:

while !done do
  select * from user_role r where r.user_id = p_id;
  fetch c_cur into p_id; 
end while;

那么当你的select * from user_role r where r.user_id = p_id;找不到数据的时候,declare continue handler for not found set done = true;这句就会执行,有done = true,所以循环体会提前跳出。通过测试得出,declare continue handler for not found set done = true 是对全局的select有效的,只要有一条select语句返回空,那么就是触发该语句(只要任何一个地方获取不到数据,就会触发set done = ture ,包括SELECT 和FETCH INTO )

解决方法就是确保while里面的select永远不会返回空

select * from user_role r where r.user_id = p_id;

#改成下面这样
select col1, col2, ... from
(select col1, col2, ... from user_role r where r.user_id = p_id
union all
select '' col1, '' col2, ...) t

这样的话就可以保证select肯定不是空集合。

代码里面的坑:

/*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/ 
DECLARE l_done INT DEFAULT  0;//设定默认值
DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  l_done = 1; //如果没有获取数据,则set 为1

OPEN  cur_product_data; 
FETCH  cur_product_data INTO dataValues,dataName; //获取游标数据
REPEAT  
	IF dataName=CONCAT('{定额',costName,'}')  THEN
		SET baseCost1=dataValues;
	ELSEIF dataName=CONCAT('{',costName,'}') THEN
		SET baseCost2=dataValues;
	ELSEIF dataName='{设备费}' THEN
		SET equipment=dataValues;
	ELSEIF dataName='{定额设备费}' THEN
		SET normEquipment=dataValues;
	ELSEIF dataName='{专项费用}' THEN
		SET specialFee=dataValues;
	ELSEIF dataName=CONCAT('{定额',costName,'【不含设备费及专项费用】}') THEN
		SET baseCost5=dataValues;
	ELSEIF dataName=CONCAT('{定额',costName,'【不含专项】}') THEN
		SET baseCost6=dataValues;
	END IF;
	 
FETCH  cur_product_data INTO dataValues,dataName;  
UNTIL  l_done = 1 //如果FETCH INFO 抓取数据,没有获取到,则会set l_done = 1; 
END REPEAT;
CLOSE  cur_product_data; 

--  累计计算	
SET l_done=0;
OPEN cur_product_lib;
-- 第一个循环
   lib_loop: LOOP   -- Loop through org_grade
        FETCH cur_product_lib INTO libId,libName,libBaseType,libLeastMoney,areaUid;
		IF l_done=1 THEN LEAVE lib_loop;
		END IF;
-- 结束第一个循环
	END LOOP lib_loop;
CLOSE cur_product_lib;
  • 1.在第一个循环lib_loop那里,前面有设置SET l_done=0;打开游标OPEN cur_product_lib的时候,也输出了SELECT l_done的值也是0
  • 2.在FETCH cur_product_lib INTO libId,libName,libBaseType,libLeastMoney,areaUid;的前面输出SELECT l_done的值也是0
  • 3.执行了 FETCH cur_product_lib INTO libId,libName,libBaseType,libLeastMoney,areaUid ;这句话,之后输出SELECT l_done的值就为1了
  • 4.我一直纳闷,没有调用set l_done = 1,为何l_done就从0变成1了呢?后面才知道DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  l_done = 1; //如果没有获取数据,则set 为1对全局有效,只要没有获取到数据,那么就会调用SET  l_done = 1;这里是因为 FETCH cur_product_lib INTO libId,libName,libBaseType,libLeastMoney,areaUid;没有获取到数据 即  cur_product_lib 查询的时候一条记录都没有...

DECLARE cur_product_lib CURSOR FOR SELECT l.ID,l.NAME,l.BASE_TYPE,l.LEAST_MONEY,l.AREA_UID FROM 
PROGRESSION_RATE_LIB l WHERE l.MAKE_RULE=V_MAKE_RULE AND l.COST_TYPE LIKE CONCAT 
('%',V_COST_TYPE,',%')  AND (l.AREA_UID='000000' OR l.AREA_UID=V_AREA_UID);  /*First: Delcare a 
cursor,首先这里对游标进行定义*/ 

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部