存储函数中DATE_SUB/DATE_ADD的返回值

Question

我正在尝试编写一个存储函数，它以适当的ISO格式(yyyy-mm-dd)获取一个字符串，然后从该字符串中减去一定数量的工作日。基于this question here，我已经尝试了两个公认的答案以及一些不同的答案，然而，他们都只是在说如何编写纯this question here，并且没有一个函数的示例。

我目前拥有的是这样的：

delimiter //
CREATE DEFINER=`root`@`localhost` FUNCTION `WEEKDATE_SUB` (days TINYINT, date_val VARCHAR(16))
RETURNS DATE DETERMINISTIC
BEGIN
    DECLARE SUBVAL INT;
    DECLARE dow INT;
    CASE
            WHEN dow=1 THEN SET SUBVAL = (days +(FLOOR((days-0.5)/5)+1)*2 - 1);
            WHEN dow=2 THEN SET SUBVAL = (days +(FLOOR((days-0.5)/5)+1)*2);
            WHEN dow=3 THEN SET SUBVAL = (days-1 +(FLOOR(((days-1)-0.5)/5)+1)*2 + 1);
            WHEN dow=4 THEN SET SUBVAL = (days-2 +(FLOOR(((days-2)-0.5)/5)+1)*2 + 2);
            WHEN dow=5 THEN SET SUBVAL = (days-3 +(FLOOR(((days-3)-0.5)/5)+1)*2 + 3);
            WHEN dow=6 THEN SET SUBVAL = (days-4 +(FLOOR(((days-4)-0.5)/5)+1)*2 + 4);
            WHEN dow=7 THEN SET SUBVAL = (days-5 +(FLOOR(((days-5)-0.5)/5)+1)*2 + 5);
    END CASE
    RETURN DATE_SUB(date_val, INTERVAL SUBVAL DAY);
END;//

当我尝试添加它时，我得到一个模糊的错误(就像mysql喜欢提供的那样)：您的SQL语法中有一个错误；请查看与您的MariaDB服务器版本对应的手册，以获得正确的语法，以便在第14行使用'RETURN DATE_SUB(date_val，INTERVAL SUBVAL DAY)；END‘。

我在return上尝试了几种变体，包括尝试为Date sub定义一个变量并返回该变量，但这几乎是相同的错误。

在函数之外，我知道这是有效的，所以看起来我应该能够返回它。

SELECT DATE_SUB("2016-01-01", INTERVAL 4 DAY);

Answer 1

文档建议您使用END CASE，而不是END

此外，如果使用CASE expr WHEN value2 THEN .... WHEN value2 THEN ... END CASE版本，存储函数的执行速度可能会更快，因为它不必重复调用DAYOFWEEK函数7次。

Answer 2

您还可以使用下面这样的内容

delimiter //
CREATE DEFINER=`root`@`localhost` FUNCTION `WEEKDATE_SUB` (date_val VARCHAR(10), days TINYINT)
RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
    RETURN  date_val - INTERVAL
            FLOOR(days/5)*7 + 
            IF(DAYOFWEEK(date_val)-1 <= days - FLOOR(days/5)*5
              , (days - FLOOR(days/5)*5)+2
              , days - FLOOR(days/5)*5
           ) DAY;
END;//

示例

mysql> SELECT WEEKDATE_SUB('2017-02-06',1);
+------------------------------+
| WEEKDATE_SUB('2017-02-06',1) |
+------------------------------+
| 2017-02-03                   |
+------------------------------+
1 row in set (0,00 sec)

mysql> SELECT WEEKDATE_SUB('2017-02-07',1);
+------------------------------+
| WEEKDATE_SUB('2017-02-07',1) |
+------------------------------+
| 2017-02-06                   |
+------------------------------+
1 row in set (0,00 sec)

mysql> SELECT WEEKDATE_SUB('2017-02-07',2);
+------------------------------+
| WEEKDATE_SUB('2017-02-07',2) |
+------------------------------+
| 2017-02-03                   |
+------------------------------+
1 row in set (0,00 sec)

mysql>

Answer 3

这就是我最终需要让它工作的东西。请注意，；以及我使用CASE语句的方式的变化。

delimiter //
CREATE DEFINER=`root`@`localhost` FUNCTION `WEEKDATE_SUB` (date_val VARCHAR(10), days TINYINT)
RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
    DECLARE SUBVAL INT;
    DECLARE dow INT;
    SET dow = DAYOFWEEK(date_val);
    CASE dow
            WHEN 1 THEN SET SUBVAL = (days   +(FLOOR((days-0.5)/5)+1)*2 - 1);
            WHEN 2 THEN SET SUBVAL = (days   +(FLOOR((days-0.5)/5)+1)*2);
            WHEN 3 THEN SET SUBVAL = (days-1 +(FLOOR(((days-1)-0.5)/5)+1)*2 + 1);
            WHEN 4 THEN SET SUBVAL = (days-2 +(FLOOR(((days-2)-0.5)/5)+1)*2 + 2);
            WHEN 5 THEN SET SUBVAL = (days-3 +(FLOOR(((days-3)-0.5)/5)+1)*2 + 3);
            WHEN 6 THEN SET SUBVAL = (days-4 +(FLOOR(((days-4)-0.5)/5)+1)*2 + 4);
            WHEN 7 THEN SET SUBVAL = (days-5 +(FLOOR(((days-5)-0.5)/5)+1)*2 + 5);
            ELSE SET SUBVAL = days;
    END CASE;
    RETURN DATE_SUB(date_val, INTERVAL SUBVAL DAY);
END;//