存储函数
sh
# 准备工作:
# 1.导入atguigudb数据库
# 2.建立dbtest08数据库
CREATE DATABASE dbtest08 CHARACTER SET utf8;
# 3.切换当前数据库为dbtest08
USE dbtest08;
# 4.建立表
CREATE TABLE emps
AS
SELECT * FROM atguigudb.employees;
CREATE TABLE depts
AS
SELECT * FROM atguigudb.departments;
SELECT * FROM emps;
SELECT * FROM depts;
#-----------------------进行以下操作---------------
# 1、创建存储函数,名称为email_by_name(),参数定义为空,该函数查询Abel的email,并返回,数据类型为
字符串型。
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
BEGIN
DECLARE emp_email VARCHAR(25);
SELECT email INTO emp_email
FROM emps
WHERE last_name = 'Abel';
RETURN emp_email;
END //
DELIMITER ;
SELECT email_by_name();
# 2、创建存储函数,名称为email_by_id(),参数传入emp_id,该函数查询emp_id的email,并返回,数据类型为
字符串型。
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
DECLARE emp_email VARCHAR(25);
SELECT email INTO emp_email
FROM emps
WHERE employee_id = emp_id;
RETURN emp_email;
END //
DELIMITER ;
SELECT email_by_id(102);
# 3、创建存储函数count_by_id(),参数传入dept_id,该函数查询dept_id部门的员工人数,并返回,数据类型
为整型。
DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
BEGIN
DECLARE cnt INT;
SELECT COUNT(*) INTO cnt
FROM emps
WHERE department_id = dept_id;
RETURN cnt;
END //
DELIMITER ;
SELECT count_by_id(50);
# 4、创建存储函数diff_by_deptid(),参数传入dept_id,该函数查询dept_id部门的员工最高工资与最低工资之差,并返回,数据类型为双精度。
DELIMITER //
CREATE FUNCTION diff_by_deptid(dept_id INT)
RETURNS DOUBLE
BEGIN
DECLARE max_sal, min_sal, diff_sal DOUBLE;
SELECT MAX(salary), MIN(salary) INTO max_sal, min_sal
FROM emps
WHERE department_id = dept_id;
SET diff_sal = max_sal - min_sal;
RETURN diff_sal;
END //
DELIMITER ;
DROP FUNCTION diff_by_deptid;
SELECT diff_by_deptid(50);
# 5、创建存储函数diff_by_id(),参数传入emp_id,该函数查询emp_id员工的工资与其领导工资之差,并返回,数据类型为双精度。
DELIMITER //
CREATE FUNCTION diff_by_id(emp_id INT)
RETURNS DOUBLE
BEGIN
DECLARE emp_sal, mgr_sal, diff_sal DOUBLE;
DECLARE mgr_id INT;
#查询员工自己的工资以及领导的员工号
SELECT salary, manager_id INTO emp_sal, mgr_id
FROM emps
WHERE employee_id = emp_id;
#查询领导的工资
SELECT salary INTO mgr_sal
FROM emps
WHERE employee_id = mgr_id;
#计算员工工资与领导工资之差
SET diff_sal = emp_sal - mgr_sal;
RETURN diff_sal;
END //
DELIMITER ;
SELECT diff_by_id(102);
# 6、创建存储函数get_work_year_by_id(),参数传入emp_id,该函数查询emp_id员工的工作年限,并返回,数据类型为整型。
SELECT FLOOR(DATEDIFF(CURDATE(), '2000-01-01') / 365);
SELECT TRUNCATE(DATEDIFF(CURDATE(), '2000-01-01') / 365, 0);
DELIMITER //
CREATE FUNCTION get_work_year_by_id(emp_id INT)
RETURNS INT
BEGIN
DECLARE years INT;
SELECT FLOOR(DATEDIFF(CURDATE(), hire_date) / 365) INTO years
FROM emps
WHERE employee_id = emp_id;
RETURN years;
END //
DELIMITER ;
DROP FUNCTION get_work_year_by_id;
SELECT get_work_year_by_id(113);
# 7、显示当前数据库中所有存储函数。
SHOW FUNCTION STATUS;
# 8、显示存储函数get_work_year_by_id的创建语句。
SHOW CREATE FUNCTION get_work_year_by_id;
# 9、删除存储函数存储函数get_work_year_by_id。
DROP FUNCTION get_work_year_by_id;