PARALLEL_ENABLE AGGREGATE USING Oracle自定义
来源:互联网 发布:mac装千牛 编辑:程序博客网 时间:2024/04/27 21:19
转载自:http://blog.csdn.net/rosanu_blog/article/details/7829712
- Oracle自定义聚集函数使用简介
- 1 . oracle自定义聚集函数接口简介
- a. static function ODCIAggregateInitialize(sctx IN OUTstring_agg_type )
- return number
- 自定义聚集函数初始化设置,从这儿开始一个聚集函数
- b. member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2)
- return number
- 自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self 为当前
- 聚集函数的指针,用来与前面的计算结果进行关联
- c. member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT varchar2,flags IN number)
- return number
- 用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.
- d. member function OCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number)
- 终止聚集函数的处理,返回聚集函数处理的结果.
- SQL文件中有Functions,Type,那么在sqlplus中要start直接执行SQL文件怎么办?例如: <p>CREATE OR REPLACE FUNCTION strcat(input varchar2 )
- RETURN varchar2
- PARALLEL_ENABLE AGGREGATE USING strcat_type;</p> <p> 将上面的Functions写入SQL文件中,然后在sqlplus中用start执行此文件,后果是此Functions并没有被创建到数据库中,在后面加上commit;</p><p>也是没有用处的,提示的效果是SQL并没有结束,sqlplus并没有真正执行。</p> <p> 这怎么办?Google了老半天,也没有人写到这个问题,但是看看人家写Functions和我写的有一点点区别,修改一下!</p> <p>CREATE OR REPLACE FUNCTION strcat(input varchar2 )
- RETURN varchar2
- PARALLEL_ENABLE AGGREGATE USING strcat_type;
- /</p> <p> 在Functions最后一行加上一个/,再执行就OK了~看来sqlplus是把/作为一个Functions或者Type的结束符了!大家注意了!</p><p>
- </p><p></p><p>他山之石,可以攻玉,说的是别的山上的石头可以作为砺石,用来琢磨玉器。也许我们并没有非常好的手段和工具,但是巧用别人的思想和工具,</p><p>我们也可以把自己的东西一样的做的很好。
- Oracle提供的自定义的聚集函数的功能,就正是这他山之石, 借着他,我们可以磨砺出我们自己的美玉。
- 曾经遇到的一个系统开发中,有这样的一个需求。
- 用户的权限表,权限表是以permission来存储权限的,权限是一个数字,用2进制的方式来表示权限。一个用户有多个权限。</p><p>而总权限是位与的关系,得到总权限。
- 权限表
- user_permission(user_id, permission, permission_type)
- 比如
- SQL> select * from user_permission;
- USER_ID PERMISSION PERMISSIONTYPE
- ---------- ---------- --------------
- 1 31 1
- 1 7 1
- 1 63 1
- 其实这个需求不算很特别了,我们经常有这样的需要,不过通常我们使用常规的做法,可以写一个function把userid传入,</p><p>然后在function里拿到所有的userid的记录,然后遍历结果集,用Bitand累计进行bitand,最后把结果返回。这样解决比较简单,</p><p>而且很通俗,用到游标的知识就可以了。 但是这样就很有局限性了,如果是要统计多个userid的话,就麻烦了,要一个一个的做。
- 有没有什么更好的方法叻。
- 当然有哟,这就是我们将要给大家介绍的他山之石--Oracle自定义的聚集函数
- Oracle提供了自定义的聚合函数,达到类似sum() count()这样的功能。
- Oracle已经built-in了一些聚合函数,如sum,count,avg,min,max等, 同时Oracle也考虑到开发上的扩展性,所以通过了机制,</p><p>实现自己的聚合函数,Oracle提供了自定义的聚集函数。
- 对于自定义的聚集函数在Oracle实质上就是一个对象,这个对象需要扩展和实现
- ODCIAggregateInitialize --对象初始化
- ODCIAggregateIterate --聚合函数的迭代方法
- ODCIAggregateMerge --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
- ODCIAggregateTerminate --终止聚集函数的处理,返回聚集函数处理的结果.
- 这几个方法
- 比如,我们这里想实现的permission的bitand就可以这样定义</p><blockquote><p>
- create or replace type permission_bitand as object (
- v_permission_bitand number,
- static function ODCIAggregateInitialize(v_self in out permission_bitand ) return number,
- member function ODCIAggregateIterate(self in out permission_bitand, value in number) return number,
- member function ODCIAggregateMerge(self in out permission_bitand, v_next in permission_bitand) return number,
- member function ODCIAggregateTerminate(self in permission_bitand, return_value out number ,v_flags in number) return number
- )</p></blockquote><p>
- 定义好了以后,我们具体来看看里面方法的实现是怎样的。</p><blockquote><p>create or replace type body permission_bitand is
- static function ODCIAggregateInitialize(v_self in out permission_bitand) return number is
- begin
- v_self := permission_bitand(null);
- return ODCICONST.Success;
- end;
- member function ODCIAggregateIterate(self in out permission_bitand , value in number) return number is
- begin
- /* bitand */
- if self.v_permission_bitand is null then
- self.v_permission_bitand := value;
- else
- self.v_permission_bitand := bitand(self.v_permission_bitand, value);
- end if;
- return ODCICONST.Success;
- end;
- /* --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合 */
- member function ODCIAggregateMerge(self in out permission_bitand, v_next in permission_bitand) return number is
- begin
- /* bitand with next permission_bitand*/
- if self.v_permission_bitand is null then
- self.v_permission_bitand := nvl(v_next.v_permission_bitand, null);
- else
- if v_next.v_permission_bitand is not null then
- self.v_permission_bitand := bitand(self.v_permission_bitand, v_next.v_permission_bitand);
- end if;
- end if;
- return ODCICONST.Success;
- end;
- /*--终止聚集函数的处理,返回聚集函数处理的结果*/
- member function ODCIAggregateTerminate(self in permission_bitand, return_value out number,v_flags in number) return number is
- begin
- return_value := self.v_permission_bitand;
- return ODCICONST.Success;
- end;
- end;</p></blockquote><p>
- 上面的第三个方法ODCIAggregateMerge 是并行计算上使用的http://www.kmxlg.com/http://www.yzhbox.com,也就是和另一个返回集一起再做运算。
- 最后一个是返回值的函数, 这里可以感觉的到,这里的定义和我们的接口定义以及接口实现是一样的,Oracle通过接口回调,</p><p>从而实现我们的聚集函数的功能,最好是定义一个聚集函数叻</p><blockquote><p>
- create or replace function permission_bitand_sum(value number) return number
- parallel_enable aggregate using permission_bitand;</p></blockquote><p>
- 大功告成,我们来试试吧,先grant一把</p><blockquote><p>
- SQL>create or replace public synonym permission_bitand_sum for permission_bitand_sum;
- SQL>grant execute on permission_bitand_sum to public;
- SQL>grant execute on permission_bitand to public;</p></blockquote><p>SQL> select * from user_permission;
- USER_ID PERMISSION PERMISSIONTYPE
- ---------- ---------- --------------
- 1 31 1
- 1 7 1
- 1 63 1</p><blockquote><p>
- SQL> select permission_bitand_sum(permission) from user_permission;
- PERMISSION_BITAND_SUM(PERMISSION)
- ---------------------------------
- 7
- SQL> insert into user_permission values(1, 0, 1);
- SQL> select permission_bitand_sum(permission) from user_permission;
- PERMISSION_BITAND_SUM(PERMISSION)
- ---------------------------------
- 0</p></blockquote><p>
- SQL> update user_permission set permission=null where permission=0;
- 这里的测试,是null不参与计算,所以刚才为0,导致我们的返回为0,现在当为null的时候,不参与计算,结果应该是7
- SQL> select permission_bitand_sum(permission) from user_permission;
- PERMISSION_BITAND_SUM(PERMISSION)
- ---------------------------------
- 7
- 这里就测试通过了,好强大吧。
- 想一想,在10g里引入了一个超级强的聚集函数wmsys.wm_concat(), 不过遗憾的是,这个在9i里是没</p>
- <p>
- </p><p>
- </p>
0 0
- PARALLEL_ENABLE AGGREGATE USING Oracle自定义
- PARALLEL_ENABLE AGGREGATE USING Oracle自定义聚集函数
- Oracle 11g SQL学习笔记1——Using Aggregate Functions
- SQL Server 2005: Using OVER() with Aggregate Functions
- Oracle 聚合函数(Aggregate Functions)说明
- [Oracle]-[SORT AGGREGATE]-count与索引
- Incoming call log summary shows filtering, ordering, grouping, joining, and selection using aggregate values
- Oracle中的批处理 ,using
- Using Oracle Blocks Efficiently
- Oracle using resource manager
- Oracle using blocks efficiently
- Oracle中的批处理,using
- Using Oracle Blocks Efficiently
- Oracle中Using用法
- Oracle中Using用法
- Using Oracle Restart
- oracle之join...using
- Using Oracle Table Compression
- 程序员这样对待简历,你期望面试官怎么对待你?
- 搭建phpmyadmin数据库管理系统
- Storm vs Spark
- PHP常量
- 单片机的周期概念
- PARALLEL_ENABLE AGGREGATE USING Oracle自定义
- ionic之简单实例
- zoom的作用
- 程序员在上海之二
- Leetcode Binary Tree Zigzag Level Order Traversal
- POJ 2348 Euclid's Game
- U盘刻录后如何恢复原来容量大小
- 前端性能优化
- PHP运算符