mysql - MySQL存储例程错误

我现在正在MySQL中创建存储例程，并且在查询时遇到此错误。

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near 'DATE_ADD(sDate, INTERVAL 1 DAY); END WHILE; END IF; END' at line 30

这是我的查询：

BEGIN
DECLARE code CHAR (5) DEFAULT (00000);
IF (SELECT COUNT(allocationDate) FROM ROOM_ALLOCATION WHERE allocationDate
 BETWEEN startDate AND (SELECT DATE_SUB(endDate,INTERVAL 1 DAY)) > 0) THEN
    WHILE DATEDIFF(sDate, eDate) <> 0 DO
        SET code = 0;
        WHILE code = 0 DO
            SET code =(SELECT LEFT(MD5(RAND()*100), 5));
            IF exists (SELECT * FROM BOOKING_DETAIL WHERE bDetailCode LIKE code)
            THEN
                SET code = 0;
        END IF;
        END WHILE;
        INSERT INTO ROOM_ALLOCATION (rAllocationCode, roomID, bDetailID, allocationDate)
        VALUES
        (rAlloCode, roIDm, bDetID, sDate);
        DATE_ADD(sDate, INTERVAL 1 DAY);
    END WHILE;
END IF;
END

我很想知道这个查询在哪里出错了？

预先谢谢你。

问候

最佳答案

sDate需要声明为变量，并且我假设您正在将startDate传递给过程。

您将sDate设置为等于startDate，现在可以使用DATEADD来操作sDate。 DATEADD返回值，它不修改变量；因此，您可以使用SET并将DATEADD的返回值分配回sDate，以便WHILE循环在下一个循环中使用它。

因为eDate不存在，所以我修改了eDate实例以使用endDate（我也假设您也要传入）。因为您没有修改该值，所以将其重新分配给另一个变量没有任何意义。

BEGIN
DECLARE code CHAR (5) DEFAULT (00000);
DECLARE sDate DATETIME = startDate;
IF (SELECT COUNT(allocationDate) FROM ROOM_ALLOCATION WHERE allocationDate
 BETWEEN startDate AND (SELECT DATE_SUB(endDate,INTERVAL 1 DAY)) > 0) THEN
    WHILE DATEDIFF(sDate, endDate) <> 0 DO
        SET code = 0;
        WHILE code = 0 DO
            SET code =(SELECT LEFT(MD5(RAND()*100), 5));
            IF exists (SELECT * FROM BOOKING_DETAIL WHERE bDetailCode LIKE code)
            THEN
                SET code = 0;
        END IF;
        END WHILE;
        INSERT INTO ROOM_ALLOCATION (rAllocationCode, roomID, bDetailID, allocationDate)
        VALUES
        (rAlloCode, roIDm, bDetID, sDate);
        SET sDate = DATE_ADD(sDate, INTERVAL 1 DAY);
    END WHILE;
END IF;
END