使用临时表替换游标：提高效率

CREATE DEFINER=`root`@`%`PROCEDURE `debug`(IN `beginTime`int, IN `checkTime`int) 

1. BEGIN 
2.     DECLARE t_id VARCHAR(64)DEFAULT ''; 
3.     DECLARE t_item TINYINT DEFAULT 0; 
4.     DECLARE t_result VARCHAR(8192)DEFAULT ''; 
5.      
6.     DECLARE maxCnt INTDEFAULT 0; 
7.     DECLARE i INTDEFAULT 0; 
8.  
9.     DROP TABLE IF EXISTS Gather_Data_Tmp; 
10.     CREATE TEMPORARYTABLE Gather_Data_Tmp( 
11.         `Tmp_Id` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
12.         `Asset_Id` VARCHAR(16) NOT NULL, 
13.         `Check_Item` TINYINT(1) NOT NULL, 
14.         `Check_Result` VARCHAR(8192)NOT NULL, 
15.         PRIMARY KEY (`Tmp_Id`) 
16.     )ENGINE=MyISAM DEFAULT CHARSET=utf8; 
17.  
18.     SET @tSql = CONCAT('INSERTINTO Gather_Data_Tmp (`Asset_Id`, `Check_Item`, `Check_Result`)  
19.                                             SELECT Asset_Id, Check_Item, Check_Result  
20.                                             FROM IDC_Gather_Info  
21.                                             WHERE Check_Time >',beginTime,' AND Check_Time <= ',checkTime); 
22.     PREPARE gatherData FROM @tSql; 
23.     EXECUTE gatherData; 
24.  
25.     SELECT MIN(`Tmp_Id`)INTO i FROM Gather_Data_Tmp; 
26.     SELECT MAX(`Tmp_Id`)INTO maxCnt FROM Gather_Data_Tmp; 
27.  
28.     WHILE i <= maxCnt DO 
29.         SELECT Asset_Id, Check_Item, Check_ResultINTO t_id, t_item, t_result FROM Gather_Data_TmpWHERE Tmp_Id = i; 
30.         SET i = i + 1; 
31.     END WHILE; 
32. END