ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在11.2.0.3中使用自定义聚合函数EN_CONCAT与EN_CONCAT_DISTINCT替换WMSYS.WM_CONTACT

在11.2.0.3中使用自定义聚合函数EN_CONCAT与EN_CONCAT_DISTINCT替换WMSYS.WM_CONTACT

原创 Linux操作系统 作者:deszjj 时间:2013-07-04 21:48:46 0 删除 编辑
有一个项目使用的是Oracle 11.2.0.3 版本,有一条sql使用了WMSYS.WM_CONTACT,发现执行报错,最终发现原因是由于在10.2.0.5与11.2.0.3WMSYS.版本中出现的bug,现使用EN_CONCAT、EN_CONCAT_DISTINCT,代替WM_CONTACT功能,这两个函数由某位大师编写,但是有一些小问题,经过在实践中改进,调整了EN_CONCAT_DISTINCT函数。同事在使用过程中发现EN_CONCAT这个也有点问题,暂时先不改进,先发上改进后的函数,EN_CONCAT后续改进。
 
代码如下:
 
 

----------------------------------------------------
-- Export file for user SCOTT                 --
-- Created by Administrator on 2013-7-4, 18:48:48 --
----------------------------------------------------
spool en_concat.log
prompt
prompt Creating type EN_CONCAT_DISTINCT
prompt ================================
prompt
CREATE OR REPLACE TYPE EN_CONCAT_DISTINCT AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR VARCHAR2(32767),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT EN_CONCAT_DISTINCT)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE
  (
    SELF IN OUT EN_CONCAT_DISTINCT,
    P1   IN VARCHAR2
  ) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE
  (
    SELF        IN EN_CONCAT_DISTINCT,
    RETURNVALUE OUT VARCHAR2,
    FLAGS       IN NUMBER
  ) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE
  (
    SELF  IN OUT EN_CONCAT_DISTINCT,
    SCTX2 IN EN_CONCAT_DISTINCT
  ) RETURN NUMBER
)
;
/
prompt
prompt Creating type EN_CONCAT_IM
prompt ==========================
prompt
CREATE OR REPLACE TYPE EN_CONCAT_IM AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR VARCHAR2(32767),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT EN_CONCAT_IM)
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE
  (
    SELF IN OUT EN_CONCAT_IM,
    P1   IN VARCHAR2
  ) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE
  (
    SELF        IN EN_CONCAT_IM,
    RETURNVALUE OUT VARCHAR2,
    FLAGS       IN NUMBER
  ) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE
  (
    SELF  IN OUT EN_CONCAT_IM,
    SCTX2 IN EN_CONCAT_IM
  ) RETURN NUMBER
)
/
prompt
prompt Creating function EN_CONCAT
prompt ===========================
prompt
CREATE OR REPLACE FUNCTION EN_CONCAT(P1 VARCHAR2) RETURN VARCHAR2
    AGGREGATE USING EN_CONCAT_IM;
/
prompt
prompt Creating function EN_CONCAT_DISTINCT
prompt ============================
prompt
CREATE OR REPLACE FUNCTION EN_CONCAT_DISTINCT(P1 VARCHAR2) RETURN VARCHAR2
    AGGREGATE USING EN_CONCAT_DISTINCT;
/
prompt
prompt Creating type body EN_CONCAT_DISTINCT
prompt =====================================
prompt
CREATE OR REPLACE TYPE BODY EN_CONCAT_DISTINCT IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT EN_CONCAT_DISTINCT)
    RETURN NUMBER IS
  BEGIN
  -- object initialization
    SCTX := EN_CONCAT_DISTINCT(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  --this function is main function for all logic ,essence is for ..loop
  MEMBER FUNCTION ODCIAGGREGATEITERATE
  (
    SELF IN OUT EN_CONCAT_DISTINCT,
    P1   IN VARCHAR2
  ) RETURN NUMBER IS
  BEGIN 
    IF (CURR_STR IS NOT NULL) AND INSTR(SELF.CURR_STR, P1) = 0 THEN
      SELF.CURR_STR := CURR_STR || ',' || TO_CHAR(P1);   
    END IF;
    IF CURR_STR IS NULL THEN
      SELF.CURR_STR := TO_CHAR(P1);
    END IF;
    --remove  repeat value
    IF (CURR_STR IS NOT NULL) AND INSTR(SELF.CURR_STR, P1) = 1 THEN
      SELF.CURR_STR := SELF.CURR_STR;
    END IF;
 
    RETURN ODCICONST.SUCCESS;
  END;
    MEMBER FUNCTION ODCIAGGREGATEMERGE
  (
    SELF  IN OUT EN_CONCAT_DISTINCT,
    SCTX2 IN EN_CONCAT_DISTINCT
  ) RETURN NUMBER IS
  BEGIN
    IF (SCTX2.CURR_STR IS NOT NULL) AND
       INSTR(SELF.CURR_STR, SCTX2.CURR_STR) = 0 THEN
      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
 
  MEMBER FUNCTION ODCIAGGREGATETERMINATE
  (
    SELF        IN EN_CONCAT_DISTINCT,
    RETURNVALUE OUT VARCHAR2,
    FLAGS       IN NUMBER
  ) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := SELF.CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
END;
/
prompt
prompt Creating type body EN_CONCAT_IM
prompt ===============================
prompt
CREATE OR REPLACE TYPE BODY EN_CONCAT_IM IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT EN_CONCAT_IM)
    RETURN NUMBER IS
  BEGIN
    SCTX := EN_CONCAT_IM(NULL);
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE
  (
    SELF IN OUT EN_CONCAT_IM,
    P1   IN VARCHAR2
  ) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      CURR_STR := CURR_STR || ',' || P1;
    ELSE
      CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATETERMINATE
  (
    SELF        IN EN_CONCAT_IM,
    RETURNVALUE OUT VARCHAR2,
    FLAGS       IN NUMBER
  ) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE
  (
    SELF  IN OUT EN_CONCAT_IM,
    SCTX2 IN EN_CONCAT_IM
  ) 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;
/

spool off
 

注:如果遇到中文有乱码的情况,可使用to_char 例如:select en_concat(to_char(name) from T_USER;

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

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

注册时间:2012-04-07

  • 博文量
    42
  • 访问量
    120097