创建MySQL函数时出错

Question

创建一个函数，但它给出的错误数据库表如下所示

create function getAvg(id1 in number ,id2 in number) returns number
as
sal1 number ;
sal2 number ;
BEGIN
select esalary into sal1 from employee where eid=id1;
select esalary into sal2 from employee where eid=id2;
return (sal1 + sal2) / 2;
END;

Answer 1

我看到了创建函数时应该避免的基本语法错误。我尝试了你的数据和以下作品；

DELIMITER $$

CREATE FUNCTION getAvg(id1 INTEGER ,id2 INTEGER) RETURNS NUMERIC(10,5) DETERMINISTIC

BEGIN
DECLARE sal1 NUMERIC(10,5) ;
DECLARE sal2 NUMERIC(10,5);

SELECT esalary  FROM employee WHERE eid=id1 INTO sal1 ;
SELECT esalary FROM employee WHERE eid=id2 INTO sal2 ;
RETURN (sal1 + sal2) / 2;
END$$
DELIMITER ;
;

要检查它是否有效，请发出以下查询：

SELECT `getAvg`(1,4);