记录下工作中写的其中一块触发器和存储过程,以便于将来查询
触发器
create or replace trigger tr_tai_cell_jituan_i
after insert on tai_cell_jituan_relation
for each row
begin
insert into olapc.tai_cell_jituan_relation@olap (cell_id,jituan_id,cell_desc_jituan)
values (:new.cell_id,:new.jituan_id,:new.cell_desc_jituan);
end tr_tai_cell_jituan_i;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/****************************************/
create or replace trigger tr_tai_cell_jituan_d
after delete on tai_cell_jituan_relation
for each row
begin
delete from olapc.tai_cell_jituan_relation@olap where cell_id = :old.cell_id and jituan_id = :old.jituan_id;
end tr_tai_cell_jituan_d;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/****************************************/
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create or replace trigger tr_tai_cell_jituan_u
after update on tai_cell_jituan_relation
for each row
begin
update olapc.tai_cell_jituan_relation@olap set cell_id=:new.cell_id,jituan_id = :new.jituan_id,cell_desc_jituan=:new.cell_desc_jituan where cell_id = :old.cell_id and jituan_id = :old.jituan_id;
end tr_tai_cell_jituan_u;
触发器
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
存储过程
1
create or replace procedure sp_tpa_jituan_query(timeid number,regionname varchar2,sum_level number,jituanid number,issuccess out number) is
2
v_jituanid number;
3
v_jituanname varchar2(64);
4
v_region_name varchar2(64);
5
v_tch_traffic float;
6
v_time_desc varchar2(64);
7
v_hb_timeid number;
8
v_tb_timeid number;
9
v_hb_traffic float;
10
v_tb_traffic float;
11
v_is_have number;
12
v_hb_rate float;
13
v_tb_rate float;
14
v_count number;
15
cursor mycur is
16
select a.jituan_id,a.jituan_name,c.region_name
17
from tai_jituan a,tai_borough b,(select distinct region_id,region_name from region_city_local where region_name = regionname) c
18
where a.borough_id = b.borough_id and b.region_id = c.region_id;
19
begin
20
issuccess := -1;
21
v_count := -1;
22
v_jituanid := 0;
23
v_jituanname :='';
24
v_region_name :='';
25
v_tch_traffic :=0;
26
v_time_desc :='';
27
v_hb_timeid :=0;
28
v_tb_timeid :=0;
29
v_hb_traffic :=0;
30
v_tb_traffic :=0;
31
v_is_have :=0;
32
v_hb_rate:=0;
33
v_tb_rate :=0;
34
/*sum_level:0时;1天;2周;3月;4季;5年
35
查2007年04月02日某一时刻的(比如03时),
36
集团id=1的所属地区、集团名、话务量,前一天的这个时刻的话务量,环比增幅,
37
上个月同一日的话务量,同比增幅,及判断该集团是否是我得关注*/
38
if (sum_level = 0) then
39
--小时汇总查询
40
if (jituanid <= 0) then
41
if mycur%isopen = false then
42
open mycur;
43
end if;
44
fetch mycur into v_jituanid, v_jituanname,v_region_name;
45
while mycur%found
46
loop
47
select count(1) into v_count from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = timeid;
48
if(v_count>0)then
49
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = timeid;
50
select hour_desc,last_hour_id,yestoday_hour_id into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_hour where hour_id = timeid;
51
begin
52
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = v_hb_timeid;
53
exception
54
when others then
55
v_hb_traffic:=0;
56
end;
57
begin
58
select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = v_tb_timeid;
59
exception
60
when others then
61
v_tb_traffic:=0;
62
end;
63
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 0;
64
if (v_hb_traffic <> 0) then
65
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
66
else
67
v_hb_rate := -999999;
68
end if;
69
if (v_tb_traffic <> 0) then
70
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
71
else
72
v_tb_rate := -999999;
73
end if;
74
if (v_is_have = 0) then
75
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
76
values (timeid,v_jituanid,0,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
77
else
78
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
79
where time_id = timeid and jituan_id = v_jituanid and sum_level = 0;
80
end if;
81
commit;
82
end if;
83
fetch mycur into v_jituanid, v_jituanname,v_region_name;
84
end loop;
85
close mycur;
86
else
87
select a.jituan_id,a.jituan_name,c.region_name into v_jituanid,v_jituanname,v_region_name
88
from tai_jituan a,tai_borough b,(select distinct region_id,region_name from region_city_local where region_name = regionname) c
89
where a.borough_id = b.borough_id and b.region_id = c.region_id and a.jituan_id=jituanid;
90
select count(1) into v_count from tpa_jituan where jituan_id = jituanid and sum_level = 0 and time_id = timeid;
91
if(v_count>0)then
92
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = jituanid and sum_level = 0 and time_id = timeid;
93
select hour_desc,LAST_HOUR_ID,YESTODAY_HOUR_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_hour where hour_id = timeid;
94
begin
95
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = jituanid and sum_level = 0 and time_id = v_hb_timeid;
96
exception
97
when others then
98
v_hb_traffic :=0;
99
end;
100
begin
101
select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = jituanid and sum_level = 0 and time_id = v_tb_timeid;
102
exception
103
when others then
104
v_tb_traffic :=0;
105
end;
106
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = jituanid and sum_level = 0;
107
if (v_hb_traffic <> 0) then
108
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
109
else
110
v_hb_rate := -999999;
111
end if;
112
if (v_tb_traffic <> 0) then
113
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
114
else
115
v_tb_rate := -999999;
116
end if;
117
if (v_is_have = 0) then
118
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
119
values (timeid,jituanid,0,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
120
else
121
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
122
where time_id = timeid and jituan_id = v_jituanid and sum_level = 0;
123
end if;
124
commit;
125
end if;
126
end if;
127
elsif (sum_level = 1) then
128
--日汇总查询
129
if (jituanid <= 0) then
130
if mycur%isopen = false then
131
open mycur;
132
end if;
133
fetch mycur into v_jituanid, v_jituanname,v_region_name;
134
while mycur%found
135
loop
136
select count(1) into v_count from tpa_jituan where jituan_id = v_jituanid and sum_level = 1 and time_id = timeid;
137
if(v_count> 0)then
138
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 1 and time_id = timeid;
139
select day_desc,LAST_day_ID,lw_day_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_day where day_id = timeid;
140
begin
141
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 1 and time_id = v_hb_timeid;
142
exception
143
when others then
144
v_hb_traffic :=0;
145
end;
146
begin
147
select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 1 and time_id = v_tb_timeid;
148
exception
149
when others then
150
v_tb_traffic :=0;
151
end;
152
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 1;
153
if (v_hb_traffic <> 0) then
154
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
155
else
156
v_hb_rate := -999999;
157
end if;
158
if (v_tb_traffic <> 0) then
159
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
160
else
161
v_tb_rate := -999999;
162
end if;
163
if (v_is_have = 0) then
164
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
165
values (timeid,v_jituanid,1,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
166
else
167
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
168
where time_id = timeid and jituan_id = v_jituanid and sum_level = 1;
169
end if;
170
commit;
171
end if;
172
fetch mycur into v_jituanid, v_jituanname,v_region_name;
173
end loop;
174
close mycur;
175
else
176
select count(1) into v_count from tpa_jituan where jituan_id = jituanid and sum_level = 1 and time_id = timeid;
177
if(v_count>0)then
178
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = jituanid and sum_level = 1 and time_id = timeid;
179
select day_desc,LAST_day_ID,lw_day_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_day where day_id = timeid;
180
begin
181
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 1 and time_id = v_hb_timeid;
182
exception
183
when others then
184
v_hb_traffic :=0;
185
end;
186
begin
187
select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 1 and time_id = v_tb_timeid;
188
exception
189
when others then
190
v_tb_traffic :=0;
191
end;
192
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 1;
193
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
194
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
195
if (v_is_have = 0) then
196
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
197
values (timeid,v_jituanid,1,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
198
else
199
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
200
where time_id = timeid and jituan_id = v_jituanid and sum_level = 1;
201
end if;
202
commit;
203
end if;
204
end if;
205
elsif (sum_level = 2) then
206
--周汇总查询
207
if (jituanid <= 0) then
208
if mycur%isopen = false then
209
open mycur;
210
end if;
211
fetch mycur into v_jituanid, v_jituanname,v_region_name;
212
while mycur%found
213
loop
214
select count(1) into v_count from tpa_jituan where jituan_id = v_jituanid and sum_level = 2 and time_id = timeid;
215
if(v_count>0)then
216
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 2 and time_id = timeid;
217
select week_desc,LAST_week_ID,ly_week_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_week where week_id = timeid;
218
begin
219
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 2 and time_id = v_hb_timeid;
220
exception
221
when others then
222
v_hb_traffic :=0;
223
end;
224
begin
225
select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 2 and time_id = v_tb_timeid;
226
exception
227
when others then
228
v_tb_traffic :=0;
229
end;
230
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 2;
231
if (v_hb_traffic <> 0) then
232
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
233
else
234
v_hb_rate := -999999;
235
end if;
236
if (v_tb_traffic <> 0) then
237
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
238
else
239
v_tb_rate := -999999;
240
end if;
241
if (v_is_have = 0) then
242
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
243
values (timeid,v_jituanid,2,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
244
else
245
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
246
where time_id = timeid and jituan_id = v_jituanid and sum_level = 2;
247
end if;
248
commit;
249
end if;
250
fetch mycur into v_jituanid, v_jituanname,v_region_name;
251
end loop;
252
close mycur;
253
else
254
select count(1) into v_count from tpa_jituan where jituan_id = jituanid and sum_level = 2 and time_id = timeid;
255
if(v_count>0)then
256
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = jituanid and sum_level = 2 and time_id = timeid;
257
select week_desc,LAST_week_ID,ly_week_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_week where week_id = timeid;
258
begin
259
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 2 and time_id = v_hb_timeid;
260
exception
261
when others then
262
v_hb_traffic :=0;
263
end;
264
begin
265
select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 2 and time_id = v_tb_timeid;
266
exception
267
when others then
268
v_tb_traffic :=0;
269
end;
270
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 2;
271
if (v_hb_traffic <> 0) then
272
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
273
else
274
v_hb_rate := -999999;
275
end if;
276
if (v_tb_traffic <> 0) then
277
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
278
else
279
v_tb_rate := -999999;
280
end if;
281
if (v_is_have = 0) then
282
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
283
values (timeid,v_jituanid,2,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
284
else
285
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
286
where time_id = timeid and jituan_id = v_jituanid and sum_level = 2;
287
end if;
288
commit;
289
end if;
290
end if;
291
elsif (sum_level = 3) then
292
--月汇总查询
293
if (jituanid <= 0) then
294
if mycur%isopen = false then
295
open mycur;
296
end if;
297
fetch mycur into v_jituanid, v_jituanname,v_region_name;
298
while mycur%found
299
loop
300
select count(1) into v_count from tpa_jituan where jituan_id = v_jituanid and sum_level = 3 and time_id = timeid;
301
if(v_count>0)then
302
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 3 and time_id = timeid;
303
select month_desc,LAST_month_ID,ly_month_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_month where month_id = timeid;
304
begin
305
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 3 and time_id = v_hb_timeid;
306
exception
307
when others then
308
v_hb_traffic :=0;
309
end;
310
begin
311
select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 3 and time_id = v_tb_timeid;
312
exception
313
when others then
314
v_tb_traffic :=0;
315
end;
316
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 3;
317
if (v_hb_traffic <> 0) then
318
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
319
else
320
v_hb_rate := -999999;
321
end if;
322
if (v_tb_traffic <> 0) then
323
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
324
else
325
v_tb_rate := -999999;
326
end if;
327
if (v_is_have = 0) then
328
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
329
values (timeid,v_jituanid,3,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
330
else
331
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
332
where time_id = timeid and jituan_id = v_jituanid and sum_level = 3;
333
end if;
334
commit;
335
end if;
336
fetch mycur into v_jituanid, v_jituanname,v_region_name;
337
end loop;
338
close mycur;
339
else
340
select count(1) into v_count from tpa_jituan where jituan_id = jituanid and sum_level = 3 and time_id = timeid;
341
if(v_count>0) then
342
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = jituanid and sum_level = 3 and time_id = timeid;
343
select month_desc,LAST_month_ID,ly_month_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_month where month_id = timeid;
344
begin
345
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 3 and time_id = v_hb_timeid;
346
exception
347
when others then
348
v_hb_traffic :=0;
349
end;
350
begin
351
select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 3 and time_id = v_tb_timeid;
352
exception
353
when others then
354
v_tb_traffic :=0;
355
end;
356
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 3;
357
if (v_hb_traffic <> 0) then
358
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
359
else
360
v_hb_rate := -999999;
361
end if;
362
if (v_tb_traffic <> 0) then
363
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
364
else
365
v_tb_rate := -999999;
366
end if;
367
if (v_is_have = 0) then
368
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
369
values (timeid,v_jituanid,3,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
370
else
371
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
372
where time_id = timeid and jituan_id = v_jituanid and sum_level = 3;
373
end if;
374
commit;
375
end if;
376
end if;
377
elsif (sum_level = 5) then
378
--年度汇总查询timeid=2006
379
if (jituanid <= 0) then
380
if mycur%isopen = false then
381
open mycur;
382
end if;
383
fetch mycur into v_jituanid, v_jituanname,v_region_name;
384
while mycur%found
385
loop
386
select count(1) into v_count from tpa_jituan
387
where jituan_id = v_jituanid and sum_level = 3 and time_id in (select month_id from dim_time_month where year_id = timeid);
388
if(v_count>0)then
389
390
--本年话务量
391
select sum(tch_traffic) into v_tch_traffic from tpa_jituan
392
where jituan_id = v_jituanid and sum_level = 3 and time_id in (select month_id from dim_time_month where year_id = timeid);
393
--环比年
394
select year_desc,last_year_id into v_time_desc,v_hb_timeid from dim_time_year where year_id = timeid;
395
--环比话务量
396
begin
397
select sum(tch_traffic) into v_hb_traffic from tpa_jituan
398
where jituan_id = v_jituanid and sum_level = 3 and time_id in (select month_id from dim_time_month
399
where year_id = v_hb_timeid);
400
exception
401
when others then
402
v_hb_traffic :=0;
403
end;
404
--同比话务量不需要-999999
405
v_tb_rate := -999999;
406
select count(1) into v_is_have from tpa_jituan_query
407
where time_id = timeid and jituan_id = v_jituanid and sum_level = 5;
408
if (v_hb_traffic <> 0) then
409
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
410
else
411
v_hb_rate := -999999;
412
end if;
413
if (v_is_have = 0) then
414
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
415
values (timeid,v_jituanid,5,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
416
else
417
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
418
where time_id = timeid and jituan_id = v_jituanid and sum_level = 5;
419
end if;
420
commit;
421
end if;
422
fetch mycur into v_jituanid, v_jituanname,v_region_name;
423
end loop;
424
close mycur;
425
else
426
select count(1) into v_count from tpa_jituan
427
where jituan_id = jituanid and sum_level = 3 and time_id in (select month_id from dim_time_month where year_id = timeid);
428
if(v_count>0)then
429
--本年话务量
430
select sum(tch_traffic) into v_tch_traffic from tpa_jituan
431
where jituan_id = jituanid and sum_level = 3 and time_id in (select month_id from dim_time_month where year_id = timeid);
432
--环比年
433
select year_desc,last_year_id into v_time_desc,v_hb_timeid from dim_time_year where year_id = timeid;
434
--环比话务量
435
begin
436
select sum(tch_traffic) into v_hb_traffic from tpa_jituan
437
where jituan_id = jituanid and sum_level = 3 and time_id in (select month_id from dim_time_month
438
where year_id = v_hb_timeid);
439
exception
440
when others then
441
v_hb_traffic :=0;
442
end;
443
--同比话务量不需要
444
v_tb_rate := -999999;
445
select count(1) into v_is_have from tpa_jituan_query
446
where time_id = timeid and jituan_id = jituanid and sum_level = 5;
447
if (v_hb_traffic <> 0) then
448
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
449
else
450
v_hb_rate := -999999;
451
end if;
452
if (v_is_have = 0) then
453
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
454
values (timeid,jituanid,5,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
455
else
456
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
457
where time_id = timeid and jituan_id = jituanid and sum_level = 5;
458
end if;
459
commit;
460
end if;
461
end if;
462
end if;
463
issuccess := 1;
464
exception
465
when others then
466
rollback;
467
return;
468
469
end sp_tpa_jituan_query;
470![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
存储过程2(这个也比较长,也记下来吧)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
101
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
102
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
103
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
104
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
105
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
106
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
107
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
108
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
109
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
110
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
111
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
112
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
113
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
114
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
115
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
116
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
117
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
118
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
119
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
120
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
121
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
122
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
123
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
124
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
125
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
126
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
127
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
128
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
129
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
130
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
131
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
132
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
133
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
134
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
135
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
136
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
137
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
138
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
139
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
140
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
141
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
142
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
143
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
144
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
145
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
146
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
147
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
148
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
149
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
150
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
151
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
152
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
153
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
154
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
155
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
156
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
157
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
158
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
159
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
160
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
161
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
162
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
163
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
164
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
165
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
166
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
167
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
168
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
169
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
170
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
171
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
172
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
173
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
174
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
175
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
176
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
177
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
178
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
179
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
180
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
181
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
182
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
183
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
184
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
185
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
186
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
187
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
188
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
189
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
190
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
191
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
192
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
193
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
194
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
195
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
196
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
197
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
198
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
199
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
200
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
201
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
202
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
203
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
204
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
205
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
206
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
207
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
208
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
209
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
210
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
211
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
212
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
213
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
214
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
215
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
216
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
217
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
218
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
219
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
220
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
221
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
222
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
223
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
224
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
225
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
226
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
227
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
228
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
229
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
230
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
231
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
232
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
233
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
234
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
235
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
236
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
237
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
238
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
239
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
240
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
241
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
242
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
243
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
244
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
245
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
246
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
247
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
248
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
249
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
250
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
251
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
252
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
253
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
254
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
255
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
256
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
257
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
258
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
259
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
260
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
261
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
262
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
263
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
264
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
265
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
266
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
267
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
268
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
269
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
270
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
271
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
272
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
273
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
274
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
275
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
276
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
277
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
278
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
279
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
280
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
281
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
282
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
283
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
284
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
285
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
286
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
287
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
288
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
289
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
290
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
291
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
292
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
293
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
294
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
295
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
296
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
297
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
298
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
299
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
300
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
301
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
302
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
303
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
304
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
305
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
306
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
307
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
308
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
309
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
310
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
311
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
312
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
313
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
314
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
315
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
316
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
317
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
318
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
319
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
320
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
321
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
322
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
323
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
324
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
325
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
326
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
327
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
328
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
329
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
330
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
331
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
332
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
333
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
334
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
335
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
336
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
337
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
338
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
339
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
340
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
341
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
342
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
343
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
344
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
345
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
346
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
347
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
348
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
349
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
350
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
351
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
352
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
353
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
354
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
355
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
356
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
357
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
358
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
359
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
360
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
361
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
362
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
363
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
364
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
365
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
366
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
367
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
368
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
369
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
370
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
371
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
372
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
373
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
374
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
375
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
376
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
377
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
378
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
379
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
380
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
381
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
382
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
383
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
384
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
385
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
386
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
387
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
388
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
389
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
390
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
391
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
392
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
393
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
394
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
395
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
396
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
397
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
398
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
399
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
400
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
401
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
402
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
403
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
404
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
405
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
406
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
407
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
408
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
409
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
410
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
411
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
412
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
413
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
414
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
415
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
416
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
417
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
418
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
419
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
420
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
421
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
422
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
423
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
424
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
425
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
426
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
427
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
428
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
429
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
430
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
431
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
432
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
433
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
434
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
435
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
436
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
437
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
438
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
439
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
440
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
441
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
442
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
443
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
444
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
445
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
446
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
447
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
448
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
449
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
450
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
451
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
452
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
453
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
454
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
455
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
456
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
457
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
458
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
459
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
460
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
461
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
462
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
463
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
464
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
465
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
466
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
467
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
468
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
469
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
470
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
1
create or replace procedure sp_tpa_jituan_query2(timeid number,sum_level number,issuccess out number) is
2
v_jituanid number;
3
v_jituanname varchar2(64);
4
v_region_name varchar2(64);
5
v_tch_traffic float;
6
v_time_desc varchar2(64);
7
v_hb_timeid number;
8
v_tb_timeid number;
9
v_hb_traffic float;
10
v_tb_traffic float;
11
v_is_have number;
12
v_hb_rate float;
13
v_tb_rate float;
14
v_count number;
15
cursor mycur is
16
select a.jituan_id,a.jituan_name,c.region_name
17
from tai_jituan a,tai_borough b,(select distinct region_id,region_name from region_city_local) c
18
where a.borough_id = b.borough_id and b.region_id = c.region_id;
19
begin
20
issuccess := -1;
21
v_count := -1;
22
v_jituanid := 0;
23
v_jituanname :='';
24
v_region_name :='';
25
v_tch_traffic :=0;
26
v_time_desc :='';
27
v_hb_timeid :=0;
28
v_tb_timeid :=0;
29
v_hb_traffic :=0;
30
v_tb_traffic :=0;
31
v_is_have :=0;
32
v_hb_rate:=0;
33
v_tb_rate :=0;
34
/*查2007年04月02日某一时刻的(比如03时),
35
集团id=1的所属地区、集团名、话务量,前一天的这个时刻的话务量,环比增幅,
36
上个月同一日的话务量,同比增幅,及判断该集团是否是我得关注*/
37
if (sum_level = 0) then
38
--小时汇总查询
39
40
if mycur%isopen = false then
41
open mycur;
42
end if;
43
fetch mycur into v_jituanid, v_jituanname,v_region_name;
44
while mycur%found
45
loop
46
select count(1) into v_count from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = timeid;
47
if (v_count > 0 ) then
48
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = timeid;
49
select hour_desc,LAST_HOUR_ID,YESTODAY_HOUR_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_hour where hour_id = timeid;
50
begin
51
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = v_hb_timeid;
52
exception
53
when others then
54
v_hb_traffic := 0;
55
end;
56
begin
57
select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 0 and time_id = v_tb_timeid;
58
exception
59
when others then
60
v_tb_traffic:=0;
61
end;
62
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 0;
63
if (v_hb_traffic <> 0) then
64
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
65
else
66
v_hb_rate := -999999;
67
end if;
68
if (v_tb_traffic <> 0) then
69
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
70
else
71
v_tb_rate := -999999;
72
end if;
73
if (v_is_have = 0) then
74
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
75
values (timeid,v_jituanid,0,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
76
else
77
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
78
where time_id = timeid and jituan_id = v_jituanid and sum_level = 0;
79
end if;
80
commit;
81
end if;
82
fetch mycur into v_jituanid, v_jituanname,v_region_name;
83
end loop;
84
close mycur;
85
86
elsif (sum_level = 1) then
87
--日汇总查询
88
89
if mycur%isopen = false then
90
open mycur;
91
end if;
92
fetch mycur into v_jituanid, v_jituanname,v_region_name;
93
while mycur%found
94
loop
95
select count(1) into v_count from tpa_jituan where jituan_id = v_jituanid and sum_level = 1 and time_id = timeid;
96
if (v_count >0) then
97
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 1 and time_id = timeid;
98
select day_desc,LAST_day_ID,lm_day_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_day where day_id = timeid;
99
begin
100
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 1 and time_id = v_hb_timeid;
101
exception
102
when others then
103
v_hb_traffic := 0;
104
end;
105
begin
106
select tch_traffic into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 1 and time_id = v_tb_timeid;
107
exception
108
when others then
109
v_tb_traffic := 0;
110
end;
111
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 1;
112
if (v_hb_traffic <> 0) then
113
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
114
else
115
v_hb_rate := -999999;
116
end if;
117
if (v_tb_traffic <> 0) then
118
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
119
else
120
v_tb_rate := -999999;
121
end if;
122
if (v_is_have = 0) then
123
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
124
values (timeid,v_jituanid,1,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
125
else
126
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
127
where time_id = timeid and jituan_id = v_jituanid and sum_level = 1;
128
end if;
129
commit;
130
end if;
131
fetch mycur into v_jituanid, v_jituanname,v_region_name;
132
end loop;
133
close mycur;
134
135
elsif (sum_level = 2) then
136
--周汇总查询
137
138
if mycur%isopen = false then
139
open mycur;
140
end if;
141
fetch mycur into v_jituanid, v_jituanname,v_region_name;
142
while mycur%found
143
loop
144
select count(1) into v_count from tpa_jituan where jituan_id = v_jituanid and sum_level = 2 and time_id = timeid;
145
if(v_count>0)
146
then
147
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 2 and time_id = timeid;
148
select week_desc,LAST_week_ID,ly_week_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_week where week_id = timeid;
149
begin
150
select nvl(tch_traffic,0) into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 2 and time_id = v_hb_timeid;
151
exception
152
when others then
153
v_hb_traffic := 0;
154
end;
155
begin select nvl(tch_traffic,0) into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 2 and time_id = v_tb_timeid;
156
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 2;
157
exception
158
when others then
159
v_tb_traffic := 0;
160
end;
161
if (v_hb_traffic <> 0) then
162
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
163
else
164
v_hb_rate := -999999;
165
end if;
166
if (v_tb_traffic <> 0) then
167
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
168
else
169
v_tb_rate := -999999;
170
end if;
171
if (v_is_have = 0) then
172
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
173
values (timeid,v_jituanid,2,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
174
else
175
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
176
where time_id = timeid and jituan_id = v_jituanid and sum_level = 2;
177
end if;
178
commit;
179
end if;
180
fetch mycur into v_jituanid, v_jituanname,v_region_name;
181
end loop;
182
close mycur;
183
184
elsif (sum_level = 3) then
185
--月汇总查询
186
187
if mycur%isopen = false then
188
open mycur;
189
end if;
190
fetch mycur into v_jituanid, v_jituanname,v_region_name;
191
while mycur%found
192
loop
193
select count(1) into v_count from tpa_jituan where jituan_id = v_jituanid and sum_level = 3 and time_id = timeid;
194
if (v_count >0) then
195
select tch_traffic into v_tch_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 3 and time_id = timeid;
196
select month_desc,LAST_month_ID,ly_month_ID into v_time_desc,v_hb_timeid,v_tb_timeid from dim_time_month where month_id = timeid;
197
begin
198
select tch_traffic into v_hb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 3 and time_id = v_hb_timeid;
199
exception
200
when others then
201
v_hb_traffic := 0;
202
end;
203
begin
204
select nvl(tch_traffic,0) into v_tb_traffic from tpa_jituan where jituan_id = v_jituanid and sum_level = 3 and time_id = v_tb_timeid;
205
exception
206
when others then
207
v_tb_traffic :=0;
208
end;
209
select count(1) into v_is_have from tpa_jituan_query where time_id = timeid and jituan_id = v_jituanid and sum_level = 3;
210
if (v_hb_traffic <> 0) then
211
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
212
else
213
v_hb_rate := -999999;
214
end if;
215
if (v_tb_traffic <> 0) then
216
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;
217
else
218
v_tb_rate := -999999;
219
end if;
220
if (v_is_have = 0) then
221
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
222
values (timeid,v_jituanid,3,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
223
else
224
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
225
where time_id = timeid and jituan_id = v_jituanid and sum_level = 3;
226
end if;
227
commit;
228
end if;
229
fetch mycur into v_jituanid, v_jituanname,v_region_name;
230
end loop;
231
elsif (sum_level = 5) then
232
--年度汇总查询
233
234
if mycur%isopen = false then
235
open mycur;
236
end if;
237
fetch mycur into v_jituanid, v_jituanname,v_region_name;
238
while mycur%found
239
loop
240
select count(1) into v_count from tpa_jituan
241
where jituan_id = v_jituanid and sum_level = 3 and time_id in (select month_id from dim_time_month where year_id = timeid);
242
if(v_count>0) then
243
--本年话务量
244
begin
245
select sum(tch_traffic) into v_tch_traffic from tpa_jituan
246
where jituan_id = v_jituanid and sum_level = 3 and time_id in (select month_id from dim_time_month where year_id = timeid);
247
exception
248
when others then
249
v_tch_traffic := 0;
250
end;
251
begin
252
--环比年
253
select year_desc,last_year_id into v_time_desc,v_hb_timeid from dim_time_year where year_id = timeid;
254
--环比话务量
255
select nvl(sum(tch_traffic),0) into v_hb_traffic from tpa_jituan
256
where jituan_id = v_jituanid and sum_level = 3 and time_id in (select month_id from dim_time_month
257
where year_id = v_hb_timeid);
258
exception
259
when others then
260
v_hb_traffic := 0;
261
end;
262
--同比话务量不需要
263
v_tb_rate := -999999;
264
select count(1) into v_is_have from tpa_jituan_query
265
where time_id = timeid and jituan_id = v_jituanid and sum_level = 5;
266
if (v_hb_traffic <> 0) then
267
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;
268
else
269
v_hb_rate := -999999;
270
end if;
271
if (v_is_have = 0) then
272
insert into tpa_jituan_query (time_id,jituan_id,sum_level,jituan_name,region_name,traffic,circle_traffic,as_traffic,time_desc,hb_rate,tb_rate)
273
values (timeid,v_jituanid,5,v_jituanname,v_region_name,v_tch_traffic,v_hb_traffic,v_tb_traffic,v_time_desc,v_hb_rate,v_tb_rate);
274
else
275
update tpa_jituan_query set traffic = v_tch_traffic,circle_traffic = v_hb_traffic,as_traffic = v_tb_traffic,hb_rate =v_hb_rate ,tb_rate=v_tb_rate
276
where time_id = timeid and jituan_id = v_jituanid and sum_level = 5;
277
end if;
278
commit;
279
end if;
280
fetch mycur into v_jituanid, v_jituanname,v_region_name;
281
end loop;
282
end if;
283
issuccess := 1;
284
exception
285
when others then
286![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
287
rollback;
288
return;
289
290
end sp_tpa_jituan_query2;
291![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
292![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
101
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
102
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
103
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
104
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
105
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
106
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
107
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
108
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
109
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
110
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
111
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
112
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
113
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
114
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
115
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
116
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
117
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
118
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
119
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
120
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
121
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
122
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
123
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
124
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
125
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
126
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
127
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
128
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
129
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
130
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
131
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
132
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
133
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
134
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
135
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
136
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
137
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
138
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
139
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
140
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
141
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
142
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
143
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
144
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
145
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
146
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
147
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
148
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
149
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
150
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
151
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
152
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
153
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
154
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
155
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
156
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
157
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
158
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
159
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
160
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
161
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
162
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
163
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
164
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
165
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
166
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
167
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
168
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
169
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
170
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
171
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
172
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
173
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
174
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
175
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
176
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
177
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
178
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
179
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
180
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
181
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
182
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
183
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
184
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
185
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
186
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
187
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
188
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
189
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
190
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
191
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
192
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
193
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
194
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
195
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
196
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
197
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
198
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
199
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
200
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
201
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
202
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
203
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
204
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
205
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
206
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
207
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
208
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
209
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
210
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
211
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
212
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
213
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
214
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
215
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
216
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
217
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
218
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
219
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
220
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
221
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
222
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
223
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
224
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
225
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
226
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
227
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
228
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
229
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
230
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
231
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
232
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
233
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
234
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
235
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
236
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
237
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
238
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
239
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
240
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
241
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
242
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
243
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
244
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
245
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
246
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
247
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
248
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
249
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
250
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
251
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
252
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
253
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
254
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
255
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
256
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
257
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
258
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
259
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
260
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
261
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
262
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
263
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
264
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
265
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
266
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
267
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
268
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
269
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
270
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
271
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
272
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
273
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
274
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
275
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
276
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
277
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
278
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
279
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
280
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
281
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
282
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
283
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
284
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
285
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
286
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
287
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
288
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
289
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
290
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
291
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
292
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)