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 ;

分享到：

2011-10-28 16:26

浏览 1457

分类:数据库

评论