mysql 游标一次,MYSQL游标循环，多运行一次，为什么？

最新推荐文章于 2023-05-20 11:32:23 发布

接受现实发条熊

最新推荐文章于 2023-05-20 11:32:23 发布

阅读量86

点赞数

文章标签： mysql 游标一次

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: