记录下工作中写的其中一块触发器和存储过程，以便于将来查询
触发器
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;
/****************************************/
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;
/****************************************/
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;
触发器
create or replace trigger tr_tai_cell_jituan_i after insert on tai_cell_jituan_relation for each rowbegin 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;/****************************************/
create or replace trigger tr_tai_cell_jituan_d after delete on tai_cell_jituan_relation for each rowbegin 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;/****************************************/create or replace trigger tr_tai_cell_jituan_u after update on tai_cell_jituan_relation for each rowbegin 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;
存储过程
1create or replace procedure sp_tpa_jituan_query(timeid number,regionname varchar2,sum_level number,jituanid number,issuccess out number) is
2v_jituanid number;
3v_jituanname varchar2(64);
4v_region_name varchar2(64);
5v_tch_traffic float;
6v_time_desc varchar2(64);
7v_hb_timeid number;
8v_tb_timeid number;
9v_hb_traffic float;
10v_tb_traffic float;
11v_is_have number;
12v_hb_rate float;
13v_tb_rate float;
14v_count number;
15 cursor mycur is
16 select a.jituan_id,a.jituan_name,c.region_name
17from tai_jituan a,tai_borough b,(select distinct region_id,region_name from region_city_local where region_name = regionname) c
18where a.borough_id = b.borough_id and b.region_id = c.region_id;
19begin
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
465when others then
466rollback;
467return;
468
469end sp_tpa_jituan_query;
470
存储过程2（这个也比较长，也记下来吧）
create or replace procedure sp_tpa_jituan_query(timeid number,regionname varchar2,sum_level number,jituanid number,issuccess out number) is2
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 is16
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) c18
where a.borough_id = b.borough_id and b.region_id = c.region_id;19
begin20
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) then39
--小时汇总查询40
if (jituanid <= 0) then41
if mycur%isopen = false then42
open mycur;43
end if;44
fetch mycur into v_jituanid, v_jituanname,v_region_name;45
while mycur%found 46
loop47
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)then49
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
begin52
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
exception54
when others then55
v_hb_traffic:=0;56
end;57
begin58
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
exception60
when others then61
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) then65
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;66
else67
v_hb_rate := -999999;68
end if;69
if (v_tb_traffic <> 0) then70
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;71
else72
v_tb_rate := -999999;73
end if;74
if (v_is_have = 0) then75
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
else78
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
else87
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) c89
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
begin95
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
exception97
when others then98
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 then104
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) then108
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;109
else110
v_hb_rate := -999999;111
end if;112
if (v_tb_traffic <> 0) then113
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;114
else115
v_tb_rate := -999999;116
end if;117
if (v_is_have = 0) then118
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
else121
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) then128
--日汇总查询129
if (jituanid <= 0) then130
if mycur%isopen = false then131
open mycur;132
end if;133
fetch mycur into v_jituanid, v_jituanname,v_region_name;134
while mycur%found 135
loop136
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
begin141
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
exception143
when others then144
v_hb_traffic :=0;145
end;146
begin147
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
exception149
when others then150
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) then154
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;155
else156
v_hb_rate := -999999;157
end if;158
if (v_tb_traffic <> 0) then159
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;160
else161
v_tb_rate := -999999;162
end if;163
if (v_is_have = 0) then164
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
else167
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
else176
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
begin181
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
exception183
when others then184
v_hb_traffic :=0;185
end;186
begin187
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
exception189
when others then190
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) then196
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
else199
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) then206
--周汇总查询207
if (jituanid <= 0) then208
if mycur%isopen = false then209
open mycur;210
end if;211
fetch mycur into v_jituanid, v_jituanname,v_region_name;212
while mycur%found 213
loop214
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)then216
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
begin219
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
exception221
when others then222
v_hb_traffic :=0;223
end;224
begin225
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
exception227
when others then228
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) then232
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;233
else234
v_hb_rate := -999999;235
end if;236
if (v_tb_traffic <> 0) then237
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;238
else239
v_tb_rate := -999999;240
end if;241
if (v_is_have = 0) then242
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
else245
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
else254
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)then256
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
begin259
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
exception261
when others then262
v_hb_traffic :=0;263
end;264
begin265
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
exception267
when others then268
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) then272
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;273
else274
v_hb_rate := -999999;275
end if;276
if (v_tb_traffic <> 0) then277
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;278
else279
v_tb_rate := -999999;280
end if;281
if (v_is_have = 0) then282
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
else285
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) then294
if mycur%isopen = false then295
open mycur;296
end if;297
fetch mycur into v_jituanid, v_jituanname,v_region_name;298
while mycur%found 299
loop300
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)then302
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
begin305
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
exception307
when others then308
v_hb_traffic :=0;309
end;310
begin311
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
exception313
when others then314
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) then318
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;319
else320
v_hb_rate := -999999;321
end if;322
if (v_tb_traffic <> 0) then323
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;324
else325
v_tb_rate := -999999;326
end if;327
if (v_is_have = 0) then328
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
else331
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
else340
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) then342
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
begin345
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
exception347
when others then348
v_hb_traffic :=0;349
end;350
begin351
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
exception353
when others then354
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) then358
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;359
else360
v_hb_rate := -999999;361
end if;362
if (v_tb_traffic <> 0) then363
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;364
else365
v_tb_rate := -999999;366
end if;367
if (v_is_have = 0) then368
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
else371
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) then378
--年度汇总查询timeid=2006379
if (jituanid <= 0) then380
if mycur%isopen = false then381
open mycur;382
end if;383
fetch mycur into v_jituanid, v_jituanname,v_region_name;384
while mycur%found 385
loop386
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)then389
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
begin397
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
exception401
when others then402
v_hb_traffic :=0;403
end; 404
--同比话务量不需要-999999405
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) then409
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;410
else411
v_hb_rate := -999999;412
end if;413
if (v_is_have = 0) then414
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
else417
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
else426
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
begin436
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
exception440
when others then441
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) then448
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;449
else450
v_hb_rate := -999999;451
end if;452
if (v_is_have = 0) then453
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
else456
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
exception465
when others then466
rollback;467
return; 468
469
end sp_tpa_jituan_query;470
1create or replace procedure sp_tpa_jituan_query2(timeid number,sum_level number,issuccess out number) is
2v_jituanid number;
3v_jituanname varchar2(64);
4v_region_name varchar2(64);
5v_tch_traffic float;
6v_time_desc varchar2(64);
7v_hb_timeid number;
8v_tb_timeid number;
9v_hb_traffic float;
10v_tb_traffic float;
11v_is_have number;
12v_hb_rate float;
13v_tb_rate float;
14v_count number;
15 cursor mycur is
16 select a.jituan_id,a.jituan_name,c.region_name
17from tai_jituan a,tai_borough b,(select distinct region_id,region_name from region_city_local) c
18where a.borough_id = b.borough_id and b.region_id = c.region_id;
19begin
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
285when others then
286
287rollback;
288return;
289
290end sp_tpa_jituan_query2;
291
292
create or replace procedure sp_tpa_jituan_query2(timeid number,sum_level number,issuccess out number) is2
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 is16
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) c18
where a.borough_id = b.borough_id and b.region_id = c.region_id;19
begin20
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) then38
--小时汇总查询39
40
if mycur%isopen = false then41
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 ) then48
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 then54
v_hb_traffic := 0;55
end;56
begin57
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 then60
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) then64
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;65
else66
v_hb_rate := -999999;67
end if;68
if (v_tb_traffic <> 0) then69
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;70
else71
v_tb_rate := -999999;72
end if;73
if (v_is_have = 0) then74
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
else77
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) then87
--日汇总查询88
89
if mycur%isopen = false then90
open mycur;91
end if;92
fetch mycur into v_jituanid, v_jituanname,v_region_name;93
while mycur%found 94
loop95
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) then97
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
begin100
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 then103
v_hb_traffic := 0;104
end;105
begin106
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 then109
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) then113
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;114
else115
v_hb_rate := -999999;116
end if;117
if (v_tb_traffic <> 0) then118
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;119
else120
v_tb_rate := -999999;121
end if;122
if (v_is_have = 0) then123
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
else126
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) then136
--周汇总查询137
138
if mycur%isopen = false then139
open mycur;140
end if;141
fetch mycur into v_jituanid, v_jituanname,v_region_name;142
while mycur%found 143
loop144
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
then147
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
begin150
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 then153
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 then159
v_tb_traffic := 0;160
end;161
if (v_hb_traffic <> 0) then162
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;163
else164
v_hb_rate := -999999;165
end if;166
if (v_tb_traffic <> 0) then167
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;168
else169
v_tb_rate := -999999;170
end if;171
if (v_is_have = 0) then172
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
else175
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 then188
open mycur;189
end if;190
fetch mycur into v_jituanid, v_jituanname,v_region_name;191
while mycur%found 192
loop193
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) then195
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
begin198
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 then201
v_hb_traffic := 0;202
end;203
begin204
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 then207
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) then211
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;212
else213
v_hb_rate := -999999;214
end if;215
if (v_tb_traffic <> 0) then216
v_tb_rate := (v_tch_traffic-v_tb_traffic)/v_tb_traffic;217
else218
v_tb_rate := -999999;219
end if;220
if (v_is_have = 0) then221
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
else224
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 then235
open mycur;236
end if;237
fetch mycur into v_jituanid, v_jituanname,v_region_name;238
while mycur%found 239
loop240
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) then243
--本年话务量244
begin245
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 then249
v_tch_traffic := 0;250
end;251
begin252
--环比年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 then260
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) then267
v_hb_rate := (v_tch_traffic-v_hb_traffic)/v_hb_traffic;268
else269
v_hb_rate := -999999;270
end if;271
if (v_is_have = 0) then272
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
else275
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
exception285
when others then286
287
rollback;288
return; 289
290
end sp_tpa_jituan_query2;291
292
