mysql - 在mysql的存储过程中使用函数

Question

我在mysql中创建了一个存储过程:

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_PublicationsByDate`(IN _DateS DATE, IN _DateF DATE)
BEGIN

    SELECT 
        CONCAT(EXTRACT(YEAR FROM filed_date), " Q", EXTRACT(QUARTER FROM filed_date)) AS date, 
        count(CONCAT(EXTRACT(YEAR FROM filed_date), " Q", EXTRACT(QUARTER FROM filed_date))) AS count
    FROM 
        applications
    WHERE
        filed_date between _DateS and _DateF
    GROUP BY
        CONCAT(EXTRACT(YEAR FROM filed_date), " Q", EXTRACT(QUARTER FROM filed_date))
    ORDER BY
        CONCAT(EXTRACT(YEAR FROM filed_date), " Q", EXTRACT(QUARTER FROM filed_date));

END

我想替换长代码

CONCAT(EXTRACT(YEAR FROM filed_date), " Q", EXTRACT(QUARTER FROM filed_date))

函数调用(为方便起见，以伪 C++ 为例):

char* func(char* date)
{
return CONCAT(EXTRACT(YEAR FROM date), " Q", EXTRACT(QUARTER FROM date))
}

因为这些长表达式可能会根据外部参数(伪代码)的不同而有所不同:

DECLARE F FUNCTION;

IF (_Type = 0)
    SET F = Func1;
ELSE
    SET F = Func2;
END IF;

SELECT F(filed_date), count(F(filed_date))...

如何在 mysql 中创建它？

Answer 1

创建函数决定我的问题。

可惜的是外部的功能

CREATE DEFINER=`root`@`localhost` FUNCTION `prepare_ScaleCondition`(_Scale VARCHAR(16), _Date DATE) RETURNS varchar(64) CHARSET utf8
BEGIN
    DECLARE _ScaleCondition VARCHAR(128) DEFAULT EXTRACT(YEAR FROM _Date);

    CASE _Scale
        WHEN 'quarter' THEN SET _ScaleCondition = CONCAT(EXTRACT(YEAR FROM _Date), ' Q', EXTRACT(QUARTER FROM _Date));
        WHEN 'year' THEN SET _ScaleCondition = EXTRACT(YEAR FROM _Date);
        WHEN 'month' THEN SET _ScaleCondition = CONCAT(EXTRACT(YEAR FROM _Date), ' M', EXTRACT(MONTH FROM _Date));
        WHEN 'week' THEN SET _ScaleCondition = CONCAT(EXTRACT(YEAR FROM _Date), ' W', EXTRACT(WEEK FROM _Date));
    END CASE;

    RETURN _ScaleCondition;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_PublicationsByDate`(IN _DateS DATE, IN _DateF DATE)
BEGIN

    SELECT 
        prepare_ScaleCondition(filed_date) AS date, 
        count(prepare_ScaleCondition(filed_date)) AS count
    FROM 
        applications
    WHERE
        filed_date between _DateS and _DateF
    GROUP BY
        prepare_ScaleCondition(filed_date)
    ORDER BY
        prepare_ScaleCondition(filed_date);

END