gpt4 book ai didi

mysql - CREATE FUNCTION 在本地工作,在服务器上失败 - 相同版本的 MySQL

转载 作者:行者123 更新时间:2023-11-29 07:25:46 24 4
gpt4 key购买 nike

我有一个奇怪的问题:我的本地机器和服务器上有 MySQL v5.7.24。以下代码在本地执行良好:

DROP FUNCTION IF EXISTS SharingRatio;

CREATE FUNCTION SharingRatio(users int, sharer int, cabins int, outdoors int) RETURNS DECIMAL(6,2)
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE ratio DECIMAL(6,2);

IF cabins IS NULL THEN
SET cabins = 0;
END IF;

IF sharer IS NULL THEN
SET sharer = 1;
END IF;

IF users IS NULL THEN
SET users = 1;
END IF;

SELECT IF(sharer = 1, telco1, IF(sharer = 2, telco2, IF(sharer = 3, telco3, IF(sharer =4, telco4, IF(sharer = 5, telco5, null)))))
FROM rates r WHERE (r.type='Utility' and r.cabin = cabins and if(outdoors IS NULL, true, r.outdoor = outdoors) and r.users = users) LIMIT 1 INTO ratio;

IF ratio IS NULL THEN
SET ratio = ROUND(100 / users, 2) ;
END IF;

RETURN (ratio);
END;


DROP FUNCTION IF EXISTS SharingValue;

CREATE FUNCTION SharingValue(value decimal(10,2), users int, sharer int, cabins int, outdoors int) RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE ratio DECIMAL(6,2);

IF value IS NULL THEN
RETURN NULL;
END IF;

IF cabins IS NULL THEN
SET cabins = 0;
END IF;

IF sharer IS NULL THEN
SET sharer = 1;
END IF;

IF users IS NULL THEN
SET users = 1;
END IF;

SET ratio = SharingRatio(users, sharer, cabins, outdoors);

RETURN CAST(value * (ratio / 100) AS DECIMAL(10,2));
END;

但是,当我通过 MySQLAdmin 以根访问权限进行部署时,我得到了臭名昭著的 #1064 error saying error in line 5 near ''.

我看过许多其他帖子,没有悬空逗号,也没有 ELSE IF 导致问题。坦率地说,我被卡住了!

最佳答案

解决了!

delimter 标签是问题所在。正确的代码是:

DROP FUNCTION IF EXISTS SharingRatio;
DELIMITER $$
CREATE FUNCTION SharingRatio(users int, sharer int, cabins int, outdoors int)

RETURNS DECIMAL(6,2)
DETERMINISTIC READS SQL DATA

BEGIN
DECLARE ratio DECIMAL(6,2);

IF cabins IS NULL THEN
SET cabins = 0;
END IF;

IF sharer IS NULL THEN
SET sharer = 1;
END IF;

IF users IS NULL THEN
SET users = 1;
END IF;

SELECT IF(sharer = 1, telco1, IF(sharer = 2, telco2, IF(sharer = 3, telco3, IF(sharer =4, telco4, IF(sharer = 5, telco5, null)))))
FROM rates r WHERE (r.type='Utility' and r.cabin = cabins and if(outdoors IS NULL, true, r.outdoor = outdoors) and r.users = users) LIMIT 1 INTO ratio;

IF ratio IS NULL THEN
SET ratio = ROUND(100 / users, 2);
END IF;

RETURN (ratio);
END$$

DELIMITER ;

DROP FUNCTION IF EXISTS SharingValue;

DELIMITER $$

CREATE FUNCTION SharingValue(value decimal(10,2), users int, sharer int, cabins int, outdoors int)

RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA

BEGIN
DECLARE ratio DECIMAL(6,2);

IF value IS NULL THEN
RETURN NULL;
END IF;

IF cabins IS NULL THEN
SET cabins = 0;
END IF;

IF sharer IS NULL THEN
SET sharer = 1;
END IF;

IF users IS NULL THEN
SET users = 1;
END IF;

SET ratio = SharingRatio(users, sharer, cabins, outdoors);

RETURN CAST(value * (ratio / 100) AS DECIMAL(10,2));
END$$
DELIMITER ;

关于mysql - CREATE FUNCTION 在本地工作,在服务器上失败 - 相同版本的 MySQL,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/53878759/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com