Solutions to confusion caused by MYSQL nested cursors and precautions

The solution to the confusion caused by MYSQL nested cursors is as follows: 1. during the writing and storage process, some complicated data is sometimes processed and multiple cursors are used for nesting. If the control is poor, after the child-layer cursor records are processed, the upper-layer cursor also captures the not found exception and changes the flag value. data chaos occurs. example: [SQL] BEGIN DECLARE name_stop int default 0; DECLARE www.2cto.com cur_name CURSOR FOR SELECT NAME FROM mytable; DECLARE CONTINUE HANDLER FOR NOT FOUND SET name_stop = 1; open cur_name; FETCH cur_name into v_name; start transaction; begin declare ext_stop int DEFAULT 0; DECLARE cur_ext CURSOR for select propertyid from propertys; declare continue handler for not found set ext_stop = 1 ;.............................. when the child-layer cur_ext cursor record is processed, the not found will be thrown, NOT only affecting itself, but also the upper-layer cursor will be affected. name_stop will change to 1, then the loop will jump out and will NOT be executed. www.2cto.com
The solution is to add the tag block to the compound statement, such as BLOCK1: BEGIN .... END BLOCK1; limits the scope of action to solve the interference between the cursors. 2. in actual use, SELECT... may be used... INTO... statement, but if no record is FOUND, the NULL value is NOT assigned to the corresponding variable, but not found is thrown. if a cursor is used, this will affect normal use. therefore, it is best to put the select into statement into a separate compound statement and add the tag block. Note that a temporary HANDLER needs to be added. If no HANDLER is captured, it will still jump to the upper layer, affecting the cursor. the correct method is as follows: [SQL] BLOCK4: BEGIN DECLARE temp_a int default 0; declare continue handler for not found set temp_a =-1; select account into v_exist_account from t_user where account = v_account limit 1; end BLOCK4; www.2cto.com if v_exist_account has subsequent business processing, remember to clear the value after processing. from the column hxx688