问题:游标循环中的select!
DECLARE v_a int;
DECLARE v_b int;
DECLARE i int default 0;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR select col_a from table_a;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO v_a;
IF done=1 THEN
LEAVE emp_loop;
ELSE
set i=i+1;
select i;
select col_b into v_b from table_b where col_b=v_a;
END IF;
END LOOP emp_loop;如果没有select col_b from table_b where col_b=v_a;游标正常循环!
能循环table_a的列数次!
但是有这句select col_b into v_b from table_b where col_b=v_a;就循环了一次,就done=1了!
有这句select语句难道破坏了游标,使得游标马上退出?
DECLARE v_a int;
DECLARE v_b int;
DECLARE i int default 0;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR select col_a from table_a;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO v_a;
IF done=1 THEN
LEAVE emp_loop;
ELSE
set i=i+1;
select i;
select col_b into v_b from table_b where col_b=v_a;
END IF;
END LOOP emp_loop;如果没有select col_b from table_b where col_b=v_a;游标正常循环!
能循环table_a的列数次!
但是有这句select col_b into v_b from table_b where col_b=v_a;就循环了一次,就done=1了!
有这句select语句难道破坏了游标,使得游标马上退出?
DECLARE v_b int;
DECLARE i int default 0;
DECLARE done INT DEFAULT 0;
DECLARE done1 INT ;DECLARE cur1 CURSOR FOR select col_a from table_a;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO v_a;
IF done=1 THEN
LEAVE emp_loop;
ELSE
set i=i+1;
select i;
set done1=done;
select col_b into v_b from table_b where col_b=v_a; -- acmain , 如果这句select无记录选出,则会触发NOT FOUND SET done=1;
set done=done1;
END IF;
END LOOP emp_loop;
能循环table_a的列数次!
但是有这句select col_b into v_b from table_b where col_b=v_a;就循环了一次,就done=1了!
有这句select语句难道破坏了游标,使得游标马上退出?如果没有满足记录的条件,会DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;使条件满足而退出循环
if @ee>=1 then
....
end if
DECLARE v_b int;
DECLARE i int default 0;
DECLARE done INT DEFAULT 0;
DECLARE done1 INT ;DECLARE cur1 CURSOR FOR select col_a from table_a;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO v_a;
IF done=1 THEN
LEAVE emp_loop;
ELSE
set i=i+1;
select i;
set done1=done;
select col_b into v_b from table_b where col_b=v_a; -- acmain , 如果这句select无记录选出,则会触发NOT FOUND SET done=1;
set done=done1;
END IF;
END LOOP emp_loop;