create or replace procedure PROC_ALEX_BSC_AI
as
vDataTime1 date;
vDataTime2 date;
vAvg number(16,4);
vCounts int;
vStat number(16,4);
vSQL varchar2(1024);
begin
select max(TIME_SEG) into vDataTime1 from dgdcdw.ORDER_STDSP_DETY_R2;
select max(START_TIME) into vDataTime2 from dgdcdw.APP_BSC_PFM_CUR where ATTRIBUTE_NAME like '%A接口%';
if (vDataTime1 > vDataTime2) --新数据
then
select sysdate into vDataTime1 from dual;
select max(TIME_SEG) into vDataTime2 from dgdcdw.ORDER_RLTDP_MSC;
if (vDataTime2 is not null) -- 配置数据指令采集到数据
then
vSQL := 'truncate table dgdcdw.GTEMP_BSC_ALEX_AI01';
execute immediate vSQL;
insert into dgdcdw.GTEMP_BSC_ALEX_AI01
(START_TIME,NE_CODE)
select
max(TIME_SEG),NE_CODE
from
dgdcdw.ORDER_RLTDP_MSC
group by NE_CODE;
vSQL := 'truncate table dgdcdw.GTEMP_BSC_ALEX_AI02';
execute immediate vSQL;
insert into dgdcdw.GTEMP_BSC_ALEX_AI02
(START_TIME, NE_CODE, TOTAL, COUNTS)
select
a.TIME_SEG, a.NE_CODE, sum(a.RELCAP), count(*)
from
dgdcdw.ORDER_RLTDP_MSC a,dgdcdw.GTEMP_BSC_ALEX_AI01 b
where a.MODES='ACTIVE' and a.NE_CODE=b.NE_CODE and a.TIME_SEG=b.START_TIME
group by a.TIME_SEG, a.NE_CODE;
vSQL := 'truncate table dgdcdw.GTEMP_BSC_ALEX_AI03';
execute immediate vSQL;
insert into dgdcdw.GTEMP_BSC_ALEX_AI03
(NE_CODE, MSC, AI_RATE)
select
a.NE_CODE, a.MSC, 100*a.RELCAP/b.TOTAL
from
dgdcdw.ORDER_RLTDP_MSC a, dgdcdw.GTEMP_BSC_ALEX_AI02 b
where
a.TIME_SEG = b.START_TIME and a.NE_CODE = b.NE_CODE and a.MODES='ACTIVE';
end if; --配置数据指令没有采集到数据,不清空临时表3,则使用临时表中原有的配置数据
--以下是实时占用比例的计算
vSQL := 'truncate table dgdcdw.GTEMP_BSC_ALEX_AI01';
execute immediate vSQL;
insert into dgdcdw.GTEMP_BSC_ALEX_AI01
(START_TIME,NE_CODE)
select
max(TIME_SEG),NE_CODE
from
dgdcdw.ORDER_STDSP_DETY_R2
group by NE_CODE;
vSQL := 'truncate table dgdcdw.GTEMP_BSC_ALEX_AI02';
execute immediate vSQL;
insert into dgdcdw.GTEMP_BSC_ALEX_AI02
(START_TIME, NE_CODE, TOTAL, COUNTS)
select
a.TIME_SEG, a.NE_CODE, sum(a.NOCC), count(*)
from
dgdcdw.ORDER_STDSP_DETY_R2 a,dgdcdw.GTEMP_BSC_ALEX_AI01 b
where a.BSC='OWN' and a.NE_CODE=b.NE_CODE and a.TIME_SEG=b.START_TIME
group by a.TIME_SEG, a.NE_CODE;
vSQL := 'truncate table dgdcdw.GTEMP_BSC_ALEX_AI04';
execute immediate vSQL;
insert into dgdcdw.GTEMP_BSC_ALEX_AI04
(NE_CODE, MSC, AI_RATE)
select
a.NE_CODE, a.MSC, 100*a.NOCC/b.TOTAL
from
dgdcdw.ORDER_STDSP_DETY_R2 a, dgdcdw.GTEMP_BSC_ALEX_AI02 b
where
a.TIME_SEG = b.START_TIME and a.NE_CODE = b.NE_CODE;
--将当前的数据导入临时表，用做比较
--这是为了计算趋势那个值TREND
vSQL := 'delete from dgdcdw.GTEMP_BSC_PFM_CUR where ATTRIBUTE_NAME like ''%A接口%''';
execute immediate vSQL;
insert into dgdcdw.GTEMP_BSC_PFM_CUR
(START_TIME, STOP_TIME, NE_ID, DEVICENAME, ATTRIBUTE_TYPE, ATTRIBUTE_NAME, ATTRIBUTE_VALUE,NOTE,ALARM_FLAG,TREND)
select START_TIME, STOP_TIME, NE_ID, DEVICENAME, ATTRIBUTE_TYPE, ATTRIBUTE_NAME, ATTRIBUTE_VALUE ,NOTE,ALARM_FLAG,TREND
from dgdcdw.APP_BSC_PFM_CUR where ATTRIBUTE_NAME like '%A接口%' and NE_ID in (select NE_ID from dgdcdw.CONFIG_BSC where SWITCH=1);
--删MSS性能当前表中爱立信网元数据
vSQL := 'delete from dgdcdw.APP_BSC_PFM_CUR where ATTRIBUTE_NAME like ''%A接口%'' and NE_ID in (select NE_ID from dgdcdw.CONFIG_BSC where SWITCH=1)';
execute immediate vSQL;
insert into dgdcdw.APP_BSC_PFM_CUR
(START_TIME, STOP_TIME, NE_ID, DEVICENAME, ATTRIBUTE_TYPE, ATTRIBUTE_NAME, ATTRIBUTE_VALUE,NOTE,ALARM_FLAG,TREND)
select
vDataTime1, null, b.NE_ID, b.DEVICENAME, 1, 'A接口电路占用比例(%)', a.AI_RATE, a.MSC, null,null
from
dgdcdw.GTEMP_BSC_ALEX_AI04 a, dgdcdw.CONFIG_BSC b
where
a.NE_CODE = b.DEVICECODE;
/* for vPfm in (select NE_ID,ATTRIBUTE_NAME,NOTE from dgdcdw.APP_BSC_PFM_CUR where ATTRIBUTE_NAME='A接口电路占用比例(%)')
loop
update dgdcdw.APP_BSC_PFM_CUR a set a.TREND = (select case when a.ATTRIBUTE_VALUEb.ATTRIBUTE_VALUE then 1 else 0 end from dgdcdw.GTEMP_BSC_PFM_CUR b where b.NE_ID=vPfm.NE_ID and b.ATTRIBUTE_NAME='A接口电路占用比例(%)' and b.NOTE=vPfm.NOTE)
where a.NE_ID=vPfm.NE_ID and a.ATTRIBUTE_NAME='A接口电路占用比例(%)' and a.NOTE=vPfm.NOTE;
end loop;
*/
insert into dgdcdw.APP_BSC_PFM_HIS
(START_TIME, STOP_TIME, NE_ID, DEVICENAME, ATTRIBUTE_TYPE, ATTRIBUTE_NAME, ATTRIBUTE_VALUE,note,alarm_flag,trend)
select
START_TIME, STOP_TIME, NE_ID, DEVICENAME, ATTRIBUTE_TYPE, ATTRIBUTE_NAME, ATTRIBUTE_VALUE,note,alarm_flag,trend
from
dgdcdw.APP_BSC_PFM_CUR
where
ATTRIBUTE_NAME='A接口电路占用比例(%)';
--下面来计算均衡性
for vBSC in (select NE_ID,DEVICENAME,DEVICECODE from dgdcdw.CONFIG_BSC where switch=1)
loop
select avg(rate) into vAvg
from
(select a.NE_CODE,case when b.AI_RATE is null then 0 else 100*a.AI_RATE/b.AI_RATE end as rate
from dgdcdw.GTEMP_BSC_ALEX_AI04 a, dgdcdw.GTEMP_BSC_ALEX_AI03 b
where a.NE_CODE=vBSC.DEVICECODE and a.NE_CODE=b.NE_CODE and replace(a.MSC,chr(13),'')=replace(b.MSC,chr(13),'')
);
select count(*) into vCounts from dgdcdw.GTEMP_BSC_ALEX_AI04 a, dgdcdw.GTEMP_BSC_ALEX_AI03 b
where a.NE_CODE=vBSC.DEVICECODE and a.NE_CODE=b.NE_CODE and replace(a.MSC,chr(13),'')=replace(b.MSC,chr(13),'');
if (vCounts>0)
then
select sqrt(sum((RATE-vAvg)*(RATE-vAvg))/vCounts) into vStat
from
(select a.NE_CODE,case when b.AI_RATE is null then 0 else 100*a.AI_RATE/b.AI_RATE end as RATE
from dgdcdw.GTEMP_BSC_ALEX_AI04 a, dgdcdw.GTEMP_BSC_ALEX_AI03 b
where a.NE_CODE=vBSC.DEVICECODE and a.NE_CODE=b.NE_CODE and replace(a.MSC,chr(13),'')=replace(b.MSC,chr(13),'')
);
/*
select sqrt(sum(((case when b.AI_RATE is null then 0 else 100*a.AI_RATE/b.AI_RATE end)-vAvg)*((case when b.AI_RATE is null then 0 else 100*a.AI_RATE/b.AI_RATE end)-vAvg))/vCounts) into vStat
from
dgdcdw.GTEMP_BSC_ALEX_AI04 a, dgdcdw.GTEMP_BSC_ALEX_AI03 b
where a.NE_CODE=vBSC.DEVICECODE and a.NE_CODE=b.NE_CODE and replace(a.MSC,chr(13),'')=replace(b.MSC,chr(13),'');
*/
insert into dgdcdw.APP_BSC_PFM_CUR
(START_TIME, STOP_TIME, NE_ID, DEVICENAME, ATTRIBUTE_TYPE, ATTRIBUTE_NAME, ATTRIBUTE_VALUE,NOTE,ALARM_FLAG,TREND)
select
vDataTime1, null, vBSC.NE_ID, vBSC.DEVICENAME, 1, 'A接口电路占用均衡性(%)', vStat,NULL,NULL,NULL
from dual;
/* for vPfm in (select distinct NE_ID from dgdcdw.APP_BSC_PFM_CUR where ATTRIBUTE_NAME='A接口电路占用均衡性(%)')
loop
update dgdcdw.APP_BSC_PFM_CUR a set a.TREND = (select case when a.ATTRIBUTE_VALUEb.ATTRIBUTE_VALUE then 1 else 0 end from dgdcdw.GTEMP_BSC_PFM_CUR b where b.NE_ID=vPfm.NE_ID and b.ATTRIBUTE_NAME='A接口电路占用均衡性(%)')
where a.NE_ID=vPfm.NE_ID and a.ATTRIBUTE_NAME='A接口电路占用均衡性(%)';
end loop;*/
insert into dgdcdw.APP_BSC_PFM_HIS
(START_TIME, STOP_TIME, NE_ID, DEVICENAME, ATTRIBUTE_TYPE, ATTRIBUTE_NAME, ATTRIBUTE_VALUE,NOTE,ALARM_FLAG,TREND)
select
START_TIME, STOP_TIME, NE_ID, DEVICENAME, ATTRIBUTE_TYPE, ATTRIBUTE_NAME, ATTRIBUTE_VALUE,NOTE,ALARM_FLAG,TREND
from dgdcdw.APP_BSC_PFM_CUR
where NE_ID=vBSC.NE_ID and ATTRIBUTE_NAME='A接口电路占用均衡性(%)';
end if;
end loop;--在多个BSC之间循环
end if; --假如原始表有数据
commit;
--删除历史数据
--清空临时表
vSQL := 'truncate table dgdcdw.ORDER_STDSP_DETY_R2';
execute immediate vSQL;
vSQL := 'truncate table dgdcdw.ORDER_RLTDP_MSC';
execute immediate vSQL;
commit;
end;
