Rewrite the oracle wm_contact function script and oraclewm_contact

Rewrite the oracle wm_contact function script and oraclewm_contact

There is a requirement for a recent project to describe consecutive card numbers in one segment, for example, 1, 2, 3, 4, 5, 8, 10, 13, 14, 15, 16, to be displayed as 1-5, 8, 10, 13-16 format

However, the original wm_contact function is separated by commas and does not have this function. I collected some information on the Internet and modified something to meet this requirement. Let's look at the code below:

This code is the main code for rewriting the wm_contact function. The FUN_JOIN_STR (CURR_STR, '-') function in the script is my custom function.

Create or replace TYPE zh_concat_im AUTHID CURRENT_USER as object (CURR_STR clob, static function compute (sctx in out zh_concat_im) return number, member function compute (self in out zh_concat_im, P1 IN VARCHAR2) return number, member function odciaggresponterminate (self in zh_concat_im, returnvalue out VARCHAR2, flags in number) return number, member function compute (self in out zh_concat_im, SCTX2 IN zh_concat_im) return number ); /create or replace type body zh_concat_imIS static function odciaggresponinitialize (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 odciaggresponterminate (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, '-'); -- this operation must be processed again before the function returns. </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;/create or replace FUNCTION zh_concat (P1 VARCHAR2) RETURN VARCHAR2 aggregate using zh_concat_im ;/

The following describes the User-Defined Functions for sorting and splicing.

Create or replace type T_RET_TABLE is table of VARCHAR2 (30); -- define the 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, LENGTH (VAR_TMP )); pipe row (VAR_ELEMENT); end loop; RETURN; END response;/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 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 ;/

Call 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, large, 9700008, 9700003, large, medium, large, 9650,,9700013, 9911021,9700006, large, medium, large, small, medium, large, small, large, small, small, 9780051,9990014, 9990013,9990012, 9990011,9650126, 9700005, 9700004, 9990010,9990009, 9990008

The output data using the zh_contact function is as follows:

9990001, 9990014

 

Code connection: Click the open link

Welcome to comments and Optimization