ITPub博客

首页 > 数据库 > Oracle > oracle 自定义聚合函数

oracle 自定义聚合函数

原创 Oracle 作者:denglt 时间:2015-06-02 17:31:59 0 删除 编辑
oracle提供了聚合函数的API可以让我们方便的自己定义聚合函数
详细看oracle官方文档:
http://docs.oracle.com/cd/B14117_01/appdev.101/b10800/dciaggref.htm
http://docs.oracle.com/cd/B14117_01/appdev.101/b10800/dciaggfns.htm#g1008306

wmsys.wm_concat(合并行函数)是一个很好的例子:
FUNCTION wm_concat(P1 VARCHAR2) RETURN CLOB AGGREGATE USING WMSYS.WM_CONCAT_IMPL ;


TYPE wm_concat_impl AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR    VARCHAR2(32767),
  CURR_STR_C  CLOB,


  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,


  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER,


  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS NUMBER) RETURN NUMBER,


  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 WM_CONCAT_IMPL) RETURN NUMBER
);


TYPE BODY wm_concat_impl IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL(NULL,NULL) ;
    RETURN ODCICONST.SUCCESS;
  END;


  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR_C IS NULL AND (CURR_STR IS NULL OR LENGTH(CURR_STR)<29950)) THEN
      IF(CURR_STR IS NOT NULL) THEN 
        CURR_STR := CURR_STR || ',' || P1;
      ELSE
        CURR_STR := P1;
      END IF;
    ELSE
      IF (CURR_STR_C IS NULL) THEN
        CURR_STR_C := CURR_STR ;
        CURR_STR := NULL ;
      END IF ;


      CURR_STR_C := CURR_STR_C || ',' || P1;
    END IF ;


    RETURN ODCICONST.SUCCESS;
  END;


  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS NUMBER) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      RETURNVALUE := CURR_STR ;
    ELSE
      RETURNVALUE := CURR_STR_C ;
    END IF ;


    RETURN ODCICONST.SUCCESS;
  END;


  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    IF(SCTX2.CURR_STR IS NOT NULL) THEN 
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
    END IF;


    RETURN ODCICONST.SUCCESS;
  END;
END;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/195110/viewspace-1682558/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-04

  • 博文量
    118
  • 访问量
    702758