最近项目上有个要求，要把连续的卡号使用一个段来描述，比如：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 :=FUN_JOIN_STR(CURR_STR,'-'); --此处要在该函数返回之前再进行一次处理
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_R