mysql存储过程总是报1329错误 No data - zero rows fetched, selected, or processed

[hi_kevin]

存储过程如下：

DELIMITER $$

DROP PROCEDURE IF EXISTS `SP_BACK_BET_CHECKOVER`$$

CREATE  PROCEDURE `SP_BACK_BET_CHECKOVER`(
	IN p_drawdateId BIGINT, 
	IN p_gameId INT
)
proc:
    BEGIN
	DECLARE _number VARCHAR(4);
	DECLARE _big DECIMAL(13,6);
	DECLARE _small  DECIMAL(13,6);
	
	DECLARE _stockMaxPrize DECIMAL(20,6);
	DECLARE _weekDay INT;
	
	DECLARE _number_game_draw VARCHAR(20);
	
	DECLARE _firstBigPrize DECIMAL(13,6); 
	DECLARE _firstSmlPrize DECIMAL(13,6); 
	
	DECLARE _eatBig DECIMAL(13,6);
	DECLARE _eatSml DECIMAL(13,6);
	
	DECLARE _maxBig DECIMAL(13,6); 
	DECLARE _maxSmall DECIMAL(13,6);
	
	
	DECLARE done, error BOOLEAN DEFAULT FALSE;
	
	
	DECLARE c CURSOR FOR   
		 SELECT number,SUM(big) big,SUM(small) small FROM TB_BACK_EAT 
		 WHERE game_id=p_gameId AND drawDate_id=p_drawdateId AND STATUS=0 
		 GROUP BY number;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = TRUE,done=TRUE;
	DECLARE CONTINUE HANDLER FOR 1329 SET done = TRUE;
		 
	SELECT stock_max_prize,week_day INTO _stockMaxPrize,_weekDay 
FROM TB_BACK_DRAW_DATE WHERE id = p_drawdateId;
	SELECT first_big,first_sml INTO _firstBigPrize,_firstSmlPrize 
FROM TB_BACK_GAME WHERE id=p_gameId;
	
	SET _maxBig = _stockMaxPrize/_firstBigPrize;
	SET _maxSmall = _stockMaxPrize/_firstSmlPrize;
	
	SET autocommit=0; 
	OPEN c;
	   IF error THEN  
	      SELECT error;
	      LEAVE proc; 
	   END IF;  
	 l1:LOOP
          FETCH c INTO _number,_big,_small;
		IF NOT done THEN
--中间逻辑代码省略……
		    INSERT INTO temp(val) VALUES(1);
		ELSE
		    LEAVE l1;
	    END IF;
	   END LOOP;
	
	CLOSE c;
	COMMIT;
    END$$

DELIMITER ;

结果总是报下面的错误
0 row(s) affected, 1 warning(s)

Execution Time : 10.060 sec
Transfer Time : 0 sec
Total Time : 10.061 sec

Error Code : 1329
No data - zero rows fetched, selected, or processed
---------------------------------------------------


游标查询出来是有数据的 并且temp表里已经成功插入数据。
请教如何解决这个问题？

[ACMAIN_CHM]

建议提供相关表的 create table , insert into 语句，这样别人可以在自己的环境上创建相同测试以调试

[hi_kevin]

从新整理了一下问题 贴出测试相关语句： Mysql版本为：5.5.15 环境 win7 64位操作系统 整理后的建库建表语句如下：

CREATE DATABASE `test` ;

USE `test`;


DROP TABLE IF EXISTS `tb_back_eat`;

CREATE TABLE `tb_back_eat` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `game_id` BIGINT(20) DEFAULT '0',
  `drawDate_id` BIGINT(20) DEFAULT '0',
  `number` VARCHAR(4) DEFAULT NULL,
  `big` DECIMAL(25,6) DEFAULT '0.000000',
  `small` DECIMAL(25,6) DEFAULT '0.000000',
  `STATUS` TINYINT(4) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;


DROP TABLE IF EXISTS `temp`;

CREATE TABLE `temp` (
  `val` VARCHAR(20) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

测试数据：

INSERT INTO tb_back_eat(game_id, drawDate_id,number,big,small,STATUS)
VALUES ('1','5','1234','10','20',0),('1','5','2345','20','4',0),('1','5','3456','20','4',0),('1','5','1234','5','20',0);

存储过程如下：

DELIMITER $$


DROP PROCEDURE IF EXISTS `SP_BACK_BET_CHECKOVER`$$

CREATE  PROCEDURE `SP_BACK_BET_CHECKOVER`(
	IN p_drawdateId BIGINT, 
	IN p_gameId INT
)
proc:
    BEGIN
	DECLARE _number VARCHAR(4);
	DECLARE _big DECIMAL(13,6);
	DECLARE _small  DECIMAL(13,6);
	
	DECLARE _stockMaxPrize DECIMAL(20,6);
	DECLARE _weekDay INT;
	
	DECLARE _number_game_draw VARCHAR(20);
	
	DECLARE _firstBigPrize DECIMAL(13,6); 
	DECLARE _firstSmlPrize DECIMAL(13,6); 
	
	DECLARE _eatBig DECIMAL(13,6);
	DECLARE _eatSml DECIMAL(13,6);
	
	DECLARE _maxBig DECIMAL(13,6); 
	DECLARE _maxSmall DECIMAL(13,6);
	
	
	DECLARE done, error BOOLEAN DEFAULT FALSE;
	
	
	DECLARE c CURSOR FOR   
		 SELECT number,SUM(big) big,SUM(small) small FROM TB_BACK_EAT 
		 WHERE game_id=p_gameId AND drawDate_id=p_drawdateId AND STATUS=0 
		 GROUP BY number;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = TRUE,done=TRUE;
	DECLARE CONTINUE HANDLER FOR 1329 SET done = TRUE;
		 
	
	SET autocommit=0; 
	OPEN c;
	   IF error THEN  
	      SELECT error;
	      LEAVE proc; 
	   END IF;  
	 l1:LOOP
          FETCH c INTO _number,_big,_small;
		IF NOT done THEN
		    INSERT INTO temp(val) VALUES(1);
		ELSE
		    LEAVE l1;
	    END IF;
	   END LOOP;
	
	CLOSE c;
	COMMIT;
    END$$

DELIMITER ;

调用方法：

CALL SP_BACK_BET_CHECKOVER(5,1);

[hi_kevin]

引用 1 楼 rucypli 的回复:

INSERT INTO temp(val) VALUES(1);前面加一个select now()看看能不能打印出来

temp中可以插入数据。所以select now()肯定输出了

[hi_kevin]

引用 2 楼 WWWWA 的回复:

在SELECT　WHERE后加上 SET error = FALSE; set done=FALSE

问题在4楼已经重新编辑，没明白您什么意思，请在看一下 谢谢

[rucypli]

INSERT INTO temp(val) VALUES(1);前面加一个select now()看看能不能打印出来

[WWWWA]

在SELECT　WHERE后加上 SET error = FALSE; set done=FALSE

[sunzhujie001]

mysql> CALL SP_BACK_BET_CHECKOVER(5,1); +-------+ | error | +-------+ | 1 | +-------+ 我这里执行的，返回error 不是1329