他山之石，可以攻玉--Oracle自定义的聚集函数的强大功能

• 作者: 三十而立
• 时间：2009年10月10日 9:00:34
• 请尊重原创作品。转载请保持文章完整性,并以超链接形式注明原始作者“inthirties（三十而立）”和出处”http://blog.csdn.net/inthirties/archive/2009/10/10/4652245.aspx”,深入讨论可以联系inthirties@gmail.com。

 

 

他山之石，可以攻玉，说的是别的山上的石头可以作为砺石，用来琢磨玉器。也许我们并没有非常好的手段和工具，但是巧用别人的思想和工具，我们也可以把自己的东西一样的做的很好。 

Oracle提供的自定义的聚集函数的功能，就正是这他山之石， 借着他，我们可以磨砺出我们自己的美玉。 

曾经遇到的一个系统开发中，有这样的一个需求。 

用户的权限表，权限表是以permission来存储权限的，权限是一个数字，用2进制的方式来表示权限。一个用户有多个权限。而总权限是位与的关系，得到总权限。 

权限表 
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传入，然后在function里拿到所有的userid的记录，然后遍历结果集，用Bitand累计进行bitand，最后把结果返回。 这样解决比较简单，而且很通俗，用到游标的知识就可以了。 但是这样就很有局限性了，如果是要统计多个userid的话，就麻烦了，要一个一个的做。 

有没有什么更好的方法叻。 

当然有哟，这就是我们将要给大家介绍的他山之石--Oracle自定义的聚集函数 

Oracle提供了自定义的聚合函数，达到类似sum（） count（）这样的功能。 

Oracle已经built-in了一些聚合函数，如sum，count，avg，min，max等， 同时Oracle也考虑到开发上的扩展性，所以通过了机制，实现自己的聚合函数，Oracle提供了自定义的聚集函数。 

对于自定义的聚集函数在Oracle实质上就是一个对象,这个对象需要扩展和实现 
ODCIAggregateInitialize --对象初始化 
ODCIAggregateIterate --聚合函数的迭代方法 
ODCIAggregateMerge --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合 
ODCIAggregateTerminate --终止聚集函数的处理,返回聚集函数处理的结果. 
这几个方法 

比如，我们这里想实现的permission的bitand就可以这样定义 

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 
) 

 

定义好了以后，我们具体来看看里面方法的实现是怎样的。 

 

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; 

 

上面的第三个方法ODCIAggregateMerge 是并行计算上使用的，也就是和另一个返回集一起再做运算。 
最后一个是返回值的函数， 这里可以感觉的到，这里的定义和我们的接口定义以及接口实现是一样的，Oracle通过接口回调，从而实现我们的聚集函数的功能，最好是定义一个聚集函数叻 

create or replace function permission_bitand_sum(value number) return number 
parallel_enable aggregate using permission_bitand; 

大功告成，我们来试试吧，先grant一把 

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; 

 

SQL> select * from user_permission; 

USER_ID PERMISSION PERMISSIONTYPE 
---------- ---------- -------------- 
1           31              1 
1           7                1 
1           63              1 

 

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 

 

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里是没有的，现在知道自定义的聚集函数，可以在9i上自己做出这个聚集函数了。 

这个就算是布置给大家的作业叻。 有兴趣的朋友，大家下去自己做一下哟。

 

• 如果没有那句“三十而立”，三十岁的男人正可以轻轻松松
• 专业论坛 http://www.inthirties.com
• 技术博客 http://blog.csdn.net/inthirties
• 个人站点 http://blog.inthirties.com
• Oracle Mysql技术论坛| 打造实用的Oracle Mysql技术交流园地