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;