存储过程如下: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 secError Code : 1329
No data - zero rows fetched, selected, or processed
---------------------------------------------------
游标查询出来是有数据的 并且temp表里已经成功插入数据。
请教如何解决这个问题?mysql1329No data - zerorows fetchedC
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 secError Code : 1329
No data - zero rows fetched, selected, or processed
---------------------------------------------------
游标查询出来是有数据的 并且temp表里已经成功插入数据。
请教如何解决这个问题?mysql1329No data - zerorows fetchedC
SET error = FALSE;
set done=FALSE
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);
temp中可以插入数据。所以select now()肯定输出了
+-------+
| error |
+-------+
| 1 |
+-------+ 我这里执行的,返回error 不是1329