`
linwei_211
  • 浏览: 191564 次
  • 性别: Icon_minigender_2
  • 来自: 北京
社区版块
存档分类
最新评论

自定义聚合函数以及字符串连接超长的解决

阅读更多

Oracle的定制功能十分强大。前几天在碰到一个问题的时候,查询文档发现,oracle不但允许用户定制自己的函数,还可以定制自己的聚集函数和分析函数。

下面是我使用自定义聚集函数建立一个字符串“sum”的小例子。

类型声明:
CREATE OR REPLACE TYPE SUM_LINK AS OBJECT (
  STR VARCHAR2(30000),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN SUM_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER
  )

类型主体:
 
CREATE OR REPLACE TYPE BODY SUM_LINK IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER IS
  BEGIN
  SCTX := SUM_LINK(NULL);
  RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
  BEGIN
 SELF.STR := SELF.STR || VALUE||';';
 RETURN ODCICONST.SUCCESS;
 END;

 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN SUM_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
 BEGIN
  RETURNVALUE := SELF.STR;
 RETURN ODCICONST.SUCCESS;
 END;

 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER IS
 BEGIN
  NULL;
  RETURN ODCICONST.SUCCESS;
  END;
  END;
 
 
方法创建:
 
CREATE OR REPLACE FUNCTION SUM_LINK(P_STR VARCHAR2) RETURN VARCHAR2
  AGGREGATE USING SUM_LINK;

 

  建表、插入数据,用来测试:
  
CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(20));
   INSERT INTO TEST VALUES (1, 'AAA');
   INSERT INTO TEST VALUES (2, 'BBB');
   INSERT INTO TEST VALUES (1, 'ABC');
   INSERT INTO TEST VALUES (3, 'CCC');
   INSERT INTO TEST VALUES (2, 'DDD');
   COMMIT;

 

测试自定义函数和类型:
SQL> SELECT ID, SUM_LINK(NAME) NAME FROM TEST GROUP BY ID;

        ID NAME
---------- ----------------------------

         1      AAA;ABC;

         2      BBB;DDD;

         3      CCC;
 
这里介绍一下通过SQL的方法来解决同样的问题。

对于下面这个利用自定义聚集函数的例子,将其改写为直接用SQL实现:
SQL> SELECT * FROM TAB;
TNAME  TABTYPE  CLUSTERID
------ -------- ------------------------------------ ------- ----------
SY_NAME SYNONYM
T      TABLE
TEST   TABLE
TEST1  TABLE
SUM_BLOB TABLE
SUM_CLOB TABLE
V_T    VIEW
V_TEST VIEW
已选择8行。
SQL> SELECT TABTYPE, SUM_LINK(TNAME) TNAME FROM TAB GROUP BY TABTYPE;
TABTYPE TNAME
------- --------------------------------------------------
SYNONYM SY_NAME
TABLE T,TEST1,SUM_CLOB,SUM_BLOB,TEST
VIEW V_T,V_TEST

    除了利用自定义聚集函数外,SQL函数中能将多个字符串合并在一起的只有SYS_CONNECSUM_BY_PATH了。
而这个函数只能应用在树型查询中,为了能使用这个函数,必须人为的构造出树来。
    也就是说,必须可以构造出一个CONNECT BY列使得相同的TABTYPE的TNAME可以用SYS_CONNECSUM_BY_PATH连接起来。
连接列可以使用ROW_NUMBER() OVER()来构造,在CONNECT BY的时候指定当前列的等于父列的值加1。
并在START WITH时指定起始值为1。
    最后对TABTYPE进行分组,取得最大值就是最终需要的结果

SQL> SELECT TABTYPE, MAX(LTRIM(SYS_CONNECSUM_BY_PATH(TNAME, ','), ',')) TNAME
2 FROM
3 (
4 SELECT TABTYPE, TNAME, ROW_NUMBER() OVER(PARTITION BY TABTYPE ORDER BY TNAME) RN
5 FROM TAB
6 )
7 START WITH RN = 1
8 CONNECT BY PRIOR RN + 1 = RN
9 AND PRIOR TABTYPE = TABTYPE
10 GROUP BY TABTYPE;
TABTYPE TNAME
------- --------------------------------------------------
SYNONYM SY_NAME
TABLE T,TEST,TEST1,SUM_BLOB,SUM_CLOB
VIEW V_T,V_TEST


      不过上面两种方法都会面临一个问题,就是如果聚集连接的字符串长度如果超过了VARCHAR2类型所允许的最大长度,就会导致字符串超长的错误。


自定义聚集函数这里就不重复了,可以参考上面的介绍:
SQL> SELECT SUM_LINK(TNAME) FROM TAB;
SUM_LINK(TNAME)
-------------------------------------------------------------------------------------------
BAK_SHGOV_ORDER,BAK_SHGOV_ORDER_BAK,PLAN_TABLE,SHGOV_ORDER,SHGOV_ORDER_BAK,T,T1,TEST,SUM_SQL
SQL> SELECT SUM_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES;
SELECT SUM_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在"TEST.SUM_LINK", line 16
ORA-06512: 在line 1
SQL> SELECT MAX(LTRIM(SYS_CONNECSUM_BY_PATH(SEQUENCE_NAME, ','), ',')) NAME
2 FROM
3 (
4 SELECT SEQUENCE_NAME, ROW_NUMBER() OVER(ORDER BY SEQUENCE_NAME) RN
5 FROM ALL_SEQUENCES
6 )
7 START WITH RN = 1
8 CONNECT BY PRIOR RN + 1 = RN
9 ;
FROM ALL_SEQUENCES
*
ERROR 位于第 5 行:
ORA-01489: 字符串连接的结果过长
显然是由于要连接的字符串太长了,导致Oracle的字符串处理过程中出现了错误。
上面的两种方法都没有办法避免这个问题。
      但是ALL_SEQUENCES中的记录只有几百个,每个名称的长度不会超过30,因此最终的长度不会超过32767。
      根据Oracle给出的错误信息,显然是在处理输出参数RETURNVALUE的时候是安装SQL类型的VARCHAR2长度4000做的限制,那么只需要修改输出参数和聚集函数的返回值类型为CLOB类型即可
SQL> CREATE OR REPLACE TYPE SUM_LINK AS OBJECT (
2 STR VARCHAR2(32767),
3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER,
4 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
5 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN SUM_LINK, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER,
6 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER
7 )
8 /
类型已创建。


SQL> CREATE OR REPLACE TYPE BODY SUM_LINK IS
2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT SUM_LINK) RETURN NUMBER IS
3 BEGIN
4 SCTX := SUM_LINK(NULL);
5 RETURN ODCICONST.SUCCESS;
6 END;
7
8 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT SUM_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
9 BEGIN
10 SELF.STR := SELF.STR || VALUE || ',';
11 RETURN ODCICONST.SUCCESS;
12 END;
13
14 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN SUM_LINK, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
15 BEGIN
16 RETURNVALUE := SUBSTR(SELF.STR, 1, LENGTH(SELF.STR) - 1);
17 RETURN ODCICONST.SUCCESS;
18 END;
19
20 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT SUM_LINK, CTX2 IN SUM_LINK) RETURN NUMBER IS
21 BEGIN
22 NULL;
23 RETURN ODCICONST.SUCCESS;
24 END;
25 END;
26 /
类型主体已创建。

 

SQL> CREATE OR REPLACE FUNCTION SUM_LINK(P_STR VARCHAR2) RETURN CLOB
2 AGGREGATE USING SUM_LINK;
3 /
函数已创建。


SQL> SELECT SUM_LINK(SEQUENCE_NAME) FROM ALL_SEQUENCES;
这个检索结果截图我就不粘了 ,我的库里SEQUENCE太多了.......

 

总结一下:
  ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
  引起这种问题的根源就是存放数据的缓冲区长度比要存的数据本身的长度小,造成的,那么知道问题的原因,解决起来也就容易多了。
 
  可以参看一下
http://www.blogjava.net/wangbing/archive/2010/03/15/315482.html这篇文章中关于关于oracle中varchar2的最大长度的介绍。
 
观点是:varchar2有两个最大长度:一个是在字段类型4000;一个是在PL/SQL中变量类型32767。
  也就是说schema级varchar2的长度限制都是4000,而在PL/SQL代码级的长度限制是32767。这是一个比较容易出错的地方,也很容易被忽略。
 
因为在函数中我可以声明长度超过4000的字符串变量,并且将它作为返回值,这里是不会提示编译错误的。
  这个函数平时都可以正常执行,而且网络上的聚合函数的例子也大多都是上面那么写的,是由于作者没有考虑大数据量的问题。一旦用到项目上,遭遇大数据量,这个字符串长度超过4000,函数执行就会出错。证明这一点极容易被忽略。

  再列举一个例子:
  -- 准备自定义类型 strcat_type
 create type strcat_type as object (
    cat_string varchar2(4000),
    static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
    member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return number,
    member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number,
    member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number) return number
)

-- 准备自定义类型体
create type body strcat_type is
  static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number
  is
  begin
      cs_ctx := strcat_type( null );
      return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self IN OUT strcat_type,
                                       value IN varchar2 )
  return number
  is
  begin
      self.cat_string := self.cat_string || value || ';';
      return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self IN Out strcat_type,
                                         returnValue OUT varchar2,
                                         flags IN number)
  return number
  is
  begin
      returnValue := ltrim(rtrim(self.cat_string,','),',');
      return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT strcat_type,
                                     ctx2 IN Out strcat_type)
  return number
  is
  begin
      self.cat_string := self.cat_string || ',' || ctx2.cat_string;
      return ODCIConst.Success;
  end;

end;
  -- 创建字符串求和自定义函数
 CREATE or replace FUNCTION sum_str(input varchar2 )
 RETURN varchar2
 PARALLEL_ENABLE AGGREGATE USING strcat_type;
 -- 表和数据准备我就不详细介绍了.....

 -- 测试一下自己的方法
SQL> select sum_str(decode(id, 1, name, null)) a,
  2         sum_str(decode(id, 2, name, null)) b,
  3         sum_str(decode(id, 3, name, null)) c
  4    from test
  5  ;

A   B   C
--------  -------------  --------------
AAA;ABC;  BBB;DDD;       CCC;

这是数据少的情况下。不会报错,一但数据量过大,就会报
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小

修改一下数据类型;如下:
--修改函数返回类型为CLOB
CREATE OR REPLACE FUNCTION sum_str(input varchar2 ) RETURN
   CLOB PARALLEL_ENABLE AGGREGATE USING strcat_type;
--将类型声明中的varchar2(4000)改为varchar2(32767)
CREATE OR REPLACE TYPE "STRCAT_TYPE"                                                                                                                                                                                                                            as object ( cat_string
   varchar2(32767),
      static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
      member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return number,
      member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number,
      member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out clob,flags in number) return number
      )
--修改类型体的输出参数
create or replace type body strcat_type is static function
   ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return
   number is begin cs_ctx := strcat_type( null ); return
   ODCIConst.Success; end;

   member function ODCIAggregateIterate(self IN OUT
   strcat_type, value IN varchar2) return number is begin
   self.cat_string := self.cat_string || value || ';'; return
   ODCIConst.Success; end;

   member function ODCIAggregateTerminate(self IN Out
   strcat_type, returnValue OUT clob, flags IN number)
   return number is begin returnValue :=
   ltrim(rtrim(self.cat_string,','),','); return
   ODCIConst.Success; end;

   member function ODCIAggregateMerge(self IN OUT strcat_type,
   ctx2 IN Out strcat_type) return number is begin
   self.cat_string := self.cat_string || ',' ||
   ctx2.cat_string; return ODCIConst.Success; end;

   end;


再次使用sum_str方法,执行sql语句的时候不会再有任何问题了.......

 

分享到:
评论

相关推荐

    Oracle10g自定义聚合函数(字符串拼接)

    * 自定义聚合函数 wmsys.wm_concat 替换办法 * 超大字符串拼接,单个字符串4000、分隔符100,可拼出超4000的超长字符串 * 可自定义指定分隔符separator * 可自定义指定排序字段sequence,对于数字或日期类型的...

    Sqlserver 2014 之 自定义字符串聚合函数

    例如,文件“String_agg.cs”很可能包含了创建自定义聚合函数的C#代码。在SQL Server中,我们可以使用CLR(Common Language Runtime)集成来编写UDAs,这允许使用.NET Framework的任何语言(如C#或VB.NET)来创建...

    自定义聚合函数:用户定义的聚合函数——评估SQL Server 2005中的新功能.pdf

    在本文中,作者使用C#展示了如何创建一个名为`StringConcat`的自定义聚合函数,该函数接收字符串并将其合并。这个函数的实现逻辑涉及到在.NET环境中处理数据的细节,包括如何存储中间结果,如何在多次调用之间保持...

    C#扩展SQLServer 2005 字符串聚合函数

    创建自定义聚合函数的步骤如下: 1. **设计C#类**:首先,需要在C#中编写一个类,该类需要继承`System.Data.SqlTypes.SqlAggregate`基类,并实现必要的方法,如`Accumulate()`、`Terminate()`和`GetResult()`。这些...

    SqlServer自定义字符聚合

    标题“SqlSserver自定义字符聚合”所指向的知识点主要是关于如何在SQL Server中创建和使用自定义聚合函数,特别是在处理字符串数据时的特殊需求。自定义字符聚合允许开发者根据业务需求定制特定的聚合逻辑,比如实现...

    聚集函数(自定义分隔符拼接字符串)

    在Oracle数据库中,有时我们需要将多个字符串连接成一个单一的字符串,这在处理报告或聚合数据时非常常见。Oracle提供了一些内置的聚集函数,如`LISTAGG`,但它们可能无法满足所有需求,比如在特定场景下需要自定义...

    Sql Server 字符串聚合函数

    由于SQL Server的标准聚合函数不支持字符串聚合,所以我们需要自定义函数来解决这个问题。以下是一个实现这个功能的步骤: 1. **创建测试表和插入数据**: 首先,我们需要创建一个模拟的`AggregationTable`,并...

    Oracle字段转字符串/多行记录合并/连接/聚合字符串的几种方法

    Oracle中的`concat()`函数是最基本的字符串连接方法。它接受两个或更多个字符串作为参数,并返回它们的连接结果。例如: ```sql SELECT concat(column1, ', ', column2) AS combined_string FROM table_name; `...

    Oracle 多行记录合并_连接_聚合字符串的几种方法_oracle_脚本之家1

    Oracle数据库在处理多行记录合并、连接和聚合字符串时,有多种方法,下面将详细介绍其中的几种常见技术。 1. 被集合字段范围小且固定型 这种方法适用于字段值有限且已知的情况。通过使用`DECODE`函数,我们可以为每...

    详解MySQL中concat函数的用法(连接字符串)

    在MySQL数据库中,`CONCAT`函数用于将两个或更多的字符串连接成一个单一的字符串。这个函数非常实用,尤其是在处理涉及字符串拼接的查询时。`CONCAT`的基本语法如下: ```sql CONCAT(str1, str2, ..., str_n) ``` ...

    解决Oracle没有WM_CONCAT函数.zip

    2. 自定义聚合函数:如果你不能升级到11g或以上,或者需要兼容更早的版本,可以创建自己的PL/SQL聚合函数来模拟WM_CONCAT的行为。这正是压缩包中的PL/SQL源代码可能做的事情。 3. 使用XMLAGG函数:虽然不是为字符串...

    Oracle 多行记录合并/连接/聚合字符串的几种方法

    - **自定义SQL查询**:有时可以通过嵌套查询和`UNION ALL`等操作来实现字符串连接,但这通常会比较复杂且效率较低。 在选择合并多行记录字符串的方法时,应根据实际需求和数据库版本来决定。对于较小的数据集和...

    SQL行合并值

    在SQL查询中,有时我们需要将多行数据合并成一行,这种情况通常发生在我们想要...通过灵活运用GROUP BY、聚合函数、字符串连接以及其他的SQL特性,我们可以根据需求定制出各种合并策略,从而优化数据查询和分析的过程。

    ORA-00904 WMSYS.WM_CONCAT标识符无效解决方案

    2. **自定义函数**:如果不能使用LISTAGG(例如,在Oracle 10g或更低版本中),可以创建自己的自定义聚合函数来替代WM_CONCAT。 3. **调整查询**:在某些情况下,可能可以通过重新构造查询,使用嵌套的SELECT语句或...

    hive数仓、hive SQL 、 hive自定义函数 、hive参数深入浅出

    - 表达式与函数:内置函数包括数学、字符串、日期时间等多种类型,同时支持用户自定义函数(UDF)。 3. Hive自定义函数(UDF): - UDF定义:用户可以编写Java代码实现特定功能的函数,然后在Hive SQL中调用。 -...

    重写oracle wm_contact函数

    这个类型用于定义一个聚合函数,其主要功能是将多个字符串连接成一个单一的字符串,并支持多种操作,如初始化、迭代和终止等。具体来说: 1. **初始化**: `ODCIAGGREGATEINITIALIZE`方法用于初始化`zh_concat_im`...

    大数据学习:Hive函数.pdf

    本文将详细介绍Hive函数的应用,包括聚合函数、数学函数、集合函数、类型转换函数、日期函数、条件函数、字符串函数和表生成函数等。 聚合函数 聚合函数用于对一组值执行计算,通常与SELECT语句的GROUP BY子句一起...

Global site tag (gtag.js) - Google Analytics