记录一次使用mysql的 - - 存储过程 - - 实例

复杂的业务的单条语句查询，并不建议使用存储过程，因为会非常耗时（即使使用了MEMORY存储引擎）！
推荐使用字段适当的冗余以完成业务需求（甚至增加冗余表，需要非常严谨的程序逻辑保证冗余字段的数据一致性）。

BEGIN

DECLARE tmpsid BIGINT;
DECLARE tmpeid BIGINT;
DECLARE tmpsubcode text;
DECLARE tmpplantnum text;
DECLARE tmpprice DECIMAL;
DECLARE tmpplantcode text;

DECLARE i int default 0;
DECLARE attr text;
DECLARE tmpattrvaluecode text;
DECLARE s1 int default 0; 
DECLARE s2 int default 0; 
DECLARE arr1 CURSOR FOR select SID,EID,SUB_COMPARTMENT,PLANT_NUM,PLANT_PRICE,PLANT_CODE from T_PROJECT_ENGINEER_DETAIL;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s1=1;

DROP TABLE if  exists T_PROJECT_PLANT_WITH_ATTR_TMP;
create table if not exists T_PROJECT_PLANT_WITH_ATTR_TMP(
		`SID`	BIGINT(20) AUTO_INCREMENT,
		`EID` BIGINT(20) NOT NULL,
		`SUB_COMPARTMENT` CHAR(32) NOT NULL,
		`PLANT_NUM`	BIGINT(20) NOT NULL,
		`PLANT_PRICE` DECIMAL NOT NULL,
		`PLANT_CODE` CHAR(32) NOT NULL,
		`ATTRS_VALUE_CODE` TEXT NOT NULL,
		PRIMARY KEY ( `SID` )
)ENGINE=MEMORY DEFAULT CHARSET=utf8;
OPEN arr1; 
fetch  arr1 into tmpsid,tmpeid,tmpsubcode,tmpplantnum,tmpprice,tmpplantcode;
WHILE s1 <> 1 DO
	set i = (select count(SID) from T_FK_SPECIFICATION_PROJECT_DETAIL where PROJECT_DETAIL_SID = tmpsid);
	set attr = '';
	WHILE i>0 DO
		set i = i-1;
		set tmpattrvaluecode = (SELECT ATTR_VALUE_CODE from T_FK_SPECIFICATION_PROJECT_DETAIL where PROJECT_DETAIL_SID = tmpsid ORDER BY ATTR_VALUE_CODE LIMIT i,1);
		IF attr='' THEN
			set attr = tmpattrvaluecode;
		ELSE
			set attr = CONCAT(attr,',',tmpattrvaluecode);
		END IF;
	END WHILE;
	insert into T_PROJECT_PLANT_WITH_ATTR_TMP (EID,SUB_COMPARTMENT,PLANT_NUM,PLANT_PRICE,PLANT_CODE,ATTRS_VALUE_CODE) VALUES(tmpeid,tmpsubcode,tmpplantnum,tmpprice,tmpplantcode,attr);
	fetch  arr1 into tmpsid,tmpeid,tmpsubcode,tmpplantnum,tmpprice,tmpplantcode;
END WHILE;
CLOSE arr1 ;
select sum(PLANT_NUM) as plant_num,sum(PLANT_PRICE) as price, ATTRS_VALUE_CODE,PLANT_CODE from T_PROJECT_PLANT_WITH_ATTR_TMP GROUP BY PLANT_CODE,ATTRS_VALUE_CODE LIMIT 0,10;
END