mysql - 在mysql上创建函数

Question

我在mysql上有以下内容：

DELIMITER //
CREATE FUNCTION dateDiff1(contract_id INT, cust_id INT)
RETURNS INT
BEGIN
DECLARE startDate, endDate DATETIME;
DECLARE result int;
SET startDate = (SELECT startDate FROM contract WHERE insurance_cover_id = contract_id AND customer_id = cust_id);
SET endDate = (SELECT endDate FROM contract WHERE insurance_cover_id = contract_id AND customer_id = cust_id);
SET result = (SELECT TIMESTAMPDIFF(MONTH, endDate, startDate));
RETURN result;
END;
//
DELIMITER ;

SELECT dateDiff1(1,1);

它返回NULL，有什么建议吗？

标签： mysqlcreate-function

Answer 1

也许，只要您在数据库中获得了一些数据，您就可以按如下方式解决您的问题：

CREATE FUNCTION datediff1(_contract_id INT [, _cust_id INT])
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE res INT;
    SELECT TIMESTAMPDIFF(MONTH, endDate, startDate) FROM contract WHERE contract_id = 
    _contract_id [AND cust_id = _cust_id] INTO res;
RETURN res;
END

我将 cust_id 相关部分标记为可选，因为您的 contract_id 很可能已经被引用到特定客户。我希望它会有所帮助。祝你好运。