重寫oracle wm_contact函數指令碼

標籤：oracle   指令碼   wm   function   

最近項目上有個要求，要把連續的卡號使用一個段來描述，比如：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_imIS  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

 

代碼串連：點擊開啟連結

歡迎各位大師前來點評，並最佳化

重寫oracle wm_contact函數指令碼