Rewrite Oracle wm_contact function script

There is a requirement on the recent project to describe a sequential card number using a segment, such as: 1,2,3,4,5,8,10,13,14,15,16 to be displayed as: 1-5,8,10,13-16 form

But the original Wm_contact function is separated by commas, and does not have this function, I collected a bit of information on the Internet, I modified something, to meet this demand, the following see the code:

This code is the main code to override the Wm_contact function, and the script fun_join_str (curr_str, '-') function is my custom function

Create or replace TYPE zh_concat_im AUTHID current_user as OBJECT (Curr_str clob, STATIC FUNCTION Odciaggregateinitiali ZE (Sctx in Out Zh_concat_im) RETURN number, MEMBER FUNCTION odciaggregateiterate (self in Out zh_concat_im, P1 in Varch AR2) RETURN number, MEMBER FUNCTION odciaggregateterminate (self in Zh_concat_im, returnvalue out VARCHAR2, FLAGS in NUM BER) 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 N  Umber 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_OUTP Ut. Put_Line (curr_str| | ' | ' | |  P1); IF (Curr_str is isn't 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, and FLAGS in number) RETURN number is BEGIN Retu Rnvalue: =<span style= "color: #ff6666;"  >fun_join_str (Curr_str, '-'); --here to do a processing </span> return Odciconst before the function returns.  SUCCESS;  END; MEMBER FUNCTION Odciaggregatemerge (self in Out zh_concat_im, SCTX2 in Zh_concat_im) RETURN number is BEGIN IF (S CTX2. Curr_str is isn't 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;/

The following is a custom Function section, which is mainly done sorting and stitching

CREATE or replace type t_ret_table is TABLE of VARCHAR2 (30);--Define type/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, LEN    GTH (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 fromTABLE (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;/

Invocation Example:

SELECT Zh_concat (A.cardno) from Cm_carddata A WHERE a.sessionnumber=117 GROUP by a.cm_base_cardtypeoid;

The data output using Wm_contact is as follows:

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

The data output using the Zh_contact function is as follows:

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

Code Connection: Click to open link

Welcome to the master to review and optimize

Rewrite Oracle wm_contact function script