· 8 years ago · Dec 14, 2017, 01:50 PM
1DELIMITER $$
2CREATE DEFINER=`sys`@`%` PROCEDURE `Variable_getUsageList`(
3 _Name varchar(100)
4, _Type varchar(126)
5, _UserType varchar(126)
6, _TreeVersionId bigint
7, _StartNodeId bigint
8, _EndNodeId bigint)
9BEGIN
10
11 declare _num bigint;
12 declare _curNodeId bigint;
13 declare _NextNodeId bigint;
14 declare _NodeTypeId bigint;
15 declare _TrueNodeId bigint;
16 declare _FalseNodeId bigint;
17
18 declare _Parameters TEXT CHARACTER SET utf8;
19 declare _Parameters_usertype TEXT CHARACTER SET utf8;
20 declare _Parameters_usertype_consist TEXT CHARACTER SET utf8;
21
22 declare _VariableOperand varchar(4000);
23 declare _VariableOperandUserType varchar(4000);
24 declare _VariableOperandUserTypeConsist varchar(4000);
25
26 DECLARE done INT DEFAULT FALSE;
27
28 DECLARE curTMP CURSOR FOR
29 select NodeId, num from tresult;
30
31 DECLARE curCase CURSOR FOR
32 SELECT NextNodeId FROM SwitchCase
33 where NodeId=_curNodeId and _TreeVersionId>=TreeVersionIdFrom and _TreeVersionId<=TreeVersionIdTo and NextNodeId is not null;
34 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
35
36 drop table if exists _tstack;
37 drop table if exists nodeList;
38 -- drop table if exists tresult;
39
40 create TEMPORARY table tresult(num bigint AUTO_INCREMENT, NodeId bigint, primary key(num));
41 create TEMPORARY table _tstack(num bigint AUTO_INCREMENT, NodeId bigint, primary key(num));
42
43 set _VariableOperand=text_operand_variable(_name,_type);
44 set _VariableOperandUserType=text_operand_variable_usertype(_name,_type,_UserType);
45 set _VariableOperandUserTypeConsist=text_operand_variable_usertype_consist(_name,_type,_UserType);
46
47 insert into _tstack(NodeId) values(_StartNodeId);
48
49 while exists(select 1 from _tstack) do
50
51
52 set _NodeTypeId=null;
53 set _NextNodeId=null;
54
55
56 select num, NodeId into _num, _curNodeId
57 from _tstack order by num desc limit 1;
58
59 delete from _tstack where num=_num; -- !!!!!!!!!!!!!
60
61 if not exists(select 1 from tresult where NodeId=_curNodeId) then
62
63 select NodeTypeId, NextNodeId into _NodeTypeId, _NextNodeId
64 from Node where NodeId=_curNodeId and _TreeVersionId>=TreeVersionIdFrom and _TreeVersionId<=TreeVersionIdTo;
65
66 set _Parameters=null;
67 set _Parameters_usertype=null;
68 if _NodeTypeId=1 then
69 select Parameters into _Parameters
70 from SwitchCondition where NodeId=_curNodeId and _TreeVersionId>=TreeVersionIdFrom and _TreeVersionId<=TreeVersionIdTo;
71 else
72 if _NodeTypeId=2 then
73 select Parameters into _Parameters
74 from IfCondition where NodeId=_curNodeId and _TreeVersionId>=TreeVersionIdFrom and _TreeVersionId<=TreeVersionIdTo;
75 else
76 select Parameters into _Parameters
77 from TariffComponent where NodeId=_curNodeId and _TreeVersionId>=TreeVersionIdFrom and _TreeVersionId<=TreeVersionIdTo; -- !!!
78 end if;
79 end if;
80
81 set _Parameters_usertype=_Parameters;
82 set _Parameters_usertype_consist=_Parameters;
83
84 if _Parameters is not null and XmlContains(_Parameters, _VariableOperand)=0 then
85 insert into tresult(NodeId) values(_curNodeId);
86 end if;
87
88 if _Parameters_usertype is not null and XmlContains(_Parameters_usertype, _VariableOperandUserType)=0 then
89 insert into tresult(NodeId) values(_curNodeId);
90 end if;
91
92 if _Parameters_usertype_consist is not null and XmlContains(_Parameters_usertype_consist, _VariableOperandUserTypeConsist)=0 then
93 insert into tresult(NodeId) values(_curNodeId);
94 end if;
95
96 if (_EndNodeId is not null and _EndNodeId=_curNodeId) then
97 set _NextNodeId = null;
98 set _curNodeId = null;
99 end if;
100
101 if _NextNodeId is not null
102 and not exists(select 1 from tresult where NodeId=_NextNodeId)
103 and not exists(select 1 from _tstack where NodeId=_NextNodeId) then
104 insert into _tstack(NodeId) values(_NextNodeId); -- !!!!!!!
105 end if;
106
107 if _curNodeId is not null and _NodeTypeId in (1,2) then
108
109 if _NodeTypeId=1 then
110 set done=FALSE;
111 open curCase;
112 read_loop: LOOP
113 fetch curCase into _NextNodeId;
114 if done then
115 leave read_loop;
116 end if;
117
118 if not exists(select 1 from tresult where NodeId=_NextNodeId)
119 and not exists(select 1 from _tstack where NodeId=_NextNodeId) then
120 insert into _tstack(NodeId) values(_NextNodeId); -- !!!
121 end if;
122 end LOOP;
123 close curCase;
124 end if;
125
126 if _NodeTypeId=2 then
127 select TrueNodeId, FalseNodeId into _TrueNodeId,_FalseNodeId
128 from IfCondition FORCE INDEX(PRIMARY)
129 where NodeId=_curNodeId and _TreeVersionId>=TreeVersionIdFrom and _TreeVersionId<=TreeVersionIdTo;
130
131 if _TrueNodeId is not null
132 and not exists(select 1 from tresult where NodeId=_TrueNodeId)
133 and not exists(select 1 from _tstack where NodeId=_TrueNodeId) then
134 insert into _tstack(NodeId) values(_TrueNodeId);
135 end if;
136
137 if _FalseNodeId is not null
138 and not exists(select 1 from tresult where NodeId=_FalseNodeId)
139 and not exists(select 1 from _tstack where NodeId=_FalseNodeId) then
140 insert into _tstack(NodeId) values(_FalseNodeId);
141 end if;
142 end if;
143
144 end if;
145 end if;
146 end while;
147
148 select * from tresult order by num asc;
149
150 drop table _tstack;
151 drop table tresult;
152
153END$$
154DELIMITER ;