mysql - 存储过程中存在过早循环函数工作正常，但不适用于所有变量

Question

我有一个具有重复 id 的表。我创建了一个具有不同 id 的表以及我想要根据该 id 进行比较和更新的表

+----+-------+ 
| id | value |
+----+-------+
|  1 | 1:a   |
|  1 | 1     |
|  1 | 2:b   |
+----+-------+

预期输出

+----+-------+
| id | value |
+----+-------+
|  1 | 1:a 1 |
|  1 | 2:b   |
+----+-------+

我尝试过的是，我编写了一个可以完美执行的过程，但它过早地存在循环。我不知道为什么它退出有人可以指导我

DELIMITER //
CREATE PROCEDURE value_merge()
BEGIN
DECLARE v_val INTEGER DEFAULT 0;
DECLARE i INTEGER DEFAULT 1;
DECLARE row_cou INTEGER DEFAULT 0;
DECLARE colan INTEGER DEFAULT 0;
DECLARE mm_num varchar(20) DEFAULT "";
DECLARE merge_value varchar(132000) DEFAULT "";

 DEClARE merge_cursor CURSOR FOR 
 SELECT Material_Number,cou FROM zz_plant_data_given_table_form_work_bhai where flag='y';
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_val = 1;
 OPEN merge_cursor;
 get_loop: LOOP
 FETCH merge_cursor INTO mm_num,row_cou;
 IF v_val = 1 THEN 
 LEAVE get_loop;
 END IF;
  while i<=row_cou do
    SELECT Basic_Data_Text INTO merge_value FROM `zz_plant_data_given_table_form_work` where Material_Number=mm_num and seq=row_cou;
    set colan=ROUND ((LENGTH(merge_value)- LENGTH( REPLACE (merge_value, ":", "") )) / LENGTH(":"));
    IF colan>0 THEN
    set row_cou=row_cou-1;
    ELSE
    update `zz_plant_data_given_table_form_work` set sts='y' where Material_Number=mm_num and seq=row_cou;
    set row_cou=row_cou-1;
    update zz_plant_data_given_table_form_work set Basic_Data_Text=concat(Basic_Data_Text,' ',merge_value) where Material_Number=mm_num and seq=row_cou;
    END IF;
END while;
update zz_plant_data_given_table_form_work_bhai set flag='' where Material_Number=mm_num;
 END LOOP get_loop;
 CLOSE merge_cursor;
END//
DELIMITER 

;

Answer 1

这两句话会触发 NOT FOUND 处理程序，因此我建议使用另一种类型的查询来获取 merge_value

将其更改为:

set merge_value =   ( SELECT Basic_Data_Text  
                       FROM `zz_plant_data_given_table_form_work` 
                       where Material_Number=mm_num 
                        and seq=row_cou
                       limit 1 ); /* to prevent errors*/