Oracle中的统计SQL和存储过程

　　今天需要在Oracle里面做一个统计查询，关联很多表，用稍微复杂的SQL语句可以实现，如下：

select nn.GXDWMC 汉字标题1,nn.TYMC 汉字标题2,jbsl 汉字标题3,fcsl汉字标题4,GSL汉字标题5,Lsl汉字标题6 from Table0103 nn,(select b.GXDWMC as GXDWMC,b.TYMC as TYMC,count(a.TYMC) jbsl  from TABLE0901 a,Table0103 b where b.TYMC=a.SSZMC    group by  b.GXDWMC ,b.TYMC ) aa, (select b.GXDWMC as GXDWMC,b.TYMC as TYMC,count(c.TYMC) fcsl from Table0103 b ,TABLE0912 c where  b.TYMC=c.SSZMC group by b.GXDWMC,b.TYMC) bb,(select c.GXDWMC as GXDWMC,c.TYMC as TYMC,count(c.TYMC) GSL
 from Table0103 c ,TABLE0904 d where c.TYMC=d.SSZMC group by c.GXDWMC,c.TYMC) cc,(select d.GXDWMC as GXDWMC,d.TYMC as TYMC,count(d.TYMC) lSL from Table0103 d ,TABLE0906 e where d.TYMC=e.SSZMC group by  d.GXDWMC ,d.TYMC ) dd where nn.GXDWMC=aa.GXDWMC and nn.GXDWMC=bb.GXDWMC and nn.TYMC=aa.TYMC and nn.TYMC=bb.TYMC and cc.TYMC=aa.TYMC and nn.TYMC=bb.TYMC and dd.TYMC=aa.TYMC and nn.TYMC=bb.TYMC

　　这个统计最终涉及的表可能有10几张，后面改用存储过程来实现也可以，如下：

CREATE OR REPLACE PACKAGE TSP_YQCY_0F_TJ0 IS
TYPE CUR IS REF CURSOR;
PROCEDURE TSP_YQCY_0F_TJ0
 (
  P_DW IN VARCHAR2,
  C    OUT CUR
 );
END TSP_YQCY_0F_TJ0;
/
CREATE OR REPLACE PACKAGE BODY TSP_YQCY_0F_TJ0 IS
 PROCEDURE TSP_YQCY_0F_TJ0
 (
  P_DW IN VARCHAR2,
  C    OUT CUR
 ) AS

begin
  OPEN C FOR

select nn.GXDWMC　汉字标题1, nn.TYMC　汉字标题2,jbsl 汉字标题3,fcsl汉字标题4 ,GSL汉字标题5,Lsl汉字标题6 from Table0103 nn,
 (select b.GXDWMC as GXDWMC,b.TYMC as TYMC,count(a.TYMC) jbsl
   from Table0901 a,Table0103 b 
    where b.TYMC=a.SSZMC        
    group by  b.GXDWMC ,b.TYMC    
    ) aa,
  (select b.GXDWMC as GXDWMC,b.TYMC as TYMC,count(c.TYMC) fcsl
     from Table0103 b ,Table0912 c 
    where  b.TYMC=c.SSZMC 
    group by  b.GXDWMC ,b.TYMC
    ) bb,
  (select c.GXDWMC as GXDWMC,c.TYMC as TYMC,count(c.TYMC) GSL
     from Table0103 c ,Table0904 d 
    where  c.TYMC=d.SSZMC 
    group by  c.GXDWMC ,c.TYMC
    ) cc,
  (select d.GXDWMC as GXDWMC,d.TYMC as TYMC,count(d.TYMC) lSL
     from Table0103 d ,Table0906 e 
    where  d.TYMC=e.SSZMC 
    group by  d.GXDWMC ,d.TYMC
    ) dd
   where nn.GXDWMC=aa.GXDWMC and nn.GXDWMC=bb.GXDWMC
          and  nn.TYMC=aa.TYMC and nn.TYMC=bb.TYMC
          and  cc.TYMC=aa.TYMC and nn.TYMC=bb.TYMC
          and  dd.TYMC=aa.TYMC and nn.TYMC=bb.TYMC   
   ;
 END;
END;
/

　　对于复杂的统计,也可以通过创建临时表来完成,具体做法是:
　　１、建立临时表；
　　２、通过在包体完成统计，即在临时表里的统计记录的填写；
　　３、返回结果集，即游标。