代码之家 › 专栏 › 技术社区 › Maksim Khaitovich

MySQL使用if-then-else方法将一条记录选择到函数内部的变量中

stored-functions mysql-error-1064 mysql

Maksim Khaitovich · 技术社区 · 7 年前

我正在尝试构建一个mysql函数,在给定输入变量的情况下,该函数能够进入其中一个if分支并使用select设置变量。然后应返回此变量。在浏览了大量手册后,我确定了以下代码:

create function dayssincelastmo(sedol varchar(255), currDate datetime, z_score double, groupnum int)
returns int
deterministic 
begin
    if zscore<0 then
        select datediff(p.`Period (Formatted)`, currDate) into @days
            from
                price as p
            where
                p.G_SEDOL = sedol
                and p.`Period (Formatted)`<=currDate
                and p.zscore<=z_score
                and p.`GRP MODEL NUM` = groupnum
                order by p.`Period (Formatted)`
                limit 1;
    end if;
    if zscore>=0 then
        select datediff(p.`Period (Formatted)`, currDate)  into @days
            from
                price as p
            where
                p.G_SEDOL = sedol
                and p.`Period (Formatted)`<=currDate
                and p.zscore>=z_score
                and p.`GRP MODEL NUM` = groupnum
                order by p.`Period (Formatted)`
                limit 1;
    end if;
return @days
end

如您所见,我将输出数量限制为1,以便从每个分支中的select中仅获取一个值。我还使用if-then-else方法,并用end-if关闭每个if语句。

但是我在第15行从解释器那里得到一个错误,它正好有一个限制1;条目:

order by p.`Period (Formatted)`
**limit 1;**

有什么不对的建议吗?

1 回复 | 直到 7 年前

wchiquito 7 年前

别忘了分号( ; )在中 RETURN 声明:

...
-- return @days
return @days;
...

更新

我无法重现问题:

mysql> DROP FUNCTION IF EXISTS `dayssincelastmo`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER //

mysql> create function dayssincelastmo(
    ->   sedol varchar(255),
    ->   currDate datetime,
    ->   z_score double,
    ->   groupnum int
    -> )
    -> returns int
    -> deterministic 
    -> begin
    ->     if zscore<0 then
    ->         select datediff(p.`Period (Formatted)`, currDate) into @days
    ->             from
    ->                 price as p
    ->             where
    ->                 p.G_SEDOL = sedol
    ->                 and p.`Period (Formatted)`<=currDate
    ->                 and p.zscore<=z_score
    ->                 and p.`GRP MODEL NUM` = groupnum
    ->                 order by p.`Period (Formatted)`
    ->                 limit 1;
    ->     end if;
    ->     if zscore>=0 then
    ->         select datediff(p.`Period (Formatted)`, currDate)  into @days
    ->             from
    ->                 price as p
    ->             where
    ->                 p.G_SEDOL = sedol
    ->                 and p.`Period (Formatted)`<=currDate
    ->                 and p.zscore>=z_score
    ->                 and p.`GRP MODEL NUM` = groupnum
    ->                 order by p.`Period (Formatted)`
    ->                 limit 1;
    ->     end if;
    -> return @days;
    -> end//
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;