最近项目上有个要求，要把连续的卡号使用一个段来描述，比如：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 NUMBERIS BEGIN SCTX := zh_concat_im(NULL) ; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im, P1 IN VARCHAR2)RETURN NUMBERIS BEGIN –DBMS_OUTPUT.PUT_LINE(CURR_STR||'|'||P1); IF(CURR_STR IS NOT NULL) THENCURR_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 NUMBERIS 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 NUMBERIS BEGIN IF(SCTX2.CURR_STR IS NOT NULL) THENSELF.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_STRIN CLOB,VAR_SPLIT IN VARCHAR2) RETURN T_RET_TABLE PIPELINED IS VAR_TMPCLOB; VAR_ELEMENT VARCHAR2(30); N_LENGTH NUMBER := LENGTH(VAR_SPLIT);BEGIN VAR_TMP := V_STR||','; WHILE INSTR(VAR_TMP, VAR_SPLIT) > 0 LOOPVAR_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 COLLECTINTO V_STR_ARRYFROM TABLE(FUN_STRSPLIT_TABLE(V_SOURCE_STR, ',')) ORDER BY COLUMN_VALUE; FOR N IN 1 .. V_STR_ARRY.COUNT LOOPIF N = 1 THENV_RESULT := V_STR_ARRY(N);V_RANGE_STR :=V_RESULT;END IF;IF N > 1 THENV_MINUS := TO_NUMBER(V_STR_ARRY(N)) – TO_NUMBER(V_STR_ARRY(N – 1));IF V_MINUS > 1 THENIF V_COUNT > 1 THENV_RESULT := V_RESULT||','||V_RANGE_STR||V_JOIN_STR||V_STR_ARRY(N-1);V_COUNT := 0;ELSEV_RESULT := V_RESULT|| ','|| V_STR_ARRY(N);END IF;V_RANGE_STR :=V_STR_ARRY(N);ELSEV_COUNT := V_COUNT + 1;END IF;END IF; END LOOP; IF V_COUNT>0 THENV_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
代码连接下载地址：点击打开链接
欢迎各位大师前来点评，并优化
积极的人在每一次忧患中都看到一个机会，
