oracle管道化表函数

在我所做过和参与的大多数项目中,都会有用户提出的复杂的一些统计报表之内的功能要求,根据统计的复杂程度、效率及JAVA程序调用的方便性方面考虑,主要总结出以下几种方案:

1、SQL语句

该方案只能实现一些相对简单些的查询统计功能,语句嵌套多、写起来特别复杂,使程序的可读性变差,下面是实现一个按照上级机关统计下级各个*机关管辖范围内对应的各个类别社会单位数量的统计功能的SQL:

Sql代码  oracle管道化表函数
  1. select rpad(gajg_dm,12,'0'),   
  2.   sum(decode(C01, 0, 0, C01)) as C01,  
  3.   sum(decode(C02, 0, 0, C02)) as C02,  
  4.   sum(decode(C03, 0, 0, C03)) as C03,  
  5.   sum(decode(C04, 0, 0, C04)) as C04,  
  6.   sum(decode(C05, 0, 0, C05)) as C05,  
  7.   sum(decode(C06, 0, 0, C06)) as C06,  
  8.   sum(decode(C07, 0, 0, C07)) as C07,  
  9.   sum(decode(C08, 0, 0, C08)) as C08,  
  10.   sum(decode(C09, 0, 0, C09)) as C09,  
  11.   sum(decode(C10, 0, 0, C10)) as C10,  
  12.   sum(decode(C11, 0, 0, C11)) as C11  
  13.   from  
  14.   (  
  15.     select substr(b.gajg_dm,0,decode(substr(b.gajg_dm,0,8),'41000030',8,'41000006',8,'41000061',8,'41000060',8,4)) gajg_dm,/*b.gajg_dm,*/ cslb_dm,   
  16.       sum(decode(cslb_dm, '01', 1, 0)) as C01,  
  17.       sum(decode(cslb_dm, '02', 1, 0)) as C02,  
  18.       sum(decode(cslb_dm, '03', 1, 0)) as C03,  
  19.       sum(decode(cslb_dm, '04', 1, 0)) as C04,  
  20.       sum(decode(cslb_dm, '05', 1, 0)) as C05,  
  21.       sum(decode(cslb_dm, '06', 1, 0)) as C06,  
  22.       sum(decode(cslb_dm, '07', 1, 0)) as C07,  
  23.       sum(decode(cslb_dm, '08', 1, 0)) as C08,  
  24.       sum(decode(cslb_dm, '09', 1, 0)) as C09,  
  25.       sum(decode(cslb_dm, '10', 1, 0)) as C10,  
  26.       sum(decode(cslb_dm, '11', 1, 0)) as C11  
  27.       from yf_cs_jbxx a, dm_gajg b where b.gajg_dm=a.gajg_dm(+) and b.gajg_dm like '41%' --and b.gajg_pcs_bz<>'N'   
  28.      --group by substr(b.gajg_dm,0,4), cslb_dm  
  29.      group by substr(b.gajg_dm,0,decode(substr(b.gajg_dm,0,8),'41000030',8,'41000006',8,'41000061',8,'41000060',8,4)),cslb_dm  
  30.    ) t group by rpad(gajg_dm,12,'0')--gajg_dm  

在该SQL语句中,主要有三个步骤:

1)统计各个机关下各个类别的单位数量

2)对编码不规则机关进行decode和截位处理

3)对1中的统计结果进行行列转换

够复杂了吧,而且还很别扭,看着就晕。。。。更别说代码数据再发生点变化了

 

2、存储过程返回游标

对于该方案是被我们直接PASS掉的一种方案,主要考虑其性能太差,这里就不再啰嗦了

 

3、临时表(或中间表)

对于该方案主要分为两步完成统计:

1)通过存储过程或函数完成对数据的统计

2)将统计结果插入到临时表中

这样程序在执行统计时就要求先调用执行统计的存储过程,然后再查询临时表以取出存储过程产生的统计结果

 

