/*打开日志输出*/
Set serveroutput on ;
/*创建类型*/
create or replace type type_flux_data_stat_o as object
(
ifinoctetsbps number ,
ifoutoctetsbps number ,
collecttime number
);
/*创建类型归属为表类型*/
create or replace type type_flux_data_stat as table of type_flux_data_stat_o;
/*pipelined创建函数 返回表类型*/
create or replace FUNCTION f_linkgroupstat(begin_time IN NUMBER,
end_time IN NUMBER,
lg_id in varchar2,
table_name varchar2 )
return type_flux_data_stat
pipelined as
/*游标申明*/
v_Cur SYS_REFCURSOR ;
/*sql临时变量*/
v_SQLStatement string (10000 );
/*表类型*/
v_Table type_flux_data_stat_o;
/*流入字节数临时变量*/
tmp_ifinoctetsbps NUMBER ;
/*流出字节数临时变量*/
tmp_ifoutoctetsbps NUMBER ;
/*流入字节数汇总*/
total_ifinoctetsbps NUMBER ;
/*流出字节数汇总*/
total_ifoutoctetsbps NUMBER ;
/*起始时间窗格*/
tmp_begin_time NUMBER ;
/*结束时间窗格*/
tmp_end_time NUMBER ;
begin
/*时间窗格偏移量为5分钟(300秒)*/
tmp_begin_time := begin_time;
tmp_end_time := begin_time + 300 ;
total_ifinoctetsbps := 0 ;
total_ifoutoctetsbps := 0 ;
loop
exit when tmp_begin_time > end_time;
v_SQLStatement := 'select sum(ifinoctetsbps) ifinoctetsbps,sum(ifoutoctetsbps) ifoutoctetsbps from ' ||
table_name ||
' a where exists (select 1 from tm_linkgroup_cportdirection b where a.getway = b.getway and a.port_info=b.ifindex_info and lg_id in (' ||
lg_id ||
') and a.device_id = b.device_id ) and a.collecttime >=' ||
tmp_begin_time || ' and a.collecttime <=' ||
tmp_end_time || ' order by collecttime' ;
Dbms_Output.put_line(v_SQLStatement);
/*针对字符串sql打开游标*/
open v_Cur for v_SQLStatement;
tmp_begin_time := tmp_begin_time + 300 ;
tmp_end_time := tmp_end_time + 300 ;
total_ifinoctetsbps := 0 ;
total_ifoutoctetsbps := 0 ;
loop
/*将游标的值放入零食变量中*/
fetch v_Cur
into tmp_ifinoctetsbps, tmp_ifoutoctetsbps;
/*当游标中不存在值时跳出游标*/
EXIT WHEN v_Cur% NOTFOUND;
total_ifinoctetsbps := total_ifinoctetsbps + tmp_ifinoctetsbps;
total_ifoutoctetsbps := total_ifoutoctetsbps + tmp_ifoutoctetsbps;
end loop ;
/*单行记录初始化*/
v_Table := type_flux_data_stat_o(total_ifinoctetsbps,
total_ifoutoctetsbps,
tmp_begin_time);
/*将记录压入至结果集中*/
pipe row (v_Table);
/*关闭游标*/
close v_Cur;
end loop ;
Exception
when others then
Dbms_Output.put_line( Sqlerrm );
end f_linkgroupstat;