mysql 临时表替代游标

mysql 临时表代替游标

DELIMITER $$

USE `tech_soft_drp_demo2`$$

DROP PROCEDURE IF EXISTS `update_brandId`$$

CREATE DEFINER=`root`@`%` PROCEDURE `update_brandId`(IN $tableName VARCHAR(50) ,IN $table_item_Name VARCHAR(50),IN $mainId VARCHAR(50) ,IN $mainIdValue VARCHAR(50))
BEGIN
 DECLARE id VARCHAR(255);  
 DECLARE brandIdss VARCHAR(255);
 DECLARE l_find_pos   INT;  
 DECLARE l_new_string VARCHAR(255);
 DECLARE maxCnt INT DEFAULT 0;  
 DECLARE i INT DEFAULT 0;  
 
DROP TABLE IF EXISTS temptabl;
CREATE TEMPORARY TABLE temptabl (Tmp_Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,  mainId VARCHAR(50), brandId VARCHAR(50) ,PRIMARY KEY (`Tmp_Id`));
 SET @SQL = CONCAT('INSERT INTO TempTabl(mainId,brandId) ','select ',$mainId,' ,base_sku.BRAND_ID from ', $table_item_Name , ' LEFT JOIN  base_sku ON  ', $table_item_Name ,'.sku_Id =base_sku.id  where  ',$mainId,' IS  NOT NULL and base_sku.id is not null   and  ',$mainId,'= \'',$mainIdValue,'\'');
SELECT @SQL;	 
 PREPARE stmt FROM @SQL;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt; 
    SET l_new_string='';  
    SELECT MIN(`Tmp_Id`) INTO i FROM temptabl;  
    SELECT MAX(`Tmp_Id`) INTO maxCnt FROM temptabl;  
    WHILE i <= maxCnt DO  
        SELECT brandId INTO brandIdss FROM temptabl WHERE Tmp_Id = i; 
     
        SET l_find_pos=INSTR(l_new_string,brandIdss);  
          IF (l_find_pos=0) THEN            
             SET l_new_string = CONCAT(brandIdss,',',l_new_string);  
         END IF;

        SET i = i + 1;  
    END WHILE;  
    
     IF (LENGTH (l_new_string)>0) THEN   
     SET l_new_string = LEFT(l_new_string,LENGTH (l_new_string)-1);   
     END IF;      
    
    SET @SQL = CONCAT('UPDATE ',$tableName,' set BRAND_IDs = \'',l_new_string,'\' where id =\'',$mainIdValue,'\'');
   SELECT @SQL;	
   PREPARE stmttt FROM @SQL;
   EXECUTE stmttt;
   
   DEALLOCATE PREPARE stmttt; 

    
    #select l_new_string;	 
   
END$$
DELIMITER ;

DELIMITER $$

USE `tech_soft_drp_demo2`$$

DROP PROCEDURE IF EXISTS `updateAllTable_brandId`$$

CREATE DEFINER=`root`@`%` PROCEDURE `updateAllTable_brandId` (IN $tableName VARCHAR(50) ,IN $table_item_Name VARCHAR(50),IN $mainId VARCHAR(50))
   BEGIN
   
      DECLARE idss VARCHAR(255) ;  

      DECLARE maxCnt INT DEFAULT 0; 
      DECLARE i INT DEFAULT 0;       
      
      DROP TABLE IF EXISTS tempTable002;
      CREATE TEMPORARY TABLE tempTable002 ( Tmp_Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,mainId VARCHAR(50) ,PRIMARY KEY (`Tmp_Id`) );
      
      SET @SQL = CONCAT('INSERT INTO tempTable002(mainId) ','select id from ',$tableName);
      PREPARE stmt FROM @SQL;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      
       SET idss=''; 
     
      SELECT MIN(`Tmp_Id`) INTO i FROM tempTable002;  
      SELECT MAX(`Tmp_Id`) INTO maxCnt FROM tempTable002; 
       
       WHILE i <= maxCnt DO  
       SELECT mainId INTO idss FROM tempTable002 WHERE Tmp_Id = i; 
      
       CALL update_brandId($tableName,$table_item_Name,$mainId,idss);

        SET i = i + 1;  
    END WHILE;      


SELECT * FROM tempTable002;
    END$$

DELIMITER ;