呵呵,每个统计还要对应建一个临时表,看着就闲麻烦。。。

 

4、管道表函数

管道化表函数是我见过的最佳的实现统计的解决方案(当然是在我做的项目中,具体东西具体环境具体应用吗oracle管道化表函数),这里给出两个实例和说明,供大家参考,但该方案同样有一个缺点,就是在PLSQL下调试极其不方面,但基本还能忍受

Sql代码  oracle管道化表函数
  1. CREATE OR REPLACE PACKAGE pkg1 AS  
  2.   -- Purpose : 对表函数的应用实例  
  3.   TYPE ty_rec_user IS record (--定义一个record类型的TYPE  
  4.        id number(20),  
  5.        name varchar2(60)  
  6.   );  
  7.   TYPE out_rec_set is table of ty_rec_user;--定义一个嵌套表集合类型out_rec_set,作为表函数的返回类型  
  8.   --定义返回集合类型的管道表函数  
  9.   FUNCTION f1(x NUMBER) RETURN out_rec_set PIPELINED;  
  10.     
  11.   --引用在外部自定义的object类型作为表函数的集合类型  
  12.   TYPE out_obj_set is table of TY_OBJ_USER;  
  13.   FUNCTION F_PIE_TEST(c NUMBER) RETURN out_obj_set PIPELINED;  
  14. END pkg1;  
  15.   
  16.   
  17. CREATE OR REPLACE PACKAGE BODY pkg1 AS  
  18.   -- Purpose : 对表函数的应用实例  
  19. FUNCTION f1(x NUMBER) RETURN out_rec_set PIPELINED IS  
  20.   user_rec ty_rec_user;  
  21.   BEGIN  
  22.     FOR i IN 1..x LOOP  
  23.       --user_rec:=ty_rec_user(i,'user'||i);--ty_rec_user定义为record类型时不能这样赋值,只有定义成obj时才可以  
  24.       user_rec.id:=i;  
  25.       user_rec.name:='user'||i;  
  26.       --PIPE ROW(1, 'user'||1);  
  27.       pipe row(user_rec);  
  28.     END LOOP;  
  29.     RETURN;  
  30.   END;  
  31. --使用在外部自定义的object类型表函数  
  32. FUNCTION F_PIE_TEST(c NUMBER) RETURN out_obj_set PIPELINED is  
  33.   user_ty_obj TY_OBJ_USER;  
  34.   BEGIN  
  35.      FOR i in 1..c LOOP  
  36.          user_ty_obj:=TY_OBJ_USER(i,'name'||i);  
  37.          PIPE ROW(user_ty_obj);  
  38.      END LOOP;  
  39.      RETURN;  
  40.   END;  
  41. END pkg1;  
  42.   
  43. --外部自定义的object类型  
  44. create or replace type TY_OBJ_USER as object  
  45. (  
  46.   -- Purpose : 测试  
  47.   id number(20),  
  48.   name varchar2(60),  
  49. )  

 表函数的调用:

Sql代码  oracle管道化表函数
  1. select * from table(pkg1.f1(4))--直接在plsql中执行  
Sql代码  oracle管道化表函数
  1. select *  FROM  TABLE(CAST(pkg1.f1(4) AS out_rec_set))--java端程序调用  

 看到这相信很多人已经开始感觉到爽了吧~~,特别是数据开发人员,不用再在数 据端实现统计后还要给应用程序开发人员讲半天如何调用了,应用程序开发人员在调用复杂的统计时一个select语句就搞定,不用考虑什么游标啊、临时表这 些乱七八糟的东西了,直接一个select就出来结果,和查询一个表一样的简单



本文转自左正博客园博客,原文链接:http://www.cnblogs.com/soundcode/archive/2012/01/05/2312851.html,如需转载请自行联系原作者

上一篇:oracle易忘函数用法(5)


下一篇:Oracle中Decode()函数使用技巧