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

天堂1111

　　




背景：

　　在做测试用例管理的时候，需要做测试用例的拷贝，测试用例下挂载测试步骤子表，测试步骤下挂载测试数据子表，都是一对多的关系。涉及遍历母表根据母表每条记录对应的子表记录进行拷贝，一开始考虑用游标，但是之前做过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;