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 ;