本文主要是介绍MySQL-函数,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
![image](/upload/202205/27/202205270221455460.png)
![image](/upload/202205/27/202205270221457184.png)
![image](/upload/202205/27/202205270221458136.png)
案例展示
1.无参有返回
返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义变量
SELECT COUNT(*) INTO c #赋值
FROM `employees`;
RETURN c;
END $
SELECT myf1()$
![image](/upload/202205/27/202205270221460187.png)
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('kochhar')$
![image](/upload/202205/27/202205270221462389.png)
根据部门名,返回该部门的平均工资
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.`department_id`=d.`department_id`
WHERE d.`department_name`=deptName;
RETURN sal;
END $
SELECT myf3('IT')$
![image](/upload/202205/27/202205270221464909.png)
查看函数
SELECT CREATE FUNCTION myf3;
删除函数
DROP FUNCTION myf3;
案例:创建函数,实现传入两个float,返回二者之和
CREATE FUNCTION myf4(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0; #定义局部变量
SET SUM=num1+num2;
RETURN SUM;
END $
DROP FUNCTION myf4(1,2);
![image](/upload/202205/27/202205270221467433.png)
这篇关于MySQL-函数的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!