MYSQL游标循环，多运行一次，为什么？ [英] MYSQL cursor loop, runs one extra round, why?

问题描述

我在MYSQL存储过程中循环访问游标结果集。我面临的一个问题是，循环总是运行完最后一个记录两次。这是我的代码，

  BEGIN 
 DECLARE not_found_creadit INT DEFAULT 0; 
 
 DECLARE cur_credit CURSOR FOR 
 SELECT customer_id，amount，status，user_type，employee，note FROM credit WHERE status ='approved'AND customer_id = int_cust_id; 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found_creadit = 1; 
 OPEN cur_credit; 
 SET not_found_creadit = 0; 
 credit_loop：LOOP 
如果not_found_creadit THEN 
关闭cur_credit; 
 LEAVE credit_loop; 
 END IF; 
 FETCH cur_credit INTO vc_customer，dec_amount，vc_status，vc_user_type，vc_emp，vc_note; 
 SELECT vc_customer，dec_amount，vc_status，vc_user_type，vc_emp，vc_note; 
 ...... 
 ...... 
 END LOOP; 
 END; 
 

意味着如果我有3条记录，循环运行4次，如果是10次循环运行11次

处理程序设置 not_found_creadit = 1

code>返回没有行，但在执行 FETCH之前检查其值时触发code> ，所以当 FETCH 失败时，循环的主体将执行一个额外的时间，然后循环循环在 之后，重新安排您的代码以检查您的变量的值。 ：

  credit_loop：LOOP 
 FETCH cur_credit INTO vc_customer，dec_amount，vc_status，vc_user_type， vc_emp，vc_note; 
 IF not_found_creadit THEN 
关闭cur_credit; 
 LEAVE credit_loop; 
 END IF; 
 SELECT vc_customer，dec_amount，vc_status，vc_user_type，vc_emp，vc_note; 
 ...... 
 ...... 
 END LOOP; 
  

考虑将您的变量的拼写更正为 not_found_credit

I'm looping through a cursor result set in a MYSQL stored procedure. I'm facing an issue which is that the loop always run thorough the last record twice. Here is my code,

BEGIN
DECLARE not_found_creadit INT DEFAULT 0;

DECLARE cur_credit CURSOR FOR 
SELECT customer_id, amount, status, user_type, employee, note FROM credit WHERE status = 'approved' AND customer_id = int_cust_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found_creadit = 1;
OPEN cur_credit;
  SET not_found_creadit = 0;
  credit_loop : LOOP 
      IF not_found_creadit THEN
        CLOSE cur_credit;
        LEAVE credit_loop;
      END IF;
      FETCH cur_credit INTO vc_customer, dec_amount, vc_status, vc_user_type, vc_emp, vc_note;
      SELECT vc_customer, dec_amount, vc_status, vc_user_type, vc_emp, vc_note;
      ......
      ......
  END LOOP;
END;

Means if I have 3 records, loop runs 4 times, if it is 10 records loop runs 11 times, etc. Any idea whats happening here?

解决方案

The handler, which sets not_found_creadit = 1, is fired when the FETCH returns no rows, but you are checking its value before executing FETCH, so the main body of your loop will execute one extra time when the FETCH fails, then the loop loop exits at the start of the next iteration.

Rearrange your code to check the value of your variable immediately after the FETCH:

credit_loop : LOOP 
    FETCH cur_credit INTO vc_customer, dec_amount, vc_status, vc_user_type, vc_emp, vc_note;
    IF not_found_creadit THEN
        CLOSE cur_credit;
        LEAVE credit_loop;
    END IF;
    SELECT vc_customer, dec_amount, vc_status, vc_user_type, vc_emp, vc_note;
    ......
    ......
END LOOP;

Also, consider correcting the spelling of your variable to not_found_credit

这篇关于MYSQL游标循环，多运行一次，为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！