Spaces:
Running on Zero
Running on Zero
File size: 4,548 Bytes
19dfd32 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 | use employee;
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_department //
--
-- 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
department
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
employee
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_employee
AS
SELECT
emp_no,
first_name , last_name ,
birth_date , gender,
hire_date,
emp_dept_name(emp_no) as department
from
employee;
--
-- selects the department list with manager names
--
CREATE OR REPLACE VIEW v_full_department
AS
SELECT
dept_no, dept_name, current_manager(dept_no) as manager
FROM
department;
delimiter //
--
-- shows the department with the number of employee
-- per department
--
create procedure show_department()
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_department
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 employee_usage //
drop procedure if exists employee_help //
CREATE FUNCTION employee_usage ()
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN
'
== USAGE ==
====================
PROCEDURE show_department()
shows the department with the manager and
number of employee 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 employee_help()
deterministic
begin
select employee_usage() as info;
end//
delimiter ;
|