1
drop
table
if
exists
employee;
2
3
create
table
employee
4
(
5
emp_id
smallint
unsigned
not
null
auto_increment
primary
key
,
6
name
varchar
(
32
)
not
null
,
7
boss_id
smallint
unsigned
null
,
8
key
boss_id_idx(boss_id)
9
)engine
=
innodb;
10
11
12
insert
into
employee (name, boss_id)
values
13
(
'
foo
'
,
null
),
14
(
'
ali later
'
,
1
), (
'
megan fox
'
,
1
),
15
(
'
jessica alba
'
,
2
), (
'
eva longoria
'
,
2
),
16
(
'
keira knightley
'
,
3
), (
'
liv tyler
'
,
3
),
17
(
'
sophie marceau
'
,
5
);
18
19
20
delimiter ;
21
22
drop
procedure
if
exists
employee_hier;
23
24
delimiter #
25
26
create
procedure
employee_hier
27
(
28
in
p_emp_id
smallint
unsigned
29
)
30
begin
31
32
declare
p_done
tinyint
unsigned
default
(
0
);
33
declare
p_depth
smallint
unsigned
default
(
0
);
34
35
create
temporary
table
hier(
36
boss_id
smallint
unsigned,
37
emp_id
smallint
unsigned,
38
depth
smallint
unsigned
39
)engine
=
memory;
40
41
insert
into
hier
values
(
null
, p_emp_id, p_depth);
42
43
/*
http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html
*/
44
45
create
temporary
table
emps engine
=
memory
select
*
from
hier;
46
47
while
p_done
<>
1
do
48
49
if
exists
(
select
1
from
employee e
inner
join
hier
on
e.boss_id
=
hier.emp_id
and
hier.depth
=
p_depth)
then
50
51
insert
into
hier
select
e.boss_id, e.emp_id, p_depth
+
1
52
from
employee e
inner
join
emps
on
e.boss_id
=
emps.emp_id
and
emps.depth
=
p_depth;
53
54
set
p_depth
=
p_depth
+
1
;
55
56
truncate
table
emps;
57
insert
into
emps
select
*
from
hier
where
depth
=
p_depth;
58
59
else
60
set
p_done
=
1
;
61
end
if
;
62
63
end
while
;
64
65
select
66
e.emp_id,
67
e.name
as
emp_name,
68
b.emp_id
as
boss_emp_id,
69
b.name
as
boss_name,
70
hier.depth
71
from
72
hier
73
inner
join
employee e
on
hier.emp_id
=
e.emp_id
74
inner
join
employee b
on
hier.boss_id
=
b.emp_id;
75
76
drop
temporary
table
if
exists
hier;
77
drop
temporary
table
if
exists
emps;
78
79
end
#
80
81
delimiter ;
82
83
/*
84
85
select * from employee;
86
87
emp_id name boss_id
88
====== ==== =======
89
1 foo null
90
2 ali later 1
91
3 megan fox 1
92
4 jessica alba 2
93
5 eva longoria 2
94
6 keira knightley 3
95
7 liv tyler 3
96
8 sophie marceau 5
97
98
call employee_hier(1);
99
100
emp_id emp_name boss_emp_id boss_name depth
101
====== ======== =========== ========= =====
102
2 ali later 1 foo 1
103
3 megan fox 1 foo 1
104
4 jessica alba 2 ali later 2
105
5 eva longoria 2 ali later 2
106
6 keira knightley 3 megan fox 2
107
7 liv tyler 3 megan fox 2
108
8 sophie marceau 5 eva longoria 3
109
110
call employee_hier(3);
111
112
emp_id emp_name boss_emp_id boss_name depth
113
====== ======== =========== ========= =====
114
6 keira knightley 3 megan fox 1
115
7 liv tyler 3 megan fox 1
116
*/
