[转载]Informix存储过程示例(页 1) - 常用数据库类{ Database Security } - 邪恶八进制信息安全团队技术讨论组努力为祖国的信息安全撑起一片蓝天

ring04h 2006-3-14 12:59

[转载]Informix存储过程示例

信息来源:邪恶八进制信息安全团队
<DIV>
<DIV>create procedure "npmuser".chk_grade(s_time datetime year to second)</DIV>
<DIV> 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;</DIV>
<DIV> SET DEBUG FILE TO '/tmp/chk_grade.log'; TRACE 'BEGIN 24h traff'; TRACE ON;</DIV>
<DIV> delete from chk_grade where first_result=s_time;</DIV>
<DIV> 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;</DIV>
<DIV> 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; -------------------------------------</DIV>
<DIV> end foreach; end procedure;</DIV></DIV>

ring04h 2006-3-14 12:59

create procedure "npmuser".super_busy_cell(s_time datetime year to second)
SET DEBUG FILE TO '/tmp/superbusycell.log';
TRACE 'BEGIN 24h traff';
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;

ring04h 2006-3-14 12:59

虽然写的很简单，但是做完了才发现写一个存储过程是多么的英明，省了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 '/tmp/factbusytable.log';
TRACE 'BEGIN 24h traff';
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;

页: [1]

邪恶八进制信息安全团队技术讨论组's Archiver

[转载]Informix存储过程示例