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
| #主存储过程
CREATE PROCEDURE `pro_find_tree_value`(IN `schema_name` VARCHAR(50), IN `table_name` VARCHAR(50), IN `type` INT, IN `self_value` VARCHAR(50), IN `id_name` VARCHAR(50), IN `self_name` VARCHAR(50), IN `parent_name` VARCHAR(50), IN `recure_num` INT, INOUT `rt_value` MEDIUMTEXT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
pro_begin:BEGIN
DECLARE _value varchar(100);
DECLARE rtValue text default '';
DECLARE _schema varchar(100);
DECLARE cou int;
DECLARE tree_sql1 VARCHAR(500);
DECLARE id_value varchar(100);
if length(schema_name)=0
then select database() into _schema;
else set _schema=schema_name ;
end if;
select count(1) into cou
from information_schema.columns a where a.table_schema = _schema and a.table_name= table_name
and a.column_name=self_name;
if cou=0
then leave pro_begin;
end if;
select count(1) into cou
from information_schema.columns a where a.table_schema = _schema and a.table_name= table_name
and a.column_name=parent_name;
if cou=0
then leave pro_begin;
end if;
set tree_sql1= concat('select count(1) into @l_value from ',table_name,' t where t.',self_name,' = ',self_value);
select tree_sql1;
set @tree_sql1=tree_sql1; ##注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
prepare stmt from @tree_sql1; ##预处理需要执行的动态SQL，其中stmt是一个变量
EXECUTE stmt; ##执行SQL语句
deallocate prepare stmt; ##释放掉预处理段
set cou=@l_value;
select cou;
if cou=0
then leave pro_begin;
end if;
#type =1
#table_name的self_name值为self_value，返回parent_name的上级的上级的上级....（用分号隔开）
if type =1 then
ontTypeLoop:loop
set tree_sql1= concat('select ',parent_name,' into @l_value ',/*,id_name,' into @l_id','*/'from ',table_name,' t where t.',self_name,' = ',self_value);
#select tree_sql1;
set @tree_sql1=tree_sql1; ##注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
prepare stmt from @tree_sql1; ##预处理需要执行的动态SQL，其中stmt是一个变量
EXECUTE stmt; ##执行SQL语句
deallocate prepare stmt; ##释放掉预处理段
if length(rt_value)=0 or rt_value is null
then
set rt_value=concat(@l_value);
else
set rt_value=concat(rt_value,';',@l_value);
end if;
set self_value=@l_value;
set tree_sql1= concat('select count(1) into @l_value from ',table_name,' t where t.',self_name,' = ',self_value);
set @tree_sql1=tree_sql1;
prepare stmt from @tree_sql1;
EXECUTE stmt;
deallocate prepare stmt;
set cou=@l_value;
if cou=0
then leave ontTypeLoop;
end if;
end loop;
end if;
#type =2
#树的子节点查询：根节点值：self_value，上级节点名：parent_name，节点id名：id_name
#利用递归返回table_name的parent_name值为self_value的下级的下级的下级....
if type=2 then
select 1;
call pro_find_tree_recursion(table_name,parent_name,id_name,self_value,rt_value);
end if;
leave pro_begin;
END
|
