1 Star 0 Fork 0

iothers / test_db

加入 Gitee
与超过 600 万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
该仓库未指定开源许可证,未经作者的许可,此代码仅用于学习,不能用于其他用途。
克隆/下载
objects.sql 4.46 KB
一键复制 编辑 Web IDE 原始数据 按行查看 历史
Giuseppe Maxia 提交于 2015-09-19 11:18 . Added help function to objects.sql
use employees;
delimiter //
drop function if exists emp_dept_id //
drop function if exists emp_dept_name //
drop function if exists emp_name //
drop function if exists current_manager //
drop procedure if exists show_departments //
--
-- returns the department id of a given employee
--
create function emp_dept_id( employee_id int )
returns char(4)
reads sql data
begin
declare max_date date;
set max_date = (
select
max(from_date)
from
dept_emp
where
emp_no = employee_id
);
set @max_date=max_date;
return (
select
dept_no
from
dept_emp
where
emp_no = employee_id
and
from_date = max_date
limit 1
);
end //
--
-- returns the department name of a given employee
--
create function emp_dept_name( employee_id int )
returns varchar(40)
reads sql data
begin
return (
select
dept_name
from
departments
where
dept_no = emp_dept_id(employee_id)
);
end//
--
-- returns the employee name of a given employee id
--
create function emp_name (employee_id int)
returns varchar(32)
reads SQL data
begin
return (
select
concat(first_name, ' ', last_name) as name
from
employees
where
emp_no = employee_id
);
end//
--
-- returns the manager of a department
-- choosing the most recent one
-- from the manager list
--
create function current_manager( dept_id char(4) )
returns varchar(32)
reads sql data
begin
declare max_date date;
set max_date = (
select
max(from_date)
from
dept_manager
where
dept_no = dept_id
);
set @max_date=max_date;
return (
select
emp_name(emp_no)
from
dept_manager
where
dept_no = dept_id
and
from_date = max_date
limit 1
);
end //
delimiter ;
--
-- selects the employee records with the
-- latest department
--
CREATE OR REPLACE VIEW v_full_employees
AS
SELECT
emp_no,
first_name , last_name ,
birth_date , gender,
hire_date,
emp_dept_name(emp_no) as department
from
employees;
--
-- selects the department list with manager names
--
CREATE OR REPLACE VIEW v_full_departments
AS
SELECT
dept_no, dept_name, current_manager(dept_no) as manager
FROM
departments;
delimiter //
--
-- shows the departments with the number of employees
-- per department
--
create procedure show_departments()
modifies sql data
begin
DROP TABLE IF EXISTS department_max_date;
DROP TABLE IF EXISTS department_people;
CREATE TEMPORARY TABLE department_max_date
(
emp_no int not null primary key,
dept_from_date date not null,
dept_to_date date not null, # bug#320513
KEY (dept_from_date, dept_to_date)
);
INSERT INTO department_max_date
SELECT
emp_no, max(from_date), max(to_date)
FROM
dept_emp
GROUP BY
emp_no;
CREATE TEMPORARY TABLE department_people
(
emp_no int not null,
dept_no char(4) not null,
primary key (emp_no, dept_no)
);
insert into department_people
select dmd.emp_no, dept_no
from
department_max_date dmd
inner join dept_emp de
on dmd.dept_from_date=de.from_date
and dmd.dept_to_date=de.to_date
and dmd.emp_no=de.emp_no;
SELECT
dept_no,dept_name,manager, count(*)
from v_full_departments
inner join department_people using (dept_no)
group by dept_no;
# with rollup;
DROP TABLE department_max_date;
DROP TABLE department_people;
end //
drop function if exists employees_usage //
drop procedure if exists employees_help //
CREATE FUNCTION employees_usage ()
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN
'
== USAGE ==
====================
PROCEDURE show_departments()
shows the departments with the manager and
number of employees per department
FUNCTION current_manager (dept_id)
Shows who is the manager of a given departmennt
FUNCTION emp_name (emp_id)
Shows name and surname of a given employee
FUNCTION emp_dept_id (emp_id)
Shows the current department of given employee
';
END //
create procedure employees_help()
deterministic
begin
select employees_usage() as info;
end//
delimiter ;

评论 ( 0 )

你可以在登录后,发表评论

1
https://gitee.com/4ningjian/test_db.git
git@gitee.com:4ningjian/test_db.git
4ningjian
test_db
test_db
master

搜索帮助