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 ;