Informix存储过程示例-服务器专区

Informix存储过程示例

作者：网络　佚名编辑： IT168 2006-02-18 00:00

　　【IT168 服务器学院】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 ''/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;

虽然写的很简单，但是做完了才发现写一个存储过程是多么的英明，省了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;

关注我们