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

系统管理员 2023-06-17 04:51 14阅读 0赞

> 复杂的业务的单条语句查询，并不建议使用存储过程，因为会非常耗时（即使使用了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