mysql while 游标_mysql：while循环后声明游标

我创建了一个存储过程.在执行某些操作后,我想声明游标并从该游标获取数据.但是,它给出了语法错误.下面是我的存储过程

DELIMITER $$

USE `adserver`$$

DROP PROCEDURE IF EXISTS `getDaypartTimeDetail`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getDaypartTimeDetail`(currentDate DATE,noOfdays INT,cityId BIGINT)

BEGIN

DECLARE TotalFiles, TotalDuration BIGINT;

DECLARE i INT;

DECLARE da DATE;

DECLARE dateCnt INT;

SET dateCnt = 0;

SET TotalFiles = 0;

SET TotalDuration = 0;

DROP TEMPORARY TABLE IF EXISTS tmp_date;

DROP TEMPORARY TABLE IF EXISTS temp_data;

CREATE TEMPORARY TABLE tmp_date(dday DATE);

CREATE TEMPORARY TABLE temp_data(dday DATE,daypartId INT,totalFile BIGINT,totalDur BIGINT);

WHILE(dateCnt < noOfdays) DO

SET i = 1;

INSERT INTO tmp_date(dday) VALUES (DATE_ADD(currentDate, INTERVAL dateCnt DAY));

SET dateCnt = dateCnt + 1;

END WHILE;

DECLARE exit_flag INT DEFAULT 0; ****

DECLARE dday DATE;

DECLARE dId BIGINT;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1;

DECLARE cur CURSOR FOR SELECT dday, id FROM tmp_date, daypart;

OPEN cur;

all_details_id:LOOP

FETCH cur INTO dday,dId;

IF record_not_found THEN

LEAVE all_details_id;

END IF;

INSERT INTO temp_data(dday,daypartId) VALUES (dday,dId);

END LOOP all_details_id;

CLOSE cur;

SELECT * FROM temp_data;

END$$

DELIMITER ;

最佳答案 MySQL中存在(非常奇怪的)规则,即在任何SET,INSERT等语句之前,DECLARE语句应该在BEGIN之后.请参阅这篇文章

http://rpbouman.blogspot.com/2005/10/nesting-mysql-cursor-loops.html

因此,您的脚本应如下所示：

DELIMITER $$

USE `adserver`$$

DROP PROCEDURE IF EXISTS `getDaypartTimeDetail`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getDaypartTimeDetail`(currentDate DATE,noOfdays INT,cityId BIGINT)

BEGIN

DECLARE TotalFiles, TotalDuration BIGINT;

DECLARE i INT;

DECLARE da DATE;

DECLARE dateCnt INT;

DECLARE exit_flag INT DEFAULT 0; ****

DECLARE dday DATE;

DECLARE dId BIGINT;

DECLARE cur CURSOR FOR SELECT dday, id FROM tmp_date, daypart;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET exit_flag = 1;

SET dateCnt = 0;

SET TotalFiles = 0;

SET TotalDuration = 0;

DROP TEMPORARY TABLE IF EXISTS tmp_date;

DROP TEMPORARY TABLE IF EXISTS temp_data;

CREATE TEMPORARY TABLE tmp_date(dday DATE);

CREATE TEMPORARY TABLE temp_data(dday DATE,daypartId INT,totalFile BIGINT,totalDur BIGINT);

WHILE(dateCnt < noOfdays) DO

SET i = 1;

INSERT INTO tmp_date(dday) VALUES (DATE_ADD(currentDate, INTERVAL dateCnt DAY));

SET dateCnt = dateCnt + 1;

END WHILE;

OPEN cur;

all_details_id:LOOP

FETCH cur INTO dday,dId;

IF record_not_found THEN

LEAVE all_details_id;

END IF;

INSERT INTO temp_data(dday,daypartId) VALUES (dday,dId);

END LOOP all_details_id;

CLOSE cur;

SELECT * FROM temp_data;

END$$

DELIMITER ;

希望这可以帮助…