MariaDB在MySQL上运行时创建函数时出错 [英] MariaDB Error creating Function when it worked on MySQL
问题描述
请考虑以下功能定义.我在MySQL 5.1上创建并设置了它,但是在MariaDB 5.5中失败了
Please consider the following function defination. I created and set it up on MySQL 5.1 but it's failing in MariaDB 5.5
CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `weighted_mean_by_kpi`(`KPIID` INT, `employee_id` INT, `date` DATE)
RETURNS decimal(6,3)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE rating_number INT DEFAULT 0;
DECLARE rating_count INT DEFAULT 0;
DECLARE rating_total INT DEFAULT 0;
DECLARE weighted_total DOUBLE DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT COUNT(rating), rating FROM employees_KPIs WHERE kpi_id = KPIID AND employee_id = employee_id AND employees_KPIs.created_at LIKE CONCAT("%",DATE_FORMAT(date,'%Y-%m'),"%") GROUP BY rating;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
RATING: LOOP
FETCH cur INTO rating_count, rating_number;
IF done = 1 THEN
LEAVE RATING;
END IF;
SET weighted_total = weighted_total + (rating_number * rating_count);
SET rating_total = rating_total + rating_count;
END LOOP RATING;
return (weighted_total/rating_total);
#return (weighted_total);
CLOSE cur;
END
我收到以下错误:
第1行的错误1064(42000):您的SQL语法有错误;检查与您的MariaDB服务器版本相对应的手册以获取正确的语法,以在第8行的''附近使用
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 8
谢谢
推荐答案
Mysql看到';'函数中的定界符并破坏您的CREATE FUNCTION
语句.
Mysql sees the ';' delimiters in the function and breaks your CREATE FUNCTION
statement.
为避免这种情况,请在定义函数之前更改定界符,然后再将其改回:
To avoid this, change the delimiter before you define the function, and then change it back afterward:
赞:
DELIMITER //
-- your create function definition statement here
//
DELIMITER ;
因为在您的代码中在line 8
处找到了第一个;
分号,所以它尝试执行直到';'为止的代码,并且语法无效,因为它不完整(BEGIN
没有
As in your code the first ;
semicolon was found at line 8
, it tried to execute it the code up to the ';', and the syntax was invalid because it was incomplete (BEGIN
without END
).
这篇关于MariaDB在MySQL上运行时创建函数时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!