重写oracle wm_contact函数脚本

最近项目上有个要求,要把连续的卡号使用一个段来描述,比如:1,2,3,4,5,8,10,13,14,15,16 要显示成:1-5,8,10,13-16的形式

但是原有的wm_contact函数是用逗号隔开,并没有该功能,我在网上搜集了点资料,自己再修改了点东西,满足了这个需求,下面看代码:

此代码是重写 wm_contact 函数的主要代码,脚本中 FUN_JOIN_STR(CURR_STR,‘-‘)  函数是我自定义的函数

create or replace TYPE zh_concat_im  
AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR clob,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,  
  P1 IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
  RETURNVALUE OUT VARCHAR2,
  FLAGS IN NUMBER)
  RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,  
  SCTX2 IN zh_concat_im) RETURN NUMBER
);
/
create or replace TYPE BODY zh_concat_im
IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
  RETURN NUMBER  
  IS
  BEGIN
  SCTX := zh_concat_im(NULL) ;
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
  P1 IN VARCHAR2)  
  RETURN NUMBER  
  IS
  BEGIN
  --DBMS_OUTPUT.PUT_LINE(CURR_STR||'|'||P1);
  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 zh_concat_im,
  RETURNVALUE OUT VARCHAR2,
  FLAGS IN NUMBER)
  RETURN NUMBER  
  IS
  BEGIN
  RETURNVALUE :=<span style="color:#ff6666;">FUN_JOIN_STR(CURR_STR,'-');  --此处要在该函数返回之前再进行一次处理</span>
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,  
  SCTX2 IN zh_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;
/
create or replace FUNCTION zh_concat(P1 VARCHAR2)  
RETURN VARCHAR2 AGGREGATE USING zh_concat_im;
/

以下是自定义函数部分,主要完成排序和拼接

CREATE OR REPLACE TYPE T_RET_TABLE IS TABLE OF VARCHAR2(30);--定义类型
/
CREATE OR REPLACE FUNCTION FUN_STRSPLIT_TABLE(V_STR     IN CLOB,
                                              VAR_SPLIT IN VARCHAR2)
  RETURN T_RET_TABLE
  PIPELINED IS
  VAR_TMP     CLOB;
  VAR_ELEMENT VARCHAR2(30);
  N_LENGTH    NUMBER := LENGTH(VAR_SPLIT);
BEGIN
  VAR_TMP := V_STR||',';
  WHILE INSTR(VAR_TMP, VAR_SPLIT) > 0 LOOP
    VAR_ELEMENT := SUBSTR(VAR_TMP, 1, INSTR(VAR_TMP, VAR_SPLIT) - 1);
    VAR_TMP     := SUBSTR(VAR_TMP,
                          INSTR(VAR_TMP, VAR_SPLIT) + N_LENGTH,
                          LENGTH(VAR_TMP));
    PIPE ROW(VAR_ELEMENT);
  END LOOP;
  RETURN;
END FUN_STRSPLIT_TABLE;
/
CREATE OR REPLACE FUNCTION FUN_JOIN_STR(V_SOURCE_STR IN CLOB,
                                        V_JOIN_STR   IN VARCHAR2)
  RETURN CLOB IS
  V_RESULT   CLOB;
  V_STR_ARRY T_RET_TABLE;
  V_MINUS    INTEGER;
  V_COUNT    INTEGER := 0;
  V_RANGE_STR VARCHAR2(50);
BEGIN
  SELECT COLUMN_VALUE BULK COLLECT
    INTO V_STR_ARRY
    FROM TABLE(FUN_STRSPLIT_TABLE(V_SOURCE_STR, ','))
   ORDER BY COLUMN_VALUE;
  FOR N IN 1 .. V_STR_ARRY.COUNT LOOP
    IF N = 1 THEN
      V_RESULT := V_STR_ARRY(N);
      V_RANGE_STR :=V_RESULT;
    END IF;
    IF N > 1 THEN
      V_MINUS := TO_NUMBER(V_STR_ARRY(N)) - TO_NUMBER(V_STR_ARRY(N - 1));
      IF V_MINUS > 1 THEN
        IF V_COUNT > 1 THEN
          V_RESULT := V_RESULT||','||V_RANGE_STR||V_JOIN_STR||V_STR_ARRY(N-1);
          V_COUNT := 0;
        ELSE
          V_RESULT := V_RESULT|| ','|| V_STR_ARRY(N);
        END IF;
        V_RANGE_STR :=V_STR_ARRY(N);
      ELSE
        V_COUNT := V_COUNT + 1;
      END IF;
    END IF;
  END LOOP;
  IF V_COUNT>0 THEN
     V_RESULT := V_RESULT||','||V_RANGE_STR||V_JOIN_STR||V_STR_ARRY(V_STR_ARRY.COUNT);
  END IF;
  RETURN(V_RESULT);
END FUN_JOIN_STR;
/

调用示例:

SELECT zh_concat(A.CARDNO) FROM CM_CARDDATA A WHERE A.SESSIONNUMBER=117 GROUP BY A.CM_BASE_CARDTYPEOID;

使用wm_contact 输出的数据如下:

9007001,9900011,9700009,9700001,9700011,9700002,9700008,9700003,9700012,9990007,9990006,9990005,9990004,9990003,9990002,9990001,9650201,9700013,9911021,9700006,9780040,9780044,9911021,9464603,9464601,9214509,9214510,9780046,9780057,9780056,9780055,9900082,9780054,9780045,9780043,9780042,9700007,9780041,9780053,9780052,9780051,9990014,9990013,9990012,9990011,9650126,9700005,9700004,9990010,9990009,9990008

使用zh_contact 函数输出的数据如下:

9007001,9214509,9464601,9464603,9650126,9650201,9700001,9700001-9700009,9700011-9700013,9780040-9780046,9780051-9780057,9900082,9911021,9990001,9990001-9990014

 

代码连接下载地址:点击打开链接

欢迎各位大师前来点评,并优化

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。