oracle管道化表函数

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

1、SQL语句

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

select rpad(gajg_dm,12,'0'), 
  sum(decode(C01, 0, 0, C01)) as C01,
  sum(decode(C02, 0, 0, C02)) as C02,
  sum(decode(C03, 0, 0, C03)) as C03,
  sum(decode(C04, 0, 0, C04)) as C04,
  sum(decode(C05, 0, 0, C05)) as C05,
  sum(decode(C06, 0, 0, C06)) as C06,
  sum(decode(C07, 0, 0, C07)) as C07,
  sum(decode(C08, 0, 0, C08)) as C08,
  sum(decode(C09, 0, 0, C09)) as C09,
  sum(decode(C10, 0, 0, C10)) as C10,
  sum(decode(C11, 0, 0, C11)) as C11
  from
  (
    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, 
      sum(decode(cslb_dm, '01', 1, 0)) as C01,
      sum(decode(cslb_dm, '02', 1, 0)) as C02,
      sum(decode(cslb_dm, '03', 1, 0)) as C03,
      sum(decode(cslb_dm, '04', 1, 0)) as C04,
      sum(decode(cslb_dm, '05', 1, 0)) as C05,
      sum(decode(cslb_dm, '06', 1, 0)) as C06,
      sum(decode(cslb_dm, '07', 1, 0)) as C07,
      sum(decode(cslb_dm, '08', 1, 0)) as C08,
      sum(decode(cslb_dm, '09', 1, 0)) as C09,
      sum(decode(cslb_dm, '10', 1, 0)) as C10,
      sum(decode(cslb_dm, '11', 1, 0)) as C11
      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' 
     --group by substr(b.gajg_dm,0,4), cslb_dm
     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
   ) t group by rpad(gajg_dm,12,'0')--gajg_dm

在该SQL语句中，主要有三个步骤：

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

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

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

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

 

2、存储过程返回游标

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

 

3、临时表（或中间表）

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

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

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

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

 

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

 

4、管道表函数

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

CREATE OR REPLACE PACKAGE pkg1 AS
  -- Purpose : 对表函数的应用实例
  TYPE ty_rec_user IS record (--定义一个record类型的TYPE
       id number(20),
       name varchar2(60)
  );
  TYPE out_rec_set is table of ty_rec_user;--定义一个嵌套表集合类型out_rec_set,作为表函数的返回类型
  --定义返回集合类型的管道表函数
  FUNCTION f1(x NUMBER) RETURN out_rec_set PIPELINED;
  
  --引用在外部自定义的object类型作为表函数的集合类型
  TYPE out_obj_set is table of TY_OBJ_USER;
  FUNCTION F_PIE_TEST(c NUMBER) RETURN out_obj_set PIPELINED;
END pkg1;


CREATE OR REPLACE PACKAGE BODY pkg1 AS
  -- Purpose : 对表函数的应用实例
FUNCTION f1(x NUMBER) RETURN out_rec_set PIPELINED IS
  user_rec ty_rec_user;
  BEGIN
    FOR i IN 1..x LOOP
      --user_rec:=ty_rec_user(i,'user'||i);--ty_rec_user定义为record类型时不能这样赋值，只有定义成obj时才可以
      user_rec.id:=i;
      user_rec.name:='user'||i;
      --PIPE ROW(1, 'user'||1);
      pipe row(user_rec);
    END LOOP;
    RETURN;
  END;
--使用在外部自定义的object类型表函数
FUNCTION F_PIE_TEST(c NUMBER) RETURN out_obj_set PIPELINED is
  user_ty_obj TY_OBJ_USER;
  BEGIN
     FOR i in 1..c LOOP
         user_ty_obj:=TY_OBJ_USER(i,'name'||i);
         PIPE ROW(user_ty_obj);
     END LOOP;
     RETURN;
  END;
END pkg1;

--外部自定义的object类型
create or replace type TY_OBJ_USER as object
(
  -- Purpose : 测试
  id number(20),
  name varchar2(60),
)

 表函数的调用：

select * from table(pkg1.f1(4))--直接在plsql中执行

select *  FROM  TABLE(CAST(pkg1.f1(4) AS out_rec_set))--java端程序调用

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

 

欢迎大家提出更好的方案共同探讨~~

本文主要参考：http://blog.chinaunix.net/u1/57759/showart_458451.html

评论

1 楼 MyDicta 2010-08-19  

楼主， oracle中管道函数可以动态传参么？

你上面写的函数：

select * from table(pkg1.f1(4))--直接在plsql中执行

其中
select id,name  from table(pkg1.f1(4)) where id=1; -- 正确

但 “4” 可以是基于一个查询的结果值么， 应该怎么写呢？