触发器和存储过程

记录下工作中写的其中一块触发器和存储过程，以便于将来查询
触发器

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 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

存储过程2（这个也比较长，也记下来吧）

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

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

elsif (sum_level = 1) then
87

--日汇总查询
88

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

287

rollback;
288

return;
289

290

end sp_tpa_jituan_query2;
291

292

posted on 2007-05-29 17:55 Emma 阅读(498) 评论(0) 收藏举报