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 cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record.
When working with MySQL cursor, you must also declare a NOT FOUND handler to handle the situation when the cursor could not find any row. Because each time you call the FETCH statement, the cursor attempts to read the next row in the result set.
The MySQL stored program language offers three types of loops : Simple loops using the LOOP and END LOOP clauses. Loops that continue while a condition is true, using the WHILE and END WHILE clauses. Loops that continue until a condition is true, using the REPEAT and UNTIL clauses.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With