The MySQL stored procedure uses the data queried by cursor in the cursor loop as part of the SQL statement.

This is not easy to describe in brief.

 

It is probably to update the data based on the query results, and the queried data has many rows.

The code below looks complicated, but it must be prepare as follows; otherwise, the update statement does not work.

 

drop PROCEDURE `UPGRADE_SKILL`//CREATE DEFINER=`root`@`%` PROCEDURE `UPGRADE_SKILL`()BEGIN DECLARE stopFlag, us_skill_id , s_id, ct int(11);     DECLARE s_uuid, str_sql varchar(255);           DECLARE cursor1 CURSOR FOR      SELECT us.skill_id , s.id, us.uuid FROM user_skill us, dragon_fx.skill s  WHERE s.old_id=us.skill_id and s.id>us.skill_id limit 1;          DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;           SET stopFlag=0;          OPEN cursor1;     SET ct =0;     REPEAT     SET ct=ct+1;            FETCH cursor1 INTO us_skill_id , s_id, s_uuid;            SET @s_id=s_id;            SET @s_uuid=s_uuid;            SET @str_sql=concat('UPDATE user_skill SET `skill_id` = ? WHERE `uuid` = ?;');            PREPARE stmt FROM @str_sql;            EXECUTE stmt USING @s_id,@s_uuid;     UNTIL stopFlag = 1 END REPEAT;     select ct;END;//call `UPGRADE_SKILL`//select * from user_skill where uuid='00012bd21f614b02b42450fc8ec5e9f6'//

You may think that an SQL statement can be used: Update dragon. user_skill us and dragon_fx.skill S.
Set us. skill_id = S. ID
Where S. old_id = us. skill_id and S. ID> us. skill_id

However, the execution is slow, intolerable, and does not seem to work at all (in confusion ).