创建包声明的过程脚本:
CREATE OR REPLACE PACKAGE "COM_BOS" is
-----------------------------------------------------------------------
-- 描述:
-- 构造b类型缓存的sql语句
-- 参数:
-- @i_bostype:业务类型
-- @i_bostime:时间类型
-- @i_timecond:时间条件
-- 返回值:
-- 返回进行缓存操作的SQL语句
-----------------------------------------------------------------------
function fn_compose_b_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2;
-----------------------------------------------------------------------
-- 描述:
-- 构造e类型缓存的sql语句
-- 参数:
-- @i_bostype:业务类型
-- @i_bostime:时间类型
-- @i_timecond:时间条件
-- 返回值:
-- 返回进行缓存操作的SQL语句
-----------------------------------------------------------------------
function fn_compose_e_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2;
-----------------------------------------------------------------------
-- 描述:
-- 构造d类型缓存的sql语句
-- 参数:
-- @i_bostype:业务类型
-- @i_bostime:时间类型
-- @i_timecond:时间条件
-- 返回值:
-- 返回进行缓存操作的SQL语句
-----------------------------------------------------------------------
function fn_compose_d_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2;
-----------------------------------------------------------------------
-- 描述:
-- 构造f类型缓存的sql语句
-- 参数:
-- @i_bostype:业务类型
-- @i_bostime:时间类型
-- @i_timecond:时间条件
-- 返回值:
-- 返回进行缓存操作的SQL语句
-----------------------------------------------------------------------
function fn_compose_f_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2;
-----------------------------------------------------------------------
-- 描述:
-- 缓存调用的入口
-- 参数:
-- @i_type:缓存类型
-- @i_subtype:缓存子类型
-- @i_starttime:数据开始时间
-- 返回值:
-- void
-----------------------------------------------------------------------
function fnsummarysql1(in_kpitype number, in_timetype number, in_time number, in_cunt0 number,
in_traders number,
in_paths number,
in_limit1 number,
in_limit2 number ) return varchar2;
function fnsummarysql2(in_kpitype number, in_time number ) return varchar2;
-----------------------------------------------------------------------
-- 描述:
-- 获取时延统计概要数据
-- 参数:
-- @out_result:输出数据
-- 返回值:
-- void
-----------------------------------------------------------------------
procedure getlatencysummary(
out_result out sys_refcursor
);
-----------------------------------------------------------------------
-- 描述:
-- 获取时延统计概要数据
-- 参数:
-- @out_result:输出数据
-- 返回值:
-- void
-----------------------------------------------------------------------
procedure dosummarycachenow(
in_s number
);
-----------------------------------------------------------------------
-- 描述:
-- 获取时延随时间分布数据
-- 参数:
-- @out_result:输出数据
-- @in_bostype:业务类型
-- @in_bostime:时间类型
-- @in_latencytype:延迟类型
-- @in_delta:统计粒度
-- @in_from:统计开始时间
-- @in_to:统计结束时间
-- 返回值:
-- void
-----------------------------------------------------------------------
procedure getlatency(
out_result out sys_refcursor,
in_bostype number,
in_bostime number,
in_latencytype number,
in_delta number,
in_from number,
in_to number
);
end com_bos;
创建包体的过程:
CREATE OR REPLACE PACKAGE BODY "COM_BOS" is
-----------------------------------------------------------------------
-- 描述:
-- 构造b类型缓存的sql语句
-- 参数:
-- @i_bostype:业务类型
-- @i_bostime:时间类型
-- @i_timecond:时间条件
-- 返回值:
-- 返回进行缓存操作的SQL语句
-----------------------------------------------------------------------
function fn_compose_b_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2
as
v_table varchar2(32);
begin
v_table := 'cache_b'||i_bostype||'t'||i_bostime;
return 'select * from (select * from '||v_table||' union all
select max(time),sum(cunt),sum(cunt0),sum(cunt1),sum(cunt2),sum(cunt3),sum(totallatency),max(maxlatency),min(minlatency) from '||v_table||'m
)' || i_timecond;
end;
-----------------------------------------------------------------------
-- 描述:
-- 构造e类型缓存的sql语句
-- 参数:
-- @i_bostype:业务类型
-- @i_bostime:时间类型
-- @i_timecond:时间条件
-- 返回值:
-- 返回进行缓存操作的SQL语句
-----------------------------------------------------------------------
function fn_compose_e_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2
as
v_table varchar2(32);
begin
v_table := 'cache_e'||i_bostype||'t'||i_bostime;
return ' select * from (select * from '||v_table||' union all
select max(time),point,sum(pcunt),sum(ps0cunt),sum(ps1cunt),sum(ps2cunt),sum(perrcunt) from '||v_table||'m group by point
)' || i_timecond;
end;
-----------------------------------------------------------------------
-- 描述:
-- 构造d类型缓存的sql语句
-- 参数:
-- @i_bostype:业务类型
-- @i_bostime:时间类型
-- @i_timecond:时间条件
-- 返回值:
-- 返回进行缓存操作的SQL语句
-----------------------------------------------------------------------
function fn_compose_d_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2
as
v_table varchar2(32);
begin
v_table := 'cache_d'||i_bostype||'t'||i_bostime;
return 'select * from (select * from '||v_table||' union all
select max(time), point1, point2,sum(cunt),sum(cunt1),sum(cunt2),sum(cunt3),sum(totallatency),max(maxlatency),min(minlatency) from '||v_table||'m group by point1, point2
)' || i_timecond;
end;
-----------------------------------------------------------------------
-- 描述:
-- 构造f类型缓存的sql语句
-- 参数:
-- @i_bostype:业务类型
-- @i_bostime:时间类型
-- @i_timecond:时间条件
-- 返回值:
-- 返回进行缓存操作的SQL语句
-----------------------------------------------------------------------
function fn_compose_f_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2
as
v_table varchar2(32);
begin
v_table := 'cache_f'||i_bostype||'t'||i_bostime;
return 'select * from (select * from '||v_table||' union all
select max(time),sum(totallatency),max(maxlatency),min(minlatency),sum(pcunt) from '||v_table||'
) '|| i_timecond;
end;
-----------------------------------------------------------------------
-- 描述:
-- 缓存调用的入口
-- 参数:
-- @i_type:缓存类型
-- @i_subtype:缓存子类型
-- @i_starttime:数据开始时间
-- 返回值:
-- void
-----------------------------------------------------------------------
function fnsummarysql1(
in_kpitype number,
in_timetype number,
in_time number,
in_cunt0 number,
in_traders number,
in_paths number,
in_limit1 number,
in_limit2 number
) return varchar2
as
v_timetype number;
v_from number;
begin
v_timetype := 0 ;
if in_timetype = 0 then --当前一分钟
v_from := in_time-59;
elsif in_timetype = 1 then --当前5分钟
v_from := in_time-299;
elsif in_timetype = 2 then --当前1小时
v_timetype := 1 ;
v_from := in_time-3599;
end if;
return 'insert into TMP_CACHE_SUMMARY1 select '||in_kpitype||' as s, '||in_timetype||' as t, nvl('||in_cunt0||',0) as cunt0, nvl(a.cunt1,0) as cunt1,
nvl(a.cunt2,0) as cunt2,nvl(a.cunt3,0) as cunt3,nvl(a.cunt,0) as cunt,'||in_traders||' as traders, '||in_paths||' as paths,
nvl(a.minlatency, 0) as minlatency, nvl(a.avglatency,0) as avglatency, nvl(a.maxlatency,0) as maxlatency,
'||in_limit1||' as limit1, '||in_limit2||' as limit2 from ( select sum(cunt1) as cunt1,sum(cunt2) as cunt2, sum(cunt3) as cunt3,sum(cunt) as cunt,
min(minlatency)/1000 as minlatency, sum(totallatency)/decode(sum(cunt),0,1,sum(cunt))/1000 as avglatency, max(maxlatency)/1000 as maxlatency
from cache_a'||in_kpitype||'t'||v_timetype||' where time between '||v_from||' and '||in_time||') a';
end;
function fnsummarysql2(
in_kpitype number,
in_time number
) return varchar2
as
v_wday number;
begin
v_wday := com.fngetweekday(in_time);
return ' select '||in_kpitype||' as s, 3 as t, nvl(b.cunt0,0) as cunt0, nvl(a.cunt1,0) as cunt1,
nvl(a.cunt2,0) as cunt2,nvl(a.cunt3,0) as cunt3,nvl(a.cunt,0) as cunt,c.traders as traders, d.paths as paths,
nvl(a.minlatency, 0) as minlatency, nvl(a.avglatency,0) as avglatency, nvl(a.maxlatency,0) as maxlatency,
e.limit1 as limit1, e.limit2 as limit2 from ( select sum(cunt1) as cunt1,sum(cunt2) as cunt2, sum(cunt3) as cunt3,sum(cunt) as cunt,
min(minlatency)/1000 as minlatency, sum(totallatency)/greatest(sum(cunt),1)/1000 as avglatency, max(maxlatency)/1000 as maxlatency
from cache_a'||in_kpitype||'t1) a, ( select sum(errordercount1sec) as cunt0 from fact_t0d'||v_wday||') b,
( select count(traderno) as traders from ( select distinct traderno from cache_a'||in_kpitype||'t1)) c,
( select count(point1) as paths from (select distinct point1 from cache_c'||in_kpitype||'t1)) d,
( select limit1/1000 as limit1,limit2/1000 as limit2 from cfg_latencylimit where point1=0 and point2=0 and kpitype ='||in_kpitype||') e';
end;
-----------------------------------------------------------------------
-- 描述:
-- 进行某种业务的延迟概要缓存
-- 参数:
-- @in_s:业务类型
-- 返回值:
-- void
-----------------------------------------------------------------------
procedure dosummarycachenow(
in_s number
)
as
v_time number;
v_limit1 number;
v_limit2 number;
v_wday number;
begin
select wday into v_wday from cache_time;--获取周期时间
---获取当前时间
execute immediate 'select nvl(max(time-1), 0) from fact_i0d'||v_wday||' ' into v_time;
execute immediate 'select limit1/1000 as limit1,limit2/1000 as limit2 from cfg_latencylimit where point1 = 0 and point2 = 0 and kpitype ='|| in_s into v_limit1,v_limit2;
--select outpoint into v_pointout from cfg_iopoint where id = 1 ;
execute immediate 'insert into CACHE_SUMMARYH select '||in_s||' as s, 0 as t, nvl(b.cunt0,0) as cunt0, nvl(a.cunt1,0) as cunt1,
nvl(a.cunt2,0) as cunt2,nvl(a.cunt3,0) as cunt3,nvl(a.cunt,0) as cunt,c.traders as traders, d.paths as paths,
nvl(a.minlatency, 0) as minlatency, nvl(a.totallatency,0) as avglatency, nvl(a.maxlatency,0) as maxlatency,'||v_limit1||' as limit1, '||v_limit2||' as limit2
from (select sum(cunt1) as cunt1,sum(cunt2) as cunt2, sum(cunt3) as cunt3,sum(cunt) as cunt,min(minlatency)/1000 as minlatency, sum(totallatency)/greatest(sum(cunt),1)/1000 as totallatency, max(maxlatency)/1000 as maxlatency
from cache_a'||in_s||'t0 where time between '||(v_time-59)||' and '||v_time||') a,
(select sum(errordercount1sec) as cunt0 from fact_t0d'||v_wday||' where time between '||(v_time-59)||' and '||v_time||' and point=140 ) b,
(select count(distinct traderno) as traders from cache_a'||in_s||'t0 where time between '||(v_time-59)||' and '||v_time||' and traderno <>0 ) c,
(select count(distinct point1) as paths from cache_c'||in_s||'t0 where time between '||(v_time-59)||' and '||v_time||' and point1>0 ) d';
execute immediate 'insert into CACHE_SUMMARYH select '||in_s||' as s, 1 as t, nvl(b.cunt0,0) as cunt0, nvl(a.cunt1,0) as cunt1,
nvl(a.cunt2,0) as cunt2,nvl(a.cunt3,0) as cunt3,nvl(a.cunt,0) as cunt,c.traders as traders, d.paths as paths,
nvl(a.minlatency, 0) as minlatency, nvl(a.totallatency,0) as avglatency, nvl(a.maxlatency,0) as maxlatency,
'||v_limit1||' as limit1, '||v_limit2||' as limit2 from (select sum(cunt1) as cunt1,sum(cunt2) as cunt2, sum(cunt3) as cunt3,sum(cunt) as cunt,
min(minlatency)/1000 as minlatency, sum(totallatency)/greatest(sum(cunt),1)/1000 as totallatency, max(maxlatency)/1000 as maxlatency
from cache_a'||in_s||'t0 where time between '||(v_time-299)||' and '||v_time||' ) a,
(select sum(errordercount1sec) as cunt0 from fact_t0d'||v_wday||' where time between '||(v_time-299)||' and '||v_time||' and point=140) b,
(select count(distinct traderno) as traders from cache_a'||in_s||'t0 where time between '||(v_time-299)||' and '||v_time||' and traderno<>0) c,
(select count(distinct point1) as paths from cache_c'||in_s||'t0 where time between '||(v_time-299)||' and '||v_time||' and point1>0) d';
execute immediate 'insert into CACHE_SUMMARYH select '||in_s||' as s, 2 as t, nvl(b.cunt0,0) as cunt0, nvl(a.cunt1,0) as cunt1,
nvl(a.cunt2,0) as cunt2,nvl(a.cunt3,0) as cunt3,nvl(a.cunt,0) as cunt,c.traders as traders, d.paths as paths,
nvl(a.minlatency, 0) as minlatency, nvl(a.totallatency,0) as avglatency, nvl(a.maxlatency,0) as maxlatency,
'||v_limit1||' as limit1, '||v_limit2||' as limit2 from ( select sum(cunt1) as cunt1,sum(cunt2) as cunt2, sum(cunt3) as cunt3,sum(cunt) as cunt,
min(minlatency)/1000 as minlatency, sum(totallatency)/greatest(sum(cunt),1)/1000 as totallatency, max(maxlatency)/1000 as maxlatency
from cache_a'||in_s||'t0 where time between '||(v_time-3599)||' and '||v_time||' ) a,
(select sum(errordercount1sec) as cunt0 from fact_t0d'||v_wday ||' where point=140 ) b,
(select count(distinct traderno) as traders from cache_a'||in_s||'t0 where time between '||(v_time-3599)||' and '||v_time||' and traderno<>0) c,
(select count(distinct point1) as paths from cache_c'||in_s||'t0 where time between '||(v_time-3599)||' and '||v_time||' and point1>0) d';
commit;
-- dbms_output.put_line(v_sql);
end;
-----------------------------------------------------------------------
-- 描述:
-- 获取时延统计概要数据
-- 参数:
-- @out_result:输出数据
-- 返回值:
-- void
-----------------------------------------------------------------------
procedure getlatencysummary(
out_result out sys_refcursor
)
as
begin
--清除缓存表
execute immediate 'truncate table CACHE_SUMMARYH';
execute immediate 'call com_bos.dosummarycachenow(0)'; --汇总一分钟的时延分布数据
execute immediate 'call com_bos.dosummarycachenow(1)'; --汇总五分钟的时延分布统计
execute immediate 'call com_bos.dosummarycachenow(2)'; --汇总一小时的时延分布统计
open out_result for 'select s as "s",t as "t",cunt0 as "cunt0",cunt1 as "cunt1",cunt2 as "cunt2",cunt3 as "cunt3",cunt as "cunt",
traders as "traders",paths as "paths",minlatency as "minlatency",avglatency as "avglatency",maxlatency as "maxlatency",
limit1 as "limit1",limit2 as "limit2" from (select * from CACHE_SUMMARYH union all select * from CACHE_SUMMARY)';
/*
execute immediate 'truncate table TMP_CACHE_SUMMARY1';
execute immediate 'call com_bos.dosummarycachenow(0)';
execute immediate 'call com_bos.dosummarycachenow(1)';
execute immediate 'call com_bos.dosummarycachenow(2)';
open out_result for 'select * from TMP_CACHE_SUMMARY1 union all select * from TMP_CACHE_SUMMARY ';
*/
EXCEPTION
when OTHERS then
dbms_output.put_line(sqlerrm);
end;
-----------------------------------------------------------------------
-- 描述:
-- 获取时延随时间分布数据
-- 参数:
-- @out_result:输出数据
-- @in_bostype:业务类型
-- @in_bostime:时间类型
-- @in_latencytype:延迟类型
-- @in_delta:统计粒度
-- @in_from:统计开始时间
-- @in_to:统计结束时间
-- 返回值:
-- void
-----------------------------------------------------------------------
procedure getlatency(
out_result out sys_refcursor,
in_bostype number,
in_bostime number,
in_latencytype number,
in_delta number,
in_from number,
in_to number
)
as
v_sql varchar2(2000); -- 拼凑执行的sql
v_timeregular varchar2(150);
v_timecond varchar2(128); --时间条件
v_select varchar(256); --查询的字段
v_timeregular1 varchar2(150);--转换的时间格式
v_currenttime number;--当前时间
v_fromtime number;--开始时间
v_wday number;--星期数
v_facttable varchar2(128);--事实表
v_to number;--结束时间
--v_bostime number;
begin
if in_bostime > 2 then --当天昨天历史
if in_delta = 1 then -- 1秒粒度 时间范围1分钟
v_timeregular := com.fngetsecondtostrsql('time', 'hh24:mi:ss');
v_timeregular1 := 'time';
v_to := in_to ;
elsif in_delta = 60 then --1分钟粒度 时间范围30分钟
v_timeregular := com.fngetsecondtostrsql('time', 'hh24:mi');
v_timeregular1 := com.fngetsecondtostrsql('time', 'hh24:mi');
v_to := in_to ;
elsif in_delta = 300 then --5分钟粒度 时间范围一天
v_timeregular := com.fngetsecondtostrsql('min(time)', 'hh24:mi');
v_timeregular1 := 'trunc(time/300)';
v_to := in_to ;
elsif in_delta = 3600 then
v_timeregular := com.fngetsecondtostrsql('time', 'hh24');
v_timeregular1 := com.fngetsecondtostrsql('time', 'hh24');
v_to := in_to + 1;
elsif in_delta = 86400 then
v_timeregular := com.fngetsecondtostrsql('time', 'mm-dd');
v_timeregular1 :=com.fngetsecondtostrsql('time', 'mm-dd');
v_to := in_to + 1;
end if;
-- 星期数
v_wday := com.fngetweekday(in_from);
--要查询的表
v_facttable := 'fact_s'||in_bostype||'d'||v_wday;
if in_latencytype = 0 then --最小时延
v_select := 'nvl(min(globallatency),0)/1000 as "latency", nvl(count(id), 0) as "cunt"';
elsif in_latencytype = 1 then --平均时延
v_select := 'nvl(sum(globallatency)/greatest(count(id),1),0)/1000 as "latency", nvl(count(id), 0) as "cunt"';
else --最大时延
v_select := 'nvl(max(globallatency),0)/1000 as "latency", nvl(count(id), 0) as "cunt"';
end if;
v_timecond := ' where time between '||in_from||' and '||v_to||'';
else -- 最近1小时 5分钟 1分钟
select wday into v_wday from cache_time;
execute immediate 'select nvl(max(time-1), 0) from fact_i0d'||v_wday||' ' into v_currenttime;
if in_bostime = 0 then --1分钟
v_timeregular1 := 'time';
v_fromtime := v_currenttime - 59;
v_timeregular := com.fngetsecondtostrsql('time', 'hh24:mi:ss');
elsif in_bostime = 1 then --5分钟
v_timeregular1 := 'trunc(time/5)';
v_fromtime := v_currenttime - 299;
v_timeregular := com.fngetsecondtostrsql('min(time)', 'hh24:mi:ss');
elsif in_bostime = 2 then --1小时
v_timeregular1 := com.fngetsecondtostrsql('time', 'hh24:mi');
v_fromtime := v_currenttime - 3599;
v_timeregular := com.fngetsecondtostrsql('time', 'hh24:mi');
end if;
v_facttable := 'cache_a'||in_bostype||'t0';
if in_latencytype = 0 then --最小时延
v_select := 'nvl(min(minlatency),0)/1000 as "latency", nvl(sum(cunt),0) as "cunt"';
elsif in_latencytype = 1 then --平均时延
v_select := 'nvl(sum(totallatency)/greatest(sum(cunt),1),0)/1000 as "latency", nvl(sum(cunt), 0) as "cunt"';
else --最大时延
v_select := 'nvl(max(maxlatency),0)/1000 as "latency", nvl(sum(cunt), 0) as "cunt"';
end if;
v_timecond := ' where time between ' || v_fromtime || ' and ' || v_currenttime;
end if;
v_sql := 'select '||v_timeregular||' as "time", '||v_select||' from '||v_facttable||' '||v_timecond||' group by '||v_timeregular1||' order by 1';
-- insert into debug_log values(v_sql);commit;
open out_result for v_sql;
EXCEPTION
when OTHERS then
dbms_output.put_line(sqlerrm);
end;
end com_bos;