oracle自定义聚合函数现实字符串连接

作者: qzj_jo2005 发表日期: 2008-04-02 10:34:00

  在项目经常在group by 时想把字符串也累计出来，如：

select min(t.weighttime), --过衡最小时间
        max(t.weighttime),--过衡最大时间
        sum(t.netweight), --重量
        count(t.truckboxid),--车数
        t.boxlicence   -- 车皮号
  from truckweighboxes t

  1. /* Oracle自定义聚合函数实现字符串连接 */   
  2.   
  3. -- 定义类型 聚合函数的实质就是一个对象    
  4. create or replace type strcat_type as object (   
  5.     cat_string varchar2(4000),   
  6.     --对象初始化    
  7.     static function ODCIAggregateInitialize(cs_ctx In Out strcat_type)    
  8.         return number,   
  9.         --聚合函数的迭代方法(这是最重要的方法)   
  10.     member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2)    
  11.         return number,   
  12.         --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合   
  13.     member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type)    
  14.         return number,   
  15.         --终止聚集函数的处理,返回聚集函数处理的结果   
  16.     member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number)   
  17.         return number   
  18. )   
  19. /   
  20.   
  21. /* 创建主体类型 */   
  22. create or replace type body strcat_type is  
  23.   static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number   
  24.   is  
  25.   begin  
  26.       cs_ctx := strcat_type( null );   
  27.       return ODCIConst.Success;   
  28.   end;   
  29.   member function ODCIAggregateIterate(self IN OUT strcat_type,   
  30.                                        value IN varchar2 )   
  31.   return number   
  32.   is  
  33.   begin  
  34.         /*字符串已','分割 */   
  35.       self.cat_string := self.cat_string || ','|| value;   
  36.       return ODCIConst.Success;   
  37.   end;   
  38.   member function ODCIAggregateTerminate(self IN Out strcat_type,   
  39.                                          returnValue OUT varchar2,   
  40.                                          flags IN number)   
  41.   return number   
  42.   is  
  43.   begin  
  44.         /*去除空(is null)*/   
  45.       returnValue := ltrim(rtrim(self.cat_string,','),',');   
  46.       return ODCIConst.Success;   
  47.   end;   
  48.   member function ODCIAggregateMerge(self IN OUT strcat_type,   
  49.                                      ctx2 IN Out strcat_type)   
  50.   return number   
  51.   is  
  52.   begin  
  53.       self.cat_string := self.cat_string || ',' || ctx2.cat_string;   
  54.       return ODCIConst.Success;   
  55.   end;   
  56. end;   
  57. /   
  58.   
  59. /*创建函数 func_strcat*/   
  60. CREATE OR REPLACE FUNCTION func_strcat(input varchar2 )   
  61. RETURN varchar2 -- 返回值   
  62. PARALLEL_ENABLE AGGREGATE USING strcat_type; --使平行累加   
  63. /  

测试结果

  1. select min(t.weighttime), --过衡最小时间   
  2.         max(t.weighttime),--过衡最大时间   
  3.         sum(t.netweight), --重量   
  4.         count(t.truckboxid),--车数   
  5.         func_strcat(t.boxlicence)   -- 车皮号   
  6.   from truckweighboxes t   
  7.   where rownum <= 20  

 

 

mintime                               maxtime                            netweight                       

2006-12-31 9:00:09         2007-1-3 16:07:44          415.91   

boxlicence（车皮号） ：   06175,53645,61690,55033,61379,

53807,55005,52019,53905,51068,53670,53861,53856,80507,9007,9066,62301,53670,61095,55005

 

注意：oracel varchar2 最大不能超过4000字符（超出后错误：ora-06502:PL/SQL:numeric or value erro:character string buffer too small）， 如果超过请使用clob或blob类型来处理。

 [ 尊重原创，未经允许请勿转载本站日志 ]

  共 0 条评论 »

  我来说几句：