数据表WincoBosCombined_Output是有10条记录,为什么执行结果为1条记录DELIMITER $$DROP PROCEDURE IF EXISTS `wincodb`.`P_GetBosCombinedThroughput`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `P_GetBosCombinedThroughput`($M_FOrder INT,$M_FClassID INT)
BEGIN
DECLARE $T_FAllAcount,$T_FPlanAcount DECIMAL(28,10);
DECLARE $i,$j,$m,$T_FOrder,$T_FClassID int;
DECLARE $T_FNote1 VARCHAR(128);
DECLARE $T_FNote2 VARCHAR(256);
DECLARE $T_FWasteHour DECIMAL(10,2);
DECLARE $FNeedDay FLOAT(5,2);
DECLARE $T_FAverAmount,$T_FMachineQty,$T_FBC INT;
DECLARE $T_FJtName VARCHAR(45);
#创建光标
DECLARE $done INT DEFAULT 0;
DECLARE PMC_CH CURSOR FOR
SELECT FOrder,FClassID,FWasteHour,FAverAmount,FMachineQty,FBC,FJtName
FROM WincoBosCombined_Output
ORDER BY FOrder,FClassID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1;DROP TABLE IF EXISTS PMC_GetThroughput;
CREATE TABLE PMC_GetThroughput(
FOrder INT ,
FClassID INT DEFAULT 0,
FNote1 VARCHAR(128),
FNote2 VARCHAR(256)
)ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; OPEN PMC_CH;
REPEAT
FETCH PMC_CH INTO $T_FOrder,$T_FClassID,$T_FWasteHour,$T_FAverAmount,$T_FMachineQty,$T_FBC,$T_FJtName;
IF NOT $done THEN
SET $T_FAllAcount=0;
SET $T_FPlanAcount=0;
SET $i=0;
SET $j=0;
#总体情况
SELECT SUM(FBPSL-FJGSL),COUNT(FWorkNo)
INTO $T_FAllAcount,$i
FROM WincoBosCombinedPmcEntry
WHERE(FOrder=$T_FOrder)AND(FMethodID!=2)AND(FClassID=$T_FClassID)
GROUP BY FOrder,FClassID;
SET $T_FNote1=CONCAT('當前機臺為',$T_FJtName);
SET $FNeedDay=$T_FAllAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC);
IF $FNeedDay>4 THEN
SET $T_FNote1=CONCAT($T_FNote1,',系統完成所有訂單需耗時',$FNeedDay,'天,顯示狀態為忙。');
ELSE
SET $T_FNote1=CONCAT($T_FNote1,',系統完成所有訂單需耗時',$FNeedDay,'天,顯示狀態為正常。');
END IF;
#計劃排單情況
SELECT SUM(FBPSL-FJGSL),COUNT(FWorkNo)
INTO $T_FPlanAcount,$j
FROM WincoBosCombinedPmcEntry
WHERE(FOrder=$T_FOrder)AND(FMethodID!=2)AND(FClassID=$T_FClassID)AND(FisPlan=1)
GROUP BY FOrder,FClassID,FisPlan;
IF $j=0 THEN
SET $T_FNote2='該機臺目前沒有安排生產計劃。';
ELSE
SET $FNeedDay=$T_FPlanAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC);
SET $T_FNote2=CONCAT('該機臺目前有計劃排程單據合計',$j,'項,系統完成排程工單需耗時',$FNeedDay,'天,未計劃的延后生產。');
END IF;
INSERT INTO PMC_GetThroughput
VALUES($T_FOrder,$T_FClassID,$T_FNote1,$T_FNote2);
END IF;
UNTIL $done END REPEAT;
CLOSE PMC_CH;SELECT * FROM PMC_GetThroughput ORDER BY FOrder,FClassID;
DROP TABLE PMC_GetThroughput;
END$$DELIMITER ;
BEGIN
DECLARE $T_FAllAcount,$T_FPlanAcount DECIMAL(28,10);
DECLARE $i,$j,$m,$T_FOrder,$T_FClassID int;
DECLARE $T_FNote1 VARCHAR(128);
DECLARE $T_FNote2 VARCHAR(256);
DECLARE $T_FWasteHour DECIMAL(10,2);
DECLARE $FNeedDay FLOAT(5,2);
DECLARE $T_FAverAmount,$T_FMachineQty,$T_FBC INT;
DECLARE $T_FJtName VARCHAR(45);
#创建光标
DECLARE $done INT DEFAULT 0;
DECLARE PMC_CH CURSOR FOR
SELECT FOrder,FClassID,FWasteHour,FAverAmount,FMachineQty,FBC,FJtName
FROM WincoBosCombined_Output
ORDER BY FOrder,FClassID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1;DROP TABLE IF EXISTS PMC_GetThroughput;
CREATE TABLE PMC_GetThroughput(
FOrder INT ,
FClassID INT DEFAULT 0,
FNote1 VARCHAR(128),
FNote2 VARCHAR(256)
)ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; OPEN PMC_CH;
REPEAT
FETCH PMC_CH INTO $T_FOrder,$T_FClassID,$T_FWasteHour,$T_FAverAmount,$T_FMachineQty,$T_FBC,$T_FJtName;
IF NOT $done THEN
SET $T_FAllAcount=0;
SET $T_FPlanAcount=0;
SET $i=0;
SET $j=0;
#总体情况
SELECT SUM(FBPSL-FJGSL),COUNT(FWorkNo)
INTO $T_FAllAcount,$i
FROM WincoBosCombinedPmcEntry
WHERE(FOrder=$T_FOrder)AND(FMethodID!=2)AND(FClassID=$T_FClassID)
GROUP BY FOrder,FClassID;
SET $T_FNote1=CONCAT('當前機臺為',$T_FJtName);
SET $FNeedDay=$T_FAllAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC);
IF $FNeedDay>4 THEN
SET $T_FNote1=CONCAT($T_FNote1,',系統完成所有訂單需耗時',$FNeedDay,'天,顯示狀態為忙。');
ELSE
SET $T_FNote1=CONCAT($T_FNote1,',系統完成所有訂單需耗時',$FNeedDay,'天,顯示狀態為正常。');
END IF;
#計劃排單情況
SELECT SUM(FBPSL-FJGSL),COUNT(FWorkNo)
INTO $T_FPlanAcount,$j
FROM WincoBosCombinedPmcEntry
WHERE(FOrder=$T_FOrder)AND(FMethodID!=2)AND(FClassID=$T_FClassID)AND(FisPlan=1)
GROUP BY FOrder,FClassID,FisPlan;
IF $j=0 THEN
SET $T_FNote2='該機臺目前沒有安排生產計劃。';
ELSE
SET $FNeedDay=$T_FPlanAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC);
SET $T_FNote2=CONCAT('該機臺目前有計劃排程單據合計',$j,'項,系統完成排程工單需耗時',$FNeedDay,'天,未計劃的延后生產。');
END IF;
INSERT INTO PMC_GetThroughput
VALUES($T_FOrder,$T_FClassID,$T_FNote1,$T_FNote2);
END IF;
UNTIL $done END REPEAT;
CLOSE PMC_CH;SELECT * FROM PMC_GetThroughput ORDER BY FOrder,FClassID;
DROP TABLE PMC_GetThroughput;
END$$DELIMITER ;
BEGIN
DECLARE $T_FAllAcount,$T_FPlanAcount DECIMAL(28,10);
DECLARE $i,$j,$m,$T_FOrder,$T_FClassID int;
DECLARE $T_FNote1 VARCHAR(128);
DECLARE $T_FNote2 VARCHAR(256);
DECLARE $T_FWasteHour DECIMAL(10,2);
DECLARE $FNeedDay FLOAT(5,2);
DECLARE $T_FAverAmount,$T_FMachineQty,$T_FBC INT;
DECLARE $T_FJtName VARCHAR(45);
#创建光标
DECLARE $done INT DEFAULT 0;
DECLARE $done1 INT DEFAULT 0; -- add a temp var
DECLARE PMC_CH CURSOR FOR
SELECT FOrder,FClassID,FWasteHour,FAverAmount,FMachineQty,FBC,FJtName
FROM WincoBosCombined_Output
ORDER BY FOrder,FClassID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1;DROP TABLE IF EXISTS PMC_GetThroughput;
CREATE TABLE PMC_GetThroughput(
FOrder INT ,
FClassID INT DEFAULT 0,
FNote1 VARCHAR(128),
FNote2 VARCHAR(256)
)ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;OPEN PMC_CH;
REPEAT
FETCH PMC_CH INTO $T_FOrder,$T_FClassID,$T_FWasteHour,$T_FAverAmount,$T_FMachineQty,$T_FBC,$T_FJtName;
set $done1 = $done; -- keep the done
IF NOT $done THEN
SET $T_FAllAcount=0;
SET $T_FPlanAcount=0;
SET $i=0;
SET $j=0;
#总体情况
SELECT SUM(FBPSL-FJGSL),COUNT(FWorkNo)
INTO $T_FAllAcount,$i
FROM WincoBosCombinedPmcEntry
WHERE(FOrder=$T_FOrder)AND(FMethodID!=2)AND(FClassID=$T_FClassID)
GROUP BY FOrder,FClassID;
SET $T_FNote1=CONCAT('當前機臺為',$T_FJtName);
SET $FNeedDay=$T_FAllAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC);
IF $FNeedDay>4 THEN
SET $T_FNote1=CONCAT($T_FNote1,',系統完成所有訂單需耗時',$FNeedDay,'天,顯示狀態為忙。');
ELSE
SET $T_FNote1=CONCAT($T_FNote1,',系統完成所有訂單需耗時',$FNeedDay,'天,顯示狀態為正常。');
END IF;
#計劃排單情況
SELECT SUM(FBPSL-FJGSL),COUNT(FWorkNo)
INTO $T_FPlanAcount,$j
FROM WincoBosCombinedPmcEntry
WHERE(FOrder=$T_FOrder)AND(FMethodID!=2)AND(FClassID=$T_FClassID)AND(FisPlan=1)
GROUP BY FOrder,FClassID,FisPlan;
IF $j=0 THEN
SET $T_FNote2='該機臺目前沒有安排生產計劃。';
ELSE
SET $FNeedDay=$T_FPlanAcount/((8-$T_FWasteHour)*$T_FAverAmount*$T_FMachineQty*$T_FBC);
SET $T_FNote2=CONCAT('該機臺目前有計劃排程單據合計',$j,'項,系統完成排程工單需耗時',$FNeedDay,'天,未計劃的延后生產。');
END IF;
INSERT INTO PMC_GetThroughput
VALUES($T_FOrder,$T_FClassID,$T_FNote1,$T_FNote2);
END IF;
set $done = $done1; -- restore the done
UNTIL $done END REPEAT;
CLOSE PMC_CH;SELECT * FROM PMC_GetThroughput ORDER BY FOrder,FClassID;
DROP TABLE PMC_GetThroughput;
END$$DELIMITER ;
一般是什么情况将导致done=1呢?
你的过程有还有一些 select 如果这些 select 返回空的时候,你的 DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1; 就会把 done 设置为1了。