信息来源:邪恶八进制信息安全团队

create procedure "npmuser".chk_grade(s_time datetime year to second)

define v_ne_id integer;
define v_ne_type integer;
define v_time datetime year to second;
define v42 float;
define v66 float;
define v45 float;
define v43 float;
define v65 float;
define v411 float;
define v67 float;
define f42 float;
define f66 float;
define f45 float;
define f43 float;
define f65 float;
define f411 float;
define f67 float;

SET DEBUG FILE TO '/tmp/chk_grade.log';
TRACE 'BEGIN 24h traff';
TRACE ON;

delete from chk_grade where first_result=s_time;

insert into chk_grade(ne_id,ne_type,first_result,g42,
g66,g45,g43,g65,g411,g67)
select
TPA_UNIC_MSC_SUM.ne_id,TPA_UNIC_MSC_SUM.ne_type,TPA_UNIC_MSC_SUM.first_result,
(SFB_DIVFLOAT_1((NVL(TPA_UNIC_MSC_SUM.SEND_IAI_MSG_NHO,0)+NVL(TPA_UNIC_MSC_SUM.VOICE_PAGING_
REQ,0)),(TPA_UNIC_BTS_SUM.TCH_SEIZE_NHO*SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.SEND_CALLPROCEEDING_MSG,(NVL
(TPA_UNIC_MSC_SUM.SEND_CALLPROCEEDING_MSG,0)+NVL(TPA_UNIC_MSC_SUM.RECE_CALLCONFIRM_MSG,0)),0,0)+NVL(
TPA_UNIC_MSC_SUM.RECE_IAI_MSG_NHO,0)),0,0)*(SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.SEND_CALLPROCEEDING_MSG,
(NVL(TPA_UNIC_MSC_SUM.SEND_CALLPROCEEDING_MSG,0)+NVL(TPA_UNIC_MSC_SUM.RECE_CALLCONFIRM_MSG,0)),0,0)*
SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.RAND_ACC_SUC,TPA_UNIC_BTS_SUM.RAND_ACC_ATT,0,0)*SFB_DIVFLOAT_1(TPA_U
NIC_BTS_SUM.TCH_SEIZE_NHO,TPA_UNIC_BTS_SUM.TCH_CALL_REQ_NHO,0,0)+(1-SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.
SEND_CALLPROCEEDING_MSG,(NVL(TPA_UNIC_MSC_SUM.SEND_CALLPROCEEDING_MSG,0)+NVL(TPA_UNIC_MSC_SUM.RECE_C
ALLCONFIRM_MSG,0)),0,0))*SFB_DIVFLOAT_1(NVL(TPA_UNIC_MSC_SUM.PAGING_RESP,0),NVL(TPA_UNIC_MSC_SUM.PAG
ING_REQ, 0),0,0)*SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.TCH_SEIZE_NHO,TPA_UNIC_BTS_SUM.TCH_CALL_REQ_NHO,0,0
)))*100,
NVL(SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.TCH_TRAFFIC*60,TPA_UNIC_BTS_SUM.DROP_CALL_TCH,0,0),0),
(SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.SEND_CALLPROCEEDING_MSG,(NVL(TPA_UNIC_MSC_SUM.SEND_CALLPROC
EEDING_MSG,0)+NVL(TPA_UNIC_MSC_SUM.RECE_CALLCONFIRM_MSG,0)),0,0)*SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.RAN
D_ACC_SUC,TPA_UNIC_BTS_SUM.RAND_ACC_ATT,0,0)*SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.TCH_SEIZE_NHO,TPA_UNIC_
BTS_SUM.TCH_CALL_REQ_NHO,0,0)+(1-SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.SEND_CALLPROCEEDING_MSG,(NVL(TPA_UN
IC_MSC_SUM.SEND_CALLPROCEEDING_MSG,0)+NVL(TPA_UNIC_MSC_SUM.RECE_CALLCONFIRM_MSG,0)),0,0))*SFB_DIVFLO
AT_1(NVL (TPA_UNIC_MSC_SUM.PAGING_RESP,0),NVL(TPA_UNIC_MSC_SUM.PAGING_REQ,0),0,0)*SFB_DIVFLOAT_1(TPA
_UNIC_BTS_SUM.TCH_SEIZE_NHO,TPA_UNIC_BTS_SUM.TCH_CALL_REQ_NHO,0,0))*100,
(SFB_DIVFLOAT_1((NVL(TPA_UNIC_MSC_SUM.SEND_IAI_MSG_NHO,0)+NVL(TPA_UNIC_MSC_SUM.VOICE_PAGING_
REQ,0)+NVL(TPA_UNIC_MSC_SUM.LOSE_NUM_USER_BUSY_CALLED,0)),(TPA_UNIC_BTS_SUM.TCH_SEIZE_NHO*(SFB_DIVFL
OAT_1(TPA_UNIC_MSC_SUM.SEND_CALLPROCEEDING_MSG,(NVL(TPA_UNIC_MSC_SUM.SEND_CALLPROCEEDING_MSG,0)+NVL(
TPA_UNIC_MSC_SUM.RECE_CALLCONFIRM_MSG,0)),0,0))+NVL(TPA_UNIC_MSC_SUM.RECE_IAI_MSG_NHO,0)-(NVL(TPA_UN
IC_MSC_SUM.ROUTING_QUERY_REQ,0)-NVL(TPA_UNIC_MSC_SUM.ROUTING_QUERY_SUC,0))),0,0))*100 ,
NVL(SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.DROP_CALL_TCH,TPA_UNIC_BTS_SUM.TCH_SEIZE_NHO,0,0)*100,0)
,
NVL(SFB_DIVFLOAT_1(TPA_UNIC_MSC_SUM.PAGING_RESP,TPA_UNIC_MSC_SUM.PAGING_REQ,0,0)*100,0),
NVL(SFB_DIVFLOAT_1(TPA_UNIC_BTS_SUM.WORST_CELL,TPA_GSMA_UNIC_SUM.TOTAL_CELL,0,0)*100,0)
from tpa_unic_msc_sum tpa_unic_msc_sum,tpa_unic_bts_sum tpa_unic_bts_sum,tpa_gsma_unic_sum t
pa_gsma_unic_sum
where tpa_unic_msc_sum.first_result=tpa_unic_bts_sum.first_result
and tpa_gsma_unic_sum.first_result=tpa_unic_bts_sum.first_result
and tpa_unic_msc_sum.ne_id=tpa_unic_bts_sum.ne_id
and tpa_gsma_unic_sum.ne_id=tpa_unic_bts_sum.ne_id
and tpa_unic_bts_sum.ne_type in (10000,10003)
and tpa_unic_msc_sum.first_result=s_time
and tpa_unic_msc_sum.sv_id=-1;

