oracle管道化表函数

简介:

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

1、SQL语句

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

Sql代码   收藏代码
  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、管道表函数

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

Sql代码   收藏代码
  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代码   收藏代码
  1. select * from table(pkg1.f1(4))--直接在plsql中执行  
Sql代码   收藏代码
  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,如需转载请自行联系原作者

目录
相关文章
|
3天前
|
SQL Oracle 关系型数据库
Oracle之日期计算相关函数
Oracle之日期计算相关函数
51 0
|
3天前
|
SQL Oracle 关系型数据库
Oracle之regexp系列函数详解
Oracle之regexp系列函数详解
168 1
|
3天前
|
Oracle 关系型数据库 数据库
Oracle查询优化-复制表的定义及数据
【1月更文挑战第5天】【1月更文挑战第14篇】在Oracle数据库中,复制表定义和复制表数据是两个常见的操作。
56 1
|
3天前
|
存储 Java 数据库
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数(二)
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数
42 0
|
3天前
|
SQL Oracle 关系型数据库
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
69 0
JAVAEE框架数据库技术之12_oracle常用函数和高级查询子查询
|
1天前
|
消息中间件 关系型数据库 Kafka
实时计算 Flink版产品使用合集之oracle cdc 抽取新增一张表 可以从savepoint恢复吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
13 0
|
3天前
|
NoSQL Oracle 关系型数据库
MongoDB与Oracle:管道函数兼容之道
【4月更文挑战第20天】
8 2
|
3天前
|
SQL 存储 Oracle
Oracle中的Dual表:数据世界的“神奇小盒子”
【4月更文挑战第19天】Oracle的Dual表是一个虚拟表,仅含一行一列,常用于执行SQL函数、数据类型转换、测试语句和动态SQL。它是测试和便捷操作的工具,如获取当前日期(`SELECT SYSDATE FROM DUAL`)、数字转字符串(`SELECT TO_CHAR(12345) FROM DUAL`)。在存储过程、函数和触发器中也发挥重要作用,是数据库管理员的得力助手。
|
3天前
|
Oracle 算法 关系型数据库
Oracle常用系统函数之数字类函数:数字的魔术师
【4月更文挑战第19天】Oracle数据库中的数字类函数是数字处理的魔术师,包括`ROUND`(四舍五入),`CEIL`和`FLOOR`(向上/下取整),以及`ABS`(计算绝对值)。还有`MOD`、`TRUNC`和`POWER`等函数,提供求余数、截断和计算幂的功能。熟练运用这些函数能提升数据管理效率,让处理数字变得更简单、有趣。
|
3天前
|
存储 Oracle 关系型数据库
Oracle系列之七:表的创建与管理
Oracle系列之七:表的创建与管理

相关实验场景

更多

推荐镜像

更多