MySQL non-source code learning: two easy-to-ignore vertices in the Stored Procedure

Today, I wrote a slightly more complex stored procedure and shared it with two easy-to-ignore points. If there are packages that happen to encounter the same problem, you can spend less time figuring out the technical content of wood...
 
1. Example of a stored procedure using a cursor
Suppose it is a simple requirement: traverse f from table t, and then search for qualified records in Table t2.
 
Declare stopFlag int;
DECLARE cur_name CURSOR for f from t;
Declare continue handler for not found set stopFlag = 1;
Set stopFlag = 0;
Open cur_name;
REPEAT
FETCH cur_name INTO temp_f;
Begin
Select f3 into my_result from t2 where f2 = temp_f limit 1;
End;
UNTIL stopFlag = 1
End repeat;
CLOSE cur_name;
 
2. Point 1
Suppose that the t table has n records. How many cycles does this loop have?
Actually n + 1 times. Because stopFlag is 1 when the last line of t is reached. This value is modified in the next fetch, but after the modification, it enters the begin and no further judgment. Therefore, it will be executed once more. The value of temp_f is the same as that of the previous one.
 
Solution: After Entering Begin, judge the stopFlag value.
 
3. Point 2
Sometimes you may find that you exit after n cycles. Why?
The reason is that if select f3 into my_result from t2 where f2 = temp_f limit 1; the condition cannot match the row, set stopFlag = 1 will be triggered when you try to assign values to my_result; as a result, the exit condition is met after the until judgment.
 
Solution: pay special attention to the statements that appear inside the loop body. If so, save the stopFlag to a temporary variable after entering begin, and grant the Temporary Variable back before.

Author: "follow-up knife Ding Qi"