foreach
select ne_id,ne_type,first_result,g42,g66,g45,g43,g65,g411,g67
into v_ne_id,v_ne_type,v_time,v42,v66,v45,v43,v65,v411,v67
from chk_grade
where first_result=s_time
------------------g42-------------------
if v42 - 88 >= 0 then
if v42 - 88 >= 5 then
let f42 = 5;
else
let f42 = 3 + (v42 - 88)*0.4;
end if;
end if;
if v42 - 88 < 0 then
if 88 - v42 >= 10 then
let f42 = 0;
else
let f42 = 3 + (v42 - 88)*0.3;
end if;
end if;
update chk_grade set g42_fact = f42 where ne_id = v_ne_id and ne_type = v_ne_type and first_
result = v_time;
-------------------------------------
------------------g66-------------------
if v66 - 100 >= 0 then
if v66 - 100 >= 30 then
let f66 = 5;
else
let f66 = 3 + (v66 - 100)*0.4/6;
end if;
end if;
if v66 - 100 < 0 then
if 100 - v66 >= 60 then
let f66 = 0;
else
let f66 = 3 + (v66 - 100)*0.3/6;
end if;
end if;
update chk_grade set g66_fact = f66 where ne_id = v_ne_id and ne_type = v_ne_type and first_
result = v_time;
-------------------------------------
------------------g45-------------------
if v45 - 92 >= 0 then
if v45 - 92 >= 4 then
let f45 = 3;
else
let f45 = 1.8 + (v45 - 92)*0.3;
end if;
end if;
if v45 - 92 < 0 then
if 92 - v45 >= 6 then
let f45 = 0;
else
let f45 = 1.8 + (v45 - 92)*0.3;
end if;
end if;
update chk_grade set g45_fact = f45 where ne_id = v_ne_id and ne_type = v_ne_type and first_
result = v_time;
--------------------------------------
------------------g43-------------------
if v43 >= 95 then
if v43 >= 99 then
let f43 = 3;
else
let f43 = 1.8 + (v43 - 95)*0.3;
end if;
end if;
if v43 < 95 then
if v43 <= 91 then
let f43 = 0;
else
let f43 = 3 + (v43 - 95)*0.45;
end if;
end if;
update chk_grade set g43_fact = f43 where ne_id = v_ne_id and ne_type = v_ne_type and first_
result = v_time;
-------------------------------------
------------------g65-------------------
if v65 <= 1.1 then
if v65 <= 0.7 then
let f65 = 3;
else
let f65 = 1.8 + (1.1 - v65)*3;
end if;
end if;
if v65 > 1.1 then
if v65 >= 1.7 then
let f65 = 0;
else
let f65 = 1.8 + (1.1 - v65)*3;
end if;
end if;
update chk_grade set g65_fact = f65 where ne_id = v_ne_id and ne_type = v_ne_type and first_
result = v_time;
-------------------------------------
------------------g411-------------------
if v411 >= 88 then
if v411 >= 94 then
let f411 = 3;
else
let f411 = 1.8 + (v411 - 88)*0.2;
end if;
end if;
if v411 < 88 then
if v411 <= 82 then
let f411 = 0;
else
let f411 = 1.8 + (v411 - 88)*0.3;
end if;
end if;
update chk_grade set g411_fact = f411 where ne_id = v_ne_id and ne_type = v_ne_type and firs
t_result = v_time;
-------------------------------------
------------------g67-------------------
if v67 <= 2 then
if v67 <= 0.8 then
let f67 = 3;
else
let f67 = 1.8 + (2 - v67)*1;
end if;
end if;
if v67 > 2 then
if v67 >= 3.8 then
let f67 = 0;
else
let f67 = 1.8 + (2 - v67)*1;
end if;
end if;
update chk_grade set g67_fact = f67 where ne_id = v_ne_id and ne_type = v_ne_type and first_
result = v_time;
-------------------------------------

