求优化一个存储过程.起始就是怎么遍历一行中的所有列并进行判断其值
PROCEDURE monitor_calculate(IN in_fatherid bigint, IN occurTimestamp bigint, OUT flag int)
monitor_calculate:
BEGIN
DECLARE ch1,ch2,ch3,ch4,ch5,ch6,ch7,ch8,ch9,ch10,ch11,ch12 varchar(20);
DECLARE strsql varchar(4000);
DECLARE tmp varchar(100);
DECLARE scount int DEFAULT 0;
DECLARE _select varchar(500) DEFAULT 'SELECT ';
DECLARE _where varchar(500) DEFAULT CONCAT(' WHERE', occurTimestamp);
SET flag = 0;
SELECT childrenID1,childrenID2,childrenID3,childrenID4,childrenID5,childrenID6,childrenID7, childrenID8,childrenID9,childrenID10,childrenID11,childrenID12 INTO ch1, ch2, ch3, ch4, ch5, ch6, ch7, ch8, ch9, ch10, ch11, ch12
FROM `monitor_calculate_info` WHERE fatherid = in_fatherid; -- '518000590017' ;
SET strsql = 'SELECT SUM(activeDemand),SUM(reactiveDemand),SUM(totalActivePower),SUM(totalReactivePower),SUM(totalActiveKwh), SUM(jianKwh),SUM(fengKwh),SUM(pingKwh),SUM(guKwh),SUM(inductanceKwh),SUM(capacitanceKwh) from (';
IF ch1 != 'NULL' THEN
SET @tmp=CONCAT('SELECT COUNT(*) into @nub FROM monitordata', ch1, _where);
PREPARE sqltext FROM @tmp;
EXECUTE sqltext;
DEALLOCATE PREPARE sqltext;
SELECT @nub INTO scount ;
IF scount = 0 THEN
LEAVE monitor_calculate;
ELSE
SET strsql = CONCAT(strsql,_select, ch1, _where);
END IF;
END IF;
IF ch2 != 'NULL' THEN
SET @tmp=CONCAT('SELECT COUNT(*) into @nub FROM monitordata', ch2, _where);
PREPARE sqltext FROM @tmp;
EXECUTE sqltext;
DEALLOCATE PREPARE sqltext;
SELECT @nub INTO scount ;
IF scount = 0 THEN
LEAVE monitor_calculate;
ELSE
SET strsql = CONCAT(strsql, ' union ',_select, ch2, _where);
END IF;
END IF;
IF ch3 != 'NULL' THEN
SET @tmp=CONCAT('SELECT COUNT(*) into @nub FROM monitordata', ch3, _where);
PREPARE sqltext FROM @tmp;
EXECUTE sqltext;
DEALLOCATE PREPARE sqltext;
SELECT @nub INTO scount ;
IF scount = 0 THEN
LEAVE monitor_calculate;
ELSE
SET strsql = CONCAT(strsql, ' union ',_select, ch3, _where);
END IF;
END IF;
IF ch4 != 'NULL' THEN
SET @tmp=CONCAT('SELECT COUNT(*) into @nub FROM monitordata', ch4, _where);
PREPARE sqltext FROM @tmp;
EXECUTE sqltext;
DEALLOCATE PREPARE sqltext;
SELECT @nub INTO scount ;
IF scount = 0 THEN
LEAVE monitor_calculate;
ELSE
SET strsql = CONCAT(strsql, ' union ',_select, ch4, _where);
END IF;
END IF;
IF ch5 != 'NULL' THEN
SET @tmp=CONCAT('SELECT COUNT(*) into @nub FROM monitordata', ch5, _where);
PREPARE sqltext FROM @tmp;
EXECUTE sqltext;
DEALLOCATE PREPARE sqltext;
SELECT @nub INTO scount ;
IF scount = 0 THEN
LEAVE monitor_calculate;
ELSE
SET strsql = CONCAT(strsql, ' union ',_select, ch5, _where);
END IF;
END IF;
/* 此处省略了从ch56至ch12的判断*/
SET strsql = CONCAT(strsql, ' ) a');
SET @tmp=strsql;
PREPARE sqltext FROM @tmp;
EXECUTE sqltext;-- 查询结果集
SET flag=1;-- 设置结果标志
END