为什么只查出来一条记录？

Question

DELIMITER $$DROP PROCEDURE IF EXISTS `wincodb`.`P_GetBosCombinedBegin`$$CREATE DEFINER=`root`@`%` PROCEDURE `P_GetBosCombinedBegin`($M_FDate VARCHAR(45),$M_TDate VARCHAR(45))
BEGIN
DECLARE $T_FWorkNo,$T_FCustomerName,$T_FDeliveryDate VARCHAR(45) CHARACTER SET utf8;
DECLARE $T_FDate DATE;
DECLARE $T_FName VARCHAR(125) CHARACTER SET utf8;
DECLARE $T_FQty,$T_FYSSL,$T_FZDSS,$T_FJGQty,$T_FBPSL,$LastQty DECIMAL(28,10);
DECLARE $j,$LastPC,$v_num INT;
DECLARE $FQZ,$FYS,$FGY,$FKZ,$FBZ,$FBH,$FCK,$FMK TINYINT;
#创建光标
DECLARE $done INT DEFAULT 0;
DECLARE PMC_CH CURSOR FOR
SELECT FBillNo,FCustomerName,FName,FYSSL,FZDSS,FJGQty,FDeliveryDate,FQZ,FYS,FGY,FKZ,FBZ,FBH,FCK,FMK
FROM WincoBosCombinedPmc
WHERE(FDate>=$M_FDate)AND(FDate<=$M_TDate)
UNION
SELECT DISTINCT A.FBillNo AS FBillNo,A.FCustomerName,A.FName,A.FYSSL,A.FZDSS,A.FJGQty,A.FDeliveryDate,A.FQZ,A.FYS,A.FGY,A.FKZ,A.FBZ,A.FBH,A.FCK,A.FMK
FROM WincoBosCombinedPmc A INNER JOIN WincoBosCombinedPmcEntry B
ON A.FBillNo=B.FWorkNo
WHERE(B.FDate>=$M_FDate)AND(B.FDate<=$M_TDate)
ORDER BY FBillNo;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $done=1;
DROP TABLE IF EXISTS t_GetBeginList;
CREATE TABLE t_GetBeginList(
  FBillNo VARCHAR(45) NULL,
  FCustomerName VARCHAR(45) NULL, 
  FName VARCHAR(125) NULL, 
  FGX  VARCHAR(45) NULL,
  FYSSL  DECIMAL(28,10) NULL,
  FQty DECIMAL(28,10) NULL,
  FDeliveryDate VARCHAR(45) NULL,
  FDate DATE DEFAULT '0000-00-00'
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
SET $j=1;
SET $T_FYSSL=0;
SET $T_FZDSS=0;
SET $T_FJGQty=0;
OPEN PMC_CH;
REPEAT
  FETCH PMC_CH INTO $T_FWorkNo,$T_FCustomerName,$T_FName,$T_FYSSL,$T_FZDSS,$T_FJGQty,$T_FDeliveryDate,$FQZ,$FYS,$FGY,$FKZ,$FBZ,$FBH,$FCK,$FMK;
  IF NOT $done THEN
    #最后一道工序
    IF $FQZ=1 THEN
      SET $LastPC=1;
      SET $LastQty=$T_FZDSS;
    END IF;
    IF $FYS=1 THEN
      SET $LastPC=2;
      SET $LastQty=$T_FZDSS;
    END IF;
    #工序在生产
    SELECT COUNT(FWorkNo) INTO $v_num FROM WincoBosCombinedPmcEntry 
    WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
    GROUP BY FWorkNo,FOrder,FMethodID;
    IF $v_num>0 THEN
      SELECT SUM(FBPSL) INTO $T_FBPSL FROM WincoBosCombinedPmcEntry 
      WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
      GROUP BY FWorkNo,FOrder,FMethodID;
    ELSE
      SET $T_FBPSL=0;
    END IF;
    #判断最后工序有无销单
    IF $T_FBPSL=0 THEN
      LABLE1:WHILE $j<=2 DO
        IF $FQZ=1 AND(SELECT COUNT(FWorkNo) FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID=2)=0 THEN
          IF(SELECT COUNT(FWorkNo)FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID<2)>0 THEN
            SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry 
            WHERE FWorkNo=$T_FWorkNo AND FOrder=1 AND FMethodID<2
            GROUP BY FDate;
          ELSE
            SET $T_FDate='0000-00-00';
          END IF;
          INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
          VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待切',$T_FZDSS,$T_FZDSS,$T_FDeliveryDate,$T_FDate);
          LEAVE LABLE1;
        END IF;
        IF $FYS=1 AND (SELECT COUNT(FWorkNo) FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=2 AND FMethodID=2)=0 THEN
          IF(SELECT COUNT(FWorkNo)FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=2 AND FMethodID<2)>0 THEN
            SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry 
            WHERE FWorkNo=$T_FWorkNo AND FOrder=2 AND FMethodID<2
            GROUP BY FDate;
          ELSE
            SET $T_FDate='0000-00-00';
          END IF;
          INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
          VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待印',$T_FZDSS,$T_FZDSS,$T_FDeliveryDate,$T_FDate);
          LEAVE LABLE1;
        END IF;
        SET $j=$j+1;
      END WHILE LABLE1;
    ELSEIF $T_FBPSL<$LastQty THEN
      IF $LastPC=1 THEN
        SET $T_FQty=0;
        IF(SELECT COUNT(FWorkNo) FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2)>0 THEN
          SELECT SUM(FBPSL) INTO $T_FQty FROM WincoBosCombinedPmcEntry 
          WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
          GROUP BY FWorkNo,FOrder,FMethodID;
        END IF;
        IF(SELECT COUNT(FWorkNo) FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2)>0 THEN
          SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry 
          WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2
          GROUP BY FDate;
        ELSE
          SET $T_FDate='0000-00-00';
        END IF;
        INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
        VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待切',$T_FZDSS,$T_FZDSS-$T_FQty,$T_FDeliveryDate,$T_FDate);
      END IF;
      IF $LastPC=2 THEN
        SET $T_FQty=0;
        IF(SELECT COUNT(FWorkNo) FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2)>0 THEN
          SELECT SUM(FBPSL) INTO $T_FQty FROM WincoBosCombinedPmcEntry 
          WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID=2
          GROUP BY FWorkNo,FOrder,FMethodID;
        END IF;
        IF(SELECT COUNT(FWorkNo) FROM WincoBosCombinedPmcEntry WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2)>0 THEN
          SELECT MIN(FDate) INTO $T_FDate FROM WincoBosCombinedPmcEntry 
          WHERE FWorkNo=$T_FWorkNo AND FOrder=$LastPC AND FMethodID<2
          GROUP BY FDate;
        ELSE
          SET $T_FDate='0000-00-00';
        END IF;
        INSERT INTO t_GetBeginList(FBillNo,FCustomerName,FName,FGX,FYSSL,FQty,FDeliveryDate,FDate)
        VALUE($T_FWorkNo,$T_FCustomerName,$T_FName,'待印',$T_FZDSS,$T_FZDSS-$T_FQty,$T_FDeliveryDate,$T_FDate);
      END IF;
    END IF;
  END IF;
UNTIL $done END REPEAT;
CLOSE PMC_CH;
SELECT * FROM t_GetBeginList ORDER BY FDate,FBillNo;
SELECT COUNT(FBillNo)FROM t_GetBeginList;
DROP TABLE t_GetBeginList;
END$$DELIMITER ;

Answer 1

估计你的问题是在游标移动的这个循环中，操作了其它的 select 返回空集，导致 $done = 1
REPEAT
FETCH PMC_CH INTO $T_FWorkNo,$T_FCustomerName,$T_FName,$T_FYSSL,$T_FZDSS,$T_FJGQty,$T_FDeliveryDate,$FQZ,$FYS,$FGY,$FKZ,$FBZ,$FBH,$FCK,$FMK;set $tempdone=$done
....UNTIL $tempdone END REPEAT;

Answer 2

操作了其它的 select 返回空集,我测试一下。