x
1
root:BEGIN
2
3
DECLARE _level INT;
4
DECLARE _last_row_count INT;
5
6
CREATE TEMPORARY TABLE IF NOT EXISTS _hierarchy_return_set (
7
_id VARCHAR(100)
8
, _parent_id VARCHAR(100)
9
, _level INT
10
, INDEX(_id, _level)
11
, INDEX(_parent_id, _level)
12
);
13
14
CREATE TEMPORARY TABLE IF NOT EXISTS _hierarchy_current_set (
15
_id VARCHAR(100)
16
, _parent_id VARCHAR(100)
17
, _level INT
18
);
19
20
SET _level := 1;
21
TRUNCATE TABLE _hierarchy_return_set;
22
TRUNCATE TABLE _hierarchy_current_set;
23
24
-- cleanup WHERE clause
25
IF LENGTH(TRIM(p_where_clause)) = 0 THEN
26
SET p_where_clause := NULL;
27
END IF;
28
IF p_where_clause IS NOT NULL THEN
29
SET p_where_clause := LTRIM(p_where_clause);
30
IF UPPER(SUBSTR(p_where_clause, 1, 5)) = 'WHERE' THEN -- remove WHERE
31
SET p_where_clause := LTRIM(SUBSTR(p_where_clause, 6));
32
END IF;
33
IF UPPER(SUBSTR(p_where_clause, 1, 4)) <> 'AND ' THEN -- Add AND
34
SET p_where_clause := CONCAT('AND ', p_where_clause);
35
END IF;
36
END IF;
37
38
-- Get StartWith records
39
SET @_sql = CONCAT( 'INSERT INTO _hierarchy_current_set( _id, _parent_id, _level ) '
40
,' SELECT ', p_key_column, ', ', p_parent_key_column, ', ', _level
41
,' FROM ', p_tablename
42
,' WHERE ', p_startWith, ' '
43
, IFNULL( p_where_clause, '' )
44
);
45
PREPARE stmt FROM @_sql;
46
EXECUTE stmt;
47
SET _last_row_count = ROW_COUNT();
48
49
50
-- Create the statement to get the next set of data
51
IF p_mode = 'D' THEN -- Down the tree
52
53
SET @_sql = CONCAT( 'INSERT INTO _hierarchy_current_set'
54
,' ( _id, _parent_id, _level )'
55
,' SELECT ', p_key_column, ', ', p_parent_key_column, ', ', ' @_curr_level'
56
,' FROM ', p_tableName, ' t, _hierarchy_return_set hrs '
57
,' WHERE t.', p_parent_key_column, ' = hrs._id ' -- The Parent - Child equijoin
58
,' AND hrs._level = @_last_level '
59
, IFNULL( p_where_clause, '' )
60
,';'
61
);
62
-- SELECT 'Down Tree Insert: ', @_sql;
63
64
ELSEIF p_mode = 'U' THEN
65
SET @_sql = CONCAT( 'INSERT INTO _hierarchy_current_set'
66
,' ( _id, _parent_id, _level )'
67
,' SELECT ', p_key_column, ', ', p_parent_key_column, ', ', ' @_curr_level'
68
,' FROM ', p_tableName, ' t, _hierarchy_return_set hrs '
69
,' WHERE t.', p_key_column, ' = hrs._parent_id ' -- The Parent - Child equijoin
70
,' AND hrs._level = @_last_level '
71
, IFNULL( p_where_clause, '' )
72
,';'
73
);
74
75
-- SELECT 'Up Tree Insert: ', @_sql;
76
77
ELSE -- Unknown mode, abort
78
LEAVE root;
79
END IF;
80
81
PREPARE next_recs_stmt FROM @_sql;
82
83
-- loop recursively finding parents/children
84
WHILE ( _last_row_count > 0)
85
DO
86
SET _level = _level+1;
87
88
INSERT INTO _hierarchy_return_set
89
SELECT *
90
FROM _hierarchy_current_set;
91
92
TRUNCATE TABLE _hierarchy_current_set;
93
94
SET @_last_level := _level-1;
95
SET @_curr_level := _level;
96
97
EXECUTE next_recs_stmt;
98
SET _last_row_count := ROW_COUNT();
99
100
END WHILE;
101
102
INSERT INTO _hierarchy_return_set
103
SELECT *
104
FROM _hierarchy_current_set;
105
106
END