利用PL／SQL的nested table和bulk collect into方法提高性能

Oracle9i中对于PL/SQL增加了bulk collect方法,可将检索的记录集成批插入oracle的内存表,用以提高select into, fetch into or returning into子句的性能.

示例:

CREATE OR REPLACE TYPE performance_obj AS OBJECT
(
 netlatency number(10)
);

 

CREATE OR REPLACE TYPE performance_tab IS TABLE OF performance_obj;

CREATE OR REPLACE PACKAGE Pkg_Test
AS
 TYPE performance_cur IS  REF CURSOR;
 PROCEDURE SpGetPerfChartInfo(p_confname in varchar2,p_ret OUT performance_cur);
END Pkg_Test;
/

CREATE OR REPLACE PACKAGE BODY Pkg_Test AS
 PROCEDURE SpGetPerfChartInfo(p_confname in varchar2,p_ret OUT performance_cur)
 IS
  v1 NUMBER:=0;
  v2 NUMBER:=0;
  v3 NUMBER:=0;
  v_tab performance_tab:=performance_tab();
 
 begin
  select PERFORMANCE_OBJ(c.netlatency) BULK COLLECT INTO v_tab
  from WMATTENDEEINFO a,WBXSITE b,WMCLIENTPERFORMANCEDATA_TMP c where LOWER(a.confname) like '%'||p_confname||'%'
  and (a.jointime>sysdate-1/3 and a.leavetime is null)  and LOWER(a.sitename) like '%software%'
  and a.siteid=b.siteid and a.confid=c.confid and a.siteid=c.siteid;
 
  select count(*) into v1 from TABLE (CAST (v_tab AS performance_tab)) where netlatency >0 AND  netlatency <=500;
  select count(*) into v2 from TABLE (CAST (v_tab AS performance_tab)) where netlatency >500 AND  netlatency <=1000;
  select count(*) into v3 from TABLE (CAST (v_tab AS performance_tab)) where netlatency >1000;
 
  OPEN p_ret FOR SELECT v1 as good,v2 as medium,v3 as poor FROM dual;
 
 END SpGetPerfChartInfo;
 
END Pkg_Test;
/

测试结果
sqlplus>set timing on;
sqlplus>variable c refcursor;
sqlplus>exec Pkg_Test.SpGetPerfChartInfo ('meeting', :c );
sqlplus>print c;