mysql - 使用 IF-END IF 定义 MySQL 函数时出错

Question

我正在尝试在 MySQL 中编写一个函数，它将在两天内给出差异，不包括周末和自定义假期。我的 END IF 语句以红色突出显示，表示存在语法错误。任何人都可以帮忙吗？

CREATE 
FUNCTION `GetWorkingDays_FromDateRange`(date1 DATE, date2 DATE) RETURNS int(11)
BEGIN

DECLARE dateOne  DATE;
DECLARE dateTwo  DATE;

IF date1>date2  
    THEN
        SET dateTwo = date1;
        SET dateOne = date2;
    ELSE
        SET dateTwo = date2;
        SET dateOne = date1;
END IF;

DECLARE i INT;
SET i = (SELECT COUNT(*) from holidays where calendar_date BETWEEN dateOne AND dateTwo);

RETURN ABS(DATEDIFF(dateTwo, dateOne)) + 1
     - ABS(DATEDIFF(ADDDATE(dateTwo, INTERVAL 1 - DAYOFWEEK(dateTwo) DAY),
                    ADDDATE(dateOne, INTERVAL 1 - DAYOFWEEK(dateOne) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(dateOne < dateTwo, dateOne, dateTwo)) = 1)
     - (DAYOFWEEK(IF(dateOne > dateTwo, dateOne, dateTwo)) = 7)
     - i;
END

Answer 1

将所有DECLARE 语句放在任何其他语句之前。

试试这个:

CREATE FUNCTION `GetWorkingDays_FromDateRange`(date1 DATE, date2 DATE) RETURNS INT(11)
BEGIN    
    DECLARE dateOne  DATE;
    DECLARE dateTwo  DATE;
    DECLARE i INT;

    IF date1>date2  THEN
        SET dateTwo = date1;
        SET dateOne = date2;
    ELSE
        SET dateTwo = date2;
        SET dateOne = date1;
    END IF;
    SELECT COUNT(*) INTO i FROM holidays WHERE calendar_date BETWEEN dateOne AND dateTwo;

    RETURN ABS(DATEDIFF(dateTwo, dateOne)) + 1
          - ABS(DATEDIFF(ADDDATE(dateTwo, INTERVAL 1 - DAYOFWEEK(dateTwo) DAY),
                              ADDDATE(dateOne, INTERVAL 1 - DAYOFWEEK(dateOne) DAY))) / 7 * 2
          - (DAYOFWEEK(IF(dateOne < dateTwo, dateOne, dateTwo)) = 1)
          - (DAYOFWEEK(IF(dateOne > dateTwo, dateOne, dateTwo)) = 7)
          - i;
END