end foreach;
end procedure;

create procedure "npmuser".super_busy_cell(s_time datetime year to second)
SET DEBUG FILE TO &#39;/tmp/superbusycell.log&#39;;
TRACE &#39;BEGIN 24h traff&#39;;
TRACE ON;
      delete from super_busy_cell where first_result=s_time;
      delete from busy_cell where first_result=s_time;
      delete from idle_cell where first_result=s_time;
      delete from heavy_ovfl_cell where first_result=s_time;
      delete from ovfl_cell where first_result=s_time;
      delete from worst_cell where first_result=s_time;
      insert into super_busy_cell(ne_id,first_result,cell_id,avail_tch_nbr,radio_capacity,
      tch_traffic,tch_traffic_h,tch_nbr,drop_call_tch,tch_seize_nho,vendor_id)
      select b.region_id,a.first_result,a.ne_id,a.avail_tch_nbr,
      decode(c.trx_nbr,0,0,1,2.28,2,8.25,3,14.04,4,21.04,5,27.04,34.68),
      a.tch_traffic,a.tch_traffic_h,a.tch_nbr,a.drop_call_tch,a.tch_seize_nho,b.vendor_id
      from tpc_unic_bts_ne a,tcc_ne_snap b,tcc_bts c
      where a.first_result=s_time
      and extend(s_time,year to day)=b.compress_date
      and b.compress_date=c.compress_date
      and b.ne_type=300
      and a.ne_id=b.ne_id
      and b.ne_id=c.ne_id
      and a.super_busy_cell=1;
      
      insert into busy_cell(ne_id,first_result,cell_id,avail_tch_nbr,radio_capacity,
      tch_traffic,tch_traffic_h,tch_nbr,drop_call_tch,tch_seize_nho,vendor_id)
      select b.region_id,a.first_result,a.ne_id,a.avail_tch_nbr,
      decode(c.trx_nbr,0,0,1,2.28,2,8.25,3,14.04,4,21.04,5,27.04,34.68),
      a.tch_traffic,a.tch_traffic_h,a.tch_nbr,a.drop_call_tch,a.tch_seize_nho,b.vendor_id
      from tpc_unic_bts_ne a,tcc_ne_snap b,tcc_bts c
      where a.first_result=s_time
      and extend(s_time,year to day)=b.compress_date
      and b.compress_date=c.compress_date
      and b.ne_type=300
      and a.ne_id=b.ne_id
      and b.ne_id=c.ne_id
      and a.busy_cell=1;
      insert into idle_cell(ne_id,first_result,cell_id,avail_tch_nbr,radio_capacity,
      tch_traffic,tch_traffic_h,tch_nbr,drop_call_tch,tch_seize_nho,vendor_id)
      select b.region_id,a.first_result,a.ne_id,a.avail_tch_nbr,
      decode(c.trx_nbr,0,0,1,2.28,2,8.25,3,14.04,4,21.04,5,27.04,34.68),
      a.tch_traffic,a.tch_traffic_h,a.tch_nbr,a.drop_call_tch,a.tch_seize_nho,b.vendor_id
      from tpc_unic_bts_ne a,tcc_ne_snap b,tcc_bts c
      where a.first_result=s_time
      and extend(s_time,year to day)=b.compress_date
      and b.compress_date=c.compress_date
      and b.ne_type=300
      and a.ne_id=b.ne_id
      and b.ne_id=c.ne_id
      and a.idle_cell=1;
      insert into heavy_ovfl_cell(ne_id,first_result,cell_id,avail_tch_nbr,radio_capacity,
      tch_traffic,tch_traffic_h,tch_nbr,drop_call_tch,tch_seize_nho,vendor_id)
      select b.region_id,a.first_result,a.ne_id,a.avail_tch_nbr,
      decode(c.trx_nbr,0,0,1,2.28,2,8.25,3,14.04,4,21.04,5,27.04,34.68),
      a.tch_traffic,a.tch_traffic_h,a.tch_nbr,a.drop_call_tch,a.tch_seize_nho,b.vendor_id
      from tpc_unic_bts_ne a,tcc_ne_snap b,tcc_bts c
      where a.first_result=s_time
      and extend(s_time,year to day)=b.compress_date
      and b.compress_date=c.compress_date
      and b.ne_type=300
      and a.ne_id=b.ne_id
      and b.ne_id=c.ne_id
      and a.heavy_ovfl_cell=1;
      insert into ovfl_cell(ne_id,first_result,cell_id,avail_tch_nbr,radio_capacity,
      tch_traffic,tch_traffic_h,tch_nbr,drop_call_tch,tch_seize_nho,vendor_id)
      select b.region_id,a.first_result,a.ne_id,a.avail_tch_nbr,
      decode(c.trx_nbr,0,0,1,2.28,2,8.25,3,14.04,4,21.04,5,27.04,34.68),
      a.tch_traffic,a.tch_traffic_h,a.tch_nbr,a.drop_call_tch,a.tch_seize_nho,b.vendor_id
      from tpc_unic_bts_ne a,tcc_ne_snap b,tcc_bts c
      where a.first_result=s_time
      and extend(s_time,year to day)=b.compress_date
      and b.compress_date=c.compress_date
      and b.ne_type=300
      and a.ne_id=b.ne_id
      and b.ne_id=c.ne_id
      and a.ovfl_cell=1;
      insert into worst_cell(ne_id,first_result,cell_id,avail_tch_nbr,radio_capacity,
      tch_traffic,tch_traffic_h,tch_nbr,drop_call_tch,tch_seize_nho,vendor_id)
      select b.region_id,a.first_result,a.ne_id,a.avail_tch_nbr,
      decode(c.trx_nbr,0,0,1,2.28,2,8.25,3,14.04,4,21.04,5,27.04,34.68),
      a.tch_traffic,a.tch_traffic_h,a.tch_nbr,a.drop_call_tch,a.tch_seize_nho,b.vendor_id
      from tpc_unic_bts_ne a,tcc_ne_snap b,tcc_bts c
      where a.first_result=s_time
      and extend(s_time,year to day)=b.compress_date
      and b.compress_date=c.compress_date
      and b.ne_type=300
      and a.ne_id=b.ne_id
      and b.ne_id=c.ne_id
      and a.worst_cell=1;
