oracle 返回结果集条数,oracle pipelined返回值函数 针对数据汇总统计 返回结果集方法...

/*打开日志输出*/

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;