一、创建
1、参数列表包含两部分类容:参数名、参数类型;
2、函数体:
1、无参有返回。犯规公司的员工个数
CREATE FUNCTION muf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c
FROM `employees`;
RETURN c
END
SELECT myf1();
2、有参有返。根据员工名,返回他的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0;
SELECT salary INTO @sal
FROM employees WHERE last_name=empName;
RETURN @sal;
END
SELECT myf2('k_ing');
#根据部门名,返回该部门的平均工资
create function myf3(deptName varchar(20)) returns double;
begin
declare sal double;
select avg(salary) into sal
from `employees` e
join `departments` d
on e.`employee_id`=d.`department_id`
where d.`department_name`=deptName;
return sal;
end
select myf3('IT');
三、查看函数
SHOW CREATE FUNCTION myf3;
四、删除函数
DROP FUNCTION myf3;
五、函数的应用
#输入两个float,返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT ,num2 FLOAT) RETURN FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END
SELECT test_fun1(1,2);