end procedure;

虽然写的很简单，但是做完了才发现写一个存储过程是多么的英明，省了N多事。
另外在SPL的SQL语句中不能事实first的语法；
不能使用select a into v_a from table order by 1；
不在order by 之后into 到变量中

create procedure "npmuser".fact_busy_table(s_time datetime year to day)
      define a_traf float;
      define m_time  datetime year to second;
      
SET DEBUG FILE TO &#39;/tmp/factbusytable.log&#39;;
TRACE &#39;BEGIN 24h traff&#39;;
TRACE ON;
      delete from fact_msc where compress_date=s_time;
      delete from fact_bts where compress_date=s_time;
      delete from fact_gsma where compress_date=s_time;
      select  max(a_interface_traf) into a_traf from tpa_unic_msc_sum
      where compress_date=s_time
      and ne_type=10000
      and sum_level=0
      and sv_id=-1;
      
      select first_result into m_time from tpa_unic_msc_sum
      where compress_date=s_time
      and ne_type=10000
      and sum_level=0
      and sv_id=-1
      and a_interface_traf=a_traf;
      insert into fact_msc
      select *
      from tpa_unic_msc_sum
      where first_result=m_time
      and ne_type in (101,10003,10000);
      insert into fact_bts
      select *
      from tpa_unic_bts_sum
      where first_result=m_time
      and ne_type in (200,10003,10000);
      insert into fact_gsma
      select *
      from tpa_gsma_unic_sum
      where first_result=m_time
      and ne_type in (10003,10000);
end procedure;