mysql : declare cursor after while loop

Question

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

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; ****<-- Gives error**** 
    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 ;

Answer 1

MySQL 中有一条(很奇怪的)规则，它说 DECLARE 语句应该在 BEGIN 之后，在任何 SET、 之前INSERT 等语句。请引用这篇文章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; ****<-- Gives error**** 
    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 ;

希望这会有所帮助...