Mysql之WHILE替代游标实现子母表三层遍历拷贝

背景：

在做测试用例管理的时候，需要做测试用例的拷贝，测试用例下挂载测试步骤子表，测试步骤下挂载测试数据子表，都是一对多的关系。涉及遍历母表根据母表每条记录对应的子表记录进行拷贝，一开始考虑用游标，但是之前做过erp知道游标的效率是比较低的，而且会独占表资源。所以想用while来做，google了一下相关资料，实现了代码，现将代码分享如下

CREATE DEFINER=`root`@`%` PROCEDURE `copy_case`(param_caseid int(11))
BEGIN
     DECLARE maxCnt INT DEFAULT 0;  
     DECLARE i INT DEFAULT 0;  
     INSERT INTO tc_case(testcasename,description,caselevel,runner,groupid,classname,create_time) 
            select concat(testcasename,'.01') testcasename,description,caselevel,runner,groupid,classname,now() create_time 
            from tc_case where caseid =  param_caseid;
     set @id = LAST_INSERT_ID();
     /**插入测试步骤**/
     INSERT INTO tc_step(testcaseid,steptype,stepmethod,description,module,stepid,create_time,classname,oldstepid) 
            select @id,steptype,stepmethod,description,module,stepid,now() create_time,classname,id 
            from tc_step where testcaseid = param_caseid;
            
    DROP TABLE IF EXISTS Gather_Data_Tmp;          
    CREATE TEMPORARY TABLE Gather_Data_Tmp(  
        `Tmp_Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,  
        `teststepid` int(11) NOT NULL,  
        `oldstepid` int(11) NOT NULL,
        PRIMARY KEY (`Tmp_Id`)  
    )ENGINE=MyISAM DEFAULT CHARSET=utf8;     
                     
                                                             
    SET @tSql = concat('INSERT INTO Gather_Data_Tmp (`teststepid`,`oldstepid`)   
                                            SELECT id, oldstepid   
                                            FROM tc_step   
                                            WHERE testcaseid =',@id);  
    PREPARE gatherData FROM @tSql;  
    EXECUTE gatherData;  
    SELECT MIN(`Tmp_Id`) INTO i FROM Gather_Data_Tmp;  
    SELECT MAX(`Tmp_Id`) INTO maxCnt FROM Gather_Data_Tmp;  
    /**插入测试数据**/
    WHILE i <= maxCnt DO  
        set @teststepid = (SELECT teststepid FROM Gather_Data_Tmp WHERE Tmp_Id = i);  
        set @oldstepid = (SELECT oldstepid FROM Gather_Data_Tmp WHERE Tmp_Id = i);
        
        INSERT INTO tc_step_data(value,k_key,create_time,type,description,stepid ) 
            select value,k_key,now() create_time,type,description,@teststepid 
            from tc_step_data where stepid = @oldstepid;
        SET i = i + 1;  
    END WHILE; 

   
    COMMIT